{"id":32067,"date":"2024-03-25T15:41:18","date_gmt":"2024-03-25T14:41:18","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=32067"},"modified":"2024-03-25T15:41:22","modified_gmt":"2024-03-25T14:41:22","slug":"postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/","title":{"rendered":"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber"},"content":{"rendered":"\n

Logical replication in PostgreSQL came with Version 10<\/a>, which is already out of support. I’ve written about this a long time ago<\/a> but the basic building blocks are still the same. Setting this up requires a publication on the source and a subscription on the target and a couple of parameters to bet set on both sides. Starting with PostgreSQL 16 you can setup a logical replication from a physical replica, which gives you more flexibility on how you want to distribute the load. You might want to follow this set of slides<\/a> to get a general impression on how logical replication evolved over the years.<\/p>\n\n\n\n

Starting with PostgreSQL 17 there’s the next evolution: Creating a logical replica out of a physical replica, or in other words: Converting a physical replica into a logical replica. Before we dive into that, let’s quickly look at what this tools solves.<\/p>\n\n\n\n

When you setup a logical replication between a source and a target all the data needs to be initially copied from the source to the target. Depending on how large the tables in your setup are, this might quite take some time and the longer this process takes, the more WAL needs to be retained on the source for the replica to catch up once the initial data copy is done. With pg_createsubscriber you don’t need the initial data copy anymore, as this was already done when the physical replica was setup. You can take the physical replica as the starting point and transform it into a logical replica. The downside of this is, that the source and the target need to be on the same major version of PostgreSQL (which is obvious as physical replication cannot be done across major versions of PostgreSQL). <\/p>\n\n\n\n

To see how this works lets start from scratch and create a brand new PostgreSQL 17 devel instance and prepare it for physical and logical replication:<\/p>\n\n\n

\npostgres@pgbox:\/home\/postgres\/ [pgdev] initdb --version\ninitdb (PostgreSQL) 17devel\npostgres@pgbox:\/home\/postgres\/ [pgdev] initdb -D \/var\/tmp\/source\npostgres@pgbox:\/home\/postgres\/ [pgdev] echo "wal_level=logical" >> \/var\/tmp\/source\/postgresql.auto.conf\npostgres@pgbox:\/home\/postgres\/ [pgdev] echo "max_replication_slots=10" >> \/var\/tmp\/source\/postgresql.auto.conf\npostgres@pgbox:\/home\/postgres\/ [pgdev] echo "hot_standby=on" >> \/var\/tmp\/source\/postgresql.auto.conf\npostgres@pgbox:\/home\/postgres\/ [pgdev] echo "port=8888"  >> \/var\/tmp\/source\/postgresql.auto.conf\npostgres@pgbox:\/home\/postgres\/ [pgdev] pg_ctl -D \/var\/tmp\/source start\n<\/pre><\/div>\n\n\n
Once we have that, we need a physical replica which is following this primary:<\/p>\n\n\n
\npostgres@pgbox:\/home\/postgres\/ [pgdev] pg_basebackup -D \/var\/tmp\/target --write-recovery-conf -p 8888\npostgres@pgbox:\/home\/postgres\/ [pgdev] tail -1 \/var\/tmp\/target\/postgresql.auto.conf\nport=8889\nprimary_conninfo = 'user=postgres passfile=''\/home\/postgres\/.pgpass'' channel_binding=prefer port=8888 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'\npostgres@pgbox:\/home\/postgres\/ [pgdev] pg_ctl -D \/var\/tmp\/target\/ start\nwaiting for server to start....2024-03-25 14:15:02.864 CET [23697] LOG:  starting PostgreSQL 17devel on x86_64-linux, compiled by gcc-7.5.0, 64-bit\n2024-03-25 14:15:02.864 CET [23697] LOG:  listening on IPv6 address "::1", port 8889\n2024-03-25 14:15:02.864 CET [23697] LOG:  listening on IPv4 address "127.0.0.1", port 8889\n2024-03-25 14:15:02.870 CET [23697] LOG:  listening on Unix socket "\/tmp\/.s.PGSQL.8889"\n2024-03-25 14:15:02.876 CET [23700] LOG:  database system was interrupted; last known up at 2024-03-25 14:14:08 CET\n2024-03-25 14:15:02.898 CET [23700] LOG:  starting backup recovery with redo LSN 0\/2000028, checkpoint LSN 0\/2000080, on timeline ID 1\n2024-03-25 14:15:02.898 CET [23700] LOG:  entering standby mode\n2024-03-25 14:15:02.909 CET [23700] LOG:  redo starts at 0\/2000028\n2024-03-25 14:15:02.912 CET [23700] LOG:  completed backup recovery with redo LSN 0\/2000028 and end LSN 0\/2000120\n2024-03-25 14:15:02.912 CET [23700] LOG:  consistent recovery state reached at 0\/2000120\n2024-03-25 14:15:02.912 CET [23697] LOG:  database system is ready to accept read-only connections\n2024-03-25 14:15:02.916 CET [23701] LOG:  started streaming WAL from primary at 0\/3000000 on timeline 1\n done\nserver started\n<\/pre><\/div>\n\n\n
To confirm that logical replication is actually working later on, lets populate the primary with pgbench<\/a>:<\/p>\n\n\n
\npostgres@pgbox:\/home\/postgres\/ [pgdev] pgbench -i -s 10 -p 8888\ndropping old tables...\nNOTICE:  table "pgbench_accounts" does not exist, skipping\nNOTICE:  table "pgbench_branches" does not exist, skipping\nNOTICE:  table "pgbench_history" does not exist, skipping\nNOTICE:  table "pgbench_tellers" does not exist, skipping\ncreating tables...\ngenerating data (client-side)...\nvacuuming...                                                                                \ncreating primary keys...\ndone in 1.41 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 1.03 s, vacuum 0.08 s, primary keys 0.28 s).\n<\/pre><\/div>\n\n\n
… and check if the physical replica got the data:<\/p>\n\n\n
\npostgres@pgbox:\/var\/tmp\/target\/ [pgdev] psql -c "select count(*) from pgbench_accounts" -p 8889\n  count  \n---------\n 1000000\n(1 row)\n<\/pre><\/div>\n\n\n
No it is time to convert the physical replica into a logical replica using pg_createsubscriber. The first step is to stop the replica:<\/p>\n\n\n
\npostgres@pgbox:\/home\/postgres\/ [pgdev] pg_ctl -D \/var\/tmp\/target\/ stop\n<\/pre><\/div>\n\n\n
In the next step you could either do a dryrun with pg_createsubscriber or directly go for it without a dryrun. Here is a dryrun:<\/p>\n\n\n
\npostgres@pgbox:\/home\/postgres\/ [pgdev] pg_createsubscriber --database=postgres \\\n                                             --pgdata=\/var\/tmp\/target \\\n                                             --dry-run \\\n                                             --subscriber-port=8889 \\\n                                             --publisher-server='user=postgres passfile=''\/home\/postgres\/.pgpass'' channel_binding=prefer port=8888 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable' \\\n                                             --subscriber-username=postgres \\\n                                             --publication=pub1 \\\n                                             --subscription=sub1\n2024-03-25 15:12:58.022 CET [24607] LOG:  starting PostgreSQL 17devel on x86_64-linux, compiled by gcc-7.5.0, 64-bit\n2024-03-25 15:12:58.026 CET [24607] LOG:  listening on Unix socket "\/home\/postgres\/.s.PGSQL.8889"\n2024-03-25 15:12:58.034 CET [24610] LOG:  database system was shut down in recovery at 2024-03-25 15:11:31 CET\n2024-03-25 15:12:58.034 CET [24610] LOG:  entering standby mode\n2024-03-25 15:12:58.038 CET [24610] LOG:  redo starts at 0\/15AF3B08\n2024-03-25 15:12:58.038 CET [24610] LOG:  consistent recovery state reached at 0\/15AF3C48\n2024-03-25 15:12:58.038 CET [24607] LOG:  database system is ready to accept read-only connections\n2024-03-25 15:12:58.038 CET [24610] LOG:  invalid record length at 0\/15AF3C48: expected at least 24, got 0\n2024-03-25 15:12:58.043 CET [24611] LOG:  started streaming WAL from primary at 0\/15000000 on timeline 1\n2024-03-25 15:12:58.096 CET [24607] LOG:  received fast shutdown request\n2024-03-25 15:12:58.100 CET [24607] LOG:  aborting any active transactions\n2024-03-25 15:12:58.100 CET [24611] FATAL:  terminating walreceiver process due to administrator command\n2024-03-25 15:12:58.100 CET [24608] LOG:  shutting down\n2024-03-25 15:12:58.105 CET [24607] LOG:  database system is shut down\n2024-03-25 15:12:58.219 CET [24620] LOG:  starting PostgreSQL 17devel on x86_64-linux, compiled by gcc-7.5.0, 64-bit\n2024-03-25 15:12:58.221 CET [24620] LOG:  listening on Unix socket "\/home\/postgres\/.s.PGSQL.8889"\n2024-03-25 15:12:58.230 CET [24623] LOG:  database system was shut down in recovery at 2024-03-25 15:12:58 CET\n2024-03-25 15:12:58.230 CET [24623] LOG:  entering standby mode\n2024-03-25 15:12:58.233 CET [24623] LOG:  redo starts at 0\/15AF3B08\n2024-03-25 15:12:58.233 CET [24623] LOG:  consistent recovery state reached at 0\/15AF3C48\n2024-03-25 15:12:58.233 CET [24620] LOG:  database system is ready to accept read-only connections\n2024-03-25 15:12:58.233 CET [24623] LOG:  invalid record length at 0\/15AF3C48: expected at least 24, got 0\n2024-03-25 15:12:58.237 CET [24624] LOG:  started streaming WAL from primary at 0\/15000000 on timeline 1\n2024-03-25 15:12:58.311 CET [24620] LOG:  received fast shutdown request\n2024-03-25 15:12:58.315 CET [24620] LOG:  aborting any active transactions\n2024-03-25 15:12:58.315 CET [24624] FATAL:  terminating walreceiver process due to administrator command\n2024-03-25 15:12:58.316 CET [24621] LOG:  shutting down\n2024-03-25 15:12:58.321 CET [24620] LOG:  database system is shut down\n15:12:58 postgres@pgbox:\/home\/postgres\/ [pgdev] echo $?\n0\n<\/pre><\/div>\n\n\n
Don’t count too much on the two “FATAL” messages when the walreiver was shutdown. Important is the exit code, and 0 means success.<\/p>\n\n\n\n
Doing the same without the “dryrun” option:<\/p>\n\n\n
\npostgres@pgbox:\/home\/postgres\/ [pgdev] pg_createsubscriber --database=postgres --pgdata=\/var\/tmp\/target --subscriber-port=8889 --publisher-server='user=postgres passfile=''\/home\/postgres\/.pgpass'' channel_binding=prefer port=8888 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable' --subscriber-username=postgres --publication=pub1 --subscription=sub1\n2024-03-25 15:20:25.575 CET [24669] LOG:  starting PostgreSQL 17devel on x86_64-linux, compiled by gcc-7.5.0, 64-bit\n2024-03-25 15:20:25.577 CET [24669] LOG:  listening on Unix socket "\/home\/postgres\/.s.PGSQL.8889"\n2024-03-25 15:20:25.591 CET [24672] LOG:  database system was shut down in recovery at 2024-03-25 15:12:58 CET\n2024-03-25 15:20:25.591 CET [24672] LOG:  entering standby mode\n2024-03-25 15:20:25.595 CET [24672] LOG:  redo starts at 0\/15AF3B08\n2024-03-25 15:20:25.595 CET [24672] LOG:  consistent recovery state reached at 0\/15AF3C48\n2024-03-25 15:20:25.595 CET [24669] LOG:  database system is ready to accept read-only connections\n2024-03-25 15:20:25.595 CET [24672] LOG:  invalid record length at 0\/15AF3C48: expected at least 24, got 0\n2024-03-25 15:20:25.600 CET [24673] LOG:  started streaming WAL from primary at 0\/15000000 on timeline 1\n2024-03-25 15:20:25.670 CET [24669] LOG:  received fast shutdown request\n2024-03-25 15:20:25.674 CET [24669] LOG:  aborting any active transactions\n2024-03-25 15:20:25.675 CET [24673] FATAL:  terminating walreceiver process due to administrator command\n2024-03-25 15:20:25.675 CET [24670] LOG:  shutting down\n2024-03-25 15:20:25.680 CET [24669] LOG:  database system is shut down\n2024-03-25 15:20:25.807 CET [24678] LOG:  logical decoding found consistent point at 0\/15AF3F28\n2024-03-25 15:20:25.807 CET [24678] DETAIL:  There are no running transactions.\n2024-03-25 15:20:25.807 CET [24678] STATEMENT:  SELECT lsn FROM pg_catalog.pg_create_logical_replication_slot('sub1', 'pgoutput', false, false, false)\n2024-03-25 15:20:25.840 CET [24682] LOG:  starting PostgreSQL 17devel on x86_64-linux, compiled by gcc-7.5.0, 64-bit\n2024-03-25 15:20:25.844 CET [24682] LOG:  listening on Unix socket "\/home\/postgres\/.s.PGSQL.8889"\n2024-03-25 15:20:25.853 CET [24685] LOG:  database system was shut down in recovery at 2024-03-25 15:20:25 CET\n2024-03-25 15:20:25.856 CET [24685] LOG:  entering standby mode\n2024-03-25 15:20:25.859 CET [24685] LOG:  redo starts at 0\/15AF3B08\n2024-03-25 15:20:25.859 CET [24685] LOG:  consistent recovery state reached at 0\/15AF3C48\n2024-03-25 15:20:25.859 CET [24682] LOG:  database system is ready to accept read-only connections\n2024-03-25 15:20:25.859 CET [24685] LOG:  invalid record length at 0\/15AF3C48: expected at least 24, got 0\n2024-03-25 15:20:25.864 CET [24686] LOG:  started streaming WAL from primary at 0\/15000000 on timeline 1\n2024-03-25 15:20:26.231 CET [24685] LOG:  recovery stopping after WAL location (LSN) "0\/15AF3F60"\n2024-03-25 15:20:26.231 CET [24685] LOG:  redo done at 0\/15AF3F60 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.37 s\n2024-03-25 15:20:26.231 CET [24685] LOG:  last completed transaction was at log time 2024-03-25 15:20:25.77245+01\n2024-03-25 15:20:26.231 CET [24686] FATAL:  terminating walreceiver process due to administrator command\n2024-03-25 15:20:26.234 CET [24685] LOG:  selected new timeline ID: 2\n2024-03-25 15:20:26.278 CET [24685] LOG:  archive recovery complete\n2024-03-25 15:20:26.282 CET [24683] LOG:  checkpoint starting: end-of-recovery immediate wait\n2024-03-25 15:20:26.315 CET [24683] LOG:  checkpoint complete: wrote 10 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.020 s, total=0.037 s; sync files=7, longest=0.004 s, average=0.003 s; distance=6 kB, estimate=6 kB; lsn=0\/15AF5680, redo lsn=0\/15AF5680\n2024-03-25 15:20:26.363 CET [24682] LOG:  database system is ready to accept connections\n2024-03-25 15:20:26.949 CET [24696] LOG:  logical replication apply worker for subscription "sub1" has started\n2024-03-25 15:20:26.949 CET [24682] LOG:  received fast shutdown request\n2024-03-25 15:20:26.950 CET [24698] LOG:  starting logical decoding for slot "sub1"\n2024-03-25 15:20:26.950 CET [24698] DETAIL:  Streaming transactions committing after 0\/15AF3F60, reading WAL from 0\/15AF3F28.\n2024-03-25 15:20:26.950 CET [24698] STATEMENT:  START_REPLICATION SLOT "sub1" LOGICAL 0\/15AF3F60 (proto_version '4', origin 'any', publication_names '"pub1"')\n2024-03-25 15:20:26.950 CET [24698] LOG:  logical decoding found consistent point at 0\/15AF3F28\n2024-03-25 15:20:26.950 CET [24698] DETAIL:  There are no running transactions.\n2024-03-25 15:20:26.950 CET [24698] STATEMENT:  START_REPLICATION SLOT "sub1" LOGICAL 0\/15AF3F60 (proto_version '4', origin 'any', publication_names '"pub1"')\n2024-03-25 15:20:26.957 CET [24682] LOG:  aborting any active transactions\n2024-03-25 15:20:26.957 CET [24696] FATAL:  terminating logical replication worker due to administrator command\n2024-03-25 15:20:26.958 CET [24682] LOG:  background worker "logical replication launcher" (PID 24692) exited with exit code 1\n2024-03-25 15:20:26.958 CET [24682] LOG:  background worker "logical replication apply worker" (PID 24696) exited with exit code 1\n2024-03-25 15:20:26.959 CET [24683] LOG:  shutting down\n2024-03-25 15:20:26.962 CET [24683] LOG:  checkpoint starting: shutdown immediate\n2024-03-25 15:20:27.013 CET [24683] LOG:  checkpoint complete: wrote 20 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.040 s, total=0.055 s; sync files=14, longest=0.004 s, average=0.003 s; distance=3 kB, estimate=6 kB; lsn=0\/15AF64C8, redo lsn=0\/15AF64C8\n2024-03-25 15:20:27.015 CET [24682] LOG:  database system is shut down\n<\/pre><\/div>\n\n\n
There is a lot of output here but if you follow the lines you’ll see the process of converting the physical replica into a logical replica. Lets start it up and check what we’ve got:<\/p>\n\n\n
\npostgres@pgbox:\/home\/postgres\/ [pgdev] pg_ctl -D \/var\/tmp\/target\/ start\nwaiting for server to start....2024-03-25 15:23:09.137 CET [24714] LOG:  starting PostgreSQL 17devel on x86_64-linux, compiled by gcc-7.5.0, 64-bit\n2024-03-25 15:23:09.137 CET [24714] LOG:  listening on IPv6 address "::1", port 8889\n2024-03-25 15:23:09.137 CET [24714] LOG:  listening on IPv4 address "127.0.0.1", port 8889\n2024-03-25 15:23:09.144 CET [24714] LOG:  listening on Unix socket "\/tmp\/.s.PGSQL.8889"\n2024-03-25 15:23:09.150 CET [24717] LOG:  database system was shut down at 2024-03-25 15:20:27 CET\n2024-03-25 15:23:09.150 CET [24717] LOG:  recovered replication state of node 1 to 0\/15AF3F60\n2024-03-25 15:23:09.159 CET [24714] LOG:  database system is ready to accept connections\n2024-03-25 15:23:09.165 CET [24721] LOG:  logical replication apply worker for subscription "sub1" has started\n2024-03-25 15:23:09.167 CET [24722] LOG:  0\/15AF3F60 has been already streamed, forwarding to 0\/15AF5680\n2024-03-25 15:23:09.167 CET [24722] STATEMENT:  START_REPLICATION SLOT "sub1" LOGICAL 0\/15AF3F60 (proto_version '4', origin 'any', publication_names '"pub1"')\n2024-03-25 15:23:09.167 CET [24722] LOG:  starting logical decoding for slot "sub1"\n2024-03-25 15:23:09.167 CET [24722] DETAIL:  Streaming transactions committing after 0\/15AF5680, reading WAL from 0\/15AF3F28.\n2024-03-25 15:23:09.167 CET [24722] STATEMENT:  START_REPLICATION SLOT "sub1" LOGICAL 0\/15AF3F60 (proto_version '4', origin 'any', publication_names '"pub1"')\n2024-03-25 15:23:09.167 CET [24722] LOG:  logical decoding found consistent point at 0\/15AF3F28\n2024-03-25 15:23:09.167 CET [24722] DETAIL:  There are no running transactions.\n2024-03-25 15:23:09.167 CET [24722] STATEMENT:  START_REPLICATION SLOT "sub1" LOGICAL 0\/15AF3F60 (proto_version '4', origin 'any', publication_names '"pub1"')\n done\nserver started\n<\/pre><\/div>\n\n\n
We got the publication on the source and the subscription on the target, as expected:<\/p>\n\n\n
\npostgres@pgbox:\/home\/postgres\/ [pgdev] psql -p 8888 -c "select * from pg_publication"\n  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot \n-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------\n 16438 | pub1    |       10 | t            | t         | t         | t         | t           | f\n(1 row)\npostgres@pgbox:\/home\/postgres\/ [pgdev] psql -p 8889 -c "select * from pg_subscription"\n  oid  | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpasswordrequired | subrunasowner | subfailover |                                                                                           >\n-------+---------+------------+---------+----------+------------+-----------+-----------+------------------+-----------------+---------------------+---------------+-------------+------------------------------------------------------------------------------------------->\n 24576 |       5 | 0\/0        | sub1    |       10 | t          | f         | f         | d                | f               | t                   | f             | f           | user=postgres passfile=\/home\/postgres\/.pgpass channel_binding=prefer port=8888 sslmode=pre>\n(1 row)\n<\/pre><\/div>\n\n\n
Ongoing logical replication can easily be verified by adding a row into the source and checking the same row in the target:<\/p>\n\n\n
\npostgres@pgbox:\/home\/postgres\/ [pgdev] psql -p 8888 -c "insert into pgbench_accounts values (-1,-1,-1,'aaa')"\nINSERT 0 1\npostgres@pgbox:\/home\/postgres\/ [pgdev] psql -p 8889 -c "select * from pgbench_accounts where aid=-1"\n aid | bid | abalance |                                        filler                                        \n-----+-----+----------+--------------------------------------------------------------------------------------\n  -1 |  -1 |       -1 | aaa                                                                                 \n(1 row)\n<\/pre><\/div>\n\n\n
This is really great stuff and all the credits go to the people involved with this<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Logical replication in PostgreSQL came with Version 10, which is already out of support. I’ve written about this a long time ago but the basic building blocks are still the same. Setting this up requires a publication on the source and a subscription on the target and a couple of parameters to bet set on […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-32067","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"\nPostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber\" \/>\n<meta property=\"og:description\" content=\"Logical replication in PostgreSQL came with Version 10, which is already out of support. I’ve written about this a long time ago but the basic building blocks are still the same. Setting this up requires a publication on the source and a subscription on the target and a couple of parameters to bet set on […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-25T14:41:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-25T14:41:22+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber\",\"datePublished\":\"2024-03-25T14:41:18+00:00\",\"dateModified\":\"2024-03-25T14:41:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/\"},\"wordCount\":539,\"commentCount\":2,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/\",\"name\":\"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2024-03-25T14:41:18+00:00\",\"dateModified\":\"2024-03-25T14:41:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber","og_description":"Logical replication in PostgreSQL came with Version 10, which is already out of support. I’ve written about this a long time ago but the basic building blocks are still the same. Setting this up requires a publication on the source and a subscription on the target and a couple of parameters to bet set on […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/","og_site_name":"dbi Blog","article_published_time":"2024-03-25T14:41:18+00:00","article_modified_time":"2024-03-25T14:41:22+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber","datePublished":"2024-03-25T14:41:18+00:00","dateModified":"2024-03-25T14:41:22+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/"},"wordCount":539,"commentCount":2,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/","name":"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2024-03-25T14:41:18+00:00","dateModified":"2024-03-25T14:41:22+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/32067","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=32067"}],"version-history":[{"count":17,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/32067\/revisions"}],"predecessor-version":[{"id":32084,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/32067\/revisions\/32084"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=32067"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=32067"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=32067"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=32067"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}