Eric Radman : a Journal

Bi-directional Replication with pglogical

The pglogical extension and other initiatives sponsored by 2ndQuadrant serve as basis for many of the underlying replication technologies in PostgreSQL today. PostgreSQL is a very flexible platform, and even without standardized multi-master support, we can configure two masters with bi-directional replication without resorting to unwieldy triggers.

In this scheme each is truly an independent database, so your application must not depend on or any kind of read consensus. The following guide uses two nodes, but three or four nodes in a fully-meshed configuration would work as well.

First Steps

The configuration for postgresql.conf is simple enough

wal_level = 'logical'
track_commit_timestamp = on
shared_preload_libraries = 'pglogical'
max_replication_slots = 10

track_commit_timestamp allows conflict resolution to be configured. Now we can install the extension on each database

CREATE EXTENSION pglogical;

Every pglogical node needs to know it's own connection string

-- db1
SELECT pglogical.create_node(
    node_name := 'db1_local',
    dsn := 'host=db1 dbname=localharvest user=postgres password=XYZ7;'
);

-- db2
SELECT pglogical.create_node(
    node_name := 'db2_local',
    dsn := 'host=db2 dbname=localharvest user=postgres password=XYZ7'
);

Replication Sets

We don't have any subscriptions set up yet, but we do have what's called a replication set which controls which operations are replicated (INSERT, UPDATE, DELETE, TRUNCATE). There are three replication sets installed when the extension is created. This is visible in the pglogical schema.

SELECT * FROM pglogical.replication_set;

Tables can be added to a replication set one at a time, or all at once. We'll go ahead and use the default set

SELECT pglogical.replication_set_add_all_tables('default', '{public}', true)

Sequences

There are many clever ways of generating a global sequence, but the simplest thing we can do is configre sequences to begin and increment in a way that doesn't conflict

-- node A
ALTER SEQUENCE project_id_seq RESTART WITH 10 INCREMENT 10;
-- node B
ALTER SEQUENCE project_id_seq RESTART WITH 11 INCREMENT 10;

Instead of incrementing by base 10 we could use prime numbers

-- node A
ALTER SEQUENCE project_id_seq RESTART WITH 3 INCREMENT 3;
-- node B
ALTER SEQUENCE project_id_seq RESTART WITH 7 INCREMENT 7;

After importing data we can contrive a function for fixing up the sequences to match the max data type

DO $$
DECLARE
  tn text;
BEGIN
  FOR tn IN (select table_name
            from information_schema.tables
            where table_catalog='localharvest'
            and table_schema='public')
  LOOP
    BEGIN
      EXECUTE 'SELECT setval(''"' || tn || '_id_seq"'', (SELECT (ceil(max("id")/10) * 10 + 11)::bigint FROM ' || tn || '));';
    EXCEPTION WHEN undefined_table THEN
      NULL;
    END;
  END LOOP;
END $$;

Verify the increment for each squence using

SELECT sequence_name,data_type,increment,start_value FROM information_schema.sequences;
-- and
SELECT nextval('project_id_seq');
SELECT currval('project_id_seq');

If a table has a high insert rate you may want to convert a sequence to a 8-byte integer

SELECT pglogical.replicate_ddl_command('ALTER TABLE public.project ALTER COLUMN id TYPE bigint', '{default}');

Subscriptions

-- db2
SELECT pglogical.create_subscription(
    subscription_name := 'db1_subscription',
    forward_origins := '{}',
    provider_dsn := 'host=db2 dbname=localharvest user=postgres password=XYZ7'
);

-- db2
SELECT pglogical.create_subscription(
    subscription_name := 'db2_subscription',
    forward_origins := '{}',
    provider_dsn := 'host=db1 dbname=localharvest user=postgres password=XYZ7;'
);

Tables must be empty since synchronization is the default. If the data is already in place, add synchronize_data := false.

Client Connections

Since PostgreSQL 10, clients have been able to specify multiple hostnames in a connection URL or with the DSN host parameter:

postgresql://localharvest:XYZ123@db1,db2/localharvest

The hosts are tried in order. Add ?target_session_attrs=read-write To ensure the only writable databases are selected.

Notes

pglogical3 has not been released publicly, so support for PostgreSQL 12 is unclear. These are some hints for running pglogical2:

Last updated on November 19, 2019