Create 3 servers on the cloud of choice.
This is a diagram of the servers:
Node 1
On Node 1 we install postgres, start the postgresql service and add the port to firewall:
sudo apt install -y postgresql
sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo systemctl status postgresql
sudo ufw allow from 10.0.0.0/16 to any port 5432
Next, we open psql to create the replication user:
sudo -u postgres psql
CREATE USER ruser REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'rpassword';
CREATE USER dummy WITH SUPERUSER PASSWORD '123456';
We should open /etc/postgresql/14/main/pg_hba.conf and add the users:
host replication ruser 10.0.0.0/16 md5
host all dummy 10.0.0.0/16 md5
Now we should edit /etc/postgresql/14/main/postgresql.conf for replication:
listen_addresses = 'localhost,10.0.0.3' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 500 # (change requires restart)
wal_level = replica # minimal, replica, or logical
# (change requires restart)
wal_log_hints = on # also do full page writes of non-critical updates
# (change requires restart)
wal_compression = on # enable compression of full-page writes
max_wal_size = 1GB
min_wal_size = 80MB
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
shared_preload_libraries = 'pg_stat_monitor'
pg_stat_monitor.pgsm_query_max_len = 2048
pg_stat_monitor.pgsm_normalized_query = 1
pg_stat_monitor.pgsm_enable_query_plan = yes
Now, to install Percona pg_stat_monitor:
curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo apt install gnupg2 lsb-release ./percona-release_latest.generic_all.deb
sudo apt update
sudo percona-release setup ppg-14
sudo apt install percona-pg-stat-monitor14
Next we restart postgresql.
sudo systemctl restart postgresql
Node 2
On Node 2 we install postgres, start the postgresql service and add the port to firewall:
sudo apt install -y postgresql
sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo systemctl status postgresql
sudo ufw allow from 10.0.0.0/16 to any port 5432
Next, we open psql to create the replication user:
sudo -u postgres psql
CREATE USER ruser REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'rpassword';
CREATE USER dummy WITH SUPERUSER PASSWORD '123456';
We should open /etc/postgresql/14/main/pg_hba.conf and add the users:
host replication ruser 10.0.0.0/16 md5
host all dummy 10.0.0.0/16 md5
Now we should edit /etc/postgresql/14/main/postgresql.conf for replication:
listen_addresses = 'localhost,10.0.0.4' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 500 # (change requires restart)
wal_level = replica # minimal, replica, or logical
# (change requires restart)
wal_log_hints = on # also do full page writes of non-critical updates
# (change requires restart)
wal_compression = on # enable compression of full-page writes
max_wal_size = 1GB
min_wal_size = 80MB
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
shared_preload_libraries = 'pg_stat_monitor'
pg_stat_monitor.pgsm_query_max_len = 2048
pg_stat_monitor.pgsm_normalized_query = 1
pg_stat_monitor.pgsm_enable_query_plan = yes
Now, to install Percona pg_stat_monitor:
curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo apt install gnupg2 lsb-release ./percona-release_latest.generic_all.deb
sudo apt update
sudo percona-release setup ppg-14
sudo apt install percona-pg-stat-monitor14
Next we restart postgresql to check the updated settings
sudo systemctl restart postgresql
Now, let’s find out the data dir for postgres:
sudo -u postgres psql
SHOW data_directory;
data_directory
-----------------------------
/var/lib/postgresql/14/main
If everything is ok, we should stop postgresql on node 2 and prepare the replication:
sudo systemctl stop postgresql
sudo cp -pr /var/lib/postgresql/14/main /var/lib/postgresql/14/main_orig
sudo rm -rf /var/lib/postgresql/14/main
sudo mkdir /var/lib/postgresql/14/main
sudo chmod -R 775 /var/lib/postgresql/14/main
We should backup node 1:
sudo pg_basebackup -h 10.0.0.3 -D /var/lib/postgresql/14/main -U ruser -P -v -R -X stream
sudo chown -R postgres:postgres /var/lib/postgresql/14/main
sudo chmod -R 700 /var/lib/postgresql/14/main
If everything is’s ok, we should backup the configuration and start postgresql:
sudo systemctl start postgresql
To check if the replication is running, we open psql:
sudo -u postgres psql
SELECT * FROM pg_stat_wal_receiver;
And if we have 1 row, everything is ok.
Master
Now, let’s install pgpool2 and allow port 5432 on the firewall:
sudo apt install pgpool2 postgresql-client
sudo ufw allow 5432
Let’s edit the configuration file /etc/pgpool2/pgpool.conf:
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
# - pgpool Connection Settings -
listen_addresses = '192.168.10.10'
# Host name or IP address to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
port = 5432
# Port number
# (change requires restart)
socket_dir = '/var/run/postgresql'
# Unix domain socket path
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
listen_backlog_multiplier = 2
# Set the backlog parameter of listen(2) to
# num_init_children * listen_backlog_multiplier.
# (change requires restart)
serialize_accept = off
# whether to serialize accept() call to avoid thundering herd problem
# (change requires restart)
reserved_connections = 0
# Number of reserved connections.
# Pgpool-II does not accept connections if over
# num_init_chidlren - reserved_connections.
# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = '*'
# Host name or IP address for pcp process to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
pcp_port = 9898
# Port number for pcp
# (change requires restart)
pcp_socket_dir = '/var/run/postgresql'
# Unix domain socket path for pcp
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
# - Backend Connection Settings -
backend_hostname0 = '10.0.0.3'
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_weight0 = 1
# Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/var/lib/postgresql/main'
# Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Controls various backend behavior
# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
# or ALWAYS_MASTER
backend_application_name0 = 'postgresql-master.internal'
# walsender's application_name, used for "show pool_nodes" command
backend_hostname1 = '10.0.0.4'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/standby'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'postgresql-slave01.internal'
# - Authentication -
enable_pool_hba = on
# Use pool_hba.conf for client authentication
pool_passwd = 'pool_passwd'
# File name of pool_passwd for md5 authentication.
# "" disables pool_passwd.
# (change requires restart)
authentication_timeout = 60
# Delay in seconds to complete client authentication
# 0 means no timeout.
allow_clear_text_frontend_auth = on
# Allow Pgpool-II to use clear text password authentication
# with clients, when pool_passwd does not
# contain the user password
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
# - Concurrent session and pool size -
num_init_children = 100 # Number of concurrent sessions allowed
# (change requires restart)
max_pool = 10 # Number of connection pool caches per connection
# (change requires restart)
# - Life time -
child_life_time = 300
# Pool exits after being idle for this many seconds
child_max_connections = 0
# Pool exits after receiving that many connections
# 0 means no exit
connection_life_time = 0
# Connection to backend closes after being idle for this many seconds
# 0 means no close
client_idle_limit = 0
# Client is disconnected after being idle for that many seconds
# (even inside an explicit transactions!)
# 0 means no disconnection
#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------
connection_cache = on
# Activate connection pools
# (change requires restart)
# Semicolon separated list of queries
# to be issued at the end of a session
# The default is for 8.3 and later
reset_query_list = 'ABORT; DISCARD ALL'
#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
replication_mode = off
# Activate replication mode
# (change requires restart)
replicate_select = off
# Replicate SELECT statements
# when in replication mode
# replicate_select is higher priority than
# load_balance_mode.
insert_lock = on
# Automatically locks a dummy row or a table
# with INSERT statements to keep SERIAL data
# consistency
# Without SERIAL, no lock will be issued
lobj_lock_table = ''
# When rewriting lo_creat command in
# replication mode, specify table name to
# lock
# - Degenerate handling -
replication_stop_on_mismatch = off
# On disagreement with the packet kind
# sent from backend, degenerate the node
# which is most likely "minority"
# If off, just force to exit this session
failover_if_affected_tuples_mismatch = off
# On disagreement with the number of affected
# tuples in UPDATE/DELETE queries, then
# degenerate the node which is most likely
# "minority".
# If off, just abort the transaction to
# keep the consistency
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
# Activate load balancing mode
# (change requires restart)
ignore_leading_white_space = on
# Ignore leading white spaces of each query
white_function_list = ''
# Comma separated list of function names
# that don't write to database
# Regexp are accepted
black_function_list = 'currval,lastval,nextval,setval'
# Comma separated list of function names
# that write to database
# Regexp are accepted
black_query_pattern_list = ''
# Semicolon separated list of query patterns
# that should be sent to primary node
# Regexp are accepted
# valid for streaming replicaton mode only.
database_redirect_preference_list = ''
# comma separated list of pairs of database and node id.
# example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
# valid for streaming replicaton mode only.
app_name_redirect_preference_list = ''
# comma separated list of pairs of app name and node id.
# example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
# valid for streaming replicaton mode only.
allow_sql_comments = off
# if on, ignore SQL comments when judging if load balance or
# query cache is possible.
# If off, SQL comments effectively prevent the judgment
# (pre 3.4 behavior).
disable_load_balance_on_write = 'transaction'
# Load balance behavior when write query is issued
# in an explicit transaction.
# Note that any query not in an explicit transaction
# is not affected by the parameter.
# 'transaction' (the default): if a write query is issued,
# subsequent read queries will not be load balanced
# until the transaction ends.
# 'trans_transaction': if a write query is issued,
# subsequent read queries in an explicit transaction
# will not be load balanced until the session ends.
# 'always': if a write query is issued, read queries will
# not be load balanced until the session ends.
statement_level_load_balance = off
# Enables statement level load balancing
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on
# Activate master/slave mode
# (change requires restart)
master_slave_sub_mode = 'stream'
# Master/slave sub mode
# Valid values are combinations stream, slony
# or logical. Default is stream.
# (change requires restart)
# - Streaming -
sr_check_period = 0
# Streaming replication check period
# Disabled (0) by default
sr_check_user = 'ruser'
# Streaming replication check user
# This is necessary even if you disable
# streaming replication delay check with
# sr_check_period = 0
sr_check_password = 'rpassword'
# Password for streaming replication check user.
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
sr_check_database = 'postgres'
# Database name for streaming replication check
delay_threshold = 0
# Threshold before not dispatching query to standby node
# Unit is in bytes
# Disabled (0) by default
#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
health_check_period = 0
# Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = 'ruser'
# Health check user
health_check_password = 'rpassword'
# Password for health check user
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
health_check_database = ''
# Database name for health check. If '', tries 'postgres' frist, then 'template1'
health_check_max_retries = 0
# Maximum number of times to retry a failed health check before giving up.
health_check_retry_delay = 1
# Amount of time to wait (in seconds) between retries.
connect_timeout = 10000
# Timeout value in milliseconds before giving up to connect to backend.
# Default is 10000 ms (10 second). Flaky network user may want to increase
# the value. 0 means no timeout.
# Note that this value is not only used for health check,
# but also for ordinary conection to backend.
Next, we should generate a secret for password hashing:
openssl rand -base64 32 > /home/user/.pgpoolkey
sudo cp /home/user/.pgpoolkey /var/lib/postgresql/
sudo chown postgres:postgres /var/lib/postgresql/.pgpoolkey
sudo chmod 600 /var/lib/postgresql/.pgpoolkey
Now let’s edit /etc/pgpool2/pool_hba.conf and add our users:
host all ruser all scram-sha-256
host all dummy all scram-sha-256
Running to register username with sha25sum:
sudo pg_enc -m -k /var/lib/postgresql/.pgpoolkey -f /etc/pgpool2/pgpool.conf -u ruser -p
sudo pg_enc -m -k /var/lib/postgresql/.pgpoolkey -f /etc/pgpool2/pgpool.conf -u dummy -p
Finally let’s start pgpool2:
sudo systemctl start pgpool2
Documentation and links:
- Postgres Replication Guide - https://medium.com/@umairhassan27/setting-up-postgresql-replication-on-slave-server-a-step-by-step-guide-1ff36bb9a47f
- Percona pg_stat_monitor install - https://github.com/percona/pg_stat_monitor?tab=readme-ov-file#installation-guidelines
- setting replication for postgres - https://medium.com/@umairhassan27/setting-up-postgresql-replication-on-slave-server-a-step-by-step-guide-1ff36bb9a47f