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.

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

This is the database server in our facility in e.g. Munich

initdb -D /var/lib/postgresql/daselement-munich

change postgres.conf to enable networking

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

# 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

add line to allow access for the replication user

vim /var/lib/postgresql/daselement-data/pg_hba.conf

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
# IPv4 local connections
host    all             repuser             localhost               trust

# restart the Postgres server because of changes in the config files
pg_ctl -D /var/lib/postgresql/daselement-munich restart

The primary system is now ready. Let’s move on to the replica system!

Create replica database server

Now we are at a clean database server in a different facility. E.g. Vancouver

# create the replica Postgres server
# this command pg_basebackup copies the files from the primary database

pg_basebackup -h db-munich -U repuser --checkpoint=fast -D /var/lib/postgresql/daselement-munich-replica/ -R --slot=daselement_munich -C --port 5432

In the replica directory you will find these two files

standby.signal

this file defines that the database that it is a replica

postgresql.auto.conf

contains information to connect to the primary server

Now start the replica server

# start the Postgres server
pg_ctl -D /var/lib/postgresql/daselement-munich-replica

In the other facility import the library (.lib) from main facility.
Make sure to lock the library (Lock Library option) to prevent editing and ingesting of new elements!

Advanced Setup

If you want to take it a step further it’s a good idea to look into these topics:

high availability
load balancing
connection pooling
automatic fail-over

Here is a great video tutorial using PGPool-II to deal with these topics.

Monitoring

Monitoring should be done on the primary database server

To monitor the databases you can for example use pgwatch2

Troubleshooting

psql - commands

command	description
`\l`	list all databases
`\dt`
`\du`	list all users
`\dRp`	lists available publications
`\dRs`	lists available subscriptions
`ALTER USER user_name WITH PASSWORD 'new_password';`	change user password

Can not start Postgres Database

FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.5434.lock": Permission denied

Solution is to fix the permissions on the folder. For example like this:

sudo chmod a+w+r /var/lib/postgresql

initdb: command not found

The initdb is located in the installation directory of Postgres.

Fix for Ubuntu:

# 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.