pglogical on PostgreSQL 9.6 – small hints (on Debian)

• install PostgreSQL 9.6 – https://raonyguimaraes.com/how-to-install-postgresql-9-6-on-ubuntudebianlinux-mint/
• install package “postgresql-contrib-9.6”
• install pglogical package – https://2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/
• continue with installation on master database as described here – https://2ndquadrant.com/en/resources/pglogical/pglogical-docs/
  • do not forget to make changes in “postgresql.conf” file – mainly ” shared_preload_libraries=’pglogical’ and wal_level = ‘logical’ “
  • allow replication access in pg_hba.conf -” host replication username IP/32 or 0.0.0.0/0 md5″
  • restart postgresql service
• continue in postgresql (on master):

  create extension pglogical;
  -- create node
  select pglogical.drop_node( node_name := 'test1', ifexists := true );
  
  SELECT pglogical.create_node(
  node_name := 'test1',
  dsn := 'host=master_IP_address port=5432 dbname=master_db_name' );
  -- reply "node oid"
  
  -- create replication set
  select pglogical.drop_replication_set(set_name := 'set_name');
  
  select pglogical.create_replication_set(set_name := 'set_name');
  -- reply "set oid"
  
  select pglogical.replication_set_add_table(set_name := 'set_name'::name, relation :='schemaname.tablename'::regclass, synchronize_data := true );
  -- reply "replication set oid"
  

• repeat postgresql 9.6 and pglogical installation on replica
• continue in postgresql (on replica):

  create extension pglogical;
  -- create node
  SELECT pglogical.drop_subscription( subscription_name := 'replica_subscription', ifexists := true);
  -- reply "t"
  
  select pglogical.drop_node(node_name := 'unique_node_name', ifexists := true);
  -- reply "t"
  
  SELECT pglogical.create_node(
  node_name := 'unique_node_name',
  dsn := 'host=replica_IP_address port=5432 dbname=replica_db_name user=replica_user_name password=replica_user_password');
  -- note: I tested ~/.pgpass file but looks like pglogical does not take user and password from it and both must be supplied in "create_node" command
  -- reply "node oid"
  
  SELECT pglogical.create_subscription(
  subscription_name := 'replica_subscription',
  provider_dsn := 'host=replica_IP_address port=5432 dbname=replica_db_name user=replica_user_name password=replica_user_password',
  replication_sets := ARRAY['set_name'],
  synchronize_structure := true,
  synchronize_data := true);
  -- reply "subscription oid"
  

• with all this done logical replication should work – if not, check /var/log/postgresql/postgresql-9.6* logs on both machines

Possible errors:

• you can get error message

  ERROR: table ..... cannot be added to replication set .....
  SQL state: 22023
  Detail: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
  Hint: Add a PRIMARY KEY to the table
  

  if so you must either add a primary key to the table or to set parameters “replicate_update” and “replicate_delete” in “create_replication_set” command to FALSE.