DevOps Series Configuring a PostgreSQL Master-Slave Setup Using Ansible

0
10987

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here