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.

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-data 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

END STREAMING

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.