1

I'm trying to setup replication from postgres DB source (pg 11.7, pglogical 2.2.1) to target (pg 13.5, pglogical 2.3.3)

The connectivity and access across DBs are configured and tested.

I've manually duplicated roles from source to target using pg_dump -g globals on source then psql -f globals.sql on target.

I've manually duplicated schema from source to target using pg_dump -Fc -s -f ~/schema.dmp mydatabase on source then pg_restore -d mydatabase schema.dmp on target.

I've modified each DBs postgres.conf with:

wal_level='logical'
max_worker_processes=10
max_replcation_slots=10
max_wal_senders=10
shared_preload_libraries='pglogical'

On both DBs I alter system set shared_preload_libraries = 'pglogical'; and restarted

On both DBs I CREATE EXTENSION pglogical;

On the source I create the node with SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=<source_IP> port=5432 dbname=mydatabase user=pglogical password=<password>');

On the source I add all tables to replication set with SELECT pglogical.replication_set_add_all_tables('default', '{public}'::text[]);

On the source I add all sequences to replication set with SELECT pglogical.replication_set_add_all_sequences(set_name := 'default', schema_names := '{public}'::text[], synchronize_data := true );

On the target I create node with SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=<target_IP> port=5432 dbname=mydatabase user=pglogical password=<password>');

Then finally I attempt to create subscription on source with `SELECT pglogical.create_subscription(subscription_name := 'subscription', provider_dsn := 'host=<source_IP> port=5432 dbname=mydatabase user=pglogical password=', replication_sets := '{default}'::text[]);

which results in the following error on the source:

ERROR:  could not fetch remote node info: ERROR:  function pglogical.pglogical_node_info() does not exist
LINE 1: ..., node_name, sysid, dbname, replication_sets FROM pglogical....
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

and this error in found in journal on the target:

LOG:  connection authorized: user=pglogical database=mydatabase SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)
ERROR:  function pglogical.pglogical_node_info() does not exist at character 65
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT:  SELECT node_id, node_name, sysid, dbname, replication_sets FROM pglogical.pglogical_node_info()

I've tried that pglogical.create_subscription function with all typecasts included, doesn't change anything.

On the target I see:

postgres=# select * from pglogical.pglogical_node_info()
;
  node_id   | node_name  |        sysid        |  dbname  |               replication_sets
------------+------------+---------------------+----------+----------------------------------------------
 2941155235 | subscriber | 7067748448099432568 | postgres | "걵wN`PXU","\x04\x0B鐣wNPXU","\x0FNl7wNxPXU"
(1 row)

on the source I see:

postgres=# select * from pglogical.pglogical_node_info()
;
  node_id   |       node_name       |        sysid        |  dbname  |             replication_sets
------------+-----------------------+---------------------+----------+-------------------------------------------
 2678724765 | provider | 6825764350976429997 | postgres | "\x08P\x180U"," \x03;%\x180U","BBԝ\x180U"
(1 row)

Why can't I create this subscription? Is streaming from PG11.7 to PG13.5 supported by pglogical?

Greg Toews
  • 31
  • 4

2 Answers2

1

I think your create_subscription statement is incorrect.

pglogical.create_subscription(subscription_name := 'subscription', provider_dsn := 'host=<target_IP> port=5432 dbname=mydatabase user=pglogical password=', replication_sets := '{default}'::text[]);

You are including <target_IP> instead of <source_IP>

Another possible issue - I'm not sure about your syntax for replication sets. You may want to remove that. You don't need to list them again here as you've already set it up at the node level.

Further resources:

  1. pglogical docs. Search for references to create_subscription
  2. Helpful AWS blog post walkthrough for using pglogical. I am using now myself to upgrade PG.
Mark Grobaker
  • 11
  • 1
  • Thanks for catching that, unfortunately I substituted `target_IP` for `source_IP` only here in the problem description. I've now edited to correct that mistake. Also I've tried many different forms of `create_subscription` including with only the minimum arguments. I've also tried type-casting every argument. I know I'm close when the the source side receives command and outputs logs. The logs on the source are always the same: `No function matches the given name and argument types`. I will examine AWS blog post next. Thank you! – Greg Toews Mar 06 '22 at 19:04
  • Got it. I think you should also confirm that pglogical is in fact installed on both target and source. For example, you can run this on both target and source: `SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'pglogical' ;` You should see several tables. If not, pglogical is not installed and that is your problem If you do see the tables, then inspect them and see if your node is appearing there. – Mark Grobaker Mar 10 '22 at 21:04
1

After creating another much simpler test bed, I've discovered what was likely the issue here. The pglogical subscriptions are not managed globally. They are specific to each database. For example, to create node and subscription one must explicitly connect to the database to replicated first.

postgres=# SELECT subscription_name, status FROM pglogical.show_subscription_status();
 subscription_name | status
-------------------+--------
(0 rows)

postgres=# \c mydatabase
You are now connected to database "mydatabase" as user "postgres".
mydatabase=# SELECT subscription_name, status FROM pglogical.show_subscription_status();
 subscription_name |   status
-------------------+-------------
 subscription      | replicating
(1 row)
Greg Toews
  • 31
  • 4