Test driving pglogical

Graham Hay

We have been using DMS for two different tasks: upgrading to a new major pg version (when the db is too big to just click the button), and removing some old data during failover (sadly we don’t have some partitions we can just unlink).

Each time we have used it has been a “success”, i.e. we haven’t had any major disasters, or had to bail out of the failover; but it has never been a comfortable ride. Sometimes that was self inflicted, e.g. not running analyze/freeze after the import; but just setting up stable replication was a struggle, and there’s very little insight when it fails.

As we don’t need any of the more exotic features (e.g. moving from an on-prem Oracle DB to PG RDS), it felt like there must be an easier way. Postgres has had logical replication since v10, which would solve one of our problems. But it doesn’t (yet) provide a way to only replicate a subset of data.

There are a number of people selling alternative solutions, but one option that regularly crops up is pglogical. The situation isn’t entirely clear, as 2ndQ are now part of EDB, which is offering v3 (there is no obvious way to download it, so I assume that is part of their consultancy package). But afaict it is only supported as part of BDR, and the v2 repo still seems to be active.

If you’re willing to accept that risk, it’s pretty easy to do some local testing, using docker:

FROM postgres:11-bullseye

RUN apt-get update
RUN apt-get install postgresql-11-pglogical

...

Create an image with the (right version of the) extension installed, and whatever scripts you have to create a shell db (you’re using migrations, right?), and spin up two instances with compose:

version: '3'
services:
  db1:
    image: pg11
    volumes:
      - "./postgresql-11.conf:/var/lib/postgresql/data/postgresql.conf"
    environment:
      POSTGRES_PASSWORD: postgres
  db2:
    image: pg11
    volumes:
      - "./postgresql-11.conf:/var/lib/postgresql/data/postgresql.conf"
    environment:
      POSTGRES_PASSWORD: postgres

You can get the pg config template from the base image, and make the necessary changes. You need to set a password for the user, because reasons.

Once this is up:

docker-compose up

You can connect to the publisher:

docker-compose exec db1 psql -U postgres -d foodb

And create a node:

CREATE EXTENSION pglogical;

SELECT pglogical.create_node(node_name := 'db1', dsn := 'host=db1 port=5432 dbname=foodb user=postgresql password=postgresql');

Then set up the tables you want to replicate:

SELECT pglogical.replication_set_add_table('default', 'foo.bar', true, null, null);

Or with a row filter:

SELECT pglogical.replication_set_add_table('default', 'foo.bar', true, null, E'started_at > \'2022-1-1\'');

Finally, connect to the other instance, and create the subscription:

SELECT pglogical.create_node(node_name := 'db2', dsn := 'host=db2 port=5432 dbname=foodb user=postgres password=postgres');

SELECT pglogical.create_subscription(subscription_name := 'subscription1', provider_dsn := 'host=db1 port=5432 dbname=foodb user=postgres password=postgres');

(this dsn is where the password is needed)

If you get bored of typing this in, you can pipe a script through:

cat publisher.sql | docker-compose exec -T db1 psql -U postgres -d foodb

You should now be able to insert a row in db1, and see it appear in db2 (or not, depending on the filter).

You can also easily test replication from, e.g. 11 -> 14. The bigger questions are how long the initial import takes, and how stable replication is, but those will need to be answered in a production like environment.

There is also some evidence online that RDS allows the extension, but I haven’t tried it yet.

Leave a comment