Configuring OpenResty Edge Database High Availability Using Interactive Scripts

# 1. Introduction

OpenResty Edge uses PostgreSQL as its database system. In this architecture, we refer to the database used by Edge Admin as the Edge Admin DB, and the database used by Edge Log Server as the Edge Log Server DB. These two databases can share the same PostgreSQL instance or cluster.

To ensure data security and system availability, we strongly recommend configuring at least one standby server. This guide will walk you through the process of configuring high availability for OpenResty Edge databases using interactive scripts, applicable to both PostgreSQL 9 and 12 versions.

# 2. Configuring the Master

1. SSH into the PG Master machine

2. Download the configuration script:

   curl -O https://openresty.com/client/oredge/openresty-edge-db-ha-master.sh
   

3. Execute the configuration script:

   sudo bash openresty-edge-db-ha-master.sh
   

4. Follow the prompts to enter the following information:

   • Standby host address: Used for configuring access control, e.g., 192.168.0.2
   • Master data directory: Default is /var/postgres12/data (PG12) or /var/postgres/data (PG9)
   • Replication username: Used for data replication, default is replicator
   • Replication user password: For new users, you can use a randomly generated password

5. Confirm restarting the PostgreSQL service

6. After the script completes, it will display the master configuration information. Please save this information securely.

# 3. Configuring the Standby

1. SSH into the PG Standby machine

2. Download the configuration script:

   curl -O https://openresty.com/client/oredge/openresty-edge-db-ha-standby.sh
   

3. Execute the configuration script:

   sudo bash openresty-edge-db-ha-standby.sh
   

4. Follow the prompts to enter the following information:

   • Standby port: Default is 5432
   • Standby data directory: Default is /var/postgres12/data (PG12) or /var/postgres/data (PG9)
   • Master host address: e.g., 192.168.0.1
   • Master port: Default is 5432
   • Replication username: Same as configured in the master
   • Replication user password: Same as configured in the master

Note: If the specified data directory is not empty, the script will ask if you want to clear it. Ensure that the data in the directory can be safely deleted; otherwise, please back it up first.

# 4. Verifying the Configuration

Execute the following command on the master machine to verify the replication status:

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

# For PG9
/usr/local/openresty/postgresql/bin/psql -x -c "select * from pg_stat_replication" -U postgres

If the configuration is successful, you will see output similar to the following:

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

# 5. Promoting the Standby to Master

In case of a master failure, you may need to promote the standby to become the new master.

# 5.1 Checking Master-Standby Replication Status

Execute the following command on the respective machine:

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

# For PG9
sudo -u postgres /usr/local/openresty/postgresql/bin/pg_controldata /var/postgres/data

Possible states:

• Master normal: Database cluster state: in production
• Master failure: Database cluster state: shut down
• Standby: Database cluster state: in archive recovery

# 5.2 Promoting the Standby to Master

Execute on the standby:

# For PG12
sudo -u postgres /usr/local/openresty-postgresql12/bin/pg_ctl promote -D /var/postgres12/data

# For PG9
sudo -u postgres /usr/local/openresty/postgresql/bin/pg_ctl promote -D /var/postgres/data

After successful promotion, the standby will become the new master and can accept read and write operations. Note that you need to update the database connection information in your applications and consider configuring a new standby for the new master to maintain high availability.