Versions Compared

Old Version 33

changes.mady.by.user Jonas Kluger

Saved on

compared with

New Version Current

changes.mady.by.user Jonas Kluger

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel7

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

  1. change postgres.conf to enable networking

  2. create a replication user

  3. allow remote access in pg_hba.conf

Infonote

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

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

add line to allow access for the replication user

Code Block
vim /var/lib/postgresql/daselement-datamunich/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

Configure replication server

Note

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

Code Block
breakoutModefull-width
# 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:

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:
  1. update the library root
  2. update the database information
  3. lock the library (Lock Library option) to prevent editing and ingesting of new elements!

An option is to setup the main facility to automatically sync the Proxy Files to the other facilities when an element is ingested. This can be done with an additional transcoding task and a custom command task.
 Code Block
# Example:
your-sync-tool <paths.proxy.directory> /path/to/other/facility
your-sync-tool <paths.filmstrip.directory> /path/to/other/facility
your-sync-tool <paths.thumbnail.directory> /path/to/other/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:
  • high availability
  • load balancing
  • connection pooling
  • automatic fail-over
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
\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
 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:
...