In one of our previous blogs, a custom method for switching from PostgreSQL physical replication to logical replication was discussed, using pg_create_logical_replication_slot and pg_replication_slot_advance. PostgreSQL 17, set to be released this year, introduces the pg_createsubscriber utility, simplifying the conversion from physical to logical replication. This post will cover using pg_createsubscriber to convert physical to logical replication and avoid the initial data copy.
Prerequisites
- Source and target servers must have the same major PostgreSQL version as pg_createsubscriber.
- The target data directory must have the same system identifier as the source.
- The user for the target data directory must have privileges for creating subscriptions and using pg_replication_origin_advance().
Primary server
- Must accept connections from the target server.
- Must not be in recovery.
- wal_level must be set to logical.
- max_replication_slots >= number of specified databases + existing replication slots.
- max_wal_senders >= number of specified databases + existing WAL sender processes.
Physical standby
- The target server must be used as a physical standby.
- max_replication_slots and max_logical_replication_workers >= number of specified databases.
- max_worker_processes > number of specified databases.
- Must accept local connections.
Please refer to some of these blog posts from Percona to learn more about logical replication.
Steps to use the pg_createsubscriber utility to convert a physical replica to a logical replica:
The test servers are running the PostgreSQL 17 beta version, which includes the pg_createsubscriber utility.
- Primary server: 172.31.42.65
- Physical replica: 172.31.42.66
Validate prerequisites: Ensure both the primary server and the physical replica meet all prerequisites.
The primary server meets all required prerequisites.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
postgres@ip-172-31-42-65:~$ psql psql (17beta1) Type "help" for help. postgres=# select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit (1 row) postgres=# SELECT system_identifier FROM pg_control_system(); system_identifier --------------------- 7384116299847179803 (1 row) postgres=# select name , setting from pg_settings where name in ('wal_level','max_replication_slots','max_wal_senders'); name | setting -----------------------+--------- max_replication_slots | 10 max_wal_senders | 10 wal_level | logical (3 rows) |
The physical replica also meets the necessary prerequisites.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
postgres@ip-172-31-42-66:~$ psql psql (17beta1) Type "help" for help. postgres=# select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit (1 row) postgres=# select name , setting from pg_settings where name in ('max_worker_processes','max_replication_slots','max_logical_replication_workers'); name | setting ---------------------------------+--------- max_logical_replication_workers | 4 max_replication_slots | 10 max_worker_processes | 8 (3 rows) postgres=# SELECT system_identifier FROM pg_control_system(); system_identifier --------------------- 7384116299847179803 (1 row) |
Verify setup:
Check if the primary and physical replica setup is working correctly. For help with setting up physical replication, refer to the PostgreSQL streaming replication blog post.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<strong>On primary:</strong> postgres=# select usename,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,replay_lag from pg_stat_replication postgres-# ; -[ RECORD 1 ]--------- usename | postgres state | streaming sent_lsn | 0/4CF44F8 write_lsn | 0/4CF44F8 flush_lsn | 0/4CF44F8 replay_lsn | 0/4CF44F8 replay_lag | <strong>On Physical replica:</strong> postgres=# select status,written_lsn,flushed_lsn,slot_name,conninfo from pg_stat_wal_receiver; -[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- status | streaming written_lsn | 0/4CF44F8 flushed_lsn | 0/4CF44F8 slot_name | physical_solt conninfo | user=postgres passfile=/var/lib/postgresql/.pgpass channel_binding=disable dbname=replication host=172.31.42.66 port=5432 fallback_application_name=walreceiver sslmode=disable sslnegotiation=postgres sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable |
Physical replication is in place
Conversion process:
The primary server has a Percona database with three tables. The goal is to convert the physical replication into logical replication for the Percona database using the pg_createsubscriber utility.
Stop the physical replica and execute the following command on the physical replica to convert it to a logical replica:
|
1 2 3 4 5 6 7 8 9 |
pg_ctl -D /var/lib/postgresql/data2 stop pg_createsubscriber -D /var/lib/postgresql/data2 --publisher-server='user=postgres passfile=/var/lib/postgresql/.pgpass channel_binding=disable dbname=replication host=172.31.42.66 port=5432 fallback_application_name=walreceiver sslmode=disable sslnegotiation=postgres sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable' --database percona --subscriber-username=postgres --replication-slot=percona_db_slot --publication=percona_db_publication --subscription=percona_db_subscription --verbose |
Conversion log:
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
pg_createsubscriber: validating connection string on publisher pg_createsubscriber: validating connection string on subscriber pg_createsubscriber: checking if directory "/var/lib/postgresql/data2" is a cluster data directory pg_createsubscriber: getting system identifier from publisher pg_createsubscriber: system identifier is 7384116299847179803 on publisher pg_createsubscriber: getting system identifier from subscriber pg_createsubscriber: system identifier is 7384116299847179803 on subscriber pg_createsubscriber: starting the standby with command-line options 2024-06-25 05:28:21.066 UTC [98080] LOG: starting PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit 2024-06-25 05:28:21.069 UTC [98080] LOG: listening on Unix socket "/var/lib/postgresql/data/.s.PGSQL.50432" 2024-06-25 05:28:21.079 UTC [98083] LOG: database system was shut down in recovery at 2024-06-25 05:25:34 UTC 2024-06-25 05:28:21.079 UTC [98083] LOG: entering standby mode 2024-06-25 05:28:21.083 UTC [98083] LOG: redo starts at 0/4CF43F0 2024-06-25 05:28:21.083 UTC [98083] LOG: consistent recovery state reached at 0/4CF44F8 2024-06-25 05:28:21.083 UTC [98083] LOG: invalid record length at 0/4CF44F8: expected at least 24, got 0 2024-06-25 05:28:21.084 UTC [98080] LOG: database system is ready to accept read-only connections 2024-06-25 05:28:21.090 UTC [98084] LOG: started streaming WAL from primary at 0/4000000 on timeline 1 pg_createsubscriber: server was started pg_createsubscriber: checking settings on subscriber pg_createsubscriber: checking settings on publisher pg_createsubscriber: primary has replication slot "physical_solt" pg_createsubscriber: stopping the subscriber 2024-06-25 05:28:21.168 UTC [98080] LOG: received fast shutdown request 2024-06-25 05:28:21.171 UTC [98080] LOG: aborting any active transactions 2024-06-25 05:28:21.171 UTC [98084] FATAL: terminating walreceiver process due to administrator command 2024-06-25 05:28:21.173 UTC [98081] LOG: shutting down 2024-06-25 05:28:21.178 UTC [98080] LOG: database system is shut down pg_createsubscriber: server was stopped pg_createsubscriber: creating publication "percona_db_publication" on database "percona" pg_createsubscriber: creating the replication slot "percona_db_slot" on database "percona" pg_createsubscriber: create replication slot "percona_db_slot" on publisher pg_createsubscriber: starting the subscriber 2024-06-25 05:28:21.327 UTC [98096] LOG: starting PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit 2024-06-25 05:28:21.330 UTC [98096] LOG: listening on Unix socket "/var/lib/postgresql/data/.s.PGSQL.50432" 2024-06-25 05:28:21.340 UTC [98099] LOG: database system was shut down in recovery at 2024-06-25 05:28:21 UTC 2024-06-25 05:28:21.340 UTC [98099] LOG: entering standby mode 2024-06-25 05:28:21.344 UTC [98099] LOG: redo starts at 0/4CF43F0 2024-06-25 05:28:21.344 UTC [98099] LOG: consistent recovery state reached at 0/4CF44F8 2024-06-25 05:28:21.344 UTC [98099] LOG: invalid record length at 0/4CF44F8: expected at least 24, got 0 2024-06-25 05:28:21.345 UTC [98096] LOG: database system is ready to accept read-only connections 2024-06-25 05:28:21.354 UTC [98100] LOG: started streaming WAL from primary at 0/4000000 on timeline 1 pg_createsubscriber: server was started pg_createsubscriber: waiting for the target server to reach the consistent state 2024-06-25 05:31:02.672 UTC [98099] LOG: recovery stopping after WAL location (LSN) "0/4CF4878" 2024-06-25 05:31:02.672 UTC [98099] LOG: redo done at 0/4CF4878 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 161.32 s 2024-06-25 05:31:02.672 UTC [98099] LOG: last completed transaction was at log time 2024-06-25 05:28:21.273709+00 2024-06-25 05:31:02.672 UTC [98100] FATAL: terminating walreceiver process due to administrator command 2024-06-25 05:31:02.678 UTC [98099] LOG: selected new timeline ID: 2 2024-06-25 05:31:02.758 UTC [98099] LOG: archive recovery complete 2024-06-25 05:31:02.820 UTC [98097] LOG: checkpoint starting: end-of-recovery immediate wait 2024-06-25 05:31:02.837 UTC [98097] LOG: checkpoint complete: wrote 8 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.004 s, sync=0.005 s, total=0.020 s; sync files=5, longest=0.003 s, average=0.001 s; distance=1 kB, estimate=1 kB; lsn=0/4CF4898, redo lsn=0/4CF4898 2024-06-25 05:31:02.849 UTC [98096] LOG: database system is ready to accept connections pg_createsubscriber: target server reached the consistent state pg_createsubscriber: hint: If pg_createsubscriber fails after this point, you must recreate the physical replica before continuing. pg_createsubscriber: dropping publication "percona_db_publication" on database "percona" pg_createsubscriber: creating subscription "percona_db_subscription" on database "percona" pg_createsubscriber: setting the replication progress (node name "pg_24711" ; LSN 0/4CF4878) on database "percona" pg_createsubscriber: enabling subscription "percona_db_subscription" on database "percona" pg_createsubscriber: dropping the replication slot "physical_solt" on database "percona" 2024-06-25 05:31:03.530 UTC [98277] LOG: logical replication apply worker for subscription "percona_db_subscription" has started pg_createsubscriber: stopping the subscriber 2024-06-25 05:31:03.535 UTC [98096] LOG: received fast shutdown request 2024-06-25 05:31:03.539 UTC [98096] LOG: aborting any active transactions 2024-06-25 05:31:03.539 UTC [98277] FATAL: terminating logical replication worker due to administrator command 2024-06-25 05:31:03.540 UTC [98096] LOG: background worker "logical replication launcher" (PID 98273) exited with exit code 1 2024-06-25 05:31:03.542 UTC [98096] LOG: background worker "logical replication apply worker" (PID 98277) exited with exit code 1 2024-06-25 05:31:03.542 UTC [98097] LOG: shutting down 2024-06-25 05:31:03.544 UTC [98097] LOG: checkpoint starting: shutdown immediate 2024-06-25 05:31:03.561 UTC [98097] LOG: checkpoint complete: wrote 19 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.004 s, sync=0.005 s, total=0.019 s; sync files=13, longest=0.002 s, average=0.001 s; distance=2 kB, estimate=2 kB; lsn=0/4CF5398, redo lsn=0/4CF5398 2024-06-25 05:31:03.563 UTC [98096] LOG: database system is shut down pg_createsubscriber: server was stopped pg_createsubscriber: modifying system identifier of subscriber pg_createsubscriber: system identifier is 7384309198420242198 on subscriber pg_createsubscriber: running pg_resetwal on the subscriber pg_createsubscriber: subscriber successfully changed the system identifier pg_createsubscriber: Done! |
Final steps:
The physical replica is now converted to a logical replica. Start the logical replica and check its status.
|
1 2 3 4 5 6 7 8 9 |
pg_ctl -D /var/lib/postgresql/data2 -l /var/log/postgresql/postgres.log start 2024-06-25 05:32:42.404 UTC [98312] LOG: starting PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit 2024-06-25 05:32:42.404 UTC [98312] LOG: listening on IPv4 address "0.0.0.0", port 5434 2024-06-25 05:32:42.404 UTC [98312] LOG: listening on IPv6 address "::", port 5434 2024-06-25 05:32:42.410 UTC [98312] LOG: listening on Unix socket "/tmp/.s.PGSQL.5434" 2024-06-25 05:32:42.418 UTC [98315] LOG: database system was shut down at 2024-06-25 05:31:03 UTC 2024-06-25 05:32:42.418 UTC [98315] LOG: recovered replication state of node 1 to 0/4CF4878 2024-06-25 05:32:42.426 UTC [98312] LOG: database system is ready to accept connections 2024-06-25 05:32:42.436 UTC [98319] LOG: logical replication apply worker for subscription "percona_db_subscription" has started |
Confirm that the logical slot and publication have been created on the primary server.
|
1 2 3 4 5 6 7 8 9 10 |
percona=# select * from pg_publication; oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot -------+------------------------+----------+--------------+-----------+-----------+-----------+-------------+------------ 16519 | percona_db_publication | 10 | t | t | t | t | t | f (1 row) percona=# select slot_name,slot_type from pg_replication_slots ; slot_name | slot_type -----------------+----------- percona_db_slot | logical |
On the logical replica, verify that the subscription is active.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
percona=# select * from pg_subscription ; oid | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpasswordrequired | subrunasowner | subfailover | subconninfo | subslotname | subsynccommi t | subpublications | suborigin -------+---------+------------+-------------------------+----------+------------+-----------+-----------+------------------+-----------------+---------------------+---------------+-------------+-------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+------------- --+--------------------------+----------- 24711 | 16483 | 0/0 | percona_db_subscription | 10 | t | f | f | d | f | t | f | f | user=po stgres passfile=/var/lib/postgresql/.pgpass channel_binding=disable host=localhost port=5432 fallback_application_name=walreceiver sslmode=disable sslnegotiation=postgres sslcompression=0 sslcertmode=di sable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable dbname=percona | percona_db_slot | off | {percona_db_publication} | any |
Pros and cons of pg_createsubscriber
Pros:
- All tables in the specified databases are included in the logical replication setup.
- This method is beneficial for avoiding the initial data copy for large databases
Cons:
- The major version must be the same on both servers.
- If pg_createsubscriber fails after the target server is promoted, rebuilding the physical replica will be necessary.
Converting a physical replica to a logical replica using the pg_createsubscriber utility in PostgreSQL 17 simplifies the process. Following these steps makes the transition seamless, ensuring the logical replication setup is active and operational.
Our services and software are designed to make running high-performance, highly available PostgreSQL in critical environments practically effortless. They enhance PostgreSQL with security and performance features that are all certified and tested to work seamlessly together.
Why Customers Choose Percona for PostgreSQL
About the Author
Bhargav KamineniBhargav Kamineni is a PostgreSQL DBA and has more than 6+ years of working with PostgreSQL.He holds a degree in Computer Science & Technology and joined Percona in 2021 as a PostgreSQL DBA in Managed Services Team. Before joining Percona, he worked at Amazon.
Subscribe
0 Comments
Oldest
