| 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
| Note |
|---|
Now we are at a clean database server in another facility - e.g. Vancouver |
...
| Code Block |
|---|
# start the Postgres server pg_ctl -D /var/lib/postgresql/daselement-munich-replica |
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:
...