The Magic of PITR, pg_upgrade, and Logical Replication When Used Together for PostgreSQL Version Upgrades

The Magic of PITR, pg_upgrade, and Logical Replication When Used Together for PostgreSQL Version Upgrades

PostgreSQL Version Upgrades

Recently, I was reading a brilliant blog by Perconian David Gonzalez titled The 1-2-3 for PostgreSQL Logical Replication Using an RDS Snapshot, exploring the intricacies of setting up logical replication on Amazon RDS using RDS snapshots. It was a fascinating read, shedding light on leveraging AWS snapshots’ capabilities to avoid initial data copy within the RDS environment.

Inspired by David’s insights, I embarked on a journey to explore logical replication from a different perspective – within the realm of on-premises server databases. 

We have several PostgreSQL versions that support logical decoding to replicate data changes from a source database to a target database, which is a cool and very powerful tool that gives the option to replicate all the tables in a database, only one schema, a specific set of tables, or even only some columns/rows. Also, it is a helpful method for version upgrades since the target database can run on a different (minor or major) PostgreSQL version.

In this post, we describe the simplest and most basic steps we used to set up the logical replication solution, avoiding the initial copy of data from the source database to the target and creating the target instance using the PITR/Standby database instance and pg_upgrade. Certainly, you can take advantage of this when you work with a large data set, and the initial copy could lead to long timeframes or network saturation.

The scenario

Service considerations

In this exercise, we wanted to perform a major version upgrade from PostgreSQL v12.16 to PostgreSQL v15.4. We could perform a direct upgrade using the build-in option pg_upgrade, but that requires a downtime window that can vary depending on the:

  1. Size of the database
  2. An approach that we are considering with or without a hard link
  3. ANALYZE time after the pg_upgrade

Therefore, by considering David’s blog, we chose logical replication as the mechanism to achieve the objective. We can aim for a quicker switch-over if we create the new instance in the desired version and just replicate all the data changes. Then, we need a small downtime window just to move the traffic from the original instance to the upgraded one.


  • A DB user with privileges to create the PUBLICATION on source and the SUBSCRIPTION on target. 
  • Enough disk space on the same server to fit one more database of the same size; the target database can also be hosted on another server to avoid the write load.

While we’ve contemplated taking a physical backup and conducting PITR, it’s worth noting that PITR can also be executed on the existing replica or delayed replica to expedite the process.

The 1-2-3 Steps

Per the title of this post, the next is the list of steps to perform the PITR, then pg_upgrade to v15.4, and then set up a PostgreSQL logical replication between a PostgreSQL v12.16 and a v15.4.

  1. Verify the PostgreSQL parameters
  2. Create the replication user
  3. Identify tables without Primary or Unique Indexes
  4. Set replica identity
  5. Create the PUBLICATION
  7. Take a physical backup
  8. Get the current LSN position from the primary
  9. Configure PITR parameters
  10. Perform PITR with the LSN
  11. Create the PostgreSQL-15 cluster
  12. Perform major version upgrade using pg_upgrade
  13. ANALYZE upgraded database
  14. Create the SUBSCRIPTION
  15. Advance the SUBSCRIPTION 
  16. Enable the SUBSCRIPTION
  17. Plan for the cutover

1. Verify the PostgreSQL parameters

Ensure the PostgreSQL parameters are set correctly for physical backups and logical replication.

demodb=# select name,setting from pg_settings where name in (
        'max_wal_senders') ;
         name          | setting
 max_replication_slots | 10
 max_wal_senders       | 10
 max_worker_processes  | 8
 wal_level             | replica
(4 rows)

2. Create the Replication User

Create a replication user and grant the necessary privileges.

demodb=# CREATE USER pgrepuser WITH password 'SECRET' replication;
demodb=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgrepuser;

3. Identify Tables without Primary or Unique indexes

List tables without primary keys or unique indexes.

demodb=# SELECT schemaname, relname AS table_name
FROM pg_stat_user_tables
WHERE pg_stat_user_tables.relid NOT IN (
    SELECT indexrelid
    FROM pg_index
    WHERE indisprimary OR indisunique
 schemaname |   table_name
 public     | test_log_update
(1 row)

4. Set replica identity

Set the replica identity to “FULL” for tables without primary keys or unique indexes.

demodb=# ALTER TABLE public.test_log_update REPLICA IDENTITY FULL;

5. Create the PUBLICATION

Create a publication for all tables.

demodb=# CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES;


Create a replication slot to capture changes.

demodb=# SELECT pg_create_logical_replication_slot('pglogical_rep01', 'pgoutput');
(1 row)

PITR steps
7. Take a physical backup

Take a physical backup from the primary database.

pg_basebackup -h -p 5432 -S slot_logical_upgrade -R -c fast -Xs -P -D /data/PG12_new -C
32610/32610 kB (100%), 1/1 tablespace

8. Modify PostgreSQL configuration

Adjust parameters in the postgresql.conf of the new data directory.

port = 5433
data_directory = '/data/PG12_new'
hba_file = '/data/PG12_new/pg_hba.conf'
external_pid_file = '/var/run/postgresql/'

9. Get the Current LSN

Retrieve the current LSN from the primary database.

demodb=# SELECT pg_current_wal_lsn();
(1 row)

10. Configure PITR parameters

Add PITR parameters to in the new data directory.


11. Start the new instance to perform the PITR with LSN

Start the new PostgreSQL instance. This will perform the PITR with LSN and promote the database after the recovery.

/usr/lib/postgresql/12/bin/pg_ctl -D /data/PG12_new start
2023-11-08 05:31:06.030 UTC [108718] LOG:  listening on IPv4 address "", port 5433
server started

Here, we have the database instance ready with v12.16 recovered till the LSN mentioned.

Perform the pg_upgrade

12. Create the PostgreSQL 15 cluster

Initialize the PostgreSQL 15 cluster (this will be our target database).

/usr/lib/postgresql/15/bin/initdb -D /data/PG15

13. Copy configuration files

Copy configuration files from the PG12 cluster to the PG15 cluster.

cp /data/PG12_new/pg_hba.conf /data/PG12_new/pg_ident.conf /data/PG12_new/postgresql.conf /data/PG15/

14. Modify PG15 configuration

Adjust parameters in the postgresql.conf of the PG15 cluster.

port = 5434
data_directory = '/data/PG15'
hba_file = '/data/PG15/pg_hba.conf'
external_pid_file = '/var/run/postgresql/'

15. Stop the PG12 PITR cluster

Halt the PostgreSQL v12.16 PITR cluster to proceed with pg_upgrade.

/usr/lib/postgresql/12/bin/pg_ctl -D /data/PG12_new stop
waiting for server to shut down.... done
server stopped

16. Perform pg_upgrade

Execute the pg_upgrade process.

/usr/lib/postgresql/15/bin/pg_upgrade -d /data/PG12_new -D /data/PG15 -p 5433 -P 5434 -b /usr/lib/postgresql/12/bin -B /usr/lib/postgresql/15/bin --link
 /usr/lib/postgresql/15/bin/pg_upgrade -d /data/PG12_new -D /data/PG15 -p 5433 -P 5434 -b /usr/lib/postgresql/12/bin -B /usr/lib/postgresql/15/bin --link
Performing Consistency Checks
Checking cluster versions                                   ok
Performing Upgrade
Analyzing all rows in the new cluster                       ok
Upgrade Complete
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:

17. Start the new PG15 cluster

/usr/lib/postgresql/15/bin/pg_ctl -D /data/PG15 start

18. Perform ANALYZE process

Start the ANALYZE process as part of the post-upgrade activity and proceed with the next steps in parallel.

/usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

Target Database Side

19. Create subscription

Set up a subscription to replicate data from the source database to the target.

demodb=# CREATE SUBSCRIPTION pglogical_sub01
CONNECTION 'host= port=5432 dbname=demodb user=pgrepuser password=SECRET'
PUBLICATION pglogical_rep01
    COPY_DATA = false,
    create_slot = false,
    enabled = false,
    connect = true,
    slot_name = 'pglogical_rep01'

20. Advance the subscription
20.1 Get the subscription ID

emodb=# SELECT 'pg_'||oid::text AS "external_id" FROM pg_subscription WHERE subname = 'pglogical_sub01';
(1 row)

20.2 Advance the replication origin using the pre-fetched LSN

demodb=# SELECT pg_replication_origin_advance('pg_16413', '0/9000060');

(1 row)

20.3 Enable the subscription

Enable the subscription to start receiving replicated data.

demodb=# ALTER SUBSCRIPTION pglogical_sub01 ENABLE;

Once we are done with all the steps, the data changes should flow from the source database to the target; we can check the status at the pg_stat_replication view on the source side.

21. Reset the sequences

Before opening the database for the applications, it’s crucial to handle sequences by resetting them with the maximum values from their corresponding columns. We have used the below query to reset the sequences:

demodb=# DO
v_sql text DEFAULT '';
FOR v_sql in (
       quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' ||
       quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend
    INNER JOIN pg_class AS class_sequence
        ON class_sequence.oid = pg_depend.objid
            AND class_sequence.relkind = 'S'
    INNER JOIN pg_class AS class_table
        ON class_table.oid = pg_depend.refobjid
    INNER JOIN pg_attribute
        ON pg_attribute.attrelid = class_table.oid
            AND pg_depend.refobjsubid = pg_attribute.attnum
    INNER JOIN pg_namespace as table_namespace
        ON table_namespace.oid = class_table.relnamespace
    INNER JOIN pg_namespace AS sequence_namespace
        ON sequence_namespace.oid = class_sequence.relnamespace
ORDER BY sequence_namespace.nspname, class_sequence.relname
--raise notice '%',v_sql;
EXECUTE v_sql;

22. Route the traffic to the upgraded instance

When the time is right, perform the cutover and redirect all traffic to the PostgreSQL-15 instance, ensuring a smooth transition to the upgraded version.


Logical replication combined with Point-In-Time Recovery (PITR) in PostgreSQL offers a powerful strategy for version upgrades without significant downtime. Inspired by insights from RDS and David’s blog, we explored how this approach applies to on-premises databases. This approach will empower database managers to evolve their systems with minimal interruption, leveraging the best of PITR and logical replication for smoother version upgrades.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.


Download Percona Distribution for PostgreSQL Today!

The post The Magic of PITR, pg_upgrade, and Logical Replication When Used Together for PostgreSQL Version Upgrades appeared first on