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.

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:

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.

Comments

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).

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.

A not so short guide to TDD SaltStack formulas

One of the hardest parts about Infrastructure As Code and Configuration Management is establishing a discipline on developing, testing and deploying changes.
Developers follow established practices and tools have been built and perfected over the last decade and a half. On the other hand sysadmins and ops people do not have the same tooling and culture because estensive automation has only become a trend recently.

So if Infrastructure As Code allows you to version the infrastructure your code runs on, what good is it if then there are no tools or established practices to follow?

Luckily the situation is changing and in this post I'm outlining a methodology for test driven development of SaltStack Formulas.

The idea is that with a single command you can run your formula against a matrix of platforms (operating systems) and suites (or configurations). Each cell of the matrix will be tested and the result is a build failure or success much alike to what every half-decent developer of…