How to Back Up Your PostgreSQL Database
Updated by Jared Kobos Written by Jared Kobos
If you are using PostgreSQL in a production environment, it is important to take precautions to ensure that your users’ data is not lost. By frequently backing up your database, and/or automating backups with a cron task, you will be able to quickly restore your system in the event that your database is lost or corrupted. Fortunately, PostgreSQL includes tools to make this task simple and easy to manage.
Before You Begin
You should have a working installation of PostgreSQL on your system before beginning this guide. Go through our How to Install PostgreSQL on Ubuntu guide to install PostgreSQL and create a sample database.
NoteThe steps in this guide require root privileges. Be sure to run the steps below as
rootor with the
sudoprefix. For more information on privileges, see our Users and Groups guide.
One-Time SQL Dump
PostgreSQL provides the
pg_dump utility to simplify backing up a single database. This command must be run as a user with read permissions to the database you intend to back up.
Log in as the
su - postgres
Dump the contents of a database to a file by running the following command. Replace
dbnamewith the name of the database to be backed up.
pg_dump dbname > dbname.bak
The resulting backup file,
dbname.bak, can be transferred to another host with
scpor stored locally for later use.
To demonstrate restoring lost data, delete your example database and create an empty database in its place:
dropdb dbname createdb dbname
Restore the database using
psql test < dbname.bak
There are several options for the backup format:
*.bak: compressed binary format
*.sql: plaintext dump
By default, PostgreSQL will ignore any errors that occur during the backup process. This can result in an incomplete backup. To prevent this, you can run the
pg_dumpcommand with the
-1option. This will treat the entire backup procedure as a single transaction, which will prevent partial backups in the event of an error:
pg_dump -1 dbname > dbname.bak
psql allows you to connect to a remote host,
pg_dump can be run from a client computer to back up data on a remote server. Use the
-h flag to specify the IP address of your Linode and
-p to identify the port on which PostgreSQL is listening:
pg_dump -h 198.51.100.0 -p 5432 dbname > dbname.bak
pg_dump only creates a backup of one database at a time, it does not store information about database roles or other cluster-wide configuration. To store this information, and back up all of your databases simultaneously, you can use
Create a backup file:
pg_dumpall > pg_backup.bak
Restore all databases from the backup:
psql -f pg_backup.bak postgres
Automate Backups with a Cron Task
You may want to set up a cron job so that your database will be backed up automatically at regular intervals. The steps in this section will set up a cron task that will run
pg_dump once every week.
Make sure you are logged in as the
su - postgres
Create a directory to store the automatic backups:
mkdir -p ~/postgres/backups
Edit the crontab to create the new cron task:
Add the following line to the end of the crontab:
0 * * * 0 pg_dump -U postgres dbname > ~/postgres/backups/dbname.bak
Save and exit from the editor. Your database will be backed up at midnight every Sunday. To change the time or frequency of the updates, see our Schedule Tasks with Cron guide.
PostgreSQL also offers more advanced ways to back up your databases. The official docs describe how to set up continuous archiving and point-in-time recovery. This is a much more complex process, but it will maintain a constant archive of your database and make it possible replay PostgreSQL’s logs to recover the state of the database at any point in the past.
This method can also be helpful if you have a very large database although continuously archiving a large database consumes resources. Since the process is ongoing, there is no need to make frequent and time consuming full backups.
You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.
Join our Community
This guide is published under a CC BY-ND 4.0 license.