Postgres Pro for Azure

Microsoft Azure Postgres Pro Quick Start Guide

Internet access and valid Microsoft Azure account are required to use Postgres Pro Standard 9.6.2 database in Microsoft Azure cloud.

Postgres Pro Standard 9.6.2 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

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

 

Connecting to Azure and checking the environment

  • 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.7 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’ 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')]"

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

  • Obtain VM Postgres Pro image name available in Microsoft Azure Marketplace for ‘postgres-pro’ publisher in ‘northeurope’ location:
az vm image list \
--all \
--location northeurope \
--publisher postgres-pro

‘postgres-pro:postgres-pro-standard:hrly:9.6.2’ VM Postgres Pro image name will be used further.

 

VM creation

  • Configure programmatic deployment on https://portal.azure.com:
    • menu ‘New’
    • menu ‘Search the marketplace’ - ‘Postgres Pro Standard 9.6.2’
    • ‘Want to deploy programmatically? Get started ->’
    • Subscription status ‘Enable’
    • ‘Save’
  • Create resource group:
az group create \
--location northeurope \
--name myresourcegroup
  • Create VM from VM image available in Microsoft Azure Marketplace:
az vm create \
--name myvm-xx \
--resource-group myresourcegroup \
--image postgres-pro:postgres-pro-standard:hrly:9.6.2 \
--location northeurope \
--size Standard_DS1 \
--admin-password Azure1234567890+ \
--admin-username azureuser \
--authentication-type password \
--public-ip-address-dns-name myvm-dnsname-xx \
--os-disk-name myosdisk-xx

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

  • (Optional) Additional VM OS settings:
az vm extension set \
--resource-group myresourcegroup \
--vm-name myvm-xx \
--name customScript \
--publisher Microsoft.Azure.Extensions \
--settings script-config-pgpro_profile.json

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

  • (Optional) ‘script-config-pgpro_profile.json’ file contents:
{
"commandToExecute": "echo 'export PGHOME=/usr/pgpro-9.6' > /var/lib/pgsql/.pgpro_profile; \
echo 'export PATH=$PGHOME/bin:$PATH' >> /var/lib/pgsql/.pgpro_profile; \
echo 'export MANPATH=$PGHOME/share/man:$MANPATH' >> /var/lib/pgsql/.pgpro_profile; \
echo 'export PGDATA=/var/lib/pgpro/9.6/data' >> /var/lib/pgsql/.pgpro_profile; \
echo 'export PGDATABASE=postgres' >> /var/lib/pgsql/.pgpro_profile; \
echo 'export PGUSER=postgres' >> /var/lib/pgsql/.pgpro_profile; \
echo 'export PGHOST=127.0.0.1' >> /var/lib/pgsql/.pgpro_profile; \
echo 'export PGPORT=5433' >> /var/lib/pgsql/.pgpro_profile; \
chown postgres:postgres /var/lib/pgsql/.pgpro_profile; \
chmod 0640 /var/lib/pgsql/.pgpro_profile"
}

 

Connection to VM

As a result VM is created with FQDN of ‘myvm-dnsname-xx.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) with password ‘Azure1234567890+’ (password is according to security requirements)

  • Connect to VM:
ssh azureuser@myvm-dnsname-xx.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
  • 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

 

Connection to Postgres Pro database

  • Switch to ‘postgres’ account:
sudo su - postgres
  • To connect to Postgres Pro database use the following command:
psql --port=5433
  • To exit from ‘psql’ use the following command:
\q
  • To returun 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 \
--port 5433 \
--resource-group myresourcegroup \
--name myvm-xx \
--priority 1001
  • TCP-port 80 has to be opened for external connection to database monitoring server:
az vm open-port \
--port 80 \
--resource-group myresourcegroup \
--name myvm-xx \
--priority 1002

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

 

External connection to Postgres Pro database

  • For external connection to Postgres Pro database using ‘psql’ utility use the following command:
psql --host=myvm-dnsname-xx.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-dnsname-xx.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

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

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

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

 

VM configuration change

For example, let’s change VM size from ‘Standard_DS1’ to ‘Standard_DS3’

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

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

 

In another example, let’s change OS-disk size from 50 GB to 80 GB

  • Obtain OS-disk size details:
az disk list \
--resource-group myresourcegroup \
--query '[*].{Name:name,Gb:diskSizeGb,Tier:accountType}'
  • Deallocate VM temporarily:
az vm deallocate \
--resource-group myresourcegroup \
--name myvm-xx
  • Increase OS-disk size:
az disk update \
--name myosdisk-xx \
--resource-group myresourcegroup \
--size-gb 80
  • Verify new OS-disk size:
az disk list \
--resource-group myresourcegroup \
--query '[*].{Name:name,Gb:diskSizeGb,Tier:accountType}'
  • Start VM:
az vm start \
--resource-group myresourcegroup \
--name myvm-xx
  • Connect to VM:
ssh azureuser@myvm-dnsname-xx.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-dnsname-xx.northeurope.cloudapp.azure.com
  • Increase filesystem ‘/’ size:
sudo resize2fs /dev/sda2
  • Restart VM:
 sudo reboot

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

 

Postgres Pro Standard 9.6.2 VM content:

Linux CentOS 7.3 (64-bit)

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

OS-disk size - 50 GB

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

Main database:  Postgres Pro 9.6.2

  • TCP-port: 5433 (opened in OS-firewall settings)
  • configuration files: /var/lib/pgpro/9.6/data/postgresql.conf, /var/lib/pgpro/9.6/data/postgresql.auto.conf
  • ‘pg_config’ utility: /usr/pgpro-9.6/bin/pg_config
  • ‘psql’ utility: /usr/pgpro-9.6/bin/psql
  • database account - ’postgres/azure’ (change password accordingly in '/etc/mamonsu/agent.conf' after changing it in database)

Database monitoring (server)

  • zabbix-server 3.0
  • TCP-port: 80 (opened in OS-firewall settings)
  • account - ‘Admin/zabbix’

Database monitoring (agent)

  • zabbix-agent
  • mamonsu 2.2.9 agent (as a zabbix-agent monitoring Postgres Pro)

Auxiliary database PostgreSQL 9.2 (as a zabbix-server database)

  • TCP-port: 5432
  • configuration file: /var/lib/pgsql/data/postgresql.conf
  • ‘pg_config’ utility: /usr/bin/pg_config
  • ‘psql’ utility: /usr/bin/psql

 

Documentation links