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.
Primary server
Physical standby
Please refer to some of these blog posts from Percona to learn more about logical replication.
The test servers are running the PostgreSQL 17 beta version, which includes the pg_createsubscriber utility.
The primary server meets all required prerequisites.
|
1 |
postgres@ip-172-31-42-65:~$ psql<br>psql (17beta1)<br>Type "help" for help.<br>postgres=# select version();<br> version<br>-----------------------------------------------------------------------------------------------------<br>PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit<br>(1 row)<br>postgres=# SELECT system_identifier FROM pg_control_system();<br> system_identifier<br>---------------------<br>7384116299847179803<br>(1 row)<br>postgres=# select name , setting from pg_settings where name in ('wal_level','max_replication_slots','max_wal_senders');<br> name | setting<br>-----------------------+---------<br>max_replication_slots | 10<br>max_wal_senders | 10<br>wal_level | logical<br>(3 rows) |
The physical replica also meets the necessary prerequisites.
|
1 |
postgres@ip-172-31-42-66:~$ psql<br>psql (17beta1)<br>Type "help" for help.<br>postgres=# select version();<br> version<br>-----------------------------------------------------------------------------------------------------<br>PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit<br>(1 row)<br>postgres=# select name , setting from pg_settings where name in ('max_worker_processes','max_replication_slots','max_logical_replication_workers');<br> name | setting<br>---------------------------------+---------<br>max_logical_replication_workers | 4<br>max_replication_slots | 10<br>max_worker_processes | 8<br>(3 rows)<br>postgres=# SELECT system_identifier FROM pg_control_system();<br> system_identifier<br>---------------------<br>7384116299847179803<br>(1 row) |
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 |
<strong>On primary:</strong><br>postgres=# select usename,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,replay_lag from pg_stat_replication<br>postgres-# ;<br>-[ RECORD 1 ]---------<br>usename | postgres<br>state | streaming<br>sent_lsn | 0/4CF44F8<br>write_lsn | 0/4CF44F8<br>flush_lsn | 0/4CF44F8<br>replay_lsn | 0/4CF44F8<br>replay_lag |<br><br><strong>On Physical replica:</strong><br>postgres=# select status,written_lsn,flushed_lsn,slot_name,conninfo from pg_stat_wal_receiver;<br>-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>status | streaming<br>written_lsn | 0/4CF44F8<br>flushed_lsn | 0/4CF44F8<br>slot_name | physical_solt<br>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
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 |
pg_ctl -D /var/lib/postgresql/data2 stop<br>pg_createsubscriber -D /var/lib/postgresql/data2 <br>--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' <br>--database percona <br>--subscriber-username=postgres <br>--replication-slot=percona_db_slot <br>--publication=percona_db_publication <br>--subscription=percona_db_subscription <br>--verbose |
Conversion log:
|
1 |
pg_createsubscriber: validating connection string on publisher<br>pg_createsubscriber: validating connection string on subscriber<br>pg_createsubscriber: checking if directory "/var/lib/postgresql/data2" is a cluster data directory<br>pg_createsubscriber: getting system identifier from publisher<br>pg_createsubscriber: system identifier is 7384116299847179803 on publisher<br>pg_createsubscriber: getting system identifier from subscriber<br>pg_createsubscriber: system identifier is 7384116299847179803 on subscriber<br>pg_createsubscriber: starting the standby with command-line options<br>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<br>2024-06-25 05:28:21.069 UTC [98080] LOG: listening on Unix socket "/var/lib/postgresql/data/.s.PGSQL.50432"<br>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<br>2024-06-25 05:28:21.079 UTC [98083] LOG: entering standby mode<br>2024-06-25 05:28:21.083 UTC [98083] LOG: redo starts at 0/4CF43F0<br>2024-06-25 05:28:21.083 UTC [98083] LOG: consistent recovery state reached at 0/4CF44F8<br>2024-06-25 05:28:21.083 UTC [98083] LOG: invalid record length at 0/4CF44F8: expected at least 24, got 0<br>2024-06-25 05:28:21.084 UTC [98080] LOG: database system is ready to accept read-only connections<br>2024-06-25 05:28:21.090 UTC [98084] LOG: started streaming WAL from primary at 0/4000000 on timeline 1<br>pg_createsubscriber: server was started<br>pg_createsubscriber: checking settings on subscriber<br>pg_createsubscriber: checking settings on publisher<br>pg_createsubscriber: primary has replication slot "physical_solt"<br>pg_createsubscriber: stopping the subscriber<br>2024-06-25 05:28:21.168 UTC [98080] LOG: received fast shutdown request<br>2024-06-25 05:28:21.171 UTC [98080] LOG: aborting any active transactions<br>2024-06-25 05:28:21.171 UTC [98084] FATAL: terminating walreceiver process due to administrator command<br>2024-06-25 05:28:21.173 UTC [98081] LOG: shutting down<br>2024-06-25 05:28:21.178 UTC [98080] LOG: database system is shut down<br>pg_createsubscriber: server was stopped<br>pg_createsubscriber: creating publication "percona_db_publication" on database "percona"<br>pg_createsubscriber: creating the replication slot "percona_db_slot" on database "percona"<br>pg_createsubscriber: create replication slot "percona_db_slot" on publisher<br>pg_createsubscriber: starting the subscriber<br>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<br>2024-06-25 05:28:21.330 UTC [98096] LOG: listening on Unix socket "/var/lib/postgresql/data/.s.PGSQL.50432"<br>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<br>2024-06-25 05:28:21.340 UTC [98099] LOG: entering standby mode<br>2024-06-25 05:28:21.344 UTC [98099] LOG: redo starts at 0/4CF43F0<br>2024-06-25 05:28:21.344 UTC [98099] LOG: consistent recovery state reached at 0/4CF44F8<br>2024-06-25 05:28:21.344 UTC [98099] LOG: invalid record length at 0/4CF44F8: expected at least 24, got 0<br>2024-06-25 05:28:21.345 UTC [98096] LOG: database system is ready to accept read-only connections<br>2024-06-25 05:28:21.354 UTC [98100] LOG: started streaming WAL from primary at 0/4000000 on timeline 1<br>pg_createsubscriber: server was started<br>pg_createsubscriber: waiting for the target server to reach the consistent state<br>2024-06-25 05:31:02.672 UTC [98099] LOG: recovery stopping after WAL location (LSN) "0/4CF4878"<br>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<br>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<br>2024-06-25 05:31:02.672 UTC [98100] FATAL: terminating walreceiver process due to administrator command<br>2024-06-25 05:31:02.678 UTC [98099] LOG: selected new timeline ID: 2<br>2024-06-25 05:31:02.758 UTC [98099] LOG: archive recovery complete<br>2024-06-25 05:31:02.820 UTC [98097] LOG: checkpoint starting: end-of-recovery immediate wait<br>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<br>2024-06-25 05:31:02.849 UTC [98096] LOG: database system is ready to accept connections<br>pg_createsubscriber: target server reached the consistent state<br>pg_createsubscriber: hint: If pg_createsubscriber fails after this point, you must recreate the physical replica before continuing.<br>pg_createsubscriber: dropping publication "percona_db_publication" on database "percona"<br>pg_createsubscriber: creating subscription "percona_db_subscription" on database "percona"<br>pg_createsubscriber: setting the replication progress (node name "pg_24711" ; LSN 0/4CF4878) on database "percona"<br>pg_createsubscriber: enabling subscription "percona_db_subscription" on database "percona"<br>pg_createsubscriber: dropping the replication slot "physical_solt" on database "percona"<br>2024-06-25 05:31:03.530 UTC [98277] LOG: logical replication apply worker for subscription "percona_db_subscription" has started<br>pg_createsubscriber: stopping the subscriber<br>2024-06-25 05:31:03.535 UTC [98096] LOG: received fast shutdown request<br>2024-06-25 05:31:03.539 UTC [98096] LOG: aborting any active transactions<br>2024-06-25 05:31:03.539 UTC [98277] FATAL: terminating logical replication worker due to administrator command<br>2024-06-25 05:31:03.540 UTC [98096] LOG: background worker "logical replication launcher" (PID 98273) exited with exit code 1<br>2024-06-25 05:31:03.542 UTC [98096] LOG: background worker "logical replication apply worker" (PID 98277) exited with exit code 1<br>2024-06-25 05:31:03.542 UTC [98097] LOG: shutting down<br>2024-06-25 05:31:03.544 UTC [98097] LOG: checkpoint starting: shutdown immediate<br>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<br>2024-06-25 05:31:03.563 UTC [98096] LOG: database system is shut down<br>pg_createsubscriber: server was stopped<br>pg_createsubscriber: modifying system identifier of subscriber<br>pg_createsubscriber: system identifier is 7384309198420242198 on subscriber<br>pg_createsubscriber: running pg_resetwal on the subscriber<br>pg_createsubscriber: subscriber successfully changed the system identifier<br>pg_createsubscriber: Done! |
The physical replica is now converted to a logical replica. Start the logical replica and check its status.
|
1 |
pg_ctl -D /var/lib/postgresql/data2 -l /var/log/postgresql/postgres.log start<br>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<br>2024-06-25 05:32:42.404 UTC [98312] LOG: listening on IPv4 address "0.0.0.0", port 5434<br>2024-06-25 05:32:42.404 UTC [98312] LOG: listening on IPv6 address "::", port 5434<br>2024-06-25 05:32:42.410 UTC [98312] LOG: listening on Unix socket "/tmp/.s.PGSQL.5434"<br>2024-06-25 05:32:42.418 UTC [98315] LOG: database system was shut down at 2024-06-25 05:31:03 UTC<br>2024-06-25 05:32:42.418 UTC [98315] LOG: recovered replication state of node 1 to 0/4CF4878<br>2024-06-25 05:32:42.426 UTC [98312] LOG: database system is ready to accept connections<br>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 |
percona=# select * from pg_publication;<br> oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot<br>-------+------------------------+----------+--------------+-----------+-----------+-----------+-------------+------------<br>16519 | percona_db_publication | 10 | t | t | t | t | t | f<br>(1 row)<br><br>percona=# select slot_name,slot_type from pg_replication_slots ;<br> slot_name | slot_type<br>-----------------+-----------<br>percona_db_slot | logical |
On the logical replica, verify that the subscription is active.
|
1 |
percona=# select * from pg_subscription<br>;<br> oid | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpasswordrequired | subrunasowner | subfailover |<br> subconninfo<br> | subslotname | subsynccommi<br>t | subpublications | suborigin<br>-------+---------+------------+-------------------------+----------+------------+-----------+-----------+------------------+-----------------+---------------------+---------------+-------------+--------<br>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-------------<br>--+--------------------------+-----------<br>24711 | 16483 | 0/0 | percona_db_subscription | 10 | t | f | f | d | f | t | f | f | user=po<br>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<br>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<br> | {percona_db_publication} | any |
Pros:
Cons:
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.
