Postgresql for the SQL Server DBA: Backups, copy that file

So the next method of backup we will discuss is a file system level backup.  The file system backup involves shutting down the postgresql server and then creating a tar of the directory containing all the data files for the database.  It’s a very simple but inconvenient method to backup the database because the server must be down for the backup to be created.  On the SQL Server side, the most similar option is detaching and attaching databases.

Postgresql documentation also recommends a variation on this process where you take a frozen snapshot of the data directory and the WAL files.  This would eliminate the need to shutdown the database server.  However, in this scenario, Postgresql will assume that the database server was not properly shutdown and perform recovery.  This is the reason you need the WAL files available. You can reduce the recovery time by performing a checkpoint prior to the backup.

So lets see how the file copy backup works.

First, find the OID (similar to the database_id in sys.databases) as this is the name of the directory in the data directory where data files are stored.

Query window to find the OID number using query: SELECT oid,datname FROM pg_database.

You can place the data directory anywhere depending on the database setup but common locations are /usr/local/pgsql/data or /var/lib/pgsql/data.  Here you can see the directory for dvdrental, which is 16399.

Directory listing of the base data directory.

You can then stop the postgresql server.

Stopping the posgresql server service.

Now that the server is down,  you can run the tar command to create the tar file of the database files.

Creating a tar of the database data directory: tar -cf /root/Downloads/backup.tar /var/lib/postgresql/9.1/main/base/16399.  16339 is the OID for the database.

Finally, restart the postgresql server.

Start the postgresql server service.

See my future post about how to perform archiving of WAL files for point-in-time restores.