PostgreSQL: File System Level Backup to just Copy DATA Directory

For reading this article title, Please do not surprise.
Yes, this is possible in PostgreSQL.

We can simply copy the PostgreSQL DATA Directory and can use as a full PostgreSQL Database Server backup.

postgres files ystem backup

The File System Level backup is not always recommended, but we can use this backup method in certain condition.

In our organization, I am managing PostgreSQL backup using two approaches.

The First approach is a regular method for taking individual Database Backup using pgDump.

The Second approach is to take “frozen snapshot” of the PostgreSQL DATA directory. Our System Admin department is taking File System Level Backup of PostgreSQL.

Whenever I required new duplicate PostgreSQL server, I used this kind of file system backup of PostgreSQL.

It is faster than pgDump because It does not require any additional CPU I/O as full DATA directory already available on DISK.
System Admin has to perform simple COPY and PASTE like any other file or folder.

Few Restrictions are:

The database server must be shut down in order to get a usable backup and disallowing all connections will not work.

It is impossible to restore only a table or few tables. A File system backups only work for complete backup and restoration of an entire database cluster.

If your data files and WAL log are on different disks, or if tablespaces are on different file systems, it might not be possible to use snapshot backup or file system level backup.

A file system backup will typically be larger than an SQL dump.

After restoring of file system backup, It requires to rebuild all your Database Indexes.(For improving the performance)

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz