5.3 Configuring a Replication Database User

This section shows how the replication database user is established.

All database servers must use a superuser as the replication database user.

The replication database user for each database server is specified by the user parameter in the BART configuration file as shown by the following:

[ACCTG]

host = 127.0.0.1

port = 5444

user = enterprisedb <=== Replication Database User

cluster_owner = enterprisedb

backup_name = acctg_%year-%month-%dayT%hour:%minute

archive_command = 'cp %p %a/%f'

description = "Accounting"

 

[MKTG]

host = 192.168.2.24

port = 5444

user = repuser <=== Replication Database User

cluster_owner = enterprisedb

backup_name = mktg_%year-%month-%dayT%hour:%minute

remote_host = enterprisedb@192.168.2.24

description = "Marketing"

 

[HR]

host = 192.168.2.24

port = 5432

user = postgres <=== Replication Database User

cluster_owner = enterprisedb

backup_name = hr_%year-%month-%dayT%hour:%minute

remote_host = postgres@192.168.2.24

copy_wals_during_restore = enabled

description = "Human Resources"

Add entries to the .pgpass file on each server to allow the BART user account to initiate a backup without being prompted for credentials. The .pgpass file is located in /opt/PostgresPlus/9.5AS/.pgpass.

127.0.0.1:5444:*:enterprisedb:password

192.168.2.24:5444:*:repuser:password

192.168.2.24:5432:*:postgres:password

For more information about using a .pgpass file, please see the PostgreSQL documentation at:

https://www.postgresql.org/docs/current/libpq-pgpass.html

While connected to MKTG on 192.168.2.24, the following CREATE ROLE command is given to create the replication database superuser:

CREATE ROLE repuser WITH LOGIN SUPERUSER PASSWORD 'password';

The pg_hba.conf file for the local Advanced Server, ACCTG is set as follows:

# TYPE DATABASE USER ADDRESS METHOD

 

# "local" is for Unix domain socket connections only

local all all md5

# IPv4 local connections:

host template1 enterprisedb 127.0.0.1/32 md5

host edb enterprisedb 127.0.0.1/32 md5

#host all all 127.0.0.1/32 md5

# IPv6 local connections:

host all all ::1/128 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local replication enterprisedb md5

host replication enterprisedb 127.0.0.1/32 md5

The pg_hba.conf file for the remote Advanced Server, MKTG is set as follows:

# TYPE DATABASE USER ADDRESS METHOD

 

# "local" is for Unix domain socket connections only

local all all md5

# IPv4 local connections:

host template1 repuser 192.168.2.22/32 md5

host all enterprisedb 127.0.0.1/32 md5

#host all all 127.0.0.1/32 md5

# IPv6 local connections:

host all all ::1/128 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local replication enterprisedb md5

host replication repuser 192.168.2.22/32 md5

The pg_hba.conf file for the remote PostgreSQL server, HR is set as follows:

# TYPE DATABASE USER ADDRESS METHOD

 

# "local" is for Unix domain socket connections only

local all all md5

# IPv4 local connections:

host template1 postgres 192.168.2.22/32 md5

host all all 127.0.0.1/32 md5

# IPv6 local connections:

host all all ::1/128 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local replication postgres md5

host replication postgres 192.168.2.22/32 md5