Deploying PostgreSQL

Introduction

PostgreSQL is an open-source, highly-stable database system that provides SQL features, such as foreign keys, subqueries, triggers, and different user-defined data types and their 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 tutorial describes how you can deploy PostgreSQL in Huawei Cloud EulerOS 2.0.

Preparations

Prepare two ECSs and assign a public IP address or EIP to each ECS.

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

Procedure

Configure the master 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
systemctl daemon-reload
```
4. Run the following command to log in to the database as user postgres:
```
su - postgres
```
5. Execute the following statement to access the psql terminal:
```
psql
```
6. Execute the following statement to create an account and set the password and permission:
```
CREATE ROLE replica login replication ENCRYPTED PASSWORD 'replicationxxx!';
```
  replica is the account name, and replicationxxx! is the password. replica has the login and replication permissions.
7. Execute the following statement to view the created account:
```
SELECT usename FROM pg_user;
```
  Information similar to the following is displayed.
8. Execute the following statement to view the permissions:
```
SELECT rolname FROM pg_roles;
```
  Information similar to the following is displayed.
9. Execute the following statement to exit the psql terminal:
```
\q
```
10. Execute the following statement to 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 slave mode>          md5
host    replication     replica        <IPv4 address range of the slave mode>          md5
```
  Enter the IPv4 address range of the slave 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 slave node must be greater than that of the master 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 slave PostgreSQL node.
1. Run the following command to install the server, client, and related components on the slave node:
```
dnf install postgresql postgresql-contrib postgresql-server
```
2. Run the following command to create a backup directory on the slave node:
```
pg_basebackup -D /var/lib/pgsql/data -h <IP address of the master node> -p 5432 -U replica -X stream -P -R
```
  -X indicates the required WAL stream. -P indicates the progress. -h indicates the IP address of the master node. -p indicates the port number of the master node. -U indicates the account for logging in to the master node. -R indicates that the replication configuration is automatically written.
  
  If information similar to the following is displayed, the backup is successful.
  
  The standby.signal file is generated in the /var/lib/pgsql/data directory, and information about the master database connections is automatically written into the postgresql.auto.conf file.
3. Run the following command to modify 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 start postgresql
```
Check whether the configuration is successful.
1. Run the following command on the slave node to check whether the node has become the slave node:
```
sudo -u postgres psql -c "SELECT pg_is_in_recovery()"
```
  If t is displayed, the node has become the slave node.
2. Run the following command on the master node to obtain the slave node information:
```
sudo -u postgres psql -x -c "SELECT * FROM pg_stat_replication" -d postgres
```
  If information similar to the following is displayed, the configuration is successful.