Postgres Pro Standard in Alibaba Cloud

Postgres Pro Standard in Alibaba Cloud Quick Start Guide

Internet access and valid Alibaba Cloud account are required to use Postgres Pro 9.6/10/11 database in Alibaba Cloud.

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

Software required for installation:

• 'aliyun-cli' 3.0 for cloud management
• 'psql' or 'Pgadmin' for database connection

'aliyun-cli' 3.0 is a cross-platform command line utility.

Alibaba Cloud Console https://home.console.aliyun.com/ can be used as well.

'aliyun-cli' 3.0 installation guide: https://github.com/aliyun/aliyun-cli?spm=a2c63.p38356.a3.1.2b094388i8mudh#installation

'aliyun-cli' 3.0 reference guide: https://www.alibabacloud.com/help/doc-detail/66653.htm?spm=a2c63.p38356.b99.2.3e564020fHDYUD

Connection to Alibaba Cloud and environment check

• Connect to Alibaba Cloud and configure environment with:

aliyun configure

• Verify 'aliyun-cli' version (should be the latest available):

aliyun --version | head -1

• A list of regions and zones for VM:

aliyun ecs DescribeRegions \
--output cols=RegionId rows=Regions.Region

RegionId=ap-southeast-1
echo $RegionId

aliyun ecs DescribeZones \
--RegionId $RegionId \
--output cols=ZoneId rows=Zones.Zone

ZoneId=ap-southeast-1a
echo $ZoneId

‘ap-southeast-1’ region and 'ap-southeast-1a' zone will be used further.

• A list of available VM sizes:

aliyun ecs DescribeInstanceTypes \
--output cols=InstanceTypeId,CpuCoreCount,MemorySize rows=InstanceTypes.InstanceType

InstanceTypeId=ecs.xn4.small
echo $InstanceTypeId

‘ecs.xn4.small’ VM size will be used further.

• Obtain VM Postgres Pro image names available in Alibaba Cloud Marketplace:

for i in `seq 1 10`; do
aliyun ecs DescribeImages \
--RegionId $RegionId \
--output cols=ImageId,ImageName,Progress,Status rows=Images.Image \
--PageSize 100 \
--ImageOwnerAlias marketplace \
--PageNumber $i | grep 'Postgres Pro Standard'
done

The following VM image ID will be used further:

--- Postgres Pro Standard Database 9.6 (CentOS 7) 9.6.12.1
ImageId=m-t4nd1cy0175pblty1jf0
echo $ImageId
or
--- Postgres Pro Standard Database 10 (CentOS 7) 10.7.1
ImageId=m-t4nf34aaoe6kjtldkbjh
echo $ImageId
or
--- Postgres Pro Standard Database 11 (CentOS 7) 11.2.1
ImageId=m-t4n3svi6udc01oy5s4wa
echo $ImageId

VM creation

• Create virtual network infrastructure:

aliyun ecs CreateVpc \
--RegionId $RegionId \
--CidrBlock '192.168.0.0/16' \
--VpcName ppro-vpc \
--Description ppro-vpc

VpcId=$(aliyun ecs DescribeVpcs --RegionId $RegionId --output cols=VpcId rows=Vpcs.Vpc | grep '^vpc')
echo $VpcId

aliyun ecs CreateVSwitch \
--ZoneId $ZoneId \
--CidrBlock '192.168.0.0/24' \
--VpcId $VpcId \
--VSwitchName ppro-vsw \
--Description ppro-vsw

VSwitchId=$(aliyun ecs DescribeVSwitches --RegionId $RegionId --output cols=VSwitchId rows=VSwitches.VSwitch | grep '^vsw')
echo $VSwitchId

• Create and configure network security group:

aliyun ecs CreateSecurityGroup \
--RegionId $RegionId \
--Description ppro-sg \
--SecurityGroupName ppro-sg \
--VpcId $VpcId

SecurityGroupId=$(aliyun ecs DescribeSecurityGroups --RegionId $RegionId --output cols=SecurityGroupId rows=SecurityGroups.SecurityGroup | grep '^sg')
echo $SecurityGroupId

aliyun ecs AuthorizeSecurityGroup \
--RegionId $RegionId \
--SecurityGroupId $SecurityGroupId \
--IpProtocol tcp \
--PortRange 22/22 \
--Policy accept \
--SourceCidrIp 0.0.0.0/0 \
--Priority 1 \
--Description ssh

• Create a pair of private/public ssh-keys in ~/.ssh directory and import public ssh-key for VM connection:

ssh-keygen -t rsa -b 2048

KeyPairNameId=ppro-key
echo $KeyPairNameId

aliyun ecs ImportKeyPair \
--RegionId $RegionId \
--PublicKeyBody '...' \
--KeyPairName $KeyPairNameId

aliyun ecs DescribeKeyPairs \
--RegionId $RegionId \
--KeyPairName $KeyPairNameId

• Create VM from VM image available in Alibaba Cloud Marketplace:

InstanceNameId=myvm-std-xx
echo $InstanceNameId

aliyun ecs CreateInstance \
--RegionId $RegionId \
--ImageId $ImageId \
--InstanceType $InstanceTypeId \
--SecurityGroupId $SecurityGroupId \
--InstanceName $InstanceNameId \
--HostName $InstanceNameId \
--ZoneId $ZoneId \
--Description $InstanceNameId \
--InternetMaxBandwidthIn 1 \
--InternetMaxBandwidthOut 1 \
--VSwitchId $VSwitchId \
--InstanceChargeType PostPaid \
--IoOptimized optimized \
--SystemDisk.Category cloud_efficiency \
--SystemDisk.Size 40 \
--KeyPairName $KeyPairNameId \
--SecurityEnhancementStrategy active

• Assign external IP-address to VM and start VM:

InstanceId=$(aliyun ecs DescribeInstances --RegionId $RegionId --output cols=InstanceId rows=Instances.Instance | grep '^i-')
echo $InstanceId

aliyun ecs AllocatePublicIpAddress \
--InstanceId $InstanceId

IpAddress=$(aliyun ecs DescribeInstances --RegionId $RegionId --output cols=PublicIpAddress.IpAddress rows=Instances.Instance | grep [0-9] | tr -d '[]')
echo $IpAddress

aliyun ecs StartInstance \
--InstanceId $InstanceId

aliyun ecs DescribeInstances \
--RegionId $RegionId \
--output cols=InstanceId,Status,InstanceName rows=Instances.Instance

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

Connection to VM

As a result VM is created with external IP-address - $IpAddress.

• Connect to VM with private ssh-key:

ssh root@$IpAddress

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 'aliyun-cli' 3.0 interface run 'exit' command twice

External connection to VM

• TCP-port 5433 has to be opened for external connection to Postgres Pro database:

aliyun ecs AuthorizeSecurityGroup \
--RegionId $RegionId \
--SecurityGroupId $SecurityGroupId \
--IpProtocol tcp \
--PortRange 5433/5433 \
--Policy accept \
--SourceCidrIp 0.0.0.0/0 \
--Priority 2 \
--Description postgres

• TCP-ports 80 and 443 have to be opened for external connection to database monitoring server:

aliyun ecs AuthorizeSecurityGroup \
--RegionId $RegionId \
--SecurityGroupId $SecurityGroupId \
--IpProtocol tcp \
--PortRange 80/80 \
--Policy accept \
--SourceCidrIp 0.0.0.0/0 \
--Priority 3 \
--Description http

aliyun ecs AuthorizeSecurityGroup \
--RegionId $RegionId \
--SecurityGroupId $SecurityGroupId \
--IpProtocol tcp \
--PortRange 443/443 \
--Policy accept \
--SourceCidrIp 0.0.0.0/0 \
--Priority 4 \
--Description https

External connection to Postgres Pro database

• For external connection to Postgres Pro database set up 'postgres' user password:

ssh root@$IpAddress

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=$IpAddress --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’
  • ‘$IpAddress’ for ‘Host’
  • ‘5433’ for ‘Port’
  • ‘postgres’ for ‘Maintenance DB’
  • ‘postgres’ for ‘Username’

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

Replace 'IpAddress' by VM's external IP-address.

External connection to database monitoring server

• For connection to database monitoring server set up 'Admin' user password:

ssh root@$IpAddress

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://$IpAddress/zabbix

Replace ‘$IpAddress’ by VM's external IP-address.

VM configuration change

Let's have some examples of VM configuration change:

1) Change VM size from ‘ecs.xn4.small’ to ‘ecs.n1.medium’ 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 root@$IpAddress

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

exit

• Obtain a list of available VM sizes:

aliyun ecs DescribeInstanceTypes \
--output cols=InstanceTypeId,CpuCoreCount,MemorySize rows=InstanceTypes.InstanceType

• To change VM size use the following commands:

aliyun ecs StopInstance \
--InstanceId $InstanceId

InstanceTypeId=ecs.n1.medium
echo $InstanceTypeId

aliyun ecs ModifyInstanceSpec \
--InstanceId $InstanceId \
--InstanceType $InstanceTypeId

aliyun ecs StartInstance \
--InstanceId $InstanceId

2) Increase OS-disk size up to 80 GB

• Obtain OS-disk size details:

aliyun ecs DescribeDisks \
--RegionId $RegionId \
--DiskType=system \
--output cols=Type,Device,Size rows=Disks.Disk

• Stop VM temporarily:

aliyun ecs StopInstance \
--InstanceId $InstanceId

• Increase OS-disk size:

DiskId=$(aliyun ecs DescribeDisks --RegionId $RegionId --DiskType=system --output cols=DiskId rows=Disks.Disk | grep '^d-')
echo $DiskId

aliyun ecs ResizeDisk \
--DiskId $DiskId \
--NewSize 80

• Verify new OS-disk size:

aliyun ecs DescribeDisks \
--RegionId $RegionId \
--DiskType=system \
--output cols=Type,Device,Size rows=Disks.Disk

• Start VM:

aliyun ecs StartInstance \
--InstanceId $InstanceId

• Connect to VM:

ssh root@$IpAddress

• Increase ‘/’ filesystem partition size:

(echo d; echo 1; echo n; echo p; echo 1; echo ; echo ; echo w) | sudo fdisk /dev/vda

• Restart VM:

sudo reboot

• Connect to VM:

ssh root@$IpAddress

• Increase ‘/’ filesystem size:

sudo resize2fs /dev/vda1

• Restart VM:

sudo reboot

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:

aliyun ecs CreateDisk \
--RegionId $RegionId \
--ZoneId $ZoneId \
--DiskName myvm-std-xx-datadisk \
--Description myvm-std-xx-datadisk \
--DiskCategory cloud_efficiency \
--Size 200

DiskId=$(aliyun ecs DescribeDisks --RegionId $RegionId --DiskType=data --output cols=DiskId rows=Disks.Disk | grep '^d-')
echo $DiskId

aliyun ecs AttachDisk \
--InstanceId $InstanceId \
--DiskId $DiskId

• Connect to VM:

ssh root@$IpAddress

• 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 'fdisk' utility to find out datadisk device name (in this case it is '/dev/vdb'):

sudo fdisk -l

• 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/vdb
sudo mkfs -t ext4 /dev/vdb1

• Amend /etc/fstab file for new filesystem automount and mount it:

sudo sh -c "echo '`sudo blkid -o export /dev/vdb1 | 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 root@$IpAddress

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

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 - 40 GB

• ext4 filesystem ‘/’ (/dev/vda1) - 40 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.4.1
• 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

• Postgres Pro Standard: https://postgrespro.com/docs/postgrespro
• Zabbix: https://www.zabbix.com/documentation/3.4/start
• Mamonsu: https://github.com/postgrespro/mamonsu/#mamonsu-monitoring-agent-for-postgresql
• Pgadmin: https://www.pgadmin.org/docs