Postgres Pro Standard for Azure

Microsoft Azure VM with Postgres Pro Standard Quick Start Guide

Internet access and valid Microsoft Azure account are required to use Postgres Pro 9.6/10/11 database in Microsoft Azure cloud.

Postgres Pro 9.6/10/11 virtual machine (VM) image is available in Microsoft Azure Marketplace.

Software required for installation:

  • Azure CLI 2.0 for cloud management
  • 'psql' or 'Pgadmin' for database connection

Azure CLI 2.0 is a cross-platform command line utility.

Azure Portal https://portal.azure.com or Azure PowerShell can be used as well.

Azure CLI 2.0 Installation guide: https://docs.microsoft.com/en-us/cli/azure/install-azure-cli

Azure CLI 2.0 Get started guide: https://docs.microsoft.com/en-us/cli/azure/get-started-with-azure-cli

Azure CLI 2.0 Command reference guide: https://docs.microsoft.com/en-us/cli/azure

Azure Linux Virtual Machine Installation guide: https://docs.microsoft.com/en-us/azure/virtual-machines/linux

Azure Backup Documentation: https://docs.microsoft.com/en-us/azure/backup/

 

Connection to Azure and environment check

  • Connect to Microsoft Azure with:
az login

or

az login --username <myusername>
  • Set Azure CLI 2.0 commands table format output:
az configure --output table
  • Verify Azure CLI 2.0 version (should be 2.0.71 or higher):
az --version | head -1
  • Make sure required services Microsoft.Storage, Microsoft.Compute and Microsoft.Network are registered:
az provider show --namespace Microsoft.Storage
az provider show --namespace Microsoft.Compute
az provider show --namespace Microsoft.Network
  • If not, register them:
az provider register --namespace Microsoft.Storage
az provider register --namespace Microsoft.Compute
az provider register --namespace Microsoft.Network
  • A list of locations for VM:
az account list-locations

‘northeurope’ location will be used further.

  • A list of available VM sizes in ‘northeurope’ location:
az vm list-sizes --location northeurope

‘Standard_DS1_v2’ VM size will be used further (available for ‘Free Trial’ subscription).

  • Obtain publisher name of VM Postgres Pro image in Microsoft Azure Marketplace for ‘northeurope’ location:
az vm image list-publishers \
--location northeurope \
--query "[?starts_with(name,'postgres')].{Name:name}"

‘postgres-pro’ VM publisher name will be used further.

  • Obtain VM Postgres Pro image names available in Microsoft Azure Marketplace for ‘postgres-pro’ publisher in ‘northeurope’ location:
az vm image list \
--publisher postgres-pro \
--location northeurope \
--all \
--query "[?contains(urn,'standard')].{Urn:urn}"
  • The following VM image name will be used further:
urn_id='postgres-pro:postgres-pro-standard:std-9_6_15-centos-7_6_20190814-byol:96.15.2'
or
urn_id='postgres-pro:postgres-pro-standard-10:std-10_10-centos-7_6_20190813-byol:10.10.2'
or
urn_id='postgres-pro:postgres-pro-standard-11:std-11_5-centos-7_6_20190812-byol:11.5.4'
  • Configure VM programmatic deployment:
az vm image accept-terms --urn $urn_id
  • Create a pair of private/public ssh-keys in ~/.ssh directory to connect to VM:
ssh-keygen -t rsa -b 2048

 

VM creation

  • Create resource group:
az group create \
--name myresourcegroup \
--location northeurope
  • Create VM from VM image available in Microsoft Azure Marketplace:
az vm create \
--name myvm-std-xx \
--resource-group myresourcegroup \
--image $urn_id \
--location northeurope \
--size Standard_DS1_v2 \
--ssh-key-value ~/.ssh/id_rsa.pub \
--admin-username azureuser \
--authentication-type ssh \
--public-ip-address-dns-name myvm-std-xx-dnsname \
--os-disk-name myvm-std-xx-osdisk

Replace ‘xx’ by '01', '02', '03’ and so on.

 

Connection to VM

As a result VM is created with FQDN of ‘myvm-std-xx-dnsname.northeurope.cloudapp.azure.com’ (FQDN is a combination of short DNS-name used during VM creation, location name and ‘cloudapp.azure.com’) and OS-account of ‘azureuser’ (by default with ‘sudo’ permissions)

  • Connect to VM:
ssh azureuser@myvm-std-xx-dnsname.northeurope.cloudapp.azure.com

Replace ‘xx’ by '01', '02', '03’ and so on.

 

Postgres Pro database service status

  • Verify Postgres Pro database service status:
sudo systemctl -l status postgrespro-9.6.service
or
sudo systemctl -l status postgrespro-std-10.service
or
sudo systemctl -l status postgrespro-std-11.service
  • To stop/start Postgres Pro database service use the following commands:
sudo systemctl stop postgrespro-9.6.service
sudo systemctl start postgrespro-9.6.service
or
sudo systemctl stop postgrespro-std-10.service
sudo systemctl start postgrespro-std-10.service
or
sudo systemctl stop postgrespro-std-11.service
sudo systemctl start postgrespro-std-11.service

 

Connection to Postgres Pro database

  • Switch to ‘postgres’ account:
sudo su - postgres
  • To connect to Postgres Pro database use the following command:
psql
  • To exit from ‘psql’ use the following command:
\q
  • To return to Azure CLI 2.0 interface run 'exit' command twice

 

External connection to VM

  • TCP-port 5433 has to be opened for external connection to Postgres Pro database:
az vm open-port \
--name myvm-std-xx \
--port 5433 \
--resource-group myresourcegroup \
--priority 1001
  • TCP-ports 80 and 443 have to be opened for external connection to database monitoring server:
az vm open-port \
--name myvm-std-xx \
--port 80 \
--resource-group myresourcegroup \
--priority 1002

az vm open-port \
--name myvm-std-xx \
--port 443 \
--resource-group myresourcegroup \
--priority 1003

Replace ‘xx’ by '01', '02', '03’ and so on.

 

External connection to Postgres Pro database

  • For external connection to Postgres Pro database set up 'postgres' user password:
ssh azureuser@myvm-std-xx-dnsname.northeurope.cloudapp.azure.com

sudo su - postgres
psql -c "alter user postgres with encrypted password 'YOUR_POSTGRES_USER_PASSWORD'"
exit

exit
  • For external connection to Postgres Pro database using ‘psql’ utility use the following command:
psql --host=myvm-std-xx-dnsname.northeurope.cloudapp.azure.com --port=5433 --username=postgres --dbname=postgres
  • For external connection to Postgres Pro database using ‘Pgadmin’ utility configure the following server settings in ‘Pgadmin’ menu:
    • ‘mydb-xx’ for ‘Name’
    • ‘myvm-std-xx-dnsname.northeurope.cloudapp.azure.com’ for ‘Host’
    • ‘5433’ for ‘Port’
    • ‘postgres’ for ‘Maintenance DB’
    • ‘postgres’ for ‘Username’

Replace ‘xx’ by '01', '02', '03’ and so on.

 

External connection to database monitoring server

  • For connection to database monitoring server set up 'Admin' user password:
ssh azureuser@myvm-std-xx-dnsname.northeurope.cloudapp.azure.com

sudo su - postgres
source .pgsql_profile
psql --dbname=zabbix --username=zabbix -c "update users set passwd=md5('YOUR_ZABBIX_ADMIN_PASSWORD') where alias='Admin'"
exit

exit
  • External connection to database monitoring server is via the following link:

https://myvm-std-xx-dnsname.northeurope.cloudapp.azure.com/zabbix

(Replace ‘xx’ by '01', '02', '03’ and so on.)

 

VM configuration change

Let's have some examples of VM configuration change:

1) Change VM size from ‘Standard_DS1_v2’ to ‘Standard_DS2_v2’ to increase VM computing power

  • $PGDATA/postgresql.tune.lock file can be deleted in order to automatically adjust Postgres Pro database parameters values before increasing VM size:
ssh azureuser@myvm-std-xx-dnsname.northeurope.cloudapp.azure.com

sudo su - postgres
cp $PGDATA/postgresql.auto.conf $PGDATA/postgresql.auto.conf.ORIG
rm $PGDATA/postgresql.tune.lock
exit

exit
  • A list of available VM sizes in ‘northeurope’ location (‘az vm deallocate’ is not required):
az vm list-vm-resize-options \
--name myvm-std-xx \
--resource-group myresourcegroup
  • To change VM size use the following command:
az vm resize \
--name myvm-std-xx \
--resource-group myresourcegroup \
--size Standard_DS2_v2

Replace ‘xx’ by '01', '02', '03’ and so on. 

2) Increase OS-disk size up to 80 GB

  • Obtain OS-disk size details:
az disk list \
--resource-group myresourcegroup \
--query "[?starts_with(name,'myvm-std-xx-osdisk')].{Name:name,Gb:diskSizeGb}"
  • Deallocate VM temporarily:
az vm deallocate \
--name myvm-std-xx \
--resource-group myresourcegroup
  • Increase OS-disk size:
az disk update \
--name myvm-std-xx-osdisk \
--resource-group myresourcegroup \
--size-gb 80
  • Verify new OS-disk size:
az disk list \
--resource-group myresourcegroup \
--query "[?starts_with(name,'myvm-std-xx-osdisk')].{Name:name,Gb:diskSizeGb}"
  • Start VM:
az vm start \
--name myvm-std-xx \
--resource-group myresourcegroup
  • Connect to VM:
ssh azureuser@myvm-std-xx-dnsname.northeurope.cloudapp.azure.com
  • Increase ‘/’ filesystem partition size:
(echo d; echo 2; echo n; echo p; echo 2; echo ; echo ; echo w) | sudo fdisk /dev/sda
  • Restart VM:
sudo reboot
  • Connect to VM:
ssh azureuser@myvm-std-xx-dnsname.northeurope.cloudapp.azure.com
  • Increase ‘/’ filesystem size:
sudo xfs_growfs -d /dev/sda2
  • Restart VM:
sudo reboot

Replace ‘xx’ by '01', '02', '03’ and so on. 

3) Use dedicated datadisk for Postgres Pro database files to improve database performance

  • Let's create new 200 GB datadisk and attach it to VM:
az vm disk attach \
--disk myvm-std-xx-datadisk \
--resource-group myresourcegroup \
--vm-name myvm-std-xx \
--caching ReadOnly \
--lun 1 \
--new \
--size-gb 200
  • Connect to VM:
ssh azureuser@myvm-std-xx-dnsname.northeurope.cloudapp.azure.com
  • Stop Postgres Pro database service and verify its status:
sudo systemctl stop postgrespro-9.6.service
sudo systemctl -l status postgrespro-9.6.service
or
sudo systemctl stop postgrespro-std-10.service
sudo systemctl -l status postgrespro-std-10.service
or
sudo systemctl stop postgrespro-std-11.service
sudo systemctl -l status postgrespro-std-11.service
  • Create new filesystem mountpoint:
sudo mkdir /PGDATA
  • Use 'lsscsi' utility to find out datadisk device name (in this case it is '/dev/sdc'):
lsscsi
  • Create datadisk single partition of maximum size, create new filesystem on top of it:
(echo n; echo p; echo 1; echo ; echo ; echo w) | sudo fdisk /dev/sdc
sudo mkfs -t ext4 /dev/sdc1
  • Amend /etc/fstab file for new filesystem automount and mount it:
sudo sh -c "echo '`sudo blkid -o export /dev/sdc1 | grep UUID` /PGDATA ext4 defaults,nofail,barrier=0 1 2' >> /etc/fstab"
sudo mount /PGDATA
  • Create 'data' directory on the new filesystem and set its permissions:
sudo mkdir /PGDATA/data
sudo chown postgres:postgres /PGDATA/data
sudo chmod 0700 /PGDATA/data
  • Switch to ‘postgres’ account and move Postgres Pro database files to the new filesystem:
sudo su - postgres
mv /var/lib/pgpro/9.6/data/* /PGDATA/data; rmdir /var/lib/pgpro/9.6/data; ln -s /PGDATA/data /var/lib/pgpro/9.6/data
or
mv /var/lib/pgpro/std-10/data/* /PGDATA/data; rmdir /var/lib/pgpro/std-10/data; ln -s /PGDATA/data /var/lib/pgpro/std-10/data
or
mv /var/lib/pgpro/std-11/data/* /PGDATA/data; rmdir /var/lib/pgpro/std-11/data; ln -s /PGDATA/data /var/lib/pgpro/std-11/data
exit
  • Start Postgres Pro database service and verify its status:
sudo systemctl start postgrespro-9.6.service
sudo systemctl -l status postgrespro-9.6.service
or
sudo systemctl start postgrespro-std-10.service
sudo systemctl -l status postgrespro-std-10.service
or
sudo systemctl start postgrespro-std-11.service
sudo systemctl -l status postgrespro-std-11.service
  • Restart VM, check filesystem automount and verify Postgres Pro database service status:
sudo reboot

ssh azureuser@myvm-std-xx-dnsname.northeurope.cloudapp.azure.com

sudo mount | grep /PGDATA
sudo df -h /PGDATA

sudo systemctl -l status postgrespro-9.6.service
or
sudo systemctl -l status postgrespro-std-10.service
or
sudo systemctl -l status postgrespro-std-11.service

Replace ‘xx’ by '01', '02', '03’ and so on.

4) Database service auto restart in case of database failure

  • Edit database service systemd file and restart database service::
sudo sed -i '/KillSignal=/a Restart=on-failure' /usr/lib/systemd/system/postgrespro-9.6.service
sudo systemctl daemon-reload
sudo systemctl restart postgrespro-9.6.service
sudo systemctl -l status postgrespro-9.6.service
or
sudo sed -i '/KillSignal=/a Restart=on-failure' /usr/lib/systemd/system/postgrespro-std-10.service
sudo systemctl daemon-reload
sudo systemctl restart postgrespro-std-10.service
sudo systemctl -l status postgrespro-std-10.service
or
sudo sed -i '/KillSignal=/a Restart=on-failure' /usr/lib/systemd/system/postgrespro-std-11.service
sudo systemctl daemon-reload
sudo systemctl restart postgrespro-std-11.service
sudo systemctl -l status postgrespro-std-11.service

 

VM backup/restore

Let's consider how to do a VM backup/restore in Azure (you can find more information about it at https://docs.microsoft.com/en-us/azure/backup/quick-backup-vm-cli).

  • To take file-consistent backup of VM, create Recovery Services vault first:
az backup vault create \
--name myvault-std-xx \
--resource-group myresourcegroup \
--location northeurope

'myvault-std-xx' Recovery Services vault name will be used further.

  • Change Recovery Services vault storage type to Locally-Redundant if you don't plan to use Geo-Redundant storage:
az backup vault backup-properties set \
--name myvault-std-xx \
--resource-group myresourcegroup \
--backup-storage-redundancy LocallyRedundant
  • Enable VM backup protection with default backup policy, it will schedule VM backup once a day:
az backup protection enable-for-vm \
--vm myvm-std-xx \
--vault-name myvault-std-xx \
--policy-name DefaultPolicy \
--resource-group myresourcegroup
  • Run the first backup manually, specifying recovery point availability date in 'dd-mm-yyyy' format. Provide VM name both for '--item-name' and '--container-name' parameters.
az backup protection backup-now \
--item-name myvm-std-xx \
--container-name myvm-std-xx \
--vault-name myvault-std-xx \
--resource-group myresourcegroup \
--retain-until 31-12-2020
  • Monitor backup job status:
az backup job list \
--vault-name myvault-std-xx \
--resource-group myresourcegroup
  • After backup job is successfully finished, you can use this backup as a recovery point to restore VM disk. Create Azure storage account if you don't have it:
az storage account create \
--name mystorageaccountstd \
--resource-group myresourcegroup \
--location northeurope \
--sku Standard_LRS

'mystorageaccountstd' Azure storage account name will be used further.

  • Supply the latest recovery point for restore:
rp_id=$(az backup recoverypoint list --item-name myvm-std-xx --container-name myvm-std-xx --vault-name myvault-std-xx --resource-group myresourcegroup --query [0].name --output tsv)

az backup restore restore-disks \
--item-name myvm-std-xx \
--container-name myvm-std-xx \
--vault-name myvault-std-xx \
--resource-group myresourcegroup \
--storage-account mystorageaccountstd \
--rp-name $rp_id
  • Monitor restore job status:
az backup job list \
--vault-name myvault-std-xx \
--resource-group myresourcegroup
  • Once restore is secured in storage account, supply required information to create VM disk:
container_id=$(az storage container list --account-name mystorageaccountstd --query [0].name -o tsv)
blob_id=$(az storage blob list --container-name $container_id --account-name mystorageaccountstd --query [0].name -o tsv)
uri_id=$(az storage blob url --name $blob_id --container-name $container_id --account-name mystorageaccountstd -o tsv)

az disk create \
--name myrestoredvm-std-xx-osdisk \
--resource-group myresourcegroup \
--source $uri_id
  • Create VM 'myrestoredvm-std-xx' from restored disk:
az vm create \
--name myrestoredvm-std-xx \
--resource-group myresourcegroup \
--attach-os-disk myrestoredvm-std-xx-osdisk \
--location northeurope \
--size Standard_DS1_v2 \
--public-ip-address-dns-name myrestoredvm-std-xx-dnsname \
--os-type linux
  • Now you can connect to restored VM:
ssh azureuser@myrestoredvm-std-xx-dnsname.northeurope.cloudapp.azure.com

Replace ‘xx’ by '01', '02', '03’ and so on. 

 

Postgres Pro Standard 9.6/10/11 VM content:

Linux CentOS 7.x (64-bit)

  • OS-account - ‘postgres’
  • OS-account - ‘zabbix’
  • OS-account - ‘mamonsu’

OS-disk size - 50 GB

  • xfs filesystem ‘/boot’ (/dev/sda1) - 1 GB
  • xfs filesystem ‘/’ (/dev/sda2) - 49 GB

Main database - Postgres Pro Standard

  • DB version: 9.6/10/11
  • TCP-port: 5433 (opened in OS-firewall settings)
  • configuration file: /var/lib/pgsql/.pgpro_profile
  • database account: ’postgres’

Database monitoring (server)

  • zabbix-server version: 3.4
  • TCP-ports: 80/443 (opened in OS-firewall settings)
  • account: ‘Admin’

Database monitoring (agent)

  • zabbix-agent version: 3.4
  • mamonsu-agent version: 2.3.4
  • configuration file: /etc/mamonsu/agent.conf

Auxiliary database PostgreSQL (as a zabbix-server database)

  • DB version: 9.6/10/11
  • TCP-port: 5432
  • configuration file: /var/lib/pgsql/.pgsql_profile
  • database account: 'postgres'

 

Documentation links