...

In each facility you have a database server up and running with Postgres installed.

START STREAMING

This is for a streaming replication! We want to have a logical replication.

Create primary database server

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

Configure primary server

1. change postgres.conf to enable networking

2. create a replication user

3. allow remote access in pg_hba.conf

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 production port 5432
port = 5433

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

create a replication user

# create replication user
psql --port 5433 postgres

[local]:5433 postgres=# create user repuser replication;

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
pg_ctl -D /var/lib/postgresql/daselement-munich restart

...

Create replica database server

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

...

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

To monitor the databases you can for example use pgwatch2

...

1. initiate 2 new PostgresSQL database clusters

2. Configure Publisher server with “wal_level=logical“

3. Start the instance

4. Create a database and the tables

Configure primary server

# create database cluster
initdb -D /var/lib/postgresql/daselement-munich_publication

...

# access the database
psql --port=5432 postgres

# create user in the database
CREATE ROLE postgres LOGIN password 'postgres';

START STREAMING

This is for a streaming replication! We want to have a logical replication.

Create primary database server

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

Configure primary server

1. change postgres.conf to enable networking

2. create a replication user

3. allow remote access in pg_hba.conf

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 production port 5432
port = 5433

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

create a replication user

# create replication user
psql --port 5433 postgres

[local]:5433 postgres=# create user repuser replication;

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

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

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