
Le Van Cuong
Fullstack Developer

How to install and configure PostgreSQL 9.6 Replication Set (Ubuntu 20.04)
In this tutorial, we will install the latest version of PostgreSQL 9.6. In the official Ubuntu repository, they provide PostgreSQL 9.5 only, so we need to install the latest version from the PostgreSQL repository directly.
- Master server IP address: 10.0.15.10
- Replica server IP address: 10.0.15.11
Step 1. Install PostgreSQL Server on both servers
Add the postgreSQL 9.6 repository to the sources.list.d directory.
wget --quiet -O - https:
//www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
echo
"deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main"
| tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/
null
Now update the system repository with apt commandNext, install the PosgreSQL 9.6 package with the apt command below.
sudo apt-get install -y postgresql-9.6 postgresql-contrib-9.6
If the installation has been completed, add it to start automatically at boot time.
sudo systemctl enable postgresql
By default, PostgreSQL is running on the localhost (127.0.0.1) IP address with port 5432 on Ubuntu, check it with netstat command.
sudo netstat -lpnt | grep 5432
PostgreSQL 9.6 is running on the system. In the next step, we have to configure a password for the postgres user.
From the root account, log in to the postgres user with the su command, then access the postgres front-end terminal psql.
sudo passwd postgres
Step 2 - Configure the PostgreSQL MASTER Server
The master server has the IP address 10.0.15.10, and the postgres service will run under that IP with default port. The master server will have permission for the READ and WRITE to the database, and perform streaming replication to the slave server.
Go to the postgres configuration directory '/etc/postgresql/9.6/main' and edit the postgresql.conf file with vim.
cd /etc/postgresql/9.6/main/
vim postgresql.conf
Uncomment 'listen_addresses' line and change the value to the master server IP address '10.0.15.10'.
listen_addresses = '10.0.15.10'
Uncomment 'wal_level' line and change value to the 'hot_standby'.
wal_level = hot_standby
For the synchronization level, we will use local sync. Uncomment and change value line as below.
synchronous_commit = local
Enable archiving mode and change the archive_command option to the command 'cp %p /var/lib/postgresql/9.6/main/archive/%f'.
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'
For the 'Replication' settings, in this tutorial we use 2 servers only, master and slave, uncomment the 'wal_sender' line and change value to 2, and for the 'wal_keep_segments' value is 10.
max_wal_senders = 2
wal_keep_segments = 10
For the application name, uncomment 'synchronous_standby_names' line and change the value to the name 'pgslave001'.
synchronous_standby_names = 'pgslave001'
Save the file and exit the editor.
In the postgresql.conf file, the archive mode is enabled, so we need to create a new directory for the archive. Create a new archive directory, change the permission and change the owner to the postgres user.
mkdir -p /var/lib/postgresql/9.6/main/archive/
chmod 700 /var/lib/postgresql/9.6/main/archive/
chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/
Next, edit pg_hba.conf file for authentication configuration.
vim pg_hba.conf
Paste configuration below to the end of the line.
# Localhost
host all all 0.0.0.0/0 md5
# PostgreSQL Master IP address
host replication replica 10.0.15.10/32 md5
# PostgreSQL SLave IP address
host replication replica 10.0.15.11/32 md5
Save and exit, then restart PostgreSQL.
systemctl restart postgresql
PostgreSQL is running under the IP address 10.0.15.10, check it with netstat command.
netstat -lpnt
Next, create a new user for replication. We will create a new user named 'replica' with password 'aqwe123@'. Please choose a secure password here for your setup! Log in to the postgres user and access the postgres front-end terminal psql.
su - postgres
psql
Create new 'replica' user with password 'replica@' with postgres query below.
CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'replica@';
Now check the new user with 'du' query below, and you will see the replica user with replication privileges.
\du
MASTER server configuration has been completed.
Step 3 - Configure SLAVE Server
The SLAVE server has IP address 10.0.15.11. And this server will only have a READ permission to the database. The Postgres database server will run under the IP address of the server, not a localhost IP.
Stop the postgres service on the slave server with the systemctl command below.
systemctl stop postgresql
Go to the Postgres configuration directory '/etc/postgresql/9.6/main', then edit the configuration file 'postgresql.conf'.
cd /etc/postgresql/9.6/main/
vim postgresql.conf
Uncomment the listen_addresses line and replace the value with the slave server IP address 10.0.15.11.
listen_addresses = '10.0.15.11'
Uncomment 'wal_level' line and change the value to the 'hot_standby'.
wal_level = hot_standby
For the synchronization level, uncomment the synchronous_commit line and change the value as shown below.
synchronous_commit = local
For the replication setting, uncomment the max_wal_senders line and replace the value with '2' because just use 2 servers. And for the wal_keep_segments, change the value to '10'.
max_wal_senders = 2
wal_keep_segments = 10
Uncomment the 'synchronous_standby_names' line for the application name and change value to the name 'pgslave001'.
synchronous_standby_names = 'pgslave001'
Enable hot_standby for the slave server by uncommenting the following line and change value to 'on'.
hot_standby = on
Save the file and exit the editor.
Step 4. Synchronize Data from Master Server to Replica Server
Log in to the SLAVE server and access postgres user.
su - postgres
mv 9.6/main 9.6/main-bekup
mkdir main/
chmod 700 main/
pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/postgresql/9.6/main -P --xlog
Password:
When the data transfer is complete, go to the main data directory and create a new recovery.conf file.
cd /var/lib/postgresql/9.6/main/
vim recovery.conf
Paste the configuration below:
standby_mode = 'on'
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=aqwe123@ application_name=pgslave001'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
Save and exit, then change the permissions of the file to 600 with chmod.
chmod 600 recovery.conf
Now start PostgreSQL 9.6 on the SLAVE server and make sure the postgres service is running on IP address 10.0.15.11 with netstat.
systemctl start postgresql
netstat -lpnt | grep 5432
Data transfer and configuration for the SLAVE server has been completed.
Step 5. Check and Verify the Replication Clusters
From the Master server, run psql
su - postgres
psql
and then run this command
SELECT client_addr, state FROM pg_stat_replication;
If you see the similar output, then you have successfully created the PostgreSQL replication cluster.
Now try to create a new database on the Master server.
- su - postgres
- psql
- CREATE DATABASE manjarodb;
- \list
Now let’s check the Replica server
- su - postgres
- psql
- \list
Backup
#!/bin/bash
DISCORD_WEBHOOK=
date=$(date +%d-%m-%y)
hostname=postgres-master.dev
backup_dir="/backup/db-backup"
mkdir -p $backup_dir/$date
cd $backup_dir/$date
PGPASSWORD="password" pg_dump -v -Fc -h 10.15.0.17 -U usernam -d "database_name"
> db_name_$date.pgdump
if [ "$?" -ne "0" ] ; then
curl -H "Content-Type: application/json" -d "{\"username\": \"Mobicloud\", \"content\": \"Mobicloud - Backup postgresql on $hostname failed on database dbname \"}" $DISCORD_WEBHOOK
fi
2. Restore postgresql
pg_restore -U username -d "database_name" -v filename_backup.pgdump