Deploying PostgreSQL

Introduction

PostgreSQL is an open-source, highly-stable database system that provides SQL features, such as foreign keys, subqueries, triggers, and user-defined data types and functions. It further enhances the SQL language and provides some fine-grained capabilities to scale data workloads. It is mainly used in mobile, network, geospatial, and analysis applications and is known as the most advanced open source database in the industry. This section describes how to deploy PostgreSQL in HCE 2.0.

Preparations

Prepare two ECSs and assign a public IP address or an EIP to each of them. One ECS is used as the primary node, and the other is used as the standby node.

Ensure that inbound security group rules allow traffic to flow to the ECSs over port 5432.

Prerequisites

A yum repository has been configured. For details about how to configure a yum repository accessed over the Internet, see Configuring Repositories and Installing Software for HCE.

Procedure

Configure the primary PostgreSQL node.
1. Run the following command to install the server, client, and related components:
```
dnf install postgresql postgresql-contrib postgresql-server
```
2. Run the following command to initialize the database:
```
postgresql-setup --initdb --unit postgresql
```
3. Run the following commands in sequence to start PostgreSQL and check the PostgreSQL status:
```
systemctl start postgresql
systemctl status postgresql
```
  If active (running) is displayed, PostgreSQL is started.
  To set PostgreSQL to automatically enable upon system boot, run the following commands:
```
systemctl enable postgresql
```
4. Log in to the database as user postgres:
```
su - postgres
```
5. Access the psql terminal.
```
psql
```
6. Create an account and set the password and permissions.
```
CREATE ROLE replica login replication ENCRYPTED PASSWORD 'xxxxxx';
```
  replica is the account name. replica has the login and replication permissions. xxxxxx is the password.
7. Check the new account.
```
SELECT usename FROM pg_user;
```
  If the result shown in Figure 1 is displayed, the account is created successfully.
  
  Figure 1 New account
8. Check the new permissions.
```
SELECT rolname FROM pg_roles;
```
  If the result shown in Figure 2 is displayed, permissions are created successfully.
  
  Figure 2 New permissions
9. Exit the psql terminal.
```
\q
```
10. Exit user postgres.
```
exit
```
11. Edit the /var/lib/pgsql/data/pg_hba.conf file to configure the replica user whitelist.
  Find IPv4 local connections and add the following information:
```
host    all             all             <IPv4 address range of the secondary node>          md5
host    replication     replica        <IPv4 address range of the secondary node>          md5
```
  Enter the IPv4 address range of the secondary node.
12. Open the /var/lib/pgsql/data/postgresql.conf file and configure the following parameters:
```
listen_addresses = '*' # Private IP address listened on
max_connections = 100 # Maximum number of connections. The value of max_connections of the secondary node must be greater than that of the primary node.
wal_level = hot_standby # Enable the hot standby mode.
synchronous_commit = on # Enable synchronous replication.
max_wal_senders = 32 # Maximum number of synchronization processes
wal_sender_timeout = 60s # Timeout for the streaming replication host to send data
```
13. Run the following command to restart PostgreSQL:
```
systemctl restart postgresql
```
Configure the secondary PostgreSQL node.
1. Run the following command to install the server, client, and related components on the secondary node:
```
dnf install postgresql postgresql-contrib postgresql-server
```
2. Create a backup directory on the secondary node.
```
pg_basebackup -D /var/lib/pgsql/data -h <IP address of the primary node> -p 5432 -U replica -X stream -P -R
```
  The options are described as follows:
  
  -D: backup directory
  
  -h: IP address or server name of the primary database
  
  -p: port of the primary database
  
  -U: username for connecting to the database
  
  -X: WAL file replication mode (stream indicates streaming replication)
  
  -P: progress
  
  -R: automatically writes replication settings
  
  If information shown in Figure 3 is displayed, the backup is successful.
  
  Figure 3 Successful backup
  
  The standby.signal file is generated in the /var/lib/pgsql/data directory, and information about the primary database connections is automatically written into the postgresql.auto.conf file.
3. Run the following command to change the user group that the directory belongs to:
```
chown -R postgres.postgres /var/lib/pgsql/data
```
4. Run the following command to restart PostgreSQL:
```
systemctl restart postgresql
```
Check whether the configuration is successful.
1. Run the following command on the secondary node to check whether the node is configured successfully:
```
sudo -u postgres psql -c "SELECT pg_is_in_recovery()"
```
  If information shown in Figure 4 is displayed, the secondary node is configured successfully.
  
  Figure 4 Successful configuration of the secondary node
2. Run the following command on the primary node to obtain the secondary node information:
```
sudo -u postgres psql -x -c "SELECT * FROM pg_stat_replication" -d postgres
```
  If information shown in Figure 5 is displayed, the configuration is successful.
  
  Figure 5 Successful configuration