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.START STREAMING
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
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 a replication user
Code Block |
---|
# create replication user # postgres is the default postgres database psql --port 5433 postgres [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
Infonote |
---|
Now we are at a clean database server in a different facility. Eanother facility - e.g. Vancouver |
Code Block | breakoutMode | full-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:
| this file defines that the database that it is a replica |
| 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 |
END STREAMING
Monitoring
...
Any changes made in the primary database
...
To monitor the databases you can for example use pgwatch2
Setup VMs
install Postgres
START logical replication
Configure Logical Replication
initiate 2 new PostgresSQL database clusters
Configure Publisher server with “wal_level=logical“
Start the instance
Create a database and the tables
Configure primary server
Code Block |
---|
# create database cluster
initdb -D /var/lib/postgresql/daselement-munich_publication |
Edit the config to set the primary database cluster to the Logical Replication mode
Code Block |
---|
# edit postgresql.conf
vi /var/lib/postgresql/daselement-munich_publication/postgresql.conf |
Code Block |
---|
# edit line for wal_level to logical
wal_level = logical |
Now start the database cluster
Code Block |
---|
# start the database cluster
pg_ctl -D /var/lib/postgresql/daselement-munich_publication start |
Create Postgres Users - make sure that the Users exist in all the other databases as well
Code Block |
---|
# access the database via psql
psql --port=5432 postgres
# create superuser for IT/admins and pipeline TDs
CREATE ROLE postgres LOGIN password 'postgres';
ALTER USER root CREATEDB;
ALTER USER root CREATEROLE;
ALTER USER root SUPERUSER;
ALTER USER root REPLICATION;
# create user that is allowed to create new databases and read/write information
CREATE ROLE user_daselement LOGIN password 'password';
ALTER USER root CREATEDB;
# create replication user that is used by the other facilites
CREATE ROLE user_replication WITH REPLICATION LOGIN password 'password';
# exit the database from psql
\q |
Move to a supervisor/lead artists workstation.
Now create a new library inside the software. Use the user_daselement to connect to the databas.
...
Hurray, we have the main library for facility Munich running.
The next step is create a publication for this newly create database. Back to the Postgres server.
Code Block |
---|
# log in to the newly created database
psql --port=5432 de_munich_vfxelements |
We have to create a publication for this library/database that all the other facilities can later subscribe to.
Code Block |
---|
# give the user_replication the correct priviliges to create a publication
GRANT ALL PRIVILEGES ON DATABASE de_munich_vfxelements TO user_replication;
GRANT ALLPRIVILEGES ON ALL TABLES IN SCHEMA public to user_replication;
# create publication for all tables
create publication muc_pub for all tables;
# > the expected output is: CREATE PUBLICATION
# to show all publication use this command
\dRp |
Configure subscriber server
Create subscriber database cluster
copy database Schema from the primary database
create subscription to primary database
1 create database
Now let’s setup a database in a different facility
Code Block |
---|
# create database cluster
initdb -D /var/lib/postgresql/daselement-munich_subscription
# start the database cluster
pg_ctl -D /var/lib/postgresql/daselement-munich_subscription start |
2 copy schema
Postgres replication does not support the Postgres Data Definition Language (DLL) commands.
For us this means that we have to copy over the Database Schema from the Primary Database to the subscriber server. This step only needs to be done once.
Code Block |
---|
# create database from primary database
psql --port=5460 postgres
# create an empty database where the information will be replicated to
create database rep_de_munich_vfxelements
# expected ouptut: CREATE DATABASE |
Now copy over all the information from the primary to the subscriber. Execute this command on the primary database server.
Code Block | ||
---|---|---|
| ||
# use pg_dump to copy the data
pg_dump -s de_munich_vfxelements -p 5432 | psql -h 192.168.178.50 -p 5432 rep_de_munich_vfxelements |
3 create subscription
The last step is now to subscribe to the primary facility publication.
dbname = name of the database in the munich facility (muc_pub)
host = ip-of-publisher-postgres-server (e.g. the munich postgres server)
user = must be a superuser or replication role
Code Block | ||
---|---|---|
| ||
create subscription muc_subscription connection 'dbname=de_munich_vfxelements host=192.168.178.50 user=user_replication port=5433' publication muc_pub; |
Configure Logical Replication
Create a publication on the Publish Server
Create a subscription on the Subscriber Server
Test the replication
END logical replication
Troubleshooting
Here are some helpful commands
pg_lsclusters
- show all database clusters on the current machine
psql 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:
update the library root
update the database information
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 |
---|---|
| 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:
Code Block |
---|
# access the database
psql --port=5432 postgres
# create user in the database
CREATE ROLE postgres LOGIN password 'postgres'; |
Permission denied for table
The subscription server outputs some errror message like this:could not start initial contents copy for table "public.feature": ERROR: permission denied for table feature
Make sure that the User has the correct permission on the primary database server.
Can be fixed with this command:
Code Block |
---|
GRANT ALLPRIVILEGES ON ALL TABLES IN SCHEMA public to user_replication; |