DevOps Series Configuring a PostgreSQL Master-Slave Setup Using Ansible

Advertisement

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:

Advertisement
$ 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:

[[email protected] ~]# 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.

Advertisement

source : http://opensourceforu.com

Write a Comment

view all comments