Running PostgreSQL on GCE Container VM

This tutorial show how to run PostgreSQL on GCE Container VM with persistent disk.

  1. Create disk for store Postgres data
$ gcloud compute disks create disk-postgres-dev --size=20GB --type=pd-standard
NAME ZONE SIZE_GB TYPE STATUS
disk-postgres-dev asia-southeast1-b 20 pd-standard READY

2. Create an instance for format disk

$ gcloud compute instances create instance-1 --machine-type f1-micro
NAME ZONE MACHINE_TYPE PREEMPTIBLE INTERNAL_IP EXTERNAL_IP STATUS
instance-1 asia-southeast1-b f1-micro 10.148.0.7 35.198.233.126 RUNNING

3. Attach disk into instance

$ gcloud compute instances attach-disk instance-1 --disk disk-postgres-dev

4. SSH into the instance

$ gcloud compute ssh instance-1

5. List disks

$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 10G 0 disk
`-sda1 8:1 0 10G 0 part /
sdb 8:16 0 20G 0 disk

6. Format disk

$ sudo mkfs.ext4 -m 0 -F -E lazy_itable_init=0,lazy_journal_init=0,discard /dev/sdb
mke2fs 1.43.4 (31-Jan-2017)
Discarding device blocks: done
Creating filesystem with 5242880 4k blocks and 1310720 inodes
Filesystem UUID: 329387ab-abe4-43ce-be6c-243cf881cbfa
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000
Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

7. Exit from vm

$ exit
logout
Connection to 35.198.233.126 closed.

8. Delete the instance

$ gcloud compute instances delete instance-1

9. Create PostgreSQL container instance

$ gcloud beta compute instances create-with-container postgres-dev \
--machine-type f1-micro \
--container-image postgres:10.1 \
--container-mount-host-path mount-path=/var/lib/postgresql/data,host-path=/mnt/disks/data/data,mode=rw \
--metadata ^:^startup-script="mkdir -p /mnt/disks/data && mount -o discard,defaults /dev/sdb /mnt/disks/data" \
--disk "name=disk-postgres-dev,device-name=disk-postgres-dev,mode=rw,boot=no" \
--tags allow-postgres

10. Verify disk inside PostgreSQL instance

$ gcloud compute ssh postgres-dev
$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 10G 0 disk
├─sda1 8:1 0 5.9G 0 part /mnt/stateful_partition
├─sda2 8:2 0 16M 0 part
├─sda3 8:3 0 2G 0 part
├─sda4 8:4 0 16M 0 part
├─sda5 8:5 0 2G 0 part
├─sda6 8:6 0 512B 0 part
├─sda7 8:7 0 512B 0 part
├─sda8 8:8 0 16M 0 part /usr/share/oem
├─sda9 8:9 0 512B 0 part
├─sda10 8:10 0 512B 0 part
├─sda11 8:11 0 8M 0 part
└─sda12 8:12 0 32M 0 part
sdb 8:16 0 20G 0 disk /mnt/disks/data
$ sudo ls /mnt/disks/data/data
PG_VERSION pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans pg_wal postgresql.conf
base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_xact postmaster.opts
global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase postgresql.auto.conf postmaster.pid

11. Setup Firewall

$ gcloud compute firewall-rules create allow-postgres \
--allow tcp:5432 --target-tags allow-postgres

12. Upgrade PostgreSQL to new version

$ gloud beta compute instances update-container postgres-dev \
--container-image=postgres:10.2

Done 😀