DevOps for Databases : vagrant+ansible+postgres+HA

Hello All,

In this post, I will show you how to build a virtualbox using vagrant and install postgres with primary and standby configurations with hot standby mode. This all will be done in less than 1 hr and postgres HA is available for your learning purpose. You will be learning about vagrant and ansible and how to simply convert your scripts and commands to your ansible way of doing.

Here are few things you need to know before you proceed.

• If you are on windows, install Virtualbox and Vagrant windows variants
• Install git in your windows or just download the zip file from my git repository
• Unzip the repository
• You will see
  • VagrantFile -
    • File that builds two machines prodpsql01 and prodpsql02 for postgres with 1024MB each
    • Provision the Ansible playbook postgres.yml
  • install/postgres.yml
    • Run's a role postgres-master which creates a primary database on prodpsql01
    • Run's a role postgres-slave which creates a standby database on prodpsql02
  • group_vars/postgres_vars.yml
    • Variables for your postgres database ex: passwords, data directories etc.

Installation

Open your command prompt and navigate to the directory where you have download and extracted the repository

C:\Users\Sureshgandhi\Desktop\vagrant+ansible+postgres+ha

simply run

vagrant up

and you will see the centos image downloading, creating virtualbox, running ansible, Sample log as below. For first time it take time to download image.

Part of log that provision first node, prodpsql01

C:\Users\Sureshgandhi\Desktop\ansible-postgres-master>vagrant up
Bringing machine 'prodpsql01' up with 'virtualbox' provider...
Bringing machine 'prodpsql02' up with 'virtualbox' provider...
==> prodpsql01: Importing base box 'centos/7'...
==> prodpsql01: Matching MAC address for NAT networking...
==> prodpsql01: Checking if box 'centos/7' is up to date...
==> prodpsql01: A newer version of the box 'centos/7' for provider 'virtualbox' is
==> prodpsql01: available! You currently have version '1804.02'. The latest is version
==> prodpsql01: '1811.02'. Run `vagrant box update` to update.
==> prodpsql01: Setting the name of the VM: ansible-postgres-master_prodpsql01_1547405089078_26371
==> prodpsql01: Clearing any previously set network interfaces...
==> prodpsql01: Preparing network interfaces based on configuration...
prodpsql01: Adapter 1: nat
prodpsql01: Adapter 2: bridged
==> prodpsql01: Forwarding ports...
prodpsql01: 22 (guest) => 10122 (host) (adapter 1)
==> prodpsql01: Running 'pre-boot' VM customizations...
==> prodpsql01: Booting VM...
==> prodpsql01: Waiting for machine to boot. This may take a few minutes...
prodpsql01: SSH address: 127.0.0.1:10122
prodpsql01: SSH username: vagrant
prodpsql01: SSH auth method: private key
prodpsql01:
prodpsql01: Vagrant insecure key detected. Vagrant will automatically replace
prodpsql01: this with a newly generated keypair for better security.
prodpsql01:
prodpsql01: Inserting generated public key within guest...
prodpsql01: Removing insecure key from the guest if it's present...
prodpsql01: Key inserted! Disconnecting and reconnecting using new SSH key...
==> prodpsql01: Machine booted and ready!
[prodpsql01] No installation found.
Loaded plugins: fastestmirror
Determining fastest mirrors
* base: centos.excellmedia.net
* extras: centos.excellmedia.net
* updates: centos.excellmedia.net

Part of the log that Install postgres in first node

==> prodpsql01: Running provisioner: ansible_local...
prodpsql01: Installing Ansible...
Vagrant has automatically selected the compatibility mode '2.0'
according to the Ansible version installed (2.7.5).

Alternatively, the compatibility mode can be specified in your Vagrantfile:
https://www.vagrantup.com/docs/provisioning/ansible_common.html#compatibility_mode
prodpsql01: Running ansible-playbook...

PLAY [prodpsql01] **************************************************************

TASK [Gathering Facts] *********************************************************
ok: [prodpsql01]

TASK [postgres-master : Obtain hostname] ***************************************
changed: [prodpsql01]

TASK [postgres-master : Install Postgres and InitDB] ***************************
changed: [prodpsql01]
[WARNING]: Consider using the yum module rather than running yum. If you need
to use command because yum is insufficient you can add warn=False to this
command task or set command_warnings=False in ansible.cfg to get rid of this
message.

TASK [postgres-master : Create Repmgr directories] *****************************
changed: [prodpsql01]
[WARNING]: Consider using the file module with state=directory rather than
running mkdir. If you need to use command because file is insufficient you can
add warn=False to this command task or set command_warnings=False in
ansible.cfg to get rid of this message.

TASK [postgres-master : Copy Postgres Conf file] *******************************
changed: [prodpsql01]

TASK [postgres-master : Copy repmgr conf file] *********************************
changed: [prodpsql01]

TASK [postgres-master : Modify postgresql.conf] ********************************
ok: [prodpsql01] => (item={u'path': u'/data/postgresql.conf', u'regexp1': u'masternode', u'replace': u'prodpsql01.localdomain'})
changed: [prodpsql01] => (item={u'path': u'/data/postgresql.conf', u'regexp1': u'dataDir', u'replace': u'/data/'})

TASK [postgres-master : Replace in repmgr.conf] ********************************
changed: [prodpsql01] => (item={u'path': u'/var/lib/pgsql/repmgr/repmgr.conf', u'regexp1': u'masternode', u'replace': u'prodpsql01.localdomain'})
changed: [prodpsql01] => (item={u'path': u'/var/lib/pgsql/repmgr/repmgr.conf', u'regexp1': u'env', u'replace': u'prod'})
changed: [prodpsql01] => (item={u'path': u'/var/lib/pgsql/repmgr/repmgr.conf', u'regexp1': u'dataDir', u'replace': u'/data/'})

TASK [postgres-master : Replace in service file] *******************************
changed: [prodpsql01] => (item={u'path': u'/usr/lib/systemd/system/postgresql-9.6.service', u'regexp1': u'/var/lib/pgsql/9.6/data/', u'replace': u'/data/'})

TASK [postgres-master : Set some stuff] ****************************************
changed: [prodpsql01]

TASK [postgres-master : add .pgpass file] **************************************
changed: [prodpsql01]

TASK [postgres-master : Start Postgres] ****************************************
changed: [prodpsql01]
[WARNING]: Consider using the service module rather than running service. If
you need to use command because service is insufficient you can add warn=False
to this command task or set command_warnings=False in ansible.cfg to get rid of
this message.

TASK [postgres-master : Create Admin/Repmgr logins] ****************************
changed: [prodpsql01]
[WARNING]: Consider using 'become', 'become_method', and 'become_user' rather
than running sudo

TASK [postgres-master : Copy Postgres pg_hba file] *****************************
changed: [prodpsql01]

TASK [postgres-master : Set hostnames in the /etc/hosts] ***********************
changed: [prodpsql01]

TASK [postgres-master : stop and start postgres] *******************************
changed: [prodpsql01]

TASK [postgres-master : Register as master] ************************************
changed: [prodpsql01]
[WARNING]: Consider using 'become', 'become_method', and 'become_user' rather
than running su

PLAY [prodpsql02] **************************************************************
skipping: no hosts matched

PLAY RECAP *********************************************************************
prodpsql01 : ok=17 changed=16 unreachable=0 failed=0

Part of the log that provision second node for standby i.e prodpsql02

==> prodpsql02: Checking for guest additions in VM...
prodpsql02: No guest additions were detected on the base box for this VM! Guest
prodpsql02: additions are required for forwarded ports, shared folders, host only
prodpsql02: networking, and more. If SSH fails on this machine, please install
prodpsql02: the guest additions and repackage the box to continue.
prodpsql02:
prodpsql02: This is not an error message; everything may continue to work properly,
prodpsql02: in which case you may ignore this message.
==> prodpsql02: Setting hostname...
==> prodpsql02: Configuring and enabling network interfaces...
prodpsql02: SSH address: 127.0.0.1:10123
prodpsql02: SSH username: vagrant
prodpsql02: SSH auth method: private key
==> prodpsql02: Rsyncing folder: /cygdrive/c/Users/Sureshgandhi/Desktop/ansible-postgres-master/ => /vagrant

Part of the log that provision ansible playbook to create postgres standby on second node i.e prodpsql02

PLAY [prodpsql02] **************************************************************

TASK [Gathering Facts] *********************************************************
ok: [prodpsql02]

TASK [postgres-slave : Obtain hostname] ****************************************
changed: [prodpsql02]

TASK [postgres-slave : Install Postgres and InitDB] ****************************
changed: [prodpsql02]
[WARNING]: Consider using the yum module rather than running yum. If you need
to use command because yum is insufficient you can add warn=False to this
command task or set command_warnings=False in ansible.cfg to get rid of this
message.

TASK [postgres-slave : add .pgpass file] ***************************************
changed: [prodpsql02]

TASK [postgres-slave : Create Repmgr directories] ******************************
changed: [prodpsql02]
[WARNING]: Consider using the file module with state=directory rather than
running mkdir. If you need to use command because file is insufficient you can
add warn=False to this command task or set command_warnings=False in
ansible.cfg to get rid of this message.

TASK [postgres-slave : Copy repmgr conf file] **********************************
changed: [prodpsql02]

TASK [postgres-slave : Replace in repmgr.conf] *********************************
changed: [prodpsql02] => (item={u'path': u'/var/lib/pgsql/repmgr/repmgr.conf', u'regexp1': u'slavenode', u'replace': u'prodpsql02.localdomain'})
changed: [prodpsql02] => (item={u'path': u'/var/lib/pgsql/repmgr/repmgr.conf', u'regexp1': u'env', u'replace': u'prod'})
changed: [prodpsql02] => (item={u'path': u'/var/lib/pgsql/repmgr/repmgr.conf', u'regexp1': u'dataDir', u'replace': u'/data/'})
changed: [prodpsql02] => (item={u'path': u'/var/lib/pgsql/repmgr/repmgr.conf', u'regexp1': u'node_id=1', u'replace': u'node_id=2'})

TASK [postgres-slave : Replace in service file] ********************************
changed: [prodpsql02] => (item={u'path': u'/usr/lib/systemd/system/postgresql-9.6.service', u'regexp1': u'/var/lib/pgsql/9.6/data/', u'replace': u'/data/'})

TASK [postgres-slave : Set some stuff] *****************************************
changed: [prodpsql02]

TASK [postgres-slave : Set hostnames in the /etc/hosts] ************************
changed: [prodpsql02]

TASK [postgres-slave : Clone the standby from primary and register it] *********
changed: [prodpsql02]
[WARNING]: Consider using 'become', 'become_method', and 'become_user' rather
than running su

TASK [postgres-slave : Clone the standby from primary and register it] *********
changed: [prodpsql02]

PLAY RECAP *********************************************************************
prodpsql02 : ok=12 changed=11 unreachable=0 failed=0

That's it, Postgres with Primary and Standby is ready, let's jump on to the box and verify.

run in the same directory

vagrant ssh prodpsql01

run the repmgr command to check the cluster status

sudo su -

su - postgres -c "repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show"

and here is the snippet