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