While a hallmark feature of PostgreSQL is streaming replication, sometimes data demands a more refined and adaptable replication approach to address different requirements. Logical replication offers a distinct advantage over traditional streaming replication by providing a more detailed and selective approach to data replication, enabling organizations to replicate specific tables selectively and even filter out particular types of data.
In this blog post, we will look into the steps of transforming an existing PostgreSQL streaming replication node into a logical replication node. This method facilitates a smooth transition to logical replication without the need to start from scratch, allowing organizations to harness the enhanced capabilities of logical replication effectively.
Pre-requisites
To switch from the current streaming replication to logical replication, confirm that the primary node fulfills the following prerequisites:
- Set the wal_level to logical.
- Adjust max_replication_slots and max_wal_senders as needed.
- Ensure all tables have a replica identity set.
Steps to convert streaming replication to logical replication:
The lab setup includes two servers: primary (172.31.46.28) and standby (172.31.39.50), currently configured with streaming replication. The following steps will guide the transition from streaming to logical replication.
1. Verify that the current streaming replication setup is synchronized and that there is no lag between the primary and standby nodes.
1 2 3 4 5 6 7 8 9 10 11 | percona=# select usename,client_addr,state,replay_lag from pg_stat_replication; usename | client_addr | state | replay_lag ---------+--------------+-----------+------------ percona | 172.31.39.50 | streaming | (1 row) percona=# select slot_name,slot_type,active from pg_replication_slots; slot_name | slot_type | active -----------------------+-----------+-------- physical_replica_slot | physical | t (1 row) |
2. Create a logical replication slot on the primary node.
1 2 3 4 5 6 7 8 9 10 11 | percona=# select pg_create_logical_replication_slot('logical_replica_slot', 'pgoutput'); pg_create_logical_replication_slot ------------------------------------ (logical_replica_slot,0/7000180) (1 row) percona=# select * from pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase -----------------------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+----------- physical_replica_slot | | physical | | | f | t | 116306 | | | 0/7000180 | | reserved | | f logical_replica_slot | pgoutput | logical | 16384 | percona | f | f | | | 768 | 0/7000148 | 0/7000180 | reserved | | f (2 rows) |
3. Create publication for tables on the primary node.
1 2 | percona=# CREATE PUBLICATION my_pub FOR ALL TABLES; CREATE PUBLICATION |
4. Next, promote the intended standby node to a standalone node.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | percona=# SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) postgres=# SELECT pg_promote(); pg_promote ------------ t (1 row) postgres=# SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) |
5. Review the PostgreSQL log file on the promoted standby server and take note of the Log Sequence Number (LSN) associated with the “redo done at” message. In the below log file, the LSN is 0/7000640.
1 2 3 4 5 6 7 8 9 10 11 12 | promoted standby postgresql Logfile: 2023-12-04 13:41:11.527 UTC [37749] LOG: received promote request 2023-12-04 13:41:11.527 UTC [37750] FATAL: terminating walreceiver process due to administrator command 2023-12-04 13:41:11.527 UTC [37749] LOG: invalid record length at 0/7000678: wanted 24, got 0 2023-12-04 13:41:11.527 UTC [37749] LOG: redo done at 0/7000640 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 1638.50 s 2023-12-04 13:41:11.527 UTC [37749] LOG: last completed transaction was at log time 2023-12-04 13:40:25.619889+00 2023-12-04 13:41:11.535 UTC [37749] LOG: selected new timeline ID: 2 2023-12-04 13:41:11.622 UTC [37749] LOG: archive recovery complete 2023-12-04 13:41:11.633 UTC [37747] LOG: checkpoint starting: force 2023-12-04 13:41:11.637 UTC [37746] LOG: database system is ready to accept connections 2023-12-04 13:41:11.639 UTC [37883] WARNING: archive_mode enabled, yet archiving is not configured 2023-12-04 13:41:11.955 UTC [37747] LOG: checkpoint complete: wrote 5 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.305 s, sync=0.005 s, total=0.322 s; sync files=5, longest=0.004 s, average=0.001 s; distance=1 kB, estimate=14745 kB |
The log entry “redo done at 0/7000640” provides details about the most recently applied transaction location. We utilize this Log Sequence Number (LSN) to progress the logical replication slot created in step two. Consequently, transactions occurring after this point will be applied to the newly promoted node.
6. On the primary server, advance the logical replication slot created in step two.
1 2 3 4 | percona=# select pg_replication_slot_advance('logical_replica_slot', '0/7000640'); pg_replication_slot_advance ---------------------------------- (logical_replica_slot,0/7000640) |
7. On the standby server, create the subscription for the publication created in step three.
1 | percona=# CREATE SUBSCRIPTION my_sub CONNECTION 'user=percona password=<redacted> host=<host_ip> port=5432' PUBLICATION my_pub WITH (copy_data=false, slot_name='logical_replica_slot', create_slot=false); |
8. At this stage, logical replication will commence, which can be verified by inspecting the replication status on the primary server.
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 | percona=# select slot_name,slot_type,active from pg_replication_slots; slot_name | slot_type | active -----------------------+-----------+-------- physical_replica_slot | physical | f logical_replica_slot | logical | t (2 rows) percona=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 116806 usesysid | 16434 usename | percona application_name | my_sub client_addr | 172.31.39.50 client_hostname | client_port | 52512 backend_start | 2023-12-04 13:52:46.200631+00 backend_xmin | state | streaming sent_lsn | 0/7002148 write_lsn | 0/7002148 flush_lsn | 0/7002148 replay_lsn | 0/7002148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-12-04 14:06:19.464122+00 |
9. It’s important to remove the current physical replication slot to avoid filling up the primary server’s disk.
1 2 3 4 | postgres=# select pg_drop_replication_slot('physical_replica_slot'); pg_drop_replication_slot -------------------------- (1 row) |
The steps covered in this process make the transition to logical replication smoother, allowing for selective replication and improved data management across different parts of your database system. This change provides more control over how you manage and share data within PostgreSQL.
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.
does that support DDL operations ?
No, PostgreSQL Logical replication has this limitation: Database schema and DDL commands are not replicated.