PGCon2013 - Final Release

PGCon 2013
The PostgreSQL Conference

Keith Paskett
Day Tutorials - Day 2 - 2013-05-22
Room MRT 256
Start time 09:00
Duration 03:00
ID 612
Event type Workshop
Track Tutorial
Language used for presentation English

PostgreSQL on ZFS

Replication, Backup, and Human Disaster Recovery

The ZFS filesystem can be used simplify a number of replication, backup and recovery challenges. In this hands-on workshop, an introduction to the ZFS file system's features and capabilities will be presented, then a walk through of common scenarios where ZFS can save considerable hassle and countless hours when users and/or developers go astray. A VM will be provided for a hands-on experience.

Attendees will have access an OmniOS VitualBox(R) image with PostgreSQL installed. The virtual machine will also have PostgreSQL instances configured for file-based log shipping, and streaming log replication. All of the commands needed to complete the following exercises will be supplied.


Replicate a PostgreSQL database to a slave server with zero down time on the master using zfs send.
Update the slave server using an incremental ZFS send.
Set up timed snapshots of your production data.
Spin up a second PostgreSQL instance on a ZFS snapshot of your master or slave database. This lets you set up test or validation instances with virtually no additional disk space required. Perform tests on real data, and if things don't go so well, restore the database to its original state in seconds.
Turn the slave server into the master and send updates back to the old master.

We will also discuss file-based and streaming log replication, and how these can be used in conjunction with ZFS.

If you want to perform the exercises in the workshop, you will need a laptop capable capable of running VirtualBox with 2-4GB allocated to the VM.