pglogical is an extremely useful extension, allowing forms of replication not supported by WAL shipping; but the downside is that you have to select which tables are included.

If you are using it to perform a major version upgrade, for example, the answer is EVERYTHING (just about).

You can write a script, listing the tables; but once you’ve done that a few times, you start to think about automating it, to ensure that any new/updated tables are included correctly.

It’s relatively straightforward to get a list of all tables from PG:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pglogical', 'public', 'pg_catalog', ...)
    AND NOT (table_schema = '...' AND (table_name = '...' OR table_name = '...' OR table_name= '...'))
    AND NOT (table_schema = '...' AND table_name = '...')
ORDER BY 1, 2;

Excluding anything that causes problems (e.g. ref data added by migrations). This gives you a pipe separated list:

    table_schema    |          table_name          
--------------------+------------------------------
 foo              | bar

That you can plonk into a google sheet (other cloud based applications are available):

And finally, you need a horrifying line of VBA:

=CONCATENATE("SELECT pglogical.replication_set_add_table('", IF(ISBLANK(C2), "default", C2), "', '", A2, ".", B2, "', true, null, ", IF(ISBLANK(D2),"null", CONCATENATE("'", D2, "'")), ");")

Glorious!

(You don’t need all the escaped quotes, if your tables/schemas have sensible names; and you can ignore col D, if you aren’t filtering any data)

And then something similar, for sequences:

SELECT sequence_schema, sequence_name
FROM information_schema.sequences
WHERE sequence_schema NOT IN ('public', ...)
ORDER BY 1, 2;

And:

=CONCATENATE("SELECT pglogical.replication_set_add_sequence('default', '", A2, ".", B2, "');")

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.