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 | logical (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 indrelid 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!
About the Author
Sagar JadhavSagar Jadhav is a PostgreSQL DBA with more than 8 years of working experience with PostgreSQL. He holds a degree in Computer Science & Engineering and joined Percona in 2022 as a PostgreSQL DBA in Managed Services Team. Before joining Percona, he worked at Amazon.
Below query is not giving results as described in article on my test setup “ 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
);” It is listing all tables where as I know there are most tables with primary key / unique key.
Any reason please
Hi Paras, Thank you for your comment.
The query contained a typo, which I have corrected in the blog. Please recheck the query and let me know if it does not work as expected.
Thanks again.
I had used query you corrected but I see if table has unique composite key , It won’t allow table deletion/updation
ERROR: cannot delete from table “principal_context_principal” because it does not have a replica identity and publishes deletes
So you may need to correct query else like me will consider tables are good for logical replication based on above query o/p which is not true for all tables. We need to set replica identity for such tables having unique key.
Hi there!
In Step 1 when you check and verify the PostgreSQL parameters, shouldn’t the DB already be set in a logical replication mode (wal_level=logical)? According to the listing it had been put in a replica mode initially.
Hi Muhammet, Thank you for commenting.
I have updated the value. Please check now.
Hi Sagar , this is really good articale. I wonder can we use this process for bigger db like in Tbs for upgradation . I see we need to advance subscription to sync data post pg_upgrade but if pg_upgrade takes more than 6 hours for TB data ,will we able to sync data with advancing subscription with that LSN and all.
Hello Pooja,
Thank you for your kind words and for finding the article useful!!!
Yes, you can use this process for larger databases, even those in the TB range. However, a few key considerations and steps are crucial to ensure success:
Logical replication will work seamlessly for syncing changes made during the downtime if the following conditions are met:
1. The replication slot and publication remain intact after the upgrade.
2. WAL files generated during these 6 hours of pg_upgrade process are retained on the primary server. To ensure this, set an appropriate value for max_slot_wal_keep_size (preferably -1, which disables the limit).
3. The subscription must use the correct LSN to advance replication after the upgrade.
You should try using the –link option for pg_upgrade; using the –link option can significantly reduce the time needed for pg_upgrade since it avoids copying data files, making the upgrade process faster.
Feel free to reach out if you need any additional information or assistance. I’d be happy to help!