Add option for using pgcreatesubscriber in PG17

[NyakudyaA]

Feature description

https://www.postgresql.org/docs/current/app-pgcreatesubscriber.html

Additional context

https://www.percona.com/blog/easy-guide-to-switching-postgresql-physical-replication-to-logical-replication-with-pg_createsubscriber/

[NyakudyaA]

#!/bin/bash # Start by defining two servers on ports 5432 and 5431, initially the servers are created as primary and standby, but they will be converted to publisher and subscriber with pg_createsubscriber. port_N1=5432 port_N2=5431 # Stop any servers running on the ports: /home/pgedge/postgres/pg17/bin/pg_ctl stop -D /home/pgedge/postgres/pg17/bin/data_N1 /home/pgedge/postgres/pg17/bin/pg_ctl stop -D /home/pgedge/postgres/pg17/bin/data_N2 # Empty the data folders before performing an initdb: rm -rf data_* *log /home/pgedge/postgres/pg17/bin/initdb -D /home/pgedge/postgres/pg17/bin/data_N1 -U pgedge # Set the following configuration parameters on the primary node: cat << EOF >> /home/pgedge/postgres/pg17/bin/data_N1/postgresql.conf wal_level=logical max_replication_slots=10 hot_standby=on port=$port_N1 EOF /home/pgedge/postgres/pg17/bin/pg_ctl start -D /home/pgedge/postgres/pg17/bin/data_N1 -l N1.log # Create a standby server by taking a backup of the primary server: # Include the -R option to automatically set up primary_connifo and primary_slotname: /home/pgedge/postgres/pg17/bin/pg_basebackup -d "dbname=postgres user=pgedge port=$port_N1" -D /home/pgedge/postgres/pg17/bin/data_N2 -R -X stream # Setup GUCs:cat << EOF >> /home/pgedge/postgres/pg17/bin/data_N2/postgresql.confport=$port_N2 EOF # Start the standby server: /home/pgedge/postgres/pg17/bin/pg_ctl start -D /home/pgedge/postgres/pg17/bin/data_N2 -l n2.log sleep 1s # Populate the primary database with some tables and data: /home/pgedge/postgres/pg17/bin/pgbench -i -U pgedge -s 10 -d postgres # Check if the standby is catching up using streaming replication: /home/pgedge/postgres/pg17/bin/psql -U pgedge -d postgres -p $port_N2 -c "SELECT count(*) FROM pgbench_accounts;" # Stop the standby server so we can issue the CREATE SUBSCRIBER command: /home/pgedge/postgres/pg17/bin/pg_ctl stop -D /home/pgedge/postgres/pg17/bin/data_N2 # Run pg_createsubscriber, specifying: # -v for verbose # -D is target data directory # -P is the source server # -d database included /home/pgedge/postgres/pg17/bin/pg_createsubscriber -v -D /home/pgedge/postgres/pg17/bin/data_N2/ -P "host=localhost" -d postgres --publication=pub1 --subscription=sub 1sleep 1s # start the subscriber after pg_createsubscriber has run successfully: /home/pgedge/postgres/pg17/bin/pg_ctl start -D /home/pgedge/postgres/pg17/bin/data_N2 -l n2.log # Confirm the publication and subscription are created on the respective nodes: /home/pgedge/postgres/pg17/bin/psql -U pgedge -d postgres -p $port_N1 -c "SELECT * FROM pg_publication;" /home/pgedge/postgres/pg17/bin/psql -U pgedge -d postgres -p $port_N1 -c "SELECT * FROM pg_publication_tables;" /home/pgedge/postgres/pg17/bin/psql -U pgedge -d postgres -p $port_N2 -c "SELECT * FROM pg_subscription;"