SBN MPC Wiki

From The SBN Wiki
Revision as of 20:40, 28 May 2020 by Mamoutkine (talk | contribs) (Initial version of Postgres replication instruction. More SBN specifics could be added. Installation of the specific version also could be added.)
Jump to navigation Jump to search

Postgres database replication.


“Master” side:

  1. Make sure port 5432 is open.
  2. As postgres user check postgresql.conf:
  listen_addresses should be listening to the replica address.
  wal_level = logical
  1. In psql setup an account and give it at least read access to the database and tables to be replicated:
  CREATE ROLE <username> WITH REPLICATION LOGIN PASSWORD ‘<password>’;
  GRANT CREATE ON DATABASE <dbname> TO <username>;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO <username>;
  1. As postgres user edit pg_hba.conf:
  In the hosts section add a line like:
  host <dbname> <username> <user_IP>/32 md5
  1. Restart the postgres server.
  2. In psql in the <dbname> create a publication:
  CREATE PUBLICATION <pubname> FOR ALL TABLES;
  1. Dump the schema of all tables in the publication and send them to the replica machine:
  pg_dump -d <dbname> -t <table1> -s > <table1>_schema.sql
  pg_dump -d <dbname> -t <table2> -s > <table2>_schema.sql
  pg_dump -d <dbname> -t <table3> -s > <table3>_schema.sql
  (can be done for all tables at once by removing the -t option, but above method makes it easier to troubleshoot any issues).

Items 1,2,6 need to be done 1 time.
Items 3,4,5 need to be done with every new replication
Item 7 could be done once, but with every new replication the <username> should be updated in every *_schema.sql file:

  GRANT SELECT ON TABLE public.neocp_events TO <username>;

“Replica” side (from the scratch):

  1. Create an empty database and add empty tables using schemas from “master”:
  psql -d <dbname> -a < <table1>_schema.sql
  psql -d <dbname> -a < <table1>_schema.sql
  psql -d <dbname> -a < <table1>_schema.sql
  1. Create a subscription to the master’s publication:
  CREATE SUBSCRIPTION <subname> CONNECTION “host=<master_IP_address> port=5432 user=<username> password=<password> dbname=<dbname>” PUBLICATION <pubname>;

<pubname> in “replica” and “master” sides must match, but the <subname> must not already exist. Multiple subscribers can all connect to the same publication <pubname>.

https://www.digitalocean.com/community/tutorials/how-to-set-up-logical-replication-with-postgresql-10-on-ubuntu-18-04

“Replica” side (with existing logical replication from SBN) - how to add new table(s):

  1. Add empty tables using schemas from “master”:
  psql -d <dbname> -a < <table1>_schema.sql
  1. Modify a subscription to the master’s publication:
  ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATON;