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