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:
- Size of the database
- An approach that we are considering with or without a hard link
- 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.
Prerequisites
- 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.
- Verify the PostgreSQL parameters
- Create the replication user
- Identify tables without Primary or Unique Indexes
- Set replica identity
- Create the PUBLICATION
- Create a REPLICATION SLOT
- Take a physical backup
- Get the current LSN position from the primary
- Configure PITR parameters
- Perform PITR with the LSN
- Create the PostgreSQL-15 cluster
- Perform major version upgrade using pg_upgrade
- ANALYZE upgraded database
- Create the SUBSCRIPTION
- Advance the SUBSCRIPTION
- Enable the SUBSCRIPTION
- Plan for the cutover
1. Verify the PostgreSQL parameters
Ensure the PostgreSQL parameters are set correctly for physical backups and logical replication.
1 2 3 4 5 6 7 8 9 10 11 12 | demodb=# select name,setting from pg_settings where name in ( 'wal_level', 'max_worker_processes', 'max_replication_slots', '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.
1 2 3 4 | demodb=# CREATE USER pgrepuser WITH password 'SECRET' replication; CREATE ROLE demodb=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgrepuser; GRANT |
3. Identify Tables without Primary or Unique indexes
List tables without primary keys or unique indexes.
1 2 3 4 5 6 7 8 9 10 11 | 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.
1 2 | demodb=# ALTER TABLE public.test_log_update REPLICA IDENTITY FULL; ALTER TABLE |
5. Create the PUBLICATION
Create a publication for all tables.
1 2 | demodb=# CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES; CREATE PUBLICATION |
6. Create a REPLICATION SLOT
Create a replication slot to capture changes.
1 2 3 4 5 | demodb=# SELECT pg_create_logical_replication_slot('pglogical_rep01', 'pgoutput'); pg_create_logical_replication_slot ------------------------------------ (pglogical_rep01,0/1776FD0) (1 row) |
PITR steps
7. Take a physical backup
Take a physical backup from the primary database.
1 2 | pg_basebackup -h 127.0.0.1 -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.
1 2 3 4 | port = 5433 data_directory = '/data/PG12_new' hba_file = '/data/PG12_new/pg_hba.conf' external_pid_file = '/var/run/postgresql/PG12_new.pid' |
9. Get the Current LSN
Retrieve the current LSN from the primary database.
1 2 3 4 5 | demodb=# SELECT pg_current_wal_lsn(); pg_current_wal_lsn -------------------- 0/9000060 (1 row) |
10. Configure PITR parameters
Add PITR parameters to postgresql.auto.conf in the new data directory.
1 2 | recovery_target_lsn='0/9000060' recovery_target_action='promote' |
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.
1 2 3 4 5 | /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 "0.0.0.0", 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).
1 | /usr/lib/postgresql/15/bin/initdb -D /data/PG15 |
13. Copy configuration files
Copy configuration files from the PG12 cluster to the PG15 cluster.
1 | 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.
1 2 3 4 | port = 5434 data_directory = '/data/PG15' hba_file = '/data/PG15/pg_hba.conf' external_pid_file = '/var/run/postgresql/PG15.pid' |
15. Stop the PG12 PITR cluster
Halt the PostgreSQL v12.16 PITR cluster to proceed with pg_upgrade.
1 2 3 | /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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | /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: ./delete_old_cluster.sh |
17. Start the new PG15 cluster
1 | /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.
1 | /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.
1 2 3 4 5 6 7 8 9 10 11 | demodb=# CREATE SUBSCRIPTION pglogical_sub01 CONNECTION 'host=127.0.0.1 port=5432 dbname=demodb user=pgrepuser password=SECRET' PUBLICATION pglogical_rep01 WITH ( COPY_DATA = false, create_slot = false, enabled = false, connect = true, slot_name = 'pglogical_rep01' ); CREATE SUBSCRIPTION |
20. Advance the subscription
20.1 Get the subscription ID
1 2 3 4 5 | emodb=# SELECT 'pg_'||oid::text AS "external_id" FROM pg_subscription WHERE subname = 'pglogical_sub01'; external_id ------------- pg_16413 (1 row) |
20.2 Advance the replication origin using the pre-fetched LSN
1 2 3 4 5 | demodb=# SELECT pg_replication_origin_advance('pg_16413', '0/9000060'); pg_replication_origin_advance ------------------------------- (1 row) |
20.3 Enable the subscription
Enable the subscription to start receiving replicated data.
1 2 | demodb=# ALTER SUBSCRIPTION pglogical_sub01 ENABLE; ALTER SUBSCRIPTION |
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.
Cutover
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | demodb=# DO $$ DECLARE v_sql text DEFAULT ''; BEGIN FOR v_sql in ( SELECT 'SELECT SETVAL(' || 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 ) LOOP --raise notice '%',v_sql; EXECUTE v_sql; END LOOP; END $$; |
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.
Conclusion
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!
