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 --versionansible 2.6.0config file = /etc/ansible/ansible.cfgconfigured module search path = [‘/home/guest/.ansible/plugins/modules’, ‘/usr/share/ansible/plugins/modules’]ansible python module location = /usr/lib/python3.6/site-packages/ansibleexecutable location = /usr/bin/ansiblepython 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]pgmasterpgslave |
The host1 and host2 entries are added in the /etc/hosts file on the host system as shown below:
192.168.122.174 host1192.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 pinghost1 | SUCCESS => {“changed”: false,“ping”: “pong”}$ ansible -i inventory/kvm/inventory pgslave -m pinghost2 | 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-requisiteshosts: allbecome: yesbecome_method: sudogather_facts: yestags: [common]vars:db_password: “postgres123”tasks:- name: Install pgdg-centos96 RPMpackage:name: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpmstate: present- name: Install PostgreSQL RPMpackage:name: “{{ item }}”state: latestwith_items:- “postgresql96-server”- “postgresql96-contrib”- “nano”- name: Initialize databaseshell: sudo ./postgresql96-setup initdbargs:chdir: /usr/pgsql-9.6/bin/- name: Start PostgreSQL serversystemd:name: postgresql-9.6enabled: yesstate: started- name: Wait for server to startwait_for:port: 5432- name: Change postgres passwordshell: sudo -u postgres psql -c “ALTER USER postgres WITH password ‘{{ db_password }}’”- name: Start and enable firewalldsystemd:name: firewalldenabled: yesstate: started- name: Allow postgresql through firewallfirewalld:service: postgresqlpermanent: yesstate: enabled- name: Reload firewalldshell: firewall-cmd --reload- name: List reloaded firewallshell: 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 pgmasterhosts: pgmasterbecome: yesbecome_method: sudogather_facts: yestags: [pgmaster]vars:replica_password: ‘replica123’tasks:- name: Update /var/lib/pgsql/9.6/data/postgresql.conflineinfile:path: /var/lib/pgsql/9.6/data/postgresql.confregexp: “{{ 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 directoryfile:path: /var/lib/pgsql/9.6/archivemode: 0700owner: postgresgroup: postgresstate: directory- name: Update pg_hba.confblockinfile:path: /var/lib/pgsql/9.6/data/pg_hba.confinsertafter: ‘#host replication postgres ::1/128 ident’block: |# Localhosthost replication replica 127.0.0.1/32 md5# PostgreSQL Master IP addresshost replication replica{{ ansible_default_ipv4.address}}/32 md5# PostgreSQL Slave IP addresshost replication replica{{ hostvars[‘host2’].ansible_default_ipv4.address }}/32 md5- name: Restart PostgreSQL serversystemd:name: postgresql-9.6enabled: yesstate: restarted- name: Create replication usershell: 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 -KSUDO 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 sudochanged: [host1]PLAY [Setup pgslave] ****************************************TASK [Gathering Facts] **************************************ok: [host2]PLAY RECAP **************************************************host1 : ok=7 changed=5 unreachable=0 failed=0host2 : 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 pgslavehosts: pgslavebecome: yesbecome_method: sudogather_facts: yestags: [pgslave]vars:replica_password: ‘replica123’tasks:- name: Stop PostgreSQL serversystemd:name: postgresql-9.6state: stopped- name: Move data backupshell: mv data data-backupargs:chdir: /var/lib/pgsql/9.6/- name: Create data directoryfile:path: /var/lib/pgsql/9.6/datamode: 0700owner: postgresgroup: postgresstate: directory- name: Backup initial data from mastershell: 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.conflineinfile:path: /var/lib/pgsql/9.6/data/postgresql.confregexp: “{{ 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.confblockinfile:path: /var/lib/pgsql/9.6/data/recovery.confblock: |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: 0600owner: postgresgroup: postgresstate: presentcreate: yes- name: Start PostgreSQL serversystemd:name: postgresql-9.6state: 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 | 4054usesysid | 16384usename | replicaapplication_name | slave01client_addr | 192.168.122.113client_hostname |client_port | 53720backend_start | 2018-12-12 21:32:07.382766+05:30backend_xmin |state | streamingsent_location | 0/3000060write_location | 0/3000060flush_location | 0/3000060replay_location | 0/3000060sync_priority | 1sync_state | sync |
You can create tables and insert records on the master instance. An example is given below:
-bash-4.2$ psqlpsql (9.6.11)Type “help” for help.postgres=# CREATE TABLE student (name VARCHAR(100));CREATE TABLEpostgres=# INSERT INTO student VALUES (‘Adith’);INSERT 0 1postgres=# INSERT INTO student VALUES (‘Shakthi’);INSERT 0 1postgres=# |
You can now verify that the records exist on the slave instance using the following set of commands:
[root@host1 ~]# su - postgresLast login: Wed Dec 12 21:32:02 IST 2018-bash-4.2$ psqlpsql (9.6.11)Type “help” for help.postgres=# select * from student;name---------AdithShakthi(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 transactionpostgres=# |
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.
