Backup Postgres

Introduction

In this tutorial we’ll learn how to backup Postgres schemas, roles, and data using pg_dump and pg_dumpall utilities. We’ll look at how these commands are used via command line and we’ll go over how to use the web interface provided by pgAdmin to backup your PostgreSQL databases, whether you want to backup only the schema, only the data, or both.

Prerequisites

  • Access to a running instance of the PostgreSQL relational database system.
  • Optional: database administration software that provides visual tools for backup. We show in this article the use of the free pgAdmin tool, currently in version 4.x.
  • Not required, but helpful: Your database has some data in it or at least a table or two.

Why Backup your Postgres?

  • Safety. Whether we are talking about hardware failures, hackers, viruses, or bugs, no system is perfect. No one wants to lose their hard work of designing, building, and filling a database. Frequent backups will save you in the event one of these things happens.
  • Rollbacks. Have you ever gotten the dreaded email from a client saying they accidentally deleted or changed some data and want to know if you can “fix” it? This is where you will be glad you made frequent backups of your PostgreSQL database. The pg_restore function, that we’ll learn about in another tutorial, allows for backup restore of specific data.
  • Move your Postgres database. When you want to move your PostgreSQL database to another server, whether it is local or on a different host, one of the easiest ways to do this migration of your Postgres database is to use pg_dump and pg_restore.

Postgres pg_dump

Postgres has a built-in utility for backing up your database schema and/or data. There are many parameter choices that allow you to refine exactly what you want to back up. The pg_dump utility makes clean backups even when PostgreSQL is under intense use. There is also no danger that the backup process will interrupt any of the typical read/write operations from users accessing the database.

Postgres pg_dump syntax

In order to use the pg_dump command, you will need to find it. It typically resides in the bin folder of your Postgres install. So first, you will want to navigate to that spot on your system:

CD c:\program files\PostgreSQL\12\bin

Note, in the above command line instruction, the “12” is the Postgres version number. Important: If you are migrating from one version of Postgres to a newer version, use pg_dump from the newer version.

Syntax

pg_dump [connection options] [other options] [database name]

Postgres pg_dump example

There are quite a few ways to use pg_dump. We’ll focus here on two of the most common forms of schema backup and data backup. If you are doing a migration, you may want to do them separately, starting with a schema dump.

Postgres schema backup

pg_dump -s -d name_of_database -p 5432 -f \mybackups\2019-12-19-schema.sql

Analysis

  • -s: The “-s” tells Postgres’ pg_dump to backup only the schema, meaning the tables, etc. but NOT the data. The “s” here is case sensitive. If you capitalize this to “-S”, it means “username is next parameter”.
  • -d: The “-d” tells pg_dump to use the next string as the name of the PostgreSQL database to be backed up. The “d” here is case sensitive.
  • -p: Optional. This tells pg_dump to expect the port number as the next parameter. “5432” is used if left empty.
  • -f: This precludes the name of the path to use and text file name you want pg_dump to create. Again, case is important. If you capitalize this parameter to “-F” it means format, where the default is “p” for “plain text”. So if you want plain text, no need to use the “-F” paramter.

Another way to get the same results as above is to change out the “-f” with a “>” symbol like so:

pg_dump -s -d name_of_database -p 5432 > \mybackups\2019-12-19-schema.sql

If you want to use the free PGadmin tool for Postgres to backup, here’s how:

Step 1: Right click on your database. In this case, in the image below, you can see our database is called “practice”. From the context menu that comes up, choose “Backup”.

Backup Postgres with PGadmin

Step 2: In the dialog box that comes up, in the “General” tab, choose a path and filename, format, and encoding.

Backup PostgreSQL with PGadmin

Step 3: Click the “Dump options” tab and modify any options that apply to your situation.

Using PG admin to backup Postgres

Step 4: Scroll down to see the rest of the options available for backing up your Postgres database.

Using PG admin to backup PostgreSQL

Postgres data backup

If you used the PGadmin interface and backed up both schema and data or if you are not interested in “merely” backing up the data, then you can skip this section.

Now that you have backed up the schema, we’ll show you how to back up the data in your PostgreSQL database using the command line method, since it’s pretty much self-explanatory how to do this using the PGadmin tool.

pg_dump -d name_of_database -p 5432 -F t -f \mybackups\2019-12-19-schema.tar

Analysis

  • -d: The “-d” tells pg_dump to use the next string as the name of the PostgreSQL database to be backed up. The “d” here is case sensitive.
  • -p: Optional parameter. This tells pg_dump to expect the port number as the next parameter. “5432” is used if left empty.
  • -F: “-F” means format, “t” means use tar compression.
  • -f: This precludes the name of the path to use and text file name you want pg_dump to create. Again, case is important.

Another way to do this same operation removes the “-f” parameter and instead uses “>” to mean “save as this path and file”:

pg_dump -d name_of_database -p 5432 -F t > \mybackups\2019-12-19-schema.tar

Postgres pg_dumpall

What if you have many Postgres databases to backup? This is where the pg_dump utility can be used. Two things to be cautious about when thinking about using PostgreSQL’s pg_dumpall:

  • The process can take much more time than doing a single pg_dump.
  • Since pg_dumpall exports all your PostgreSQL databases, keep in mind – unless you otherwise specify – pg_dumpall will export all to one single file, which could get pretty huge and present challenges when wanting to restore only one database of the many that were backed up.

Postgres pg_dumpall examples

pg_dumpall schemas

The following example will export all Postgres database schemas on the current server; NOT the data.

pg_dumpall --schema-only > \mybackups\2019-12-19-schemas-all.sql

pg_dumpall roles

The following example will export all Postgres database roles on the current server; NOT the data.

pg_dumpall --roles-only > \mybackups\2019-12-19-roles-all.sql

pg_dumpall data

The following example will export all Postgres database data on the current server.

pg_dumpall -F t > \mybackups\2019-12-19-data-all.tar

Analysis

  • -F: “-F” means format, “t” means use tar compression.

Conclusion

In this tutorial we learned how to backup Postgres schemas, roles, and data using pg_dump and pg_dumpall utilities. We explored how these commands are used in the command line as well as how to use the web interface in the free pgAdmin tool to backup your PostgreSQL databases with a few mouse clicks, whether you want to backup only the schema, only the data, or both.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.