Difference between revisions of "SBN MPC Wiki"

From The SBN Wiki
Jump to navigation Jump to search
(Initial version of Postgres replication instruction. More SBN specifics could be added. Installation of the specific version also could be added.)
m (Unfold few "default" commands from the users feedback.)
Line 15: Line 15:
 
# <li value="5">Restart the postgres server.</li>
 
# <li value="5">Restart the postgres server.</li>
 
# In psql in the <dbname> create a publication:
 
# In psql in the <dbname> create a publication:
 +
  [sudo -u postgres] psql <dbname>
 
   CREATE PUBLICATION <pubname> FOR ALL TABLES;
 
   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>
 
# <li value="7">Dump the schema of all tables in the publication and send them to the replica machine:</li>
Line 28: Line 29:
 
<h2>“Replica” side (from the scratch):</h2>
 
<h2>“Replica” side (from the scratch):</h2>
 
# Create an empty database and add empty tables using schemas from “master”:
 
# Create an empty database and add empty tables using schemas from “master”:
   psql -d <dbname> -a < <table1>_schema.sql
+
   [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
   psql -d <dbname> -a < <table1>_schema.sql
+
   [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
   psql -d <dbname> -a < <table1>_schema.sql
+
   [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
 
# <li value="2">Create a subscription to the master’s publication:</li>
 
# <li value="2">Create a subscription to the master’s publication:</li>
 +
  sudo -u postgres psql
 
   CREATE SUBSCRIPTION <subname> CONNECTION “host=<master_IP_address> port=5432 user=<username> password=<password> dbname=<dbname>” PUBLICATION <pubname>;
 
   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>.
 
<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>.
Line 38: Line 40:
  
 
<h2>“Replica” side (with existing logical replication from SBN) - how to add new table(s):</h2>
 
<h2>“Replica” side (with existing logical replication from SBN) - how to add new table(s):</h2>
 +
# Download all structure_only.sql file into local disk with reading permission to all
 
# Add empty tables using schemas from “master”:
 
# Add empty tables using schemas from “master”:
   psql -d <dbname> -a < <table1>_schema.sql
+
   [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
# <li value="2">Modify a subscription to the master’s publication:</li>
+
# <li value="3">Modify a subscription to the master’s publication:</li>
 +
  sudo -u postgres psql <dbname>
 
   ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATON;
 
   ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATON;

Revision as of 20:44, 4 June 2020

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:
  [sudo -u postgres] psql <dbname>
  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”:
  [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
  [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
  [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
  1. Create a subscription to the master’s publication:
  sudo -u postgres psql
  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. Download all structure_only.sql file into local disk with reading permission to all
  2. Add empty tables using schemas from “master”:
  [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
  1. Modify a subscription to the master’s publication:
  sudo -u postgres psql <dbname>
  ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATON;