Postgres Pro Standard in Google Cloud

Postgres Pro Standard in Google Cloud Quick Start Guide

Internet access and valid Google Cloud Platform (GCP) account are required to use Postgres Pro 9.6/10/11 database in Google Cloud.

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

Software required for installation:

• Google Cloud SDK for cloud management
• 'psql' or 'Pgadmin' for database connection

'gcloud', as part of Google Cloud SDK, is a cross-platform command line utility.

Google Cloud Console https://console.cloud.google.com can be used as well.

Google Cloud SDK installation guide: https://cloud.google.com/sdk/install

Google Cloud SDK reference guide: https://cloud.google.com/sdk/

Connection to Google Cloud Platform and environment check

• Connect to Google Cloud Platform and configure environment with:

gcloud init

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

gcloud --version

• A list of regions and zones for VM:

gcloud compute regions list
gcloud compute zones list

gcloud config set compute/region europe-north1
gcloud config set compute/zone europe-north1-a

gcloud compute project-info add-metadata \
--metadata google-compute-default-region=europe-north1,google-compute-default-zone=europe-north1-a

REGION=europe-north1
echo $REGION
ZONE=europe-north1-a
echo $ZONE

‘europe-north1’ region and 'europe-north1-a' zone will be used further.

• A list of available VM types and disk types:

gcloud compute machine-types list \
--filter="zone:(europe-north1-a)"

gcloud compute disk-types list \
--filter="zone:(europe-north1-a)"

‘n1-standard-1’ VM type and ‘pd-standard’ disk type will be used further.

• Obtain VM Postgres Pro names of images available in Google Cloud Marketplace:

gcloud compute images list | grep -E "(NAME|postgres-pro)"

The following VM image ID will be used further (PROJECT and FAMILY):

PROJECT=postgres-pro
echo $PROJECT

--- Postgres Pro Standard Database 9.6
FAMILY=ppro-std96-centos7
echo $FAMILY
or
--- Postgres Pro Standard Database 10
FAMILY=ppro-std10-centos7
echo $FAMILY
or
--- Postgres Pro Standard Database 11
FAMILY=ppro-std11-centos7
echo $FAMILY

VM creation

• 'auto mode default network' will be used furhter:

gcloud compute networks list
gcloud compute routes list
gcloud compute firewall-rules list

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

ssh-keygen -t rsa -b 2048

ln -s ~/.ssh/id_rsa ~/.ssh/google_compute_engine
ln -s ~/.ssh/id_rsa.pub ~/.ssh/google_compute_engine.pub

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

INSTANCE=myvm-std-xx
echo $INSTANCE

gcloud compute instances create $INSTANCE \
--async \
--boot-disk-type=pd-standard \
--image-family=$FAMILY \
--image-project=$PROJECT \
--machine-type=n1-standard-1

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 ($IpAddress):

gcloud compute instances list

gcloud compute ssh SYSTEM@$INSTANCE
or
IpAddress=$(gcloud compute instances list | grep $INSTANCE | awk '{print $5}')
echo $IpAddress
ssh SYSTEM@$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 'gcloud' interface run 'exit' command twice

External connection to VM

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

gcloud compute firewall-rules create ppro-allow-postgres \
--allow=tcp:5433 \
--description="Allow Postgres from anywhere" \
--direction=INGRESS \
--network=default \
--priority=1000

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

gcloud compute firewall-rules create ppro-allow-http \
--allow=tcp:80 \
--description="Allow HTTP from anywhere" \
--direction=INGRESS \
--network=default \
--priority=1000

gcloud compute firewall-rules create ppro-allow-https \
--allow=tcp:443 \
--description="Allow HTTPS from anywhere" \
--direction=INGRESS \
--network=default \
--priority=1000

External connection to Postgres Pro database

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

gcloud compute ssh SYSTEM@$INSTANCE

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:

gcloud compute ssh SYSTEM@$INSTANCE

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 type from ‘n1-standard-1’ to ‘n1-standard-2’ 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:

gcloud compute ssh SYSTEM@$INSTANCE

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 types:

gcloud compute machine-types list \
--filter="zone:(europe-north1-a)"

• To change VM type use the following commands:

gcloud compute instances stop $INSTANCE

InstanceType=n1-standard-2
echo $InstanceType

gcloud compute instances set-machine-type $INSTANCE \
--machine-type $InstanceType

gcloud compute instances start $INSTANCE

2) Increase OS-disk size up to 80 GB

• Obtain OS-disk size details:

gcloud compute disks list | grep -E "(NAME|$INSTANCE)"

• Stop VM temporarily:

gcloud compute instances stop $INSTANCE

• Increase OS-disk size:

echo "y" | gcloud compute disks resize $INSTANCE \
--size=80GB \
--zone=$ZONE

• Verify new OS-disk size:

gcloud compute disks list | grep -E "(NAME|$INSTANCE)"

• Start VM:

gcloud compute instances start $INSTANCE

• Connect to VM:

gcloud compute ssh SYSTEM@$INSTANCE

• Increase ‘/’ filesystem partition size:

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

• Restart VM:

sudo reboot

• Connect to VM:

gcloud compute ssh SYSTEM@$INSTANCE

• Increase ‘/’ filesystem size:

sudo xfs_growfs -d /dev/sda1

• 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:

gcloud compute disks create $INSTANCE-datadisk \
--size=200GB \
--type=pd-standard \
--zone=$ZONE

gcloud compute instances attach-disk $INSTANCE \
--disk $INSTANCE-datadisk

• Connect to VM:

gcloud compute ssh SYSTEM@$INSTANCE

• 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/sdb'):

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/sdb
sudo mkfs -t ext4 /dev/sdb1

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

sudo sh -c "echo '`sudo blkid -o export /dev/sdb1 | 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

gcloud compute ssh SYSTEM@$INSTANCE

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

• xfs filesystem ‘/’ (/dev/sda1) - 10 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