Skip to main content

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.


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:

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:
  1. find the snapshot
  2. clone it
  3. 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.


Popular posts from this blog

Indexing Apache access logs with ELK (Elasticsearch+Logstash+Kibana)

Who said that grepping Apache logs has to be boring?

The truth is that, as Enteprise applications move to the browser too, Apache access logs are a gold mine, it does not matter what your role is: developer, support or sysadmin. If you are not mining them you are most likely missing out a ton of information and, probably, making the wrong decisions.
ELK (Elasticsearch, Logstash, Kibana) is a terrific, Open Source stack for visually analyzing Apache (or nginx) logs (but also any other timestamped data).

A not so short guide to ZFS on Linux

Updated Oct 16 2013: shadow copies, memory settings and links for further learning.
Updated Nov 15 2013: shadow copies example, samba tuning.

Unless you've been living under a rock you should have by now heard many stories about how awesome ZFS is and the many ways it can help with saving your bacon.

The downside is that ZFS is not available (natively) for Linux because the CDDL license under which it is released is incompatible with the GPL. Assuming you are not interested in converting to one of the many Illumos distributions or FreeBSD this guide might serve you as a starting point if you are attracted  by ZFS features but are reluctant to try it out on production systems.

Basically in this post I note down both the tought process and the actual commands for implementing a fileserver for a small office. The fileserver will run as a virtual machine in a large ESXi host and use ZFS as the filesystem for shared data.

From 0 to ZFS replication in 5m with syncoid

The ZFS filesystem has many features that once you try them you can never go back. One of the lesser known is probably the support for replicating a zfs filesystem by sending the changes over the network with zfs send/receive.
Technically the filesystem changes don't even need to be sent over a network: you could as well dump them on a removable disk, then receive  from the same removable disk.