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 mpc_sbn copy of database from SBN, please, consider "Master" side paragraph FOR YOUR INFORMATION only. Start from the "Replica" side (from the scratch).
listen_addresses should be listening to the replica address.
wal_level = logical
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>;
In the hosts section add a line like:
host <dbname> <username> <user_IP>/32 md5
[sudo -u postgres] psql <dbname>
CREATE PUBLICATION <pubname> FOR ALL TABLES;
or
CREATE PUBLICATION <pubname> FOR TABLES table1, table2, table3,...;
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>;
sudo -u postgres psql
CREATE DATABASE <dbname>
[sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
[sudo -u postgres] psql -d <dbname> -a < <table2>_schema.sql
[sudo -u postgres] psql -d <dbname> -a < <table3>_schema.sql
or
[sudo -u postgres] psql <dbname> < <dbname>_schemas.sql
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>.
sudo -u postgres psql mpc_sbn
SELECT * FROM neocp_obs_archive;
Another check:
sudo -u postgres psql mpc_sbn
SELECT * FROM pg_stat_replication;
Another check: View as a sudo latest log-file: [psql_path]/11/data/log/postgresql-[Wed].log
[sudo -u postgres] psql -d <dbname> -a < <table1>_schema.sql
sudo -u postgres psql <dbname>
ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATION;
[sudo -u postgres] psql <dbname>
DROP PUBLICATION <pubname>;
[sudo -u postgres psql <dbname>]
DROP SUBSCRIPTION IF EXISTS <subname>;
ALTER SUBSCRIPTION <subname> DISABLE;
ALTER SUBSCRIPTION <subname> SET (slot_name=NONE);
DROP SUBSCRIPTION <subname>;
[sudo -u postgres psql]
DROP DATABASE <dbname>;
sudo systemctl stop postgresql-11
sudo systemctl disable postgresql-11
sudo yum remove postgresql11
As a minimum: https://pgtune.leopard.in.ua/#/
More detailed: https://pgconfigurator.cybertec-postgresql.com/
SSD storage for data-dir.
Based on experience, it's the most critical for better performance of the database and replication. Even more important, than network bandwidth.RAM memory.
Better to have more bad / slow memory than less good / fast memory.CPU.
Multi-core and fast processor(s) could make a small advantage for database and replication performance.
Example of hardware specifications for the server to replicate the MPC Postgres database:
CPU - quad core or more
RAM - 32-48 GB
SSD - 4 TB
Network - 1-10 Gbps would be a good compliment.
Please avoid any hibernating devices like desktop or especially laptop.
If you have any issues with existing subscription / replication slot, please, try to resolve them first. In case of emergency, drop the existing subscription first, drop data from all tables, and then create a new subscription (with the same or different name).
ALTER SUBSCRIPTION <your_sub> DISABLE;
CREATE INDEX idx_table_column ON table(column);
ALTER SUBSCRIPTION <your_sub> ENABLE;
ALTER SUBSCRIPTION <your_sub> DISABLE;
CREATE TABLE distributors (
did integer PRIMARY KEY,
name varchar(40)
);
ALTER SUBSCRIPTION <your_sub> ENABLE;
Version 1.0, 9/28/2023
These terms of service ("Terms") govern your replication, access, and use of the data distributed from PostgreSQL database ("Database", or alternatively, the MPCDB), a public copy of the Minor Planet Center's (MPC's) live database, hosted from the NASA Planetary Data System's Small Bodies Node (SBN), located at the University of Maryland. This Database contains all publicly distributed data collected and produced by the MPC, and has live copies of the central processing tables. The database design base is Postgres and uses the Postgres built-in subscription capability.
Descriptions of the database content and structure are provided elsewhere (see the schema posted on the MPC's website: https://data.minorplanetcenter.net/postgres-schema/schema.html). By replicating, accessing, or using the Database, you agree to be bound by these Terms. If you disagree with these Terms, you may not replicate, access, or use the Database.
We offer a service that allows entities to replicate, access, and use data from our Database for their internal purposes. SBN is distributing the Database from the Minor Planet Center (MPC) as a courtesy.
We grant you a non-exclusive, non-transferable, non-sublicensable license to replicate, access, and use our database's data for your internal purposes.
All intellectual property rights in the Database, including but not limited to copyrights, trademarks, patents, and trade secrets, are owned by us or our licensors. To prevent confusion, any modifications or derived data products must be explicitly and clearly marked as such if they are redistributed.
You agree not to use the Database for any illegal purpose. You may not replicate, access, or use the Database in violation of these Terms or any applicable law or regulation. You may not use the Database to harass, defame, or threaten any person or entity.
SBN and MPC are not responsible for any damages, losses, or liabilities arising from your use of the Database, including but not limited to any errors, omissions, interruptions, or delays. We do not guarantee the accuracy or completeness of any data in the Database, and we are not liable for any reliance on such data. Users are responsible for their resource management.
This service can be negatively impacted by users at an individual endpoint. We may therefore find it necessary to terminate your access to the Database at any time for any reason. Some occurrences that may result in such termination are mentioned in Terms 11-13 below. This software should not be run on a machine that is not intended to be online 24 hours per day. Alternative means of acquiring specific information within the database will be provided; please contact SBN (see Term 10 below) for these services.
These Terms are governed by and construed in accordance with the laws of the jurisdiction in which we are located. Any dispute arising from or relating to these Terms shall be resolved through binding arbitration in accordance with the rules of the American Arbitration Association.
We reserve the right to modify or update these Terms at any time. If we make material changes to these Terms, we will notify you by email or by posting a notice on our website (https://sbnmpc.astro.umd.edu/MPC_database/statusDB.shtml). Your continued use of the Database following any such changes constitutes your acceptance of the revised Terms.
The User is responsible for responding reasonably to SBN requests related to the Database distribution service, safety, security, or integrity.
If you have any questions about these Terms or the Database, please contact us using the MPC helpdesk under the category of "Database Replication".
The live copy of the relational system is a Postgres database. To ensure integrity and longevity of the distribution system, SBN requires that the system to which the live copy is distributed (the user's system) runs an operating system that supports the earliest supported Postgres version and distribution.Currently (9/28/23) the SBN supports Postgres V14 and higher.
The MPCDB tables may grow quickly in size with the advent of new surveys. Consequently, SBN requires the user's system to complete the initial download within 3 days. Furthermore, the user's full system must be able to keep up with the database throughput rate, requiring consideration of factors such as system specification, system configuration, and data transfer bandwidth. Notional thresholds regarding these factors will be provided in the Best Practices section, and may be upgraded depending on the average database volume growth from successive generational evolution of survey detection volumes.
In order to avoid backlogs in distribution transactions, we require that the user's do not install the system on a transient server, such as a laptop that often travels with prolonged downtime. We require that the downtime be no more than an average of 14 days (cumulative) per year. The user is to notify SBN if a subscription is no longer being used.
We request no more than 4 copies be distributed from SBN to the same institution without contacting the SBN regarding additional copies; SBN grants permissions to duplicate the database within the institution.
The MPC helpdesk shall be used for first reporting user related problems, rather than contacting SBN personnel directly. The MPCDB stewards at the SBN receive tickets through the MPC's helpdesk.
Violation of any parameter from below list will result in (semi)-automatic termination of the distribution to the institution. Replication could be restored after fixing the source of violation. Technical thresholds are subject to periodic review and modification.