Tutorial

How To Backup PostgreSQL Databases on an Ubuntu VPS

Published on August 28, 2013
How To Backup PostgreSQL Databases on an Ubuntu VPS

What is PostgreSQL?

PostgreSQL is a modern database management system. It is frequently used to store and manipulate information related to websites and applications.

As with any kind of valuable data, it is important to implement a backup scheme to protect against data loss. This guide will cover some practical ways that you can backup your PostgreSQL data.

We will be using an Ubuntu 12.04 VPS with PostgreSQL 9.1. Most modern distributions and recent versions of PostgreSQL will operate in a similar way.

How to Back Up a PostgreSQL Database Using pg_dump

PostgreSQL includes a utility called "pg_dump" that can be used to dump database information into a file for backup purposes.

The pg_dump utility is run from the Linux command line. The basic syntax of the command is:

pg_dump name_of_database > name_of_backup_file

The command must be run by a user with privileges to read all of the database information, so it is run as the superuser most of the time.

For a real-world example, we can log into the "postgres" user and execute the command on the default database, also called "postgres":

sudo su - postgres
pg_dump postgres > postgres_db.bak

This command is actually a PostgreSQL client program, so it can be run from a remote system as long as that system has access to the database.

If you wish to backup a remote system, you can pass the "-h" flag for specifying the remote host, and the "-p" flag to give the remote port:

pg_dump -h remote_host -p remote_port name_of_database > name_of_backup_file

You can also specify a different user using the "-U" option if necessary. The syntax would be:

pg_dump -U user_name -h remote_host -p remote_port name_of_database > name_of_backup_file

Keep in mind that the same authentication requirements exist for pg_dump as for any other client program. This means that you must ensure that your log in credentials are valid for the systems you are trying to back up.

How to Restore Data Dumps from pg_dump with PostgreSQL

To restore a backup created by pg_dump, you can redirect the file into psql standard input:

psql empty_database < backup_file

Note: this redirection operation does not create the database in question. This must be done in a separate step prior to running the command.

For example, we can create a new database called "restored_database" and then redirect a dump called "database.bak" by issuing these commands:

createdb -T template0 restored_database
psql restored_database < database.bak

The empty database should be created using "template0" as the base.

Another step that must be performed in order to restore correctly is to recreate any users who own or have grant permissions on objects within the database.

For instance, if your database had a table owned by the user "test_user", you will have to create it on the restoration system prior to importing:

createuser test_user
psql restored_database < database.bak

Dealing with Restoration Errors

By default, PostgreSQL will attempt to continue restoring a database, even when it encounters an error along the way.

In many cases, this is undesirable for obvious reasons. It can be painful to try to sort out what operations are needed to restore the database to its proper state.

We can tell PostgreSQL to stop on any error by typing:

psql --set ON_ERROR_STOP=on restored_database < backup_file

This will cause a PostgreSQL restore operation to halt immediately when an error is encountered.

This will still leave you with a crippled database that hasn't been fully restored, but you can now handle errors as they come up instead of dealing with a list of errors at the end.

A better option in many situations can be the "-1" (the number one) or "--single-transaction" option:

psql -1 restored_database < backup_file

This option performs all of the restoration details in a single transaction.

The difference between this option and the "ON_ERROR_STOP" setting is that this will either succeed completely or not import anything.

This can be a costly trade-off for larger restorations, but in many cases, the benefit of not leaving you with a partially restored database heavily outweighs that cost.

How to Backup & Restore All Databases in PostgreSQL

To save time, if you would like to backup all of the databases in your system, there is a utility called "pg_dumpall".

They syntax of the command is very similar to the regular pg_dump command, but it does not specify the database. Instead, the command backs up every available database:

pg_dumpall > backup_file

You can restore the databases by passing the file to psql, with the default database:

psql -f backup_file postgres

Conclusion

Backups are an essential component in any kind of data storage plan. Fortunately, PostgreSQL gives you the utilities necessary to effectively backup your important information.

As with any kind of backup, it is important to test your backups regularly to ensure the copies that are created can be restored correctly. The backups you create are only useful if they can actually be used to recover your system.

By Justin Ellingwood

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

This comment has been deleted

    how to download backup file to local computer from digital ocean

    I’m using Pg_dump if I need to backup PostgreSQL database on Linux, it’s really the best way. But sometimes I have to work with PostgreSQL on Window, and in that case, I use the simple tool to backup Postgres database - PostgreSQL-Backup http://postgresql-backup.com/postgresql-blog/backup-postgres-database . I wish to use PostgreSQL-Backup to backup Postgres databases on Linux.

    When I use

    psql -f backup_file postgres
    

    I got all kinds of errors like these:

    psql:2016-08-14_02h50m_50_pg-master.bak:15427: ERROR:  multiple primary keys for table "accesstokens" are not allowed
    psql:2016-08-14_02h50m_50_pg-master.bak:15435: ERROR:  relation "accesstokens_token_unique" already exists
    psql:2016-08-14_02h50m_50_pg-master.bak:15443: ERROR:  multiple primary keys for table "app_fields" are not allowed
    psql:2016-08-14_02h50m_50_pg-master.bak:15451: ERROR:  relation "app_settings_key_unique" already exists
    psql:2016-08-14_02h50m_50_pg-master.bak:15459: ERROR:  multiple primary keys for table "app_settings" are not allowed
    psql:2016-08-14_02h50m_50_pg-master.bak:15467: ERROR:  relation "apps_name_unique" already exists
    psql:2016-08-14_02h50m_50_pg-master.bak:15475: ERROR:  multiple primary keys for table "apps" are not allowed
    psql:2016-08-14_02h50m_50_pg-master.bak:15483: ERROR:  relation "apps_slug_unique" already exists
    psql:2016-08-14_02h50m_50_pg-master.bak:15491: ERROR:  multiple primary keys for table "client_trusted_domains" are not allowed
    psql:2016-08-14_02h50m_50_pg-master.bak:15499: ERROR:  relation "clients_name_unique" already exists
    psql:2016-08-14_02h50m_50_pg-master.bak:15507: ERROR:  multiple primary keys for table "clients" are not allowed
    psql:2016-08-14_02h50m_50_pg-master.bak:15515: ERROR:  relation "clients_slug_unique" already exists
    

    So I need to manually delete each individual database !! It drives me crazy.

    How can I force an overwrite ??

    I’m using version 9.4.18 within a Docker container.

    Cheers! P

    Hi, can you help to upgrade postgres to newer version. My db is now on 9.4, want to upgrade to 9.5 and to make sure all my existing database, tables, and pg configurations and settings are transferred to newer one. I am looking for a detailed guide.

    Found a handy script here: https://gist.github.com/matthewlehner/3091458

    Looks like someone was copying the automysqlbackup script and making it work for PostgreSQL. Seems to work!

    Hi All,

    I got similar requirement in one of my project, Where I need to take back and stored the data on remote directory. Could you please let me know Do I need to modify this above script.

    this is the script that i use to backup my database, it creates a backup with a timestamp and keeps the latest 14 backups, i run it on a daily cron so this script keeps a 2 week daily backup of the database

    <pre> ls -t *.sql | sed -e ‘1,13d’ | xargs -d ‘\n’ rm echo Done at date +\%Y-\%m-\%d_\%T pg_dump dbname --username=dbuser > date +\%Y-\%m-\%d_\%T.sql </pre>

    Try DigitalOcean for free

    Click below to sign up and get $200 of credit to try our products over 60 days!

    Sign up

    Join the Tech Talk
    Success! Thank you! Please check your email for further details.

    Please complete your information!

    Get our biweekly newsletter

    Sign up for Infrastructure as a Newsletter.

    Hollie's Hub for Good

    Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

    Become a contributor

    Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

    Welcome to the developer cloud

    DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

    Learn more
    DigitalOcean Cloud Control Panel