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.