Why can't I create pglogical subscription?

Question

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?

Answer 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.

Answer 2

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)

Answer 3

If anyone find this post and hit the same issue

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....

Start from beggining and first CHOOSE database that you would like to replicate by:

\c DATABASE

or

$ psql -d DATABASE

Only then you can use CREATE EXTENSION pglogical This will create schema pglogical inside this database and then you can create nodes and subscribers.

Or you are using user with incorrect GRANTS. Check this via psql cli first.

You are most welcome :(