Difference between revisions of "SBN MPC Wiki"
Mamoutkine (talk | contribs) m |
Mamoutkine (talk | contribs) |
||
Line 19: | Line 19: | ||
[sudo -u postgres] psql <dbname> | [sudo -u postgres] psql <dbname> | ||
CREATE PUBLICATION <pubname> FOR ALL TABLES; | CREATE PUBLICATION <pubname> FOR ALL TABLES; | ||
+ | or | ||
+ | CREATE PUBLICATION <pubname> FOR TABLES table1, table2, table3,...; | ||
# <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> | ||
pg_dump -d <dbname> -t <table1> -s > <table1>_schema.sql | 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 <table2> -s > <table2>_schema.sql | ||
pg_dump -d <dbname> -t <table3> -s > <table3>_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). | (can be done for all tables at once by removing the -t option, but above method makes it easier to troubleshoot any issues). | ||
+ | pg_dump -d <dbname> > <dbname>_schemas.sql | ||
Items 1,2,6 need to be done 1 time.<br> | Items 1,2,6 need to be done 1 time.<br> | ||
Items 3,4,5 need to be done with every new replication<br> | Items 3,4,5 need to be done with every new replication<br> | ||
Line 30: | Line 34: | ||
<h2>“Replica” side (from the scratch):</h2> | <h2>“Replica” side (from the scratch):</h2> | ||
− | # Install PostgresSQL | + | # Install PostgresSQL v14.x |
# Provide IP-address of the server to SBN (''mamoutkine@astro.umd.edu'') | # Provide IP-address of the server to SBN (''mamoutkine@astro.umd.edu'') | ||
# Wait for table-structures*.sql files and shell script from SBN | # Wait for table-structures*.sql files and shell script from SBN | ||
Line 41: | Line 45: | ||
[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 | [sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql | ||
+ | or | ||
+ | [sudo -u postgres] psql <dbname> < <dbname>_schemas.sql | ||
# <li value="6">Create a subscription to the master’s publication:</li> | # <li value="6">Create a subscription to the master’s publication:</li> | ||
sudo -u postgres psql <dbname> | sudo -u postgres psql <dbname> | ||
Line 66: | Line 72: | ||
sudo -u postgres psql <dbname> | sudo -u postgres psql <dbname> | ||
ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATION; | ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATION; | ||
+ | |||
+ | <h2>"How to drop a database for complete re-start or version upgrade:</h2> | ||
+ | * If you have a publication and distribute data to somebody - drop the publication: | ||
+ | [sudo -u postgres] psql <dbname> | ||
+ | DROP PUBLICATION <pubname>; | ||
+ | * If you subscribed to another database and have an active connection - drop the subscription: | ||
+ | [sudo -u postgres psql <dbname>] | ||
+ | DROP SUBSCRIPTION IF EXISTS <subname>; | ||
+ | * If you subscribed to another database but connection is broken: | ||
+ | ALTER SUBSCRIPTION <subname> DISABLE; | ||
+ | ALTER SUBSCRIPTION <subname> SET (slot=NONE); | ||
+ | DROP SUBSCRIPTION <subname>; | ||
+ | * Delete database: | ||
+ | [sudo -u postgres psql] | ||
+ | DROP DATABASE <dbname>; | ||
+ | * If you would like to remove the old PosgreSQL version: | ||
+ | sudo systemctl stop postgresql-11 | ||
+ | sudo systemctl disable postgresql-11 | ||
+ | sudo yum remove postgresql11 | ||
+ | * To start a new version of database go to "Replica side (from the scratch)" |
Revision as of 22:56, 3 February 2023
Contents
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:
- 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:
[sudo -u postgres] psql <dbname> CREATE PUBLICATION <pubname> FOR ALL TABLES; or CREATE PUBLICATION <pubname> FOR TABLES table1, table2, table3,...;
- 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). pg_dump -d <dbname> > <dbname>_schemas.sql
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):
- Install PostgresSQL v14.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>
- 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 or [sudo -u postgres] psql <dbname> < <dbname>_schemas.sql
- Create a subscription to the master’s publication:
sudo -u postgres psql <dbname> 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>.
- 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):
- 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
- Modify a subscription to the master’s publication:
sudo -u postgres psql <dbname> ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATION;
"How to drop a database for complete re-start or version upgrade:
- If you have a publication and distribute data to somebody - drop the publication:
[sudo -u postgres] psql <dbname> DROP PUBLICATION <pubname>;
- If you subscribed to another database and have an active connection - drop the subscription:
[sudo -u postgres psql <dbname>] DROP SUBSCRIPTION IF EXISTS <subname>;
- If you subscribed to another database but connection is broken:
ALTER SUBSCRIPTION <subname> DISABLE; ALTER SUBSCRIPTION <subname> SET (slot=NONE); DROP SUBSCRIPTION <subname>;
- Delete database:
[sudo -u postgres psql] DROP DATABASE <dbname>;
- If you would like to remove the old PosgreSQL version:
sudo systemctl stop postgresql-11 sudo systemctl disable postgresql-11 sudo yum remove postgresql11
- To start a new version of database go to "Replica side (from the scratch)"