• Skip to main content
  • Skip to secondary menu
  • Skip to primary sidebar
  • Skip to footer
WebSetNet

WebSetNet

Technology News

  • Technology News
    • Mobile
    • Games
  • Internet Marketing
  • System Admin
    • Windows 11
    • Linux
    • Mac & Apple
    • Website Scripts
      • Wordpress
You are here: Home / Database System: PostgreSQL Replication on Ubuntu 16.04

Database System: PostgreSQL Replication on Ubuntu 16.04

August 5, 2020 by Martin6


img-phpz1302033ampk98a5476e4186edb0166977da67937f0campa50407ampc1455013491-9579273

 

PostgreSQL Database System

PostgreSQL is an advanced open source Object-Relational Database Management System (or ORDBMS). It is an extensible and highly-scalable database system, meaning that it can handle loads ranging from single machine applications to enterprise web services with many concurrent users. PostgreSQL is transactional and ACID-compliant (Atomicity, Consistency, Isolation, Durability).

It supports a large part of the SQL standard, and offers many features including:

  • Complex queries
  • Foreign keys
  • Triggers
  • Updatable views
  • Transactional integrity
  • Multiversion concurrency control

As previously said, the PostgreSQL database system can be extended by its users. There are different ways to do this, like adding new functions, operators, data types, index methods, procedural languages, etc.

It is developed by the PostgreSQL Global Development Group and released under the terms of the PostgreSQL License.

PostgreSQL provides many ways to replicate a database. in this tutorial we will configure the Master/Slave replication, which is the process of syncing data between two database by copying from a database on a server (the master) to one on another server (the slave).

This configuration will be done on a server running Ubuntu 16.04.

Prerequisites

PostgreSQL 9.6 installed on the Ubuntu 16.04 Servers

Configure UFW

UFW (or Uncomplicated Firewall) is a tool to manage iptables based firewall on Ubuntu systems. Install it (on both servers) through apt by executing:

# apt-get install -y ufw

Next, add PostgreSQL and SSH service to the firewall. To do this, execute:

# ufw allow ssh
# ufw allow postgresql

Enable the firewall:

# ufw enable

Configure PostgreSQL Master Server

The master server will have reading and writing permissions to the database, and will be the one capable of performing data streaming to the slave server.

With a text editor, edit the PostgreSQL main configuration file, which is /etc/postgresql/9.6/main/postgresql.conf:

# $EDITOR /etc/postgresql/9.6/main/postgresql.conf

Uncomment the listen_addresses line and edit adding the master server IP address:

listen_addresses = 'master_server_IP_address'

Next, uncomment the wal_level line changing its value:

wal_level = hot_standby

To use local syncing for the synchronization level, uncomment and edit the following line:

synchronous_commit = local

We are using two servers, so uncomment and edit the two lines as follows:

max_wal_senders = 2
wal_keep_segments = 10

Save and close the file.

Edit the pg_hba.conf file for the authentication configuration.

# $EDITOR /etc/postgresql/9.6/main/pg_hba.conf
Paste the following configuration:

# Localhost
host    replication     replica          127.0.0.1/32            md5
 
# PostgreSQL Master IP address
host    replication     replica          master_IP_address/32            md5
 
# PostgreSQL SLave IP address
host    replication     replica          slave_IP_address/32            md5

Save, exit and restart PostgreSQL:

# systemctl restart postgresql

Create a User for Replication

Create a new PostgreSQL user for the replication process. Log in to the postgres user and start PostgreSQL shell:

# su - postgres
$ psql

Create a new user:

postgres=# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'usr_strong_pwd';

Close the shell.

This concludes the master server configuration.

Configuring the Slave Server

The slave server won’t have writing permissions to the database, being that its only function is to accept streaming from the master. So it will have only READ permissions.

First, stop the PostgreSQL service:

# systemctl stop postgresql

Edit the PostgreSQL main configuration file:

# $EDITOR /etc/postgresql/9.6/main/postgresql.conf

In this file, uncomment the listen_addresses line and change its value:.

listen_addresses = 'slave_IP_address'

Next, uncomment the wal_level line and change as follow:

wal_level = hot_standby

As in the master settings, uncomment the synchronous_commit line to use local syncing.

synchronous_commit = local

Also as in the master, uncomment and edit the following two lines:

max_wal_senders = 2
wal_keep_segments = 10

Enable hot_standby for the slave server by uncommenting the following line and changing its value:

hot_standby = on

Save and exit.

Copy Data From Master to Slave

To sync from master to slave server, the PostgreSQL main directory on the slave must be replaced with the main directory from the master. In the slave server, log in to the postgres user:

# su - postgres

Make a backup of the actual data directory:

$ cd/var/lib/postgresql/9.6/
$ mv main main_bak

Create a new main directory:

$ mkdir main/

Change permissions:

$ chmod 700 main

At this point, copy the main directory from the master to the slave server by using pg_basebackup:

# pg_basebackup -h master_IP_address -U replica -D /var/lib/postgresql/9.6/main -P --xlog

Once the transfer is complete, in the main directory create a new recovery.conf file, and paste the following content:

standby_mode = 'on'
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=usr_strong_pwd'
trigger_file = '/tmp/postgresql.trigger.5432'

Save, exit and change permissions to this file:

# chmod 600 recovery.conf

Start PostgreSQL:

# systemctl start postgresql

This concludes the slave server configuration.

Conclusion

We have seen how to configure the PostgreSQL master/slave replication, by using two servers running Ubuntu 16.04. This is just one of the many replication capabilities provided by this advanced and fully open source database system.

The post Database System: PostgreSQL Replication on Ubuntu 16.04 appeared first on Unixmen.

Related posts:

  1. PostgreSQL Replication on Ubuntu Tutorial
  2. Install Moodle eLearning Platform on Debian 9
  3. How to Setup Jenkins Master and Slave on Ubuntu 18.04 LTS
  4. How to Install Askbot on CentOS 7
  5. How to Install Elastic Stack on CentOS 7
  6. Create an Online Social Network with Elgg on Debian 9
  7. How to Install and Configure Askbot with Nginx on CentOS 7
  8. How to Install Restyaboard on CentOS 7
  9. How to Install LibreNMS Monitoring Tool with Nginx on CentOS 7
  10. How to Install Cacti SNMP Monitoring Tool on Debian 9

Filed Under: Uncategorized Tagged With: database, postgresql, replication, system, Ubuntu

Primary Sidebar

Popular posts

  • 3 Ways to Disable GetApps on Xiaomi, Redmi, and Poco Phones Running MIUI
  • 5 Ways to Fix “Your SIM sent a Text Message” Issue on iPhone
  • GeForce Experience not finding games? Fix it fast
  • How To Extract & Install tar.gz Files In Ubuntu
  • How to Highlight Duplicates in Google Sheets
  • Discord Stream Has No Sound? 6 Ways to Fix
  • How to find a lost Apple Pencil using your iPad (1st and 2nd gen)
  • 8 Best Sites to Read Manga Online for Free
  • 3 Ways to Hide Tabs in Google Chrome
  • Exclamation Mark on Network Signal, Mobile Data Not Working? 8 Ways to Fix
  • How to check if your Android device supports Widevine DRM
  • What is Android System Intelligence, and why is it on your phone?
  • How to Track a Stolen or Lost Nintendo Switch
  • How to Fix YouTube Server Connection Error [400] on Android
  • How To Calculate CAGR in Excel
  • How to Share Xbox Game Pass With Multiple Consoles
  • Microsoft Edge's newest feature? Shopping in Microsoft Edge

Footer

Tags

Amazon android Apple Asus available download: edge feature features first free from galaxy Game games gaming gets google install Intel iPhone launches linux Microsoft more OnePlus phone release released review: samsung series support this Ubuntu update using video watch what will windows with xbox your

Archives

  • September 2023
  • August 2023
  • July 2023
  • June 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • December 2022
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org