Postgres Pro Standard in Yandex.Cloud

Postgres Pro Standard in Yandex.Cloud Quick Start Guide

Internet access and valid Yandex.Cloud account are required to use Postgres Pro Standard 11/12 database in Yandex.Cloud.

Postgres Pro Standard 11/12 virtual machine (VM) image is available in Yandex.Cloud Marketplace.

Software required for installation:

• 'yc' as part of Yandex.Cloud CLI for cloud management
• 'psql' or 'Pgadmin' for database connection

Yandex.Cloud Console https://console.cloud.yandex.com can be used as well.

Yandex.Cloud CLI installation guide: https://cloud.yandex.com/docs/cli/quickstart

Yandex.Cloud CLI reference guide: https://cloud.yandex.com/docs/cli/

Connection to Yandex.Cloud and environment check

• Connect to Yandex.Cloud and configure environment with:

yc init

• Verify 'yc' version (should be the latest available):

yc version

• A list of regions and zones for VM:

yc compute zone list
yc config set compute-default-zone ru-central1-a
yc config get compute-default-zone

‘ru-central1’ region and 'ru-central1-a' zone will be used further.

• A list of available disk types:

yc compute disk-type list

‘network-hdd’ disk type will be used further.

VM creation

• Create network infrastructure for VM:

NET=my-vpc

yc vpc network create \
--name $NET \
--description "$NET" \
--async

yc vpc network list

yc vpc subnet create \
--name $NET-subnet \
--range 192.168.0.0/24 \
--network-name $NET \
--description "$NET-subnet" \
--async

yc vpc subnet list

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

ssh-keygen -t rsa -b 2048

• Use the following commands to create VM from the particular Postgres Pro Standard 11/12 image version available in Yandex.Cloud Marketplace:

yc compute image list --folder-id standard-images | grep -E "(NAME|pgpro)" | awk {'print $4'}

--- Postgres Pro Standard Database 11
IMAGE_NAME=$(yc compute image list --folder-id standard-images | grep -E "(NAME|pgpro)" | awk {'print $4'} | grep std1181)
or
--- Postgres Pro Standard Database 12
IMAGE_NAME=$(yc compute image list --folder-id standard-images | grep -E "(NAME|pgpro)" | awk {'print $4'} | grep std1231)

VM_NAME=myvm-std-xx

yc compute instance create \
--name $VM_NAME \
--hostname $VM_NAME \
--cores 2 \
--memory 2 \
--create-boot-disk name=$VM_NAME-osdisk,image-folder-id=standard-images,image-name=$IMAGE_NAME \
--network-interface subnet-name=$NET-subnet,nat-ip-version=ipv4 \
--ssh-key ~/.ssh/id_rsa.pub \
--async

yc compute instances show $VM_NAME

• Use the following commands to create VM from the latest Postgres Pro Standard 11/12 image version available in Yandex.Cloud Marketplace:

yc compute image list --folder-id standard-images | grep -E "(FAMILY|pgpro)" | awk {'print $6'}

--- Postgres Pro Standard Database 11
IMAGE_FAMILY=pgpro-std11-centos7
or
--- Postgres Pro Standard Database 12
IMAGE_FAMILY=pgpro-std12-centos7

VM_NAME=myvm-std-xx

yc compute instance create \
--name $VM_NAME \
--hostname $VM_NAME \
--cores 2 \
--memory 2 \
--create-boot-disk name=$VM_NAME-osdisk,image-folder-id=standard-images,image-family=$IMAGE_FAMILY \
--network-interface subnet-name=$NET-subnet,nat-ip-version=ipv4 \
--ssh-key ~/.ssh/id_rsa.pub \
--async

yc compute instances show $VM_NAME

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

Connection to VM

• Connect to VM with private ssh-key using VM's public IP-address ($VM_IP_ADDRESS):

yc compute instances list

VM_IP_ADDRESS=$(yc compute instance show --name $VM_NAME | grep -E ' +address' | tail -n 1 | awk '{print $2}')
echo $VM_IP_ADDRESS

ssh yc-user@$VM_IP_ADDRESS

Postgres Pro database service status

• Verify Postgres Pro database service status:

sudo systemctl -l status postgrespro-std-11.service
or
sudo systemctl -l status postgrespro-std-12.service

• To stop/start Postgres Pro database service use the following commands:

sudo systemctl stop postgrespro-std-11.service
sudo systemctl start postgrespro-std-11.service
or
sudo systemctl stop postgrespro-std-12.service
sudo systemctl start postgrespro-std-12.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 'yc' interface run 'exit' command twice

External connection to VM

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

ssh yc-user@$VM_IP_ADDRESS

sudo firewall-cmd --zone=public --add-port=5433/tcp
sudo firewall-cmd --zone=public --permanent --add-port=5433/tcp
sudo firewall-cmd --zone=public --list-ports

sudo systemctl restart firewalld.service
sudo systemctl -l status firewalld.service

exit

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

ssh yc-user@$VM_IP_ADDRESS

sudo firewall-cmd --zone=public --add-port=80/tcp
sudo firewall-cmd --zone=public --permanent --add-port=80/tcp
sudo firewall-cmd --zone=public --add-port=443/tcp
sudo firewall-cmd --zone=public --permanent --add-port=443/tcp
sudo firewall-cmd --zone=public --list-ports

sudo systemctl restart firewalld.service
sudo systemctl -l status firewalld.service

exit

External connection to Postgres Pro database

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

ssh yc-user@$VM_IP_ADDRESS

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

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

Replace '$VM_IP_ADDRESS' by VM's external IP-address.

External connection to database monitoring server

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

ssh yc-user@$VM_IP_ADDRESS

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

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

VM configuration change

Let's have some examples of VM configuration change:

1) Change VM type 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 yc-user@$VM_IP_ADDRESS

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

exit

• To change VM type use the following commands:

yc compute instances show $VM_NAME

yc compute instances stop $VM_NAME

yc compute instances update $VM_NAME \
--cores 4 \
--memory 4

yc compute instances start $VM_NAME

yc compute instances list

VM_IP_ADDRESS=$(yc compute instance show --name $VM_NAME | grep -E ' +address' | tail -n 1 | awk '{print $2}')
echo $VM_IP_ADDRESS

ssh yc-user@$VM_IP_ADDRESS

cat /proc/cpuinfo
cat /proc/meminfo

exit

2) Increase OS-disk size up to 80 GB

• Obtain OS-disk size details:

yc compute disks show $VM_NAME-osdisk

• Stop VM temporarily:

yc compute instances stop $VM_NAME

• Increase OS-disk size:

yc compute disks update $VM_NAME-osdisk \
--size 80

• Verify new OS-disk size:

yc compute disks show $VM_NAME-osdisk

• Start VM:

yc compute instances start $VM_NAME

• Connect to VM:

yc compute instances list

VM_IP_ADDRESS=$(yc compute instance show --name $VM_NAME | grep -E ' +address' | tail -n 1 | awk '{print $2}')
echo $VM_IP_ADDRESS

ssh yc-user@$VM_IP_ADDRESS

df -h /

exit

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:

yc compute disks create \
--name $VM_NAME-datadisk \
--size 200 \
--type network-hdd \
--async

yc compute disks list

yc compute instances attach-disk \
--name $VM_NAME \
--disk-name $VM_NAME-datadisk \
--async

yc compute disks list

• Connect to VM:

ssh yc-user@$VM_IP_ADDRESS

• Stop Postgres Pro database service and verify its status:

sudo systemctl stop postgrespro-std-11.service
sudo systemctl -l status postgrespro-std-11.service
or
sudo systemctl stop postgrespro-std-12.service
sudo systemctl -l status postgrespro-std-12.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/std-11/data/* /PGDATA/data; rmdir /var/lib/pgpro/std-11/data; ln -s /PGDATA/data /var/lib/pgpro/std-11/data
or
mv /var/lib/pgpro/std-12/data/* /PGDATA/data; rmdir /var/lib/pgpro/std-12/data; ln -s /PGDATA/data /var/lib/pgpro/std-12/data
exit

• Start Postgres Pro database service and verify its status:

sudo systemctl start postgrespro-std-11.service
sudo systemctl -l status postgrespro-std-11.service
or
sudo systemctl start postgrespro-std-12.service
sudo systemctl -l status postgrespro-std-12.service

• Restart VM, check filesystem automount and verify Postgres Pro database service status:

sudo reboot

ssh yc-user@$VM_IP_ADDRESS

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

sudo systemctl -l status postgrespro-std-11.service
or
sudo systemctl -l status postgrespro-std-12.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-std-11.service
sudo systemctl daemon-reload
sudo systemctl restart postgrespro-std-11.service
sudo systemctl -l status postgrespro-std-11.service
or
sudo sed -i '/KillSignal=/a Restart=on-failure' /usr/lib/systemd/system/postgrespro-std-12.service
sudo systemctl daemon-reload
sudo systemctl restart postgrespro-std-12.service
sudo systemctl -l status postgrespro-std-12.service

VM backup/restore

Postgres Pro Standard 11/12 VM content:

Linux CentOS 7.x (64-bit)

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

OS-disk size - 10 GB

• xfs filesystem ‘/’ (/dev/vda1) - 10 GB

Main database - Postgres Pro Standard

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

Database monitoring (server)

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

Database monitoring (agent)

• zabbix-agent version: 4.x
• mamonsu-agent version: 2.x
• configuration file: /etc/mamonsu/agent.conf

Auxiliary database PostgreSQL (as a zabbix-server database)

• DB version: 11/12
• 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
• Mamonsu: https://github.com/postgrespro/mamonsu/#mamonsu-monitoring-agent-for-postgresql
• Pgadmin: https://www.pgadmin.org/docs