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 (Add "How to check a replication".)
(9 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
<h1>Postgres database replication.</h1><br>
 
<h1>Postgres database replication.</h1><br>
 
+
<p style="color:red">'''Note that the public database schema  is a β release,  and may be subject to change over the development period. Tables may be added, removed, or modified by the MPC.'''</p>
 +
</pre>
 +
If you decided to replicate a Postgres '''mpcbeta''' copy of database from SBN, please, consider '''"Master" side''' paragraph FOR YOUR INFORMATION only. Start from the '''"Replica" side (from the scratch)'''.
 
<h2>“Master” side:</h2>
 
<h2>“Master” side:</h2>
 
# Make sure port 5432 is open.
 
# Make sure port 5432 is open.
Line 15: Line 17:
 
# <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 27: Line 30:
  
 
<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”:
+
# Install PostgresSQL v11.x
   psql -d <dbname> -a < <table1>_schema.sql
+
# Provide IP-address of the server to SBN (''mamoutkine@astro.umd.edu'')
   psql -d <dbname> -a < <table1>_schema.sql
+
# Wait for table-structures*.sql files and shell script from SBN
   psql -d <dbname> -a < <table1>_schema.sql
+
# Download tables_structure*.sql files into local server disk with reading permission for all.
# <li value="2">Create a subscription to the master’s publication:</li>
+
# Create an empty database (mpcbeta):
 +
  sudo -u postgres psql
 +
  CREATE DATABASE <dbname>
 +
# <li value="5">Add empty tables using schemas from the "master" (Run those commands from the same directory where *.sql files):</li>
 +
   [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
 +
# <li value="6">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>.
  
 
https://www.digitalocean.com/community/tutorials/how-to-set-up-logical-replication-with-postgresql-10-on-ubuntu-18-04
 
https://www.digitalocean.com/community/tutorials/how-to-set-up-logical-replication-with-postgresql-10-on-ubuntu-18-04
 +
 +
# <li value="7">How to check a replication:</li>
 +
  sudo -u postgres psql mpcbeta
 +
  SELECT * FROM neocp_obs_archive;
 +
 +
Another check:
 +
  sudo -u postgres psql mpcbeta
 +
  SELECT * FROM pg_stat_replication;
 +
 +
Another check:
 +
View as a sudo latest log-file: [psql_path]/11/data/log/postgresql-[Wed].log
  
 
<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>
   ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATON;
+
  sudo -u postgres psql <dbname>
 +
   ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATION;

Revision as of 22:48, 1 July 2020

Postgres database replication.


Note that the public database schema is a β release, and may be subject to change over the development period. Tables may be added, removed, or modified by the MPC.

If you decided to replicate a Postgres mpcbeta copy of database from SBN, please, consider "Master" side paragraph FOR YOUR INFORMATION only. Start from the "Replica" side (from the scratch).

“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. Install PostgresSQL v11.x
  2. Provide IP-address of the server to SBN (mamoutkine@astro.umd.edu)
  3. Wait for table-structures*.sql files and shell script from SBN
  4. Download tables_structure*.sql files into local server disk with reading permission for all.
  5. Create an empty database (mpcbeta):
  sudo -u postgres psql
  CREATE DATABASE <dbname>
  1. Add empty tables using schemas from the "master" (Run those commands from the same directory where *.sql files):
  [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

  1. How to check a replication:
  sudo -u postgres psql mpcbeta
  SELECT * FROM neocp_obs_archive;

Another check:

  sudo -u postgres psql mpcbeta
  SELECT * FROM pg_stat_replication;

Another check: View as a sudo latest log-file: [psql_path]/11/data/log/postgresql-[Wed].log

“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 PUBLICATION;