Difference between revisions of "SBN MPC Wiki"

From The SBN Wiki
Jump to navigation Jump to search
(Initial test.)
m (Add "How to check a replication".)
 
(10 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<big>'''Postgres replication.'''</big>
+
<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>
 +
# 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:
 +
  [sudo -u postgres] psql <dbname>
 +
  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>
 +
# Install PostgresSQL v11.x
 +
# Provide IP-address of the server to SBN (''mamoutkine@astro.umd.edu'')
 +
# Wait for table-structures*.sql files and shell script from SBN
 +
# Download tables_structure*.sql files into local server disk with reading permission for all.
 +
# 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>;
 +
<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
 +
 
 +
# <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>
 +
# Download all structure_only.sql file into local disk with reading permission to all
 +
# Add empty tables using schemas from “master”:
 +
  [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
 +
# <li value="3">Modify a subscription to the master’s publication:</li>
 +
  sudo -u postgres psql <dbname>
 +
  ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATION;

Latest 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;