PostgreSQL is an open source relational database management system emphasizing scalability and standard compliance. PostgreSQL is ideal for enterprise-level complex online transaction processing (OLTP) systems. It supports NoSQL (JSON/XML/hstore) and Geographic Information System (GIS) data types. Featuring strong reliability and data integrity, PostgreSQL is a suitable for websites, location application systems, complex data object processing and other use cases.
This document describes how to build a PostgreSQL system on a CVM instance running CentOS 7.
This document uses the following software as an example to build PostgreSQL.
Linux: Linux operating system. This document uses CentOS 7.6 as an example.
PostgreSQL: Relational database management system. This document uses PostgreSQL 11.2 as an example.
yum update -y
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql11
yum install postgresql11-server
/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl start postgresql-11
systemctl enable postgresql-11
postgres
user.su - postgres
psql
postgres
user.ALTER USER postgres WITH PASSWORD 'Custom password';
postuser
) and set the password, login permission and backup permission.create role account name login replication encrypted password 'Custom password';
For example, use the following command to create a database account naming postuser
with the password postuser
:create role postuser login replication encrypted password 'postuser';
SELECT usename from pg_user;
If the following result is returned, it indicates that the account has been successfully created.usename
----------
postgres
postuser
(2 rows)
SELECT rolname from pg_roles;
If the following result is returned, it indicates that the permission has been successfully set.rolname
----------
postgres
postuser
(2 rows)
pg_hba.conf
file.vim /var/lib/pgsql/11/data/pg_hba.conf
IPv4 local connections
.host all all <IPv4 IP range of the secondary node’s VPC> md5 #Enable the MD5 password encryption for connections in the IP ranges of the VPC
host replication database account <IPv4 IP range of the secondary node’s VPC> md5 ##Allow data synchronization from the `replication` database.
For example, if the database account is postuser
and the IPv4 IP range of the secondary node’s VPC is 192.10.0.0/16
, add the following content to IPv4 local connections
:host all all 192.10.0.0/16 md5
host replication postuser 192.10.0.0/16 md5
postgresql.conf
file.vim /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = 'xxx.xxx.xxx.xxx' #The private IP addresses that are listened on.
max_connections = 100 #The maximum connections. The value of `max_connections` for the secondary node must be greater than that for the primary node
wal_level = hot_standby #Enable hot standby mode.
synchronous_commit = on #Enable synchronous replication
max_wal_senders = 32 #The maximum number of synchronization processes
wal_sender_timeout = 60s ##The timeout value for the streaming replication instance to send data.
systemctl restart postgresql-11
yum update -y
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql11
yum install postgresql11-server
pg_basebackup -D /var/lib/pgsql/11/data -h Private IP of primary node -p 5432 -U Database account -X stream -P
For example, if the private IP of the primary node is 192.10.123.321
, and the database account is postuser
, run the following command:pg_basebackup -D /var/lib/pgsql/11/data -h 192.10.123.321 -p 5432 -U postuser -X stream -P
Enter the password as prompted, and press Enter. If the following is returned, it indicates that the backup directory has been successfully created.Password:
24526/24526 kB (100%), 1/1 tablespace
cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf
recovery.conf
file.vim /var/lib/pgsql/11/data/recovery.conf
standby_mode = on #Declare the secondary node
primary_conninfo = ‘host=<Private IP of the primary node> port=5432 user=Database account password=Database password’ #Connection information of the primary node
recovery_target_timeline = ‘latest’ #Synchronize the latest data by using streaming replication
postgresql.conf
file.vim /var/lib/pgsql/11/data/postgresql.conf
listen_addresses= 'xxx.xx.xx.xx' #The private IP addresses that are listened on.
max_connections = 1000 #The maximum connections. The value of `max_connections` for the secondary node must be greater than that for the primary node
hot_standby = on #Enable hot standby mode
max_standby_streaming_delay = 30s #The maximum delay for streaming replication
wal_receiver_status_interval = 1s #The maximum interval for the secondary node to report its status to the primary node
hot_standby_feedback = on #Enable the secondary node to report errors during replication.
chown -R postgres.postgres /var/lib/pgsql/11/data
systemctl start postgresql-11
systemctl enable postgresql-11
Perform the following to verify the deployment.
sender
process on the primary node:ps aux |grep receiver
If the following is returned, it indicates that the sender
process is available.receiver
process on the secondary node:ps aux | grep receiver
If the following is returned, it indicates that the receiver
process is available.su - postgres
psql
select * from pg_stat_replication;
If the following is returned, it indicates that the secondary node status is available.testdb
).su - postgres
psql
create database testdb;
su - postgres
psql
"l",
If the following is returned, it indicates that the secondary node has been successfully synchronized.
Was this page helpful?