On 25th of October 2018, Michael Paquier committed patch:
Add pg_promote function
This function is able to promote a standby with this new SQL-callable
function. Execution access can be granted to non-superusers so that
failover tools can observe the principle of least privilege.
Catalog version is bumped.
Author: Laurenz Albe
Continue reading Waiting for PostgreSQL 12 – Add pg_promote function
On 23rd of March 2017, Peter Eisentraut committed patch:
Logical replication support for initial data copy
Add functionality for a new subscription to copy the initial data in the
tables and then sync with the ongoing apply process.
For the copying, add a new internal COPY option to have the COPY source
data provided by a callback function. The initial data copy works on
the subscriber by receiving COPY data from the publisher and then
providing it locally into a COPY that writes to the destination table.
A WAL receiver can now execute full SQL commands. This is used here to
obtain information about tables and publications.
Several new options were added to CREATE and ALTER SUBSCRIPTION to
control whether and when initial table syncing happens.
Change pg_dump option --no-create-subscription-slots to
--no-subscription-connect and use the new CREATE SUBSCRIPTION
... NOCONNECT option for that.
Author: Petr Jelinek
Continue reading Waiting for PostgreSQL 10 – Logical replication support for initial data copy
For a long time hash indexed were not crash safe, and couldn't be used on replication slave, because they skipped WAL. Now, thanks to these two commits, it has changed:
On 14th of March 2017, Robert Haas committed patch:
hash: Add write-ahead logging support.
The warning about hash indexes not being write-ahead logged and their
use being discouraged has been removed. "snapshot too old" is now
supported for tables with hash indexes. Most importantly, barring
bugs, hash indexes will now be crash-safe and usable on standbys.
This commit doesn't yet add WAL consistency checking for hash
indexes, as we now have for other index types; a separate patch has
been submitted to cure that lack.
Amit Kapila, reviewed and slightly modified by me. The larger patch
series of which this is a part has been reviewed and tested by Álvaro
Herrera, Ashutosh Sharma, Mark Kirkwood, Jeff Janes, and Jesper
and then, ~ 13 hours later, Robert committed also:
hash: Support WAL consistency checking.
Kuntal Ghosh, reviewed by Amit Kapila and Ashutosh Sharma, with
a few tweaks by me.
Continue reading Waiting for PostgreSQL 10 – hash indexing vs. WAL
On 20th of January, Peter Eisentraut committed patch:
- Add PUBLICATION catalogs and DDL
- Add SUBSCRIPTION catalog and DDL
- Define logical replication protocol and output plugin
- Add logical replication workers
From: Petr Jelinek
Reviewed-by: Steve Singer
Reviewed-by: Andres Freund
Reviewed-by: Erik Rijkers
Reviewed-by: Peter Eisentraut
Continue reading Waiting for PostgreSQL 10 – Logical replication
There is this idea that from 9.5 we will be able to upgrade pg clusters to newer, major, version, without downtime (well, almost), thanks to magic of logical replication.
Sounds very promising, so I figured – I'll test it. To some extent at least.
Continue reading Major-version upgrading with minimal downtime
On 17th of July, Robert Haas committed patch:
Add new function pg_notification_queue_usage.
This tells you what fraction of NOTIFY's queue is currently filled.
Brendan Jurd, reviewed by Merlin Moncure and Gurjeet Singh. A few
further tweaks by me.
Continue reading Waiting for 9.6 – Add new function pg_notification_queue_usage.
On 23rd of March, Heikki Linnakangas committed patch:
Add pg_rewind, for re-synchronizing a master server after failback.
Earlier versions of this tool were available (and still are) on github.
Thanks to Michael Paquier, Alvaro Herrera, Peter Eisentraut, Amit Kapila,
and Satoshi Nagayasu for review.
Continue reading Waiting for 9.5 – Add pg_rewind, for re-synchronizing a master server after failback.
One of my clients is upgrading some servers. The procedure we have took some time to get to current state, and we found some potential problems, so decided to write more about it.
Continue reading How to pg_upgrade …
I had to deal with this question, or some version of it, quite a few times. So, decided to write a summary on what one could (or should) do, after data is in database, and application is running. Namely – setup some kind of replication and backups.
What to use, how, and why? This is what this post is all about.
Continue reading I have PostgreSQL, loaded some data, and have app using it. Now what?
On 22nd of August, Alvaro Herrera committed patch:
Implement ALTER TABLE .. SET LOGGED / UNLOGGED
This enables changing permanent (logged) tables to unlogged and
(Docs for ALTER TABLE / SET TABLESPACE got shuffled in an order that
hopefully makes more sense than the original.)
Author: Fabrízio de Royes Mello
Reviewed by: Christoph Berg, Andres Freund, Thom Brown
Some tweaking by Álvaro Herrera
Continue reading Waiting for 9.5 – Implement ALTER TABLE .. SET LOGGED / UNLOGGED