Fun with Postgresql and ZFS
I will show how to use ZFS instant snapshotting and cloning functionality to effortlessly clone a running postgres database regardless of its size.
Setup
Install your Linux OS of choice then ZFS and Postgres. I use Centos 7 but most commands used in this post are distro-indipendent.
Create a zfs pool called tank or use whatever name suits you. In the pool create a filesystem called pgdata. For the sake of following a minimalist ZFS best practice apply the following settings:
zfs set compression=lz4 tank/pgdata zfs set xattr=sa tank/pgdata
Now move the postgresql directory to /tank/pgdata and symlink its original location to the new one. If you have selinux enabled and are in a hurry set it to permissive or just disable it. Otherwise relabel the new location under /tank/pgdata.
In the case of Centos7 I would have initialized the database with postgresql-setup initdb then moved /var/lib/pgsql to /tank/pgdata/pgsql.
For the purpose of this post it is not necessary to configure postgres to enable archive logs. If you do, and you should, everything will work the same.
Start postgres using the command available to your system. On Centos7 it is:
Let's load some data (around 5 million rows) with pgbench (run psql and pgbench as the postgres user):
While pgbench is running take a snaphost of the pgdata filesystem:
If pgbench finishes before you can take the snapshot simply run it again. If pgbench takes a long time you should try and take another snapshot or as many as you want. Notice how the snapshot is instantaneous and does not affect pgbench at all.
In real life you would probably want to use the ZFS autosnapshot tool to manage snapshots for you.
Now, suppose we need to inspect the database at a certain point in time, like while pgbench was running. All we need to do is:
We have now postgres running on port 1234 and we can login using psql or whatever sql tool.
When we are done it is sufficient to CTRL^C the postgres process and destroy the clone:
Before destroy the clone check your ZFS filesystems (zfs list) and marvel at how little space they are using. If you are so inclined also check compression factor.
In the case of Centos7 I would have initialized the database with postgresql-setup initdb then moved /var/lib/pgsql to /tank/pgdata/pgsql.
For the purpose of this post it is not necessary to configure postgres to enable archive logs. If you do, and you should, everything will work the same.
Start postgres using the command available to your system. On Centos7 it is:
systemctl start postgres
Let's load some data (around 5 million rows) with pgbench (run psql and pgbench as the postgres user):
psql -c "create database sample" pgbench -i -s 50 -n
While pgbench is running take a snaphost of the pgdata filesystem:
zfs snapshot tank/pgdata@duringpgbench
If pgbench finishes before you can take the snapshot simply run it again. If pgbench takes a long time you should try and take another snapshot or as many as you want. Notice how the snapshot is instantaneous and does not affect pgbench at all.
In real life you would probably want to use the ZFS autosnapshot tool to manage snapshots for you.
Now, suppose we need to inspect the database at a certain point in time, like while pgbench was running. All we need to do is:
- find the snapshot
- clone it
- run postgres from the clone
Note that the clone will not use any more space than that needed by postgres to recover the database. An unwritten to clone will use zero disk space, regardless of the filesystem size and usage.
In our case the snapshot is called tank/pgdata@duringpgbench and can be cloned with the following command:
zfs clone tank/pgdata@duringpgbench tank/duringpgbench_clone
We can now run postgres on the clone (remove the pid file or postgres will refuse to start):
cd /tank/duringpgbench_clone/pgsql rm data/*.pid postgres -D data -p 1234
We have now postgres running on port 1234 and we can login using psql or whatever sql tool.
When we are done it is sufficient to CTRL^C the postgres process and destroy the clone:
zfs destroy tank/duringpgbench_clone
Before destroy the clone check your ZFS filesystems (zfs list) and marvel at how little space they are using. If you are so inclined also check compression factor.