Home PostgreSQL PostgreSQL in Azure.Part 1

PostgreSQL in Azure.Part 1

by admin

With this article we start the series of notes on using PostgreSQL in Microsoft Azure.

The first article will be about installing and configuring a PostgreSQL cluster:

  • Getting to know Azure resources
  • Managing with azure cli
  • Selecting an appropriate storage location
  • Assembling a classic master-slave bundle in the same availability group

The goal of this part is to implement a scheme that includes the following components, Azure resources:
PostgreSQL in Azure.Part 1

We will do it with azure cli.You don’t have to install azure cli and you can do everything via the web interface portal.azure.com , but I found the management and creation through cli to be more intuitive.

The azure cli utility is cross-platform and works wherever nodejs can run. You can install it under linux as :

# dnf install npm || apt-get install npmNPM_CONFIG_PREFIX=~/.npm-global npm install -g 'git://github.com/Azure/azure-xplat-cli.git#v0.9.20-April2016'echo 'NPM_PACKAGES="$HOME/.npm-global"' > > ~/.bash_profileecho 'export PATH="$PATH:$NPM_PACKAGES/bin"' > > ~/.bash_profile

Read the basic commands azure cli can be found here All of the following azure cli commands are run from your running computer. We start by introducing ourselves to the system and getting a token :

azure login

Read more about authorization methods here

Create a Resource Group (Resource Container) in Northern Europe :

azure group create --name pg-resource-group --location northeurope

Create VLAN and subnet

azure network vnet create -g pg-resource-group -l northeurope --address-prefixes --name pg-vnetazure network vnet subnet create -g pg-resource-group --vnet-name pg-vnet --address-prefix --name pg-vnet-subnet

Since we are going to ssh by external ip-address, we create firewall rules and allow connections from the outside only to port 22:

azure network nsg create -g pg-resource-group -l northeurope --name pg-nsgazure network nsg rule create -g pg-resource-group --nsg-name pg-nsg --name AllowSSH --protocol Tcp --access Allow --destination-port-range 22 --priority 200 --direction Inbound

Set up the external ip-address (public-ip), the network adapter with the private network and the subnet for the master :

azure network public-ip create -g pg-resource-group -l northeurope --allocation-method Static --name pg-public-1azure network nic create -g pg-resource-group -l northeurope --subnet-name pg-vnet-subnet --subnet-vnet-name pg-vnet --private-ip-address --network-security-group-name pg-nsg --name pg-nic-1

We create an availability group. In short, this is a cluster of machines divided into groups (domains) which cannot be unavailable at the same time :

azure availset create -g pg-resource-group -l northeurope --name pg-availability-set

Read more about accessibility groups here

Now it’s time to configure the virtual machine. The available virtual machine types and their limitations can be found through the command :

azure vm sizes -l northeurope

There will be more than 50 types of machines listed, with prices available here A smaller number of disks can be connected to the cheaper ones (the total number of disks can be from 1 to 40). To test the wizard I chose Standard_DS4_v2: 8 CPU, 28GB RAM, to which up to 16 disks of premium storage can be connected.

Command to create a virtual machine, which implies a pg user with sudo rights and ssh logon with ssh key :

azure vm create -g pg-resource-group -l northeurope --nic-name pg-nic-1 --os-type Linux --image-urn OpenLogic:CentOS:7.2:latest --admin-username pg --ssh-publickey-file ~/.ssh/id_rsa.pub --vm-size Standard_DS4_v2 --public-ip-name pg-public-1 --availset-name pg-availability-set --name pg-1

To see the parameters of the created machine, as well as the external ip, you can use the command :

azure vm show -g pg-resource-group --name pg-1

Now we have a virtual machine with CentOS 7.2 installed running on a private pg-vnet virtual network with an ip address of and a public pg-public-1 which we can use to ssh from the outside. We need to create a disk, which will contain our PostgreSQL data. You can read about the different types of storage here Let’s start by presenting the results of our storage testing :

PostgreSQL in Azure.Part 1

Create a Premium LRS storage account with the name pgplrs (it must be unique within azure):

azure storage account create -g pg-resource-group -l northeurope --type PLRS pgplrs

Create a disk and connect it to the virtual machine :

azure vm disk attach-new -g pg-resource-group -l northeurope --vm-name pg-1 --lun 1 --size-in-gb 512 --vhd-name pgdata-1 --host-caching ReadOnly --storage-account-name pgplrs

Let’s create and run the pg-2 virtual machine:

azure network public-ip create -g pg-resource-group -l northeurope --allocation-method Static --name pg-public-2azure network nic create -g pg-resource-group -l northeurope --subnet-name pg-vnet-subnet --subnet-vnet-name pg-vnet --private-ip-address --network-security-group-name pg-nsg --name pg-nic-2azure vm create -g pg-resource-group -l northeurope --nic-name pg-nic-2 --os-type Linux --image-urn OpenLogic:CentOS:7.2:latest --admin-username pg --ssh-publickey-file ~/.ssh/id_rsa.pub --vm-size Standard_DS4_v2 --public-ip-name pg-public-1 --availset-name pg-availability-set --name pg-2azure vm disk attach-new -g pg-resource-group -l northeurope --vm-name pg-2 --lun 1 --size-in-gb 512 --vhd-name pgdata-2 --host-caching ReadOnly --storage-account-name pgplrsazure vm start -g pg-resource-group pg-2

We can now ssh to pg-1 as user pg:

ssh pg-public-1 -l pg -i ~/.ssh/id_rsa

All further actions we will perform on the pg-1 machine. Immediately after the disk is connected, we need to map it to the physical block device available in the virtual machine. To do this we run the command :

[root@pg-1 ~] $ dmesg | tail -n 10[ 488.417024] Adjusting hyperv_clocksource more than 11% (1945964553 vs 1862270976)[ 525.969741] scsi 5:0:0:0: Direct-Access Msft Virtual Disk 1.0 PQ: 0 ANSI: 4[ 526.001471] sd 5:0:0:0: Attached scsi generic sg3 type 0[ 526.018792] sd 5:0:0:0: [sdc] 1073741824 512-byte logical blocks: (549 GB/512 GiB)[ 526.039690] sd 5:0:0:0: [sdc] 4096-byte physical blocks[ 526.053643] sd 5:0:0:0: [sdc] Write Protect is off[ 526.065818] sd 5:0:0:0: [sdc] Mode Sense: 0f 00 10 00[ 526.065985] sd 5:0:0:0: [sdc] Write cache: enabled, read cache: enabled, supports DPO and FUA[ 526.091754] sdc: unknown partition table[ 526.105263] sd 5:0:0:0: [sdc] Attached SCSI disk

You can see from these messages that the name of the block device assigned to the connected drive is /dev/sdc. Create a file system on it :

[root@pg-1 ~] $ mkfs.ext4 /dev/sdcmke2fs 1.42.9 (28-Dec-2013)/dev/sdc is entire device, not just one partition!Proceed anyway? (y, n) y

Now we have to create an entry in fstab. We get the UUID of the device :

[root@pg-1 ~] $ blkid /dev/sdc/dev/sdc: UUID="8cb25a32-175b-4c78-b557-8153327d48ba" TYPE="ext4"

And mount the disk, having previously created a mount point and added an entry in the fstab:

[root@pg-1 ~] $ mkdir -p /var/lib/pgsql[root@pg-1 ~] $ echo 'UUID=8cb25a32-175b-4c78-b557-8153327d48ba /var/lib/pgsql ext4 defaults 0 0' > > /etc/fstab[root@pg-1 ~] $ mount -a

It’s time to install software, connect the repository :

[root@pg-1 ~] $ rpm -ivh http://repo.postgrespro.ru/pgpro-9.5/keys/postgrespro-9.5.centos95.noarch.rpm

Installing Software :

[root@pg-1 ~] $ yum install postgrespro95-server postgrespro95-contrib

Turn on the service and initialize the instance of the DBMS :

[root@pg-1 ~] $ chkconfig postgresql-9.5 on[root@pg-1 ~] $ service postgresql-9.5 initdb[root@pg-1 ~] $ service postgresql-9.5 start

We will run the following commands as postgres user:

[root@pg-1 ~] $ su -l postgres

Allow connection to the server in pg_hba with ip for replication :

[postgres@pg-1 ~] $ echo 'host replication replication md5' > > /var/lib/pgsql/9.5/data/pg_hba.conf

Create a user for replication :

[postgres@pg-1 ~] $ /usr/pgsql-9.5/bin/psql -U postgres postgrespostgres=$ CREATE ROLEreplication WITH REPLICATION PASSWORD 'password' LOGIN;CREATE ROLE

Set parameters that allow this machine to be used as a master server for replication :

[postgres@pg-1 ~] $ /usr/pgsql-9.5/bin/psql -U postgres postgrespostgres=$ alter system set listen_addresses to '*';ALTER SYSTEMpostgres=$ alter system set wal_level to hot_standby;ALTER SYSTEMpostgres=$ alter system set max_wal_senders to 3;ALTER SYSTEMpostgres=$ alter system set wal_keep_segments to 128;ALTER SYSTEM

Check if the parameters have been applied :

postgres=$ select * from pg_file_settings where not applied;sourcefile | sourceline | seqno | name | setting | applied----------------------------------------------+------------+-------+------------------+-------------+---------/var/lib/pgsql/9.5/data/postgresql.auto.conf | 3 | 20 | wal_level | hot_standby | f/var/lib/pgsql/9.5/data/postgresql.auto.conf | 4 | 21 | max_wal_senders | 3 | f/var/lib/pgsql/9.5/data/postgresql.auto.conf | 6 | 23 | listen_addresses | * | f(3 rows)

Restarting the service :

[postgres@pg-1 ~] $ /usr/pgsql-9.5/bin/pg_ctl restart -D /var/lib/pgsql/9.5/data-w

Now we can ssh to pg-2 as user pg:

ssh pg-public-2 -l pg -i ~/.ssh/id_rsa

All further commands we execute on the pg-2 machine. Now it’s time to do the replica : similarly connect the disk and install postgrespro. And we login on pg-2 as postgres user:

[root@pg-2 ~] $ su -l postgres

After that we prepare a replica with one command :

[postgres@pg-2 ~] $ /usr/pgsql-9.5/bin/pg_basebackup -U replication -D /var/lib/pgsql/9.5/data -R -x -P -c spread -h -WPassword:38895/38895 kB (100%), 1/1 tablespace

Now we have a complete copy in /var/lib/pgsql/9.5/data and the prescribed file recovery.conf , in which I recommend adding information about the trigger file, which will turn this instance of the DBMS from a slave into a full-fledged master server. Thus, the contents of the file will be :

[postgres@pg-2 ~] $ cat /var/lib/pgsql/9.5/data/recovery.confstandby_mode = 'on'primary_conninfo = 'user=replication password=password host= port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'trigger_file = '/var/lib/pgsql/stop_replication_trigger'

Running a replica instance :

[postgres@pg-2 ~] $ /usr/pgsql-9.5/bin/pg_ctl start -w -D /var/lib/pgsql/9.5/data

And check if the WAL receiver (wal receiver process) is running:

[postgres@pg-2 ~] $ ps axufwwwwpostgres 29423 0.1 0.2 372920 16564 ? S 09:07 0:00 /usr/pgsql-9.5/bin/postmaster -D /var/lib/pgsql/9.5/datapostgres 29425 0.0 0.0 225568 1608 ? Ss 09:07 0:00 _ postgres: logger processpostgres 29426 0.1 0.0 372960 2464 ? Ss 09:07 0:00 _ postgres: startup process recovering 0000010000000000000003postgres 29427 0.0 0.0 372920 1964 ? Ss 09:07 0:00 _ postgres: checkpointer processpostgres 29428 0.0 0.0 372920 1976 ? Ss 09:07 0:00 _ postgres: writer processpostgres 29429 0.2 0.0 379640 3360 ? Ss 09:07 0:00 _ postgres: wal receiver process streaming 0/3000060

So we have two virtual machines with streaming asynchronous replication configured between them, running in the same availability group. In the next part of this article, we will discuss backups and client balancing.

Wait for the sequel!

You may also like