Master DB:
1. Create a publication
CREATE PUBLICATION alltables FOR ALL TABLES;
2. Create a replication user
create role replication_user with login password 'some-pass' replication;
--GRANT whatever permissions are required
grant usage on schema some_schema to replication_user;
grant select on all tables in schema some_schema to replication_user;
Note: with logical replication, user does not seem to have to have the replication rights; this is required with std streaming replication
3. Modify the postgresql.conf to allow connections to the machine via its ip
listen_addresses = 'localhost,server_ip'
and set up a proper WAL lvl
wal_level = logical # minimal, replica, or logical
4. Modify pg_hba.conf to allow connections from specified slaves
host all replication_user slave_ip/0 md5
5. Dump db structure
pg_dump.exe --host localhost --port 5432 --username "some-user-name" --no-password --format plain --schema-only --verbose --file "C:\temp\db_schema.sql" --schema "schema-name" "db-name"
6. Review publication and replication slots (when a subscription is created)
select * from pg_publication;
select * from pg_replication_slots; --when subscription is created
7. Check replication status
select * from pg_publication;
select * from pg_stat_replication;
Replica DB:
1. recreate db structure - this is required in order to make the subscription work
createdb --host localhost --port 5432 --username user-name db-name
psql --host localhost --port 5432 --username user-name -w --dbname db-name --command "create extension if not exists postgis"
psql --host localhost --port 5434 --username postgres -w --dbname db-name --file "c:\temp\db_schema.sql"
Note: installing postgis is not required of course
2. Create a subscription
create subscription my_sub
connection 'host=master-ip dbname=db-to-replicate user=replication_user password=some-password port=5432'
publication alltables;
3. Review subscription
select * from pg_subscription;
4. Check replication status
select * from pg_stat_subscription;
More info here: https://www.postgresql.org/docs/10/logical-replication.html