Infrastructure at your Service

Daniel Westermann

Bringing up your customized PostgreSQL instance on Azure

The Azure cloud becomes more and more popular so I gave it try and started simple. The goal was to provision a VM, compiling and installing PostgreSQL and then connecting to the instance. There is also a fully managed PostgreSQL service but I wanted to do it on my own just to get a feeling about the command line tools. Here is how I’ve done it.

Obviously you need to login which is just a matter of this:

dwe@dwe:~$ cd /var/tmp
dwe@dwe:/var/tmp$ az login

For doing anything in Azure you’ll need to create a resource group which is like container holding your resources. As a resource group needs to be created in a specific location the next step is to get a list of those:

dwe@dwe:/var/tmp$ az account list-locations
[
  {
    "displayName": "East Asia",
    "id": "/subscriptions/030698d5-42d6-41a1-8740-355649c409e7/locations/eastasia",
    "latitude": "22.267",
    "longitude": "114.188",
    "name": "eastasia",
    "subscriptionId": null
  },
  {
    "displayName": "Southeast Asia",
    "id": "/subscriptions/030698d5-42d6-41a1-8740-355649c409e7/locations/southeastasia",
    "latitude": "1.283",
    "longitude": "103.833",
    "name": "southeastasia",
    "subscriptionId": null
  },
...

Once you have selected a location the resource group can be created:

dwe@dwe:/var/tmp$ az group create --name PGTEST --location "westeurope"
{
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST",
  "location": "westeurope",
  "managedBy": null,
  "name": "PGTEST",
  "properties": {
    "provisioningState": "Succeeded"
  },
  "tags": null
}

All you need to do for creating a CentOS VM is this simple command:

dwe@dwe:/var/tmp$ az vm create -n MyPg -g PGTEST --image centos --data-disk-sizes-gb 10 --size Standard_DS2_v2 --generate-ssh-keys
{
  "fqdns": "",
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.Compute/virtualMachines/MyPg",
  "location": "westeurope",
  "macAddress": "xx-xx-xx-xx-xx-xx",
  "powerState": "VM running",
  "privateIpAddress": "x.x.x.x",
  "publicIpAddress": "x.x.x.x",
  "resourceGroup": "PGTEST",
  "zones": ""
}

While the VM is getting created you can watch the resources appearing in the portal:
Selection_026
Selection_027
Selection_028

As soon as the VM is ready connecting via ssh is possible (the keys have automatically been added, no password required):

dwe@dwe:/var/tmp$ ssh x.x.x.x
The authenticity of host 'xx.xx.x.x (xx.xx.x.x)' can't be established.
ECDSA key fingerprint is SHA256:YzNOzg30JH0A3U1R+6WzuJEd3+7N4GmwpSVkznhuTuE.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'xx.xx.x.x' (ECDSA) to the list of known hosts.
[dwe@MyPg ~]$ ls -la /etc/yum.repos.d/
total 44
drwxr-xr-x.  2 root root  209 Sep 25  2017 .
drwxr-xr-x. 86 root root 8192 Aug  2 08:05 ..
-rw-r--r--.  1 root root 1706 Sep 25  2017 CentOS-Base.repo
-rw-r--r--.  1 root root 1309 Nov 29  2016 CentOS-CR.repo
-rw-r--r--.  1 root root  649 Nov 29  2016 CentOS-Debuginfo.repo
-rw-r--r--.  1 root root  314 Nov 29  2016 CentOS-fasttrack.repo
-rw-r--r--.  1 root root  630 Nov 29  2016 CentOS-Media.repo
-rw-r--r--.  1 root root 1331 Nov 29  2016 CentOS-Sources.repo
-rw-r--r--.  1 root root 2893 Nov 29  2016 CentOS-Vault.repo
-rw-r--r--.  1 root root  282 Sep 25  2017 OpenLogic.repo
[dwe@MyPg ~]$ sudo su -
[root@MyPg ~]# cat /etc/centos-release
CentOS Linux release 7.3.1611 (Core) 
[root@MyPg ~]# 

Of course we want to update all the operating system packages to the latest release before moving on. Be careful here to really exclude the WALinuxAgent because otherwise the agent will be upgraded as well (and restarted) and the script execution will fail as you lose connectivity:

dwe@dwe:/var/tmp$ az vm extension set --publisher Microsoft.Azure.Extensions --version 2.0 --name CustomScript --vm-name MyPg --resource-group PGTEST --settings '{"commandToExecute":"yum update -y --exclude=WALinuxAgent"}'
{
  "autoUpgradeMinorVersion": true,
  "forceUpdateTag": null,
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.Compute/virtualMachines/MyPg/extensions/CustomScript",
  "instanceView": null,
  "location": "westeurope",
  "name": "CustomScript",
  "protectedSettings": null,
  "provisioningState": "Succeeded",
  "publisher": "Microsoft.Azure.Extensions",
  "resourceGroup": "PGTEST",
  "settings": {
    "commandToExecute": "yum update -y --exclude=WALinuxAgent"
  },
  "tags": null,
  "type": "Microsoft.Compute/virtualMachines/extensions",
  "typeHandlerVersion": "2.0",
  "virtualMachineExtensionType": "CustomScript"
}

When we want to compile PostgreSQL we need some packages for that, so (not all of them required for compiling PostgreSQL but this is what we usually install):

dwe@dwe:/var/tmp$ az vm extension set --publisher Microsoft.Azure.Extensions --version 2.0 --name CustomScript --vm-name MyPg --resource-group PGTEST --settings '{"commandToExecute":"yum install -y gcc openldap-devel python-devel readline-devel redhat-lsb bison flex perl-ExtUtils-Embed zlib-devel crypto-utils openssl-devel pam-devel libxml2-devel libxslt-devel openssh-clients bzip2 net-tools wget screen unzip sysstat xorg-x11-xauth systemd-devel bash-completion"}'

{
  "autoUpgradeMinorVersion": true,
  "forceUpdateTag": null,
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.Compute/virtualMachines/MyPg/extensions/CustomScript",
  "instanceView": null,
  "location": "westeurope",
  "name": "CustomScript",
  "protectedSettings": null,
  "provisioningState": "Succeeded",
  "publisher": "Microsoft.Azure.Extensions",
  "resourceGroup": "PGTEST",
  "settings": {
    "commandToExecute": "yum install -y gcc openldap-devel python-devel readline-devel redhat-lsb bison flex perl-ExtUtils-Embed zlib-devel crypto-utils openssl-devel pam-devel libxml2-devel libxslt-devel openssh-clients bzip2 net-tools wget screen unzip sysstat xorg-x11-xauth systemd-devel bash-completion"
  },
  "tags": null,
  "type": "Microsoft.Compute/virtualMachines/extensions",
  "typeHandlerVersion": "2.0",
  "virtualMachineExtensionType": "CustomScript"
}

Preparation work for the user, group and directories:

dwe@dwe:~$ az vm extension set --publisher Microsoft.Azure.Extensions --version 2.0 --name CustomScript --vm-name MyPg --resource-group PGTEST --settings '{"commandToExecute":"groupadd postgres; useradd -m -g postgres postgres; mkdir -p /u01/app; chown postgres:postgres /u01/app; mkdir -p /u02/pgdata; chown postgres:postgres /u02/pgdata"}'
{
  "autoUpgradeMinorVersion": true,
  "forceUpdateTag": null,
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.Compute/virtualMachines/MyPg/extensions/CustomScript",
  "instanceView": null,
  "location": "westeurope",
  "name": "CustomScript",
  "protectedSettings": null,
  "provisioningState": "Succeeded",
  "publisher": "Microsoft.Azure.Extensions",
  "resourceGroup": "PGTEST",
  "settings": {
    "commandToExecute": "groupadd postgres; useradd -m -g postgres postgres; mkdir -p /u01/app; chown postgres:postgres /u01/app; mkdir -p /u02/pgdata; chown postgres:postgres /u02/pgdata"
  },
  "tags": null,
  "type": "Microsoft.Compute/virtualMachines/extensions",
  "typeHandlerVersion": "2.0",
  "virtualMachineExtensionType": "CustomScript"
}

For the next steps we will just copy over this script and then execute it:

dwe@dwe:~$ cat installPG.sh 
#!/bin/bash
cd /u01/app; wget https://ftp.postgresql.org/pub/source/v10.5/postgresql-10.5.tar.bz2
tar -axf postgresql-10.5.tar.bz2
rm -f postgresql-10.5.tar.bz2
cd postgresql-10.5
PGHOME=/u01/app/postgres/product/10/db_5/
SEGSIZE=2
BLOCKSIZE=8
WALSEGSIZE=16
./configure --prefix=${PGHOME} \
            --exec-prefix=${PGHOME} \
            --bindir=${PGHOME}/bin \
            --libdir=${PGHOME}/lib \
            --sysconfdir=${PGHOME}/etc \
            --includedir=${PGHOME}/include \
            --datarootdir=${PGHOME}/share \
            --datadir=${PGHOME}/share \
            --with-pgport=5432 \
            --with-perl \
            --with-python \
            --with-openssl \
            --with-pam \
            --with-ldap \
            --with-libxml \
            --with-libxslt \
            --with-segsize=${SEGSIZE} \
            --with-blocksize=${BLOCKSIZE} \
            --with-wal-segsize=${WALSEGSIZE}  \
	    --with-systemd 
make -j 4 all
make install
cd contrib
make -j 4 install

dwe@dwe:~$ scp installPG.sh x.x.x.x:/var/tmp/
installPG.sh                                                                                                100% 1111     1.1KB/s   00:00    

Of course you could also add the yum commands to the same script but I wanted to show both ways. Using the CustomScript feature and copying over a script for execution. Lets execute that:

dwe@dwe:~$ az vm extension set --publisher Microsoft.Azure.Extensions --version 2.0 --name CustomScript --vm-name MyPg --resource-group PGTEST --settings '{"commandToExecute":"chmod +x /var/tmp/installPG.sh; sudo su - postgres -c /var/tmp/installPG.sh"}'

  "autoUpgradeMinorVersion": true,
  "forceUpdateTag": null,
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.Compute/virtualMachines/MyPg/extensions/CustomScript",
  "instanceView": null,
  "location": "westeurope",
  "name": "CustomScript",
  "protectedSettings": null,
  "provisioningState": "Succeeded",
  "publisher": "Microsoft.Azure.Extensions",
  "resourceGroup": "PGTEST",
  "settings": {
    "commandToExecute": "chmod +x /var/tmp/installPG.sh; sudo su - postgres -c /var/tmp/installPG.sh"
  },
  "tags": null,
  "type": "Microsoft.Compute/virtualMachines/extensions",
  "typeHandlerVersion": "2.0",
  "virtualMachineExtensionType": "CustomScript"
}

Binaries ready. Initialize the cluster:

dwe@dwe:~$ az vm extension set --publisher Microsoft.Azure.Extensions --version 2.0 --name CustomScript --vm-name MyPg --resource-group PGTEST --settings '{"commandToExecute":"sudo su - postgres -c \"/u01/app/postgres/product/10/db_5/bin/initdb -D /u02/pgdata/PG1\""}'
{
  "autoUpgradeMinorVersion": true,
  "forceUpdateTag": null,
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.Compute/virtualMachines/MyPg/extensions/CustomScript",
  "instanceView": null,
  "location": "westeurope",
  "name": "CustomScript",
  "protectedSettings": null,
  "provisioningState": "Succeeded",
  "publisher": "Microsoft.Azure.Extensions",
  "resourceGroup": "PGTEST",
  "settings": {
    "commandToExecute": "sudo su - postgres -c \"/u01/app/postgres/product/10/db_5/bin/initdb -D /u02/pgdata/PG1\""
  },
  "tags": null,
  "type": "Microsoft.Compute/virtualMachines/extensions",
  "typeHandlerVersion": "2.0",
  "virtualMachineExtensionType": "CustomScript"
}

Startup:

dwe@dwe:~$ az vm extension set --publisher Microsoft.Azure.Extensions --version 2.0 --name CustomScript --vm-name MyPg --resource-group PGTEST --settings '{"commandToExecute":"sudo su - postgres -c \"/u01/app/postgres/product/10/db_5/bin/pg_ctl -D /u02/pgdata/PG1 start\""}'
{
  "autoUpgradeMinorVersion": true,
  "forceUpdateTag": null,xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx030698d5-42d6-41a1-8740-355649c409e7/resourceGroups/PGTEST/providers/Microsoft.Compute/virtualMachines/MyPg/extensions/CustomScript",
  "instanceView": null,
  "location": "westeurope",
  "name": "CustomScript",
  "protectedSettings": null,
  "provisioningState": "Succeeded",
  "publisher": "Microsoft.Azure.Extensions",
  "resourceGroup": "PGTEST",
  "settings": {
    "commandToExecute": "sudo su - postgres -c \"/u01/app/postgres/product/10/db_5/bin/pg_ctl -D /u02/pgdata/PG1 start\""
  },
  "tags": null,
  "type": "Microsoft.Compute/virtualMachines/extensions",
  "typeHandlerVersion": "2.0",
  "virtualMachineExtensionType": "CustomScript"
}

… and the instance is up and running:

dwe@dwe:~$ ssh x.x.x.x
Last login: Mon Aug 13 10:43:53 2018 from ip-37-201-6-36.hsi13.unitymediagroup.de
[dwe@MyPg ~]$ sudo su - postgres
Last login: Mon Aug 13 11:33:52 UTC 2018 on pts/0
[postgres@MyPg ~]$ /u01/app/postgres/product/10/db_5/bin/psql -c 'select version()'
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
[postgres@MyPg ~]$ 

When you want to access this instance from outside Azure you will need to open the port:

dwe@dwe:~$ az vm open-port --resource-group PGTEST --name MyPg --port 5432

Once you have configured PostgreSQL for accepting connections:

[postgres@MyPg ~]$ /u01/app/postgres/product/10/db_5/bin/psql
psql (10.5)
Type "help" for help.

postgres=# alter system set listen_addresses = '*';
ALTER SYSTEM
postgres=# alter user postgres password 'secret';
ALTER ROLE
postgres=# show port ;
 port 
------
 5432
(1 row)

postgres=# \q
[postgres@MyPg ~]$ echo "host    all             all             37.201.6.36/32   md5" >> /u02/pgdata/PG1/pg_hba.conf 
[postgres@MyPg ~]$ /u01/app/postgres/product/10/db_5/bin/pg_ctl -D /u02/pgdata/PG1/ restart

… you can access the instance from your outside Azure:

dwe@dwe:~$ psql -h 137.117.157.183 -U postgres
Password for user postgres: 
psql (9.5.13, server 10.5)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# 

Put all that into a well written script and you can have your customized PostgreSQL instance ready in Azure in a couple of minutes. Now that I have a feeling on how that works in general I’ll look into the managed PostgreSQL service in another post.

 

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure