Difference between revisions of "SBN MPC Wiki"
Jump to navigation
Jump to search
Mamoutkine (talk | contribs) (Initial test.) |
Mamoutkine (talk | contribs) (Initial version of Postgres replication instruction. More SBN specifics could be added. Installation of the specific version also could be added.) |
||
Line 1: | Line 1: | ||
− | < | + | <h1>Postgres database replication.</h1><br> |
+ | |||
+ | <h2>“Master” side:</h2> | ||
+ | # Make sure port 5432 is open. | ||
+ | # As postgres user check postgresql.conf: | ||
+ | ''listen_addresses'' should be listening to the replica address. | ||
+ | ''wal_level = logical'' | ||
+ | # <li value="3">In psql setup an account and give it at least read access to the database and tables to be replicated:</li> | ||
+ | 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>; | ||
+ | # <li value="4">As postgres user edit pg_hba.conf:</li> | ||
+ | In the hosts section add a line like: | ||
+ | host <dbname> <username> <user_IP>/32 md5 | ||
+ | # <li value="5">Restart the postgres server.</li> | ||
+ | # In psql in the <dbname> create a publication: | ||
+ | CREATE PUBLICATION <pubname> FOR ALL TABLES; | ||
+ | # <li value="7">Dump the schema of all tables in the publication and send them to the replica machine:</li> | ||
+ | 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.<br> | ||
+ | Items 3,4,5 need to be done with every new replication<br> | ||
+ | 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>; | ||
+ | |||
+ | <h2>“Replica” side (from the scratch):</h2> | ||
+ | # 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 | ||
+ | # <li value="2">Create a subscription to the master’s publication:</li> | ||
+ | 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 | ||
+ | |||
+ | <h2>“Replica” side (with existing logical replication from SBN) - how to add new table(s):</h2> | ||
+ | # Add empty tables using schemas from “master”: | ||
+ | psql -d <dbname> -a < <table1>_schema.sql | ||
+ | # <li value="2">Modify a subscription to the master’s publication:</li> | ||
+ | ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATON; |
Revision as of 20:40, 28 May 2020
Contents
Postgres database replication.
“Master” side:
- Make sure port 5432 is open.
- As postgres user check postgresql.conf:
listen_addresses should be listening to the replica address. wal_level = logical
- 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>;
- As postgres user edit pg_hba.conf:
In the hosts section add a line like: host <dbname> <username> <user_IP>/32 md5
- Restart the postgres server.
- In psql in the <dbname> create a publication:
CREATE PUBLICATION <pubname> FOR ALL TABLES;
- 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):
- 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
- 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>.
“Replica” side (with existing logical replication from SBN) - how to add new table(s):
- Add empty tables using schemas from “master”:
psql -d <dbname> -a < <table1>_schema.sql
- Modify a subscription to the master’s publication:
ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATON;