| Table of Contents | ||||
|---|---|---|---|---|
|
Concept
Each facility has a database server. For each site the facility library is the main library. The libraries from other facilities are replications. These replications are read-only. This means that every site is independent but still benefits from the work and libraries another facility is doing.
In Postgres terms:
Use a Streaming Replication for Multi-Site setups.
Requirements
In each facility you have a database server up and running with Postgres installed.
| Info |
|---|
In this example Linux is used to setup the database servers. It should however work with any other operating system as well. |
Create primary database server
Configure primary server
change postgres.conf to enable networking
create a replication user
allow remote access in pg_hba.conf
...
| Code Block |
|---|
initdb -D /var/lib/postgresql/daselement-munich |
change postgres.conf to enable networking
| Code Block |
|---|
vim /var/lib/postgresql/daselement-munich/postgres.conf # uncomment and change the line: listen_addresses = '*' # and change the port to 5433 to avoid conflicts with any default instances that are running on port 5432 port = 5433 # start the Postgres server pg_ctl -D /var/lib/postgresql/daselement-munich start |
Create a user accounts
| Code Block |
|---|
# postgres is the default postgres database psql --port 5433 postgres # create user that is allowed to create new databases and read/write information CREATE ROLE dbuser LOGIN password 'password'; ALTER USER dbuser CREATEDB; # create replication user [local]:5433 postgres=# create user repuser replication; |
Create a replication user
Allow remote access in pg_hba.conf
...
The primary system is now ready. Let’s move on to the replica system!
Create replica database server
Configure replication server
| Note |
|---|
Now we are at a clean database server in another facility - e.g. Vancouver |
...
Now start the replica server:
| Code Block |
|---|
# start the Postgres server pg_ctl -D /var/lib/postgresql/daselement-munich-replica |
Any changes made in the primary database get synced/streamed to the replication server(s). If the replication server is down it will add the changes once it’s back online!
Configure library
In the other facility copy and import the library (.lib) from main facility:
...
| Info |
|---|
To reduce disk space and network traffic only sync the high-res source files when needed |
Advanced Setup
If you want to take it a step further it’s a good idea to look into these topics:
...
Here is a great video tutorial using PGPool-II to deal with these topics.
Monitoring
| Info |
|---|
Monitoring should be done on the primary database server |
To monitor the databases you can for example use pgwatch2
Troubleshooting
psql - commands
command | description |
|---|---|
| list all databases |
| |
| list all users |
| lists available publications |
| lists available subscriptions |
| change user password |
Can not start Postgres Database
| Code Block |
|---|
FATAL: could not create lock file "/var/run/postgresql/.s.PGSQL.5434.lock": Permission denied |
...
| Code Block |
|---|
sudo chmod a+w+r /var/lib/postgresql |
initdb: command not found
The initdb is located in the installation directory of Postgres.
...
| Code Block |
|---|
# create a symbolic link to initdb to access the command sudo ln -s /usr/lib/postgresql/14/bin/initdb /usr/local/bin # now you can run ... initdb |
pg_ctl: command not found
The pg_ctl is located in the installation directory of Postgres.
...
| Code Block |
|---|
# create a symbolic link to initdb to access the command sudo ln -s /usr/lib/postgresql/14/bin/pg_ctl /usr/local/bin # now you can run ... pg_ctl |
Unable to connect to server; FATAL: role “postgres“ does not exist
Seems like there is no user in the database called “postgres”.
To create a user use this command:
...