This problem happened recently to couple of people on various Pg support channels, so I figured I can write a bit more about it, so that in future I have a place where I can refer people to 🙂
Tag: replication
A tale about (incomplete) upgrade from PostgreSQL 12 to 14
This might not interest many of you, but I recently heard about at least two people that stumbled upon the problems I did, so I figured I can write about problems we discovered, and how we solved them (or not).
When we began our journey, the latest Pg was 14.x, that's why we're upgrading to 14, not 15. But I suspect upgrading to 15 wouldn't change much …
Continue reading A tale about (incomplete) upgrade from PostgreSQL 12 to 14
Waiting for PostgreSQL 15 – Allow publishing the tables of schema.
On 27th of October 2021, Amit Kapila committed patch:
Allow publishing the tables of schema. A new option "FOR ALL TABLES IN SCHEMA" in Create/Alter Publication allows one or more schemas to be specified, whose tables are selected by the publisher for sending the data to the subscriber. The new syntax allows specifying both the tables and schemas. For example: CREATE PUBLICATION pub1 FOR TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2; OR ALTER PUBLICATION pub1 ADD TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2; A new system table "pg_publication_namespace" has been added, to maintain the schemas that the user wants to publish through the publication. Modified the output plugin (pgoutput) to publish the changes if the relation is part of schema publication. Updates pg_dump to identify and dump schema publications. Updates the \d family of commands to display schema publications and \dRp+ variant will now display associated schemas if any. Author: Vignesh C, Hou Zhijie, Amit Kapila Syntax-Suggested-by: Tom Lane, Alvaro Herrera Reviewed-by: Greg Nancarrow, Masahiko Sawada, Hou Zhijie, Amit Kapila, Haiying Tang, Ajin Cherian, Rahila Syed, Bharath Rupireddy, Mark Dilger Tested-by: Haiying Tang Discussion: https://www.postgresql.org/message-id/CALDaNm0OANxuJ6RXqwZsM1MSY4s19nuH3734j4a72etDwvBETQ@mail.gmail.com
Continue reading Waiting for PostgreSQL 15 – Allow publishing the tables of schema.
Waiting for PostgreSQL 13 – Add logical replication support to replicate into partitioned tables
On 6th of April 2020, Peter Eisentraut committed patch:
Add logical replication support to replicate into partitioned tables Mainly, this adds support code in logical/worker.c for applying replicated operations whose target is a partitioned table to its relevant partitions. Author: Amit Langote <amitlangote09@gmail.com> Reviewed-by: Rafia Sabih <rafia.pghackers@gmail.com> Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> Reviewed-by: Petr Jelinek <petr@2ndquadrant.com> Discussion: https://www.postgresql.org/message-id/flat/CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com
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.6 – Add new function pg_notification_queue_usage.
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.