Waiting for PostgreSQL 12 – Add pg_promote function

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
 
Discussion: https://postgr.es/m/.camel@cybertec.at

Continue reading Waiting for PostgreSQL 12 – Add pg_promote function

Waiting for PostgreSQL 10 – Logical replication support for initial data copy

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

Waiting for PostgreSQL 10 – hash indexing vs. WAL

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
Pedersen.
 
Discussion: http://postgr.es/m/CAA4eK1JOBX=YU33631Qh-XivYXtPSALh514+jR8XeD7v+K3r_Q@mail.gmail.com

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.
 
Discussion: http://postgr.es/m/CAGz5QCJLERUn_zoO0eDv6_Y_d0o4tNTMPeR7ivTLBg4rUrJdwg@mail.gmail.com

Continue reading Waiting for PostgreSQL 10 – hash indexing vs. WAL

Waiting for PostgreSQL 10 – Logical replication

On 20th of January, Peter Eisentraut committed patch:

Logical replication
 
- 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

Major-version upgrading with minimal downtime

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

Waiting for 9.5 – Add pg_rewind, for re-synchronizing a master server after failback.

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.

I have PostgreSQL, loaded some data, and have app using it. Now what?

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?

Waiting for 9.5 – Implement ALTER TABLE .. SET LOGGED / UNLOGGED

On 22nd of August, Alvaro Herrera committed patch:

Implement ALTER TABLE .. SET LOGGED / UNLOGGED
 
This enables changing permanent (logged) tables to unlogged and
vice-versa.
 
(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