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.
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.
You can then stop the postgresql server.
Now that the server is down, you can run the tar command to create the tar file of the database files.
Finally, restart the postgresql server.
See my future post about how to perform archiving of WAL files for point-in-time restores.