OpenResty Edge Database High Availability

# 1. Preamble

We use PostgreSQL as the database for Edge. We usually refer to the database used by Edge Admin as Edge Admin DB and the database used by Edge Log Server as Edge Log Server DB. Edge Admin DB and Edge Log Server DB can use the same PostgreSQL instance or cluster. We usually recommend configuring at least one standby database to ensure data security.

# 2. Configure the Master Server

You need to prepare the following information.

• MASTER_PORT: the port of MASTER, e.g. 5432
• USERNAME: The backup user, e.g. replicator
• PASSWORD: password of the backup user, e.g. examplepwd
• STANDBY_HOST: the address of STANDBY, e.g. 10.0.0.2

# 2.1 Create a Replication user

Login to the master server

# Replace: MASTER_PORT
/usr/local/openresty-postgresql12/bin/psql -h 127.0.0.1 -p MASTER_PORT -U postgres

Execute SQL

-- Replace: USERNAME、PASSWORD
create user USERNAME replication login encrypted password 'PASSWORD' connection limit -1;

# 2.2 Configure pg_hba.conf

Open the configuration file

sudo -u postgres vi /var/postgres12/data/pg_hba.conf

Add the following configuration

# Replace: USERNAME、STANDBY_HOST
host    replication     USERNAME      STANDBY_HOST/32             md5

# 2.3 Configure postgresql.conf

Open the configuration file

sudo -u postgres vi /var/postgres12/data/postgresql.conf

Modify the following fields

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 1000
archive_mode = on
archive_command = '/bin/true'
archive_timeout = 600

# 2.4 Restart

sudo systemctl restart openresty-postgresql12

# 3. Configure the Standby Server

You need to prepare the following information.

• MASTER_HOST: address of MASTER, e.g. 10.0.0.1
• MASTER_PORT: The port of MASTER, e.g. 5432
• USERNAME: the backup user (same as configured in Master), e.g. replicator
• PASSWORD: password of backup user (same as configured in Master), e.g. examplepwd

# 3.1 Stop

sudo systemctl stop openresty-postgresql12

# 3.2 Backup data from the Master Server

Make sure you have enough disk space before this step.

# Replace: MASTER_HOST、MASTER_PORT、USERNAME、PASSWORD
sudo -u postgres /usr/local/openresty-postgresql12/bin/pg_basebackup -h MASTER_HOST -p MASTER_PORT -U USERNAME -D /var/postgres12/data
# enter [% PASSWORD %]

If you get the error pg_basebackup: error: directory "/var/postgres12/data" exists but is not empty, please use another directory or delete this directory then re-run this command.

Next, please wait patiently for the sync to complete, it may take longer if you have more data.

# 3.3 Configure standby.signal

Open the configuration file

sudo -u postgres vi /var/postgres12/data/standby.signal

Add the following configuration

standby_mode = 'on'

# 3.4 Configure postgresql.conf

Open the configuration file

sudo -u postgres vi /var/postgres12/data/postgresql.conf

Modify the following fields

# Replace: MASTER_HOST、MASTER_PORT、USERNAME、PASSWORD
# max_connections should be greater than the master's configuration
max_connections = 120
primary_conninfo = 'host=MASTER_HOST port=MASTER_PORT user=USERNAME password=PASSWORD'
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
recovery_target_timeline = 'latest'

# 3.5 Restart

sudo systemctl restart openresty-postgresql12

# 3.6 Verification

Execute on the Master Server

/usr/local/openresty-postgresql12/bin/psql -x -c "select * from pg_stat_replication" -U postgres

An execution result similar to the following indicates a successful configuration

-[ RECORD 1 ]----+------------------------------
pid              | 29015
usesysid         | 22228
usename          | replicator
application_name | walreceiver
client_addr      | 192.168.122.1
client_hostname  |
client_port      | 38440
backend_start    | 2022-02-21 23:36:46.418257-08
backend_xmin     | 5222925
state            | streaming
sent_lsn         | 2/C5151CE8
write_lsn        | 2/C5151CE8
flush_lsn        | 2/C5151CE8
replay_lsn       | 2/C5151CE8
write_lag        | 00:00:00.000435
flush_lag        | 00:00:00.001187
replay_lag       | 00:00:00.001394
sync_priority    | 0
sync_state       | async
reply_time       | 2022-02-21 23:42:21.456685-08

# 4. Promote Standby to Master

# 4.1 Checking Master/Standby Server Status

sudo -u postgres /usr/local/openresty-postgresql12/bin/pg_controldata /var/postgres12/data

# Master Normal
Database cluster state:               in production

# Master Down
Database cluster state:               shut down

# Standby
Database cluster state:               in archive recovery

# 4.2 Promote Standby to Master

# Execute on the standby server
sudo -u postgres /usr/local/openresty-postgresql12/bin/pg_ctl promote -D /var/postgres12/data