How to Back Up a PostgreSQL Database

Product version: 8.5

Last modified: 26 March 2019

Question

What is a good practice for stopping/starting a PostgreSQL database to back it up with NAKIVO Backup & Replication?

Answer

Quiescing all database connections is crucial for obtaining a consistent database backup.

Currently, you cannot freeze database I/O operations or temporarily lock databases in PostgreSQL.

However, you can use the following approaches:

  • Apply a file system level backup. This requires shutting down the database server first; if the database server is not shut down, you will not get a usable backup due to internal buffering in the server.
    Moreover, if your database is split across multiple tablespaces or the write-ahead log is on a separate disk from the table’s heap, you will not be able to use this approach.
    Refer to the File System Level Backup section of the PostgreSQL Manual for more details.
  • Rely on I/O freezing/unfreezing utilities available for some file systems. You will be able to freeze the file system, copy the database files, and then unfreeze the file system.
    For example, refer to the Suspending an XFS File System section of the Red Hat Enterprise Linux 7 Storage Administration Guide.
  • Make a non-exclusive low-level backup. It is based on pg_start_backup and pg_stop_backup commands. This allows you to run several concurrent backups. In addition, it provides a consistent backup without stopping write operations and avoids using file-system-level atomic snapshots.
    For details, refer to the Making a Base Backup Using the Low Level API section of the PostgreSQL Manual.
  • Make a base backup. It is based on the pg_basebackup tool. This allows you to create a base backup either as regular files or as a .tar archive. However, it is less flexible than the previous approach.
    This is the simplest approach. Use this approach if you are not concerned about the amount of time it takes to perform a base backup.
    For details, refer to the Making a Base Backup section of the PostgreSQL Manual.