Logical Replication

On the host server

Modify postgresql.conf to be ready for logical replication

wal_level=logical
max_wal_senders=16
max_sync_workers_per_subscription=16
max_logical_replication_workers=20

Setup a replication user

CREATE ROLE repl WITH 
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    LOGIN
    REPLICATION
    BYPASSRLS
    CONNECTION LIMIT -1
    PASSWORD 'SOME SECURE PASSWORD';

Then, on all databases you want to sync, grant select permissions

grant select on all tables in schema public to repl

Next, create a publication on each database

CREATE PUBLICATION publication_name FOR ALL TABLES;

It's possible to increase replication slots by editing postgresql.conf with

max_replication_slots=20

On the target server

Modify postgresql config to speed up replication

max_wal_senders=16
max_sync_workers_per_subscription=16
max_logical_replication_workers=20

Ensure schema has been fully copied already, but no data has been copied

Then, create a subscription on each database

CREATE SUBSCRIPTION publication_name
CONNECTION 'host=<IP Addres> port=<PORT> user=repl password=<PASSWORD> dbname=<DB NAME>'
PUBLICATION publication_name;

Make sure to check logs when doing this to ensure there aren't permissions errors.

After migration

WARNING: MAKE SURE TO READ THIS

Sequence columns won't be updated properly.

To fix this, on each database, you must run the following commands.

Find the tables affected:

SELECT * FROM information_schema.sequences;

Make sure to remove _id_seq from the table names

Then run

SELECT pg_catalog.setval(pg_get_serial_sequence('"table_name"', 'id'), (SELECT MAX(id) FROM "table_name")+1);