FilipSedlak on devops

If you’re running your own installation of PostgreSQL and you ever wanted to take a snapshot to test something off, this tip is for you. You’ll have a writable snapshot of your database in a few seconds.

Note: If you run in AWS, then RDS is your best option even if its snapshot isn’t that fast. Still, RDS saves a lot of headaches.

0. Prerequisites

1. You’ll need your database data directory installed on a LVM’s “Logical Volume”. This means there will be another level of indirection between the data you write and their physical location on disks. Logical Volumes can span several disk or partitions.

2. Make sure you leave some space in the Volume Group that owns the Logical Volume for data. We’ll need this space for snapshots. Volume Group is just a group of physical disks where we can write some data.

   • On a fresh Ubuntu 18.04, you can achieve it this way:

       pvcreate /dev/sde          # prepares whole disk for LVM (you'll lose data)
       vgcreate mygroup /dev/sde  # adds a disk into the group
     
       # Create a volume. Make sure there is some space left on the disks.
       lvcreate --size 1000g --name data mygroup  # Creates a 1 TB volume.
     
       mkfs.ext4 /dev/mygroup/data  # The volume appears as a new empty disk.
                                    # Let's add a filesystem to it.
     
       mkdir -p /var/lib/postgresql/10/main
       mount /dev/mygroup/data /var/lib/postgresql/10/main
     
       # Now, when we have the space for data ready, we can install PostgreSQL. The installer
       # script will create a first database cluster named "main".
       apt-get install postgresql-10
     

3. Copy the config files /etc/postgresql/10/{main -> snapshot} and make a few changes to postgresql.conf:

   • data_directory and external_pid_file: replace main -> snapshot
   • port: assign a new one, e. g. 5433
   • cluster_name: replace main -> snapshot
     
     

4. Finally, prepare a data directory for the snapshot:

    mkdir /var/lib/postgresql/10/snapshot
   

1. Snapshot

OK, now we have a database at /var/lib/postgresql/10/main/ and its configuration at /etc/postgresql/10/main/.

Once you have this ready, you can start snapshotting.

lvcreate --size 300G --name snapshot --snapshot mygroup/data

This takes an atomic snapshot ot the data on the Logical Volume. It’s instant, as if you plugged the power cable. Databases are designed to withstand such event.

The snapshot size means the amount of data on disk that can be different between the original volume and the snapshot. This includes writing to either of the volumes. The snapshot operation is very fast because the underlying disk blocks are initially shared between the two volumes.

2. Start the snapshot

Now, mount it to filesystem:

mount /dev/mygroup/snapshot /var/lib/postgresql/10/snapshot

Start the new cluster:

systemctl start postgresql@10-snapshot

Now, the database will start recovery as if we’d plug the power off. In a second, you can connect to it via

PGPORT=5433 psql

You’ll see the same database taken in a point of time. You can test destructive changes then throw the snapshot away and start anew.

If you don’t want to put extra load on you production server, perform the snapshotting on a streaming replica. There, you’ll need to also remove the recovery.conf file from the snapshot to put the database into writable mode.

Cleaning up

systemctl stop postgresql@10-snapshot
umount /var/lib/postgresql/10/snapshot
lvremove mygroup/snapshot  # This may take 10s of seconds.

If you keep the config files in /etc/postgresql/10/snapshot, you’re ready for the next time.

Automation

If you want to automate this process, I find it easier to keep the config files within the datadir so they contain relative paths. They’re then snapshotted as well so the script will just change the port and cluster name and start a new Postgres daemon.