PostgreSQL is a free and open source, ACID-compliant, transactional database written in the C programming language. It supports updatable views, triggers, stored procedures and foreign keys, and manages concurrency using multi-version concurrency control (MVCC). In this 23rd article in the DevOps series, we will learn how to install and configure a PostgreSQL master-slave replication setup.
PostgreSQL has a number of interfaces for various programming languages and libraries that are available to interact with the database. The primary command-line tool to interact with the database is psql. GUI administrative tools are also available. It was first released in 1996 under the PostgreSQL licence.
CentOS 7 (x86_64) is used as the base OS for the PostgreSQL database server. A couple of guest CentOS VMs are launched using KVM. One instance is used as the database master, while the other is used as a replication slave. The centos users in both the VMs are given sudo access using the visudo command. SELinux is disabled for the exercise.
The host system is a Parabola GNU/Linux-libre x86_64 system and Ansible is installed using the distribution package manager. The version of Ansible used is 2.6.0 as indicated below:
$ ansible --version ansible 2.6.0 config file = /etc/ansible/ansible.cfg configured module search path = [‘/home/guest/.ansible/plugins/modules’, ‘/usr/share/ansible/plugins/modules’] ansible python module location = /usr/lib/python3.6/site-packages/ansible executable location = /usr/bin/ansible python version = 3.6.5 (default, May 11 2018, 04:00:52) [GCC 8.1.0]
The Ansible inventory and playbook file are created on the host system as follows:
ansible/inventory/kvm/ /playbooks/configuration/
The inventory/kvm/inventory file contains the following code:
[pgmaster] host1 ansible_host=192.168.122.174 ansible_connection=ssh ansible_user=centos ansible_password=centos123 [pgslave] host2 ansible_host=192.168.122.113 ansible_connection=ssh ansible_user=centos ansible_password=centos123 [all:children] pgmaster pgslave
The host1 and host2 entries are added in the /etc/hosts file on the host system as shown below:
192.168.122.174 host1 192.168.122.113 host2
You can test connectivity from Ansible to the CentOS guest VMs using the following Ansible commands:
$ ansible -i inventory/kvm/inventory pgmaster -m ping
host1 | SUCCESS => {
“changed”: false,
“ping”: “pong”
}
$ ansible -i inventory/kvm/inventory pgslave -m ping
host2 | SUCCESS => {
“changed”: false,
“ping”: “pong”
}
The Common setup
The PostgreSQL server needs to be installed on both the master and slave instances, and the database needs to be initialised on both. The Postgres user password is changed. Although the password is listed as a variable in the playbook, it can be encrypted and stored using Ansible Vault when used in production. The firewalld daemon is started and port 5432 for the database is allowed through the firewall. The playbook to install and configure the base PostgreSQL server is given below:
---
- name: Common pre-requisites
hosts: all
become: yes
become_method: sudo
gather_facts: yes
tags: [common]
vars:
db_password: “postgres123”
tasks:
- name: Install pgdg-centos96 RPM
package:
name: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
state: present
- name: Install PostgreSQL RPM
package:
name: “{{ item }}”
state: latest
with_items:
- “postgresql96-server”
- “postgresql96-contrib”
- “nano”
- name: Initialize database
shell: sudo ./postgresql96-setup initdb
args:
chdir: /usr/pgsql-9.6/bin/
- name: Start PostgreSQL server
systemd:
name: postgresql-9.6
enabled: yes
state: started
- name: Wait for server to start
wait_for:
port: 5432
- name: Change postgres password
shell: sudo -u postgres psql -c “ALTER USER postgres WITH password ‘{{ db_password }}’”
- name: Start and enable firewalld
systemd:
name: firewalld
enabled: yes
state: started
- name: Allow postgresql through firewall
firewalld:
service: postgresql
permanent: yes
state: enabled
- name: Reload firewalld
shell: firewall-cmd --reload
- name: List reloaded firewall
shell: firewall-cmd --list-all
The above playbook can be invoked using the following command:
$ ansible-playbook -i inventory/kvm/inventory playbooks/configuration/postgresql.yml --tags common -vv --K
The -vv represents the verbosity in the Ansible output. You can use up to four ‘v’ s for a more detailed output. The -K option prompts for the sudo password for the guest Centos user account.
The PostgreSQL master
The PostgreSQL configuration files need to be updated on the master node with various settings required for replication. The database server is restarted and a replication user is also created. The replica_password can be encrypted and stored using Ansible Vault, when used in production. The playbook to set up the PostgreSQL master instance is as follows:
- name: Setup pgmaster
hosts: pgmaster
become: yes
become_method: sudo
gather_facts: yes
tags: [pgmaster]
vars:
replica_password: ‘replica123’
tasks:
- name: Update /var/lib/pgsql/9.6/data/postgresql.conf
lineinfile:
path: /var/lib/pgsql/9.6/data/postgresql.conf
regexp: “{{ item.regexp }}”
line: “{{ item.line }}”
with_items:
- { regexp: “#listen_addresses = ‘localhost’”, line: “listen_addresses = ‘{{ ansible_default_ipv4.address }}’” }
- { regexp: ‘#wal_level = minimal’, line: ‘wal_level = hot_standby’ }
- { regexp: ‘#synchronous_commit = on’, line: ‘synchronous_commit = local’ }
- { regexp: ‘#archive_mode = off’, line: ‘archive_mode = on’ }
- { regexp: “#archive_command = ‘’”, line: “archive_command = ‘cp %p /var/lib/pgsql/9.6/archive/%f’” }
- { regexp: ‘#max_wal_senders = 0’, line: ‘max_wal_senders = 2’ }
- { regexp: ‘#wal_keep_segments = 0’, line: ‘wal_keep_segments = 2’ }
- { regexp: “#synchronous_standby_names = ‘’”, line: “synchronous_standby_names = ‘slave01’” }
- name: Create archive directory
file:
path: /var/lib/pgsql/9.6/archive
mode: 0700
owner: postgres
group: postgres
state: directory
- name: Update pg_hba.conf
blockinfile:
path: /var/lib/pgsql/9.6/data/pg_hba.conf
insertafter: ‘#host replication postgres ::1/128 ident’
block: |
# Localhost
host replication replica 127.0.0.1/32 md5
# PostgreSQL Master IP address
host replication replica
{{ ansible_default_ipv4.address}}/32 md5
# PostgreSQL Slave IP address
host replication replica
{{ hostvars[‘host2’].ansible_default_ipv4.address }}/32 md5
- name: Restart PostgreSQL server
systemd:
name: postgresql-9.6
enabled: yes
state: restarted
- name: Create replication user
shell: sudo -u postgres psql -c “CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD ‘{{ replica_password }}’”
The above playbook invocation and sample output is shown below, for reference:
$ ansible-playbook -i inventory/kvm/inventory playbooks/configuration/postgresql.yml --tags pgmaster -K
SUDO password:
PLAY [Common pre-requisites] ********************************
TASK [Gathering Facts] **************************************
ok: [host2]
ok: [host1]
PLAY [Setup pgmaster] ***************************************
TASK [Gathering Facts] **************************************
ok: [host1]
TASK [Update /var/lib/pgsql/9.6/data/postgresql.conf] *******
changed: [host1] => (item={‘regexp’: “#listen_addresses = ‘localhost’”, ‘line’: “listen_addresses = ‘192.168.122.174’”})
changed: [host1] => (item={‘regexp’: ‘#wal_level = minimal’, ‘line’: ‘wal_level = hot_standby’})
changed: [host1] => (item={‘regexp’: ‘#synchronous_commit = on’, ‘line’: ‘synchronous_commit = local’})
changed: [host1] => (item={‘regexp’: ‘#archive_mode = off’, ‘line’: ‘archive_mode = on’})
changed: [host1] => (item={‘regexp’: “#archive_command = ‘’”, ‘line’: “archive_command = ‘cp %p /var/lib/pgsql/9.6/archive/%f’”})
changed: [host1] => (item={‘regexp’: ‘#max_wal_senders = 0’, ‘line’: ‘max_wal_senders = 2’})
changed: [host1] => (item={‘regexp’: ‘#wal_keep_segments = 0’, ‘line’: ‘wal_keep_segments = 2’})
changed: [host1] => (item={‘regexp’: “#synchronous_standby_names = ‘’”, ‘line’: “synchronous_standby_names = ‘slave01’”})
TASK [Create archive directory] *****************************
changed: [host1]
TASK [Update pg_hba.conf] ***********************************
changed: [host1]
TASK [Restart PostgreSQL server] ****************************
changed: [host1]
TASK [Create replication user] ******************************
[WARNING]: Consider using ‘become’, ‘become_method’ ‘become_user’ rather than running sudo
changed: [host1]
PLAY [Setup pgslave] ****************************************
TASK [Gathering Facts] **************************************
ok: [host2]
PLAY RECAP **************************************************
host1 : ok=7 changed=5 unreachable=0 failed=0
host2 : ok=2 changed=0 unreachable=0 failed=0
The PostgreSQL slave
The last step is to configure the PostgreSQL slave instance to receive data from the master instance. The PostgreSQL server is initially stopped, the var/lib/pgsql/9.6/data directory is backed up, and a new data/ directory is created. The initial data is fetched from the master using pg_basebackup. The replica user password can be encrypted and stored using Ansible Vault. The postgresql.conf file is updated, a recovery.conf file is created, and the PostgreSQL server on the slave instance is started. The Ansible playbook to configure the replication slave is given below:
- name: Setup pgslave
hosts: pgslave
become: yes
become_method: sudo
gather_facts: yes
tags: [pgslave]
vars:
replica_password: ‘replica123’
tasks:
- name: Stop PostgreSQL server
systemd:
name: postgresql-9.6
state: stopped
- name: Move data backup
shell: mv data data-backup
args:
chdir: /var/lib/pgsql/9.6/
- name: Create data directory
file:
path: /var/lib/pgsql/9.6/data
mode: 0700
owner: postgres
group: postgres
state: directory
- name: Backup initial data from master
shell: su - postgres -c “PGPASSWORD={{ replica_password }} pg_basebackup -w -h {{ hostvars[‘host1’].ansible_default_ipv4.address }} -U replica -D /var/lib/pgsql/9.6/data -P --xlog”
- name: Update /var/lib/pgsql/9.6/data/postgresql.conf
lineinfile:
path: /var/lib/pgsql/9.6/data/postgresql.conf
regexp: “{{ item.regexp }}”
line: “{{ item.line }}”
with_items:
- { regexp: “#listen_addresses = ‘localhost’”, line: “listen_addresses = ‘{{ ansible_default_ipv4.address }}’” }
- { regexp: “#hot_standby = off”, line: “hot_standby = on” }
- name: Create recovery.conf
blockinfile:
path: /var/lib/pgsql/9.6/data/recovery.conf
block: |
standby_mode = ‘on’
primary_conninfo = ‘host={{ hostvars[‘host1’].ansible_default_ipv4.address }} port=5432 user=replica password={{ replica_password }} application_name=slave01’
trigger_file = ‘/tmp/postgresql.trigger.5432’
mode: 0600
owner: postgres
group: postgres
state: present
create: yes
- name: Start PostgreSQL server
systemd:
name: postgresql-9.6
state: started
The above playbook can be executed using the following command:
$ ansible-playbook -i inventory/kvm/inventory playbooks/configuration/postgresql.yml --tags pgslave -K
Testing
You can log in to the master instance, become a Postgres user, and run the following psql commands to see the synchronisation state of the setup:
# su - postgres -bash-4.2$ psql -c “select application_name, state, sync_priority, sync_state from pg_stat_replication;” application_name | state | sync_priority | sync_state ------------------+-----------+---------------+------------ slave01 | streaming | 1 | sync (1 row) -bash-4.2$ psql -x -c “select * from pg_stat_replication;” -[ RECORD 1 ]----+--------------------------------- pid | 4054 usesysid | 16384 usename | replica application_name | slave01 client_addr | 192.168.122.113 client_hostname | client_port | 53720 backend_start | 2018-12-12 21:32:07.382766+05:30 backend_xmin | state | streaming sent_location | 0/3000060 write_location | 0/3000060 flush_location | 0/3000060 replay_location | 0/3000060 sync_priority | 1 sync_state | sync
You can create tables and insert records on the master instance. An example is given below:
-bash-4.2$ psql psql (9.6.11) Type “help” for help. postgres=# CREATE TABLE student (name VARCHAR(100)); CREATE TABLE postgres=# INSERT INTO student VALUES (‘Adith’); INSERT 0 1 postgres=# INSERT INTO student VALUES (‘Shakthi’); INSERT 0 1 postgres=#
You can now verify that the records exist on the slave instance using the following set of commands:
[root@host1 ~]# su - postgres Last login: Wed Dec 12 21:32:02 IST 2018 -bash-4.2$ psql psql (9.6.11) Type “help” for help. postgres=# select * from student; name --------- Adith Shakthi (2 rows)
If you try to insert records on the slave instance, the database will throw a ‘read-only transaction’ error as shown below:
postgres=# INSERT INTO student VALUES ('Foo');
ERROR: cannot execute INSERT in a read-only transaction
postgres=#
You are encouraged to read Chapter 26 of the book ‘High Availability, Load Balancing Replication’ from the PostgreSQL documentation page at https://www.postgresql.org/docs/current/high-availability.html.
