'Why can't I create pglogical subscription?

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?

postgresqlpglogical


Solution 1:[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.

Solution 2:[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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mark Grobaker
Solution 2 Greg Toews

Related Questions