Use PostgreSQL to Backup and Restore Data

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re using PostgreSQL to store data, it’s important to take the right precautions to keep that data from being lost. Making sure that you regularly back up your databases is a smart way to safeguard valuable data– in the event that a database is corrupted or otherwise lost, you’ll be able to quickly restore your users’ data. In this article, we’ll show you how to use PostgreSQL to backup and restore data.

Prerequisites

Before we jump into the SQL statements needed to backup and restore data, let’s take a look at a couple of key prerequisites that need to be in place. First, you’ll need to install Postgres and set up a user and database for your data. If you’re not sure whether PostgreSQL is installed, use the postgres -V command. This command will return the version number of PostgreSQL that’s currently installed on your system.

Make sure that you can access the psql command-line interface with a user that has the appropriate privileges. You can use the psql -V command to confirm that it’s installed, and you can access a specific database with the psql command using the following syntax:

1
psql -U {USER_NAME} -d {DB_NAME}

NOTE: The -U and -d flags are used to pass the Postgres user and database names.

Postgres role does not exist error

If you get a psql error with the message FATAL: role "postgres" does not exist, you’ll have to access psql as a root user or with sudo privileges. You can do this with the following command:

1
sudo -u user_name

You can also try using the command su - user_name and then attempting to create a user and database once you’ve accessed psql. You would use the following SQL statements to accomplish this:

1
2
CREATE USER postgres SUPERUSER;
CREATE DATABASE test_db WITH OWNER postgres;

You’ll need to have some test data you can use to follow along with the SQL commands found in this article. It’s best to have a separate backup of this data or simply use data that you won’t mind losing.

PostgreSQL test data

Follow along with the steps outlined in this section if you don’t have any test data available in your Postgres database. If you already have test data, skip ahead to the part of the article that describes how to back up a database.

The first step is to access the psql command-line interface for your Postgres server. Make sure to connect to the target database that you’d like to back up:

1
psql -U user_name -d test_db

If you’re not sure which database to access, use the following command to have psql return all of your databases:

1
psql -l -U user_name

NOTE: Once you’re inside the psql interface, you can also use the \l command to list all of the databases.

If you haven’t yet created any databases in PostgreSQL, connect to one of the default template databases that exist by default:

1
psql -U user_name -d template1

Once you’ve connected to the template1 database, you can then use the following command to create a new database that extends permissions to your username:

1
CREATE DATABASE test_db WITH OWNER user_name;

The SQL statement shown above should return a response of CREATE DATABASE. At this point, you can either log out of psql using \q and connect to the new database, or you can use the \connect command to switch to the newly-created database:

1
\CONNECT test_db

Screenshot of psql connecting to template1 Postgres database to CREATE DATABASE

Use psql to import a CSV file

If you need some test data to practice backing up and restoring, you can import a CSV file into PostgreSQL. The following is some sample CSV data that contains information on 20 children. The CSV file’s header row is name,gender,age:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
name,gender,age
Emma,female,6
Olivia,female,8
Ava,female,10
Isabella,female,9
Sophia,female,9
Charlotte,female,8
Mia,female,6
Amelia,female,7
Harper,female,11
Evelyn,female,10
Liam,male,10
Noah,male,9
William,male,8
James,male,8
Oliver,male,9
Benjamin,male,7
Elijah,male,6
Lucas,male,7
Mason,male,9
Logan,male,11

Save this data to a file on the machine that’s running PostgreSQL. If you’re using your own data instead, make sure Postgres has access to it.

Use PostgreSQL to create a table

Before we can import the CSV data, we’ll need to first create a table with column names that match the CSV headers. The following SQL statement illustrates how you can create a table in psql for the CSV sample data shown above:

1
2
3
4
5
CREATE TABLE test_table (
    name TEXT PRIMARY KEY,
    gender VARCHAR(16),
    age INTEGER
);

NOTE: Use VARCHAR() only if you’re certain about the length of the characters for values in a column. If the data will contain large strings or blocks of text, it’s better to use the unlimited TEXT data type instead.

Use psql to copy CSV data into a Postgres table

Now, let’s use the COPY SQL command to put the CSV data into the new table. The following example uses the name, gender, age CSV header to accurately match the CSV values with the table columns:

1
2
COPY test_table(name, gender, age)
FROM '/path/to/test-data.csv' DELIMITER ',' CSV HEADER;

NOTE: To avoid getting a No such file or directory psql error, make sure to specify the absolute path to your CSV file. You can use the pwd command to verify the correct path for your current directory if needed.

Screenshot of psql COPY CSV file to Postgres table example

Database backup in Postgres

Next, let’s back up the database and table data. In PostgreSQL, there are two basic file types used for backing up data. The first is the MySQL dump file, which uses the .sql file extension; the second type uses the .dump file extension, which is a language-agnostic file type used for backing up file systems and data. However, other file extensions, such as .bak, can work as well.

Postgres SQL dump

You’ll need to exit out of the psql command-line interface before using pg_dump to back up your data. The example shown below will back up a Postgres database as a .dump file in the current directory while specifying a user with the -U flag:

1
pg_dump -U user_name -d test_db > ./database-backup.dump

This command should not return any response.

Verify the Postgres backup file

Now, let’s use the cat command, which will return the contents of the .dump backup file. This will help us to see if the PostgreSQL backup procedure was successful:

1
cat database-backup.dump

This command should return something similar to the following if it’s successful:

1
2
3
4
5
6
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.1 (Debian 12.1-1.pgdg100+1)
-- Dumped by pg_dump version 12.1 (Debian 12.1-1.pgdg100+1)

Screenshot of pg_dump PostgreSQL backup restore dump file

If the pg_dump command hangs, freezes or simply doesn’t return a response, a number of factors could be responsible. Problems may occur because you are backing up a massive amount of data; alternatively, one of your tables or databases could be corrupt. It could also be because you’re attempting to connect to PostgreSQL remotely from another server or machine.

Backup all Postgres databases

To back up all of the PostgreSQL databases on the server, use the pg_dumpall command in the same way you used pg_dump. The next example will back up all of the Postgres databases to an SQL file called all-databases.sql:

1
pg_dumpall -U user_name > all-databases.sql

Restore a PostgreSQL database

Now that we’ve backed up our data, let’s try to restore it. First, go back into the database using the psql interface. Drop the table using the following command:

1
DROP TABLE test_table;

This SQL statement should return a response of DROP TABLE.

Postgres restore from dump

We can use the pg_restore utility to restore the database data in the same way we backed up the data in the first place. This time, we’ll use the < character instead to indicate that we’re reading the data from the file:

1
pg_restore -U user_name -d test_db < ./database-backup.dump

NOTE: The --create option can also be used if you’d like to completely rebuild the database from scratch. Be sure to remove the database using the DROP DATABASE command before attempting this.

Use psql to restore a PostgreSQL backup

You can also use the psql command to restore a dump backup file. In the following example, we restore a .dump file located in the same directory:

1
psql -U user_name -d test_db < database-backup.dump

This command should get a response that looks like the following:

1
2
3
4
5
6
7
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 20
ALTER TABLE

Troubleshooting the Postgresql backup & restore

If the backup or restore process fails or returns an error, you should check the syntax of your commands and make sure that all your paths are correct.

In the event that the command execution shown above hangs or freezes, simply press CTRL+C to cancel the process and check the syntax of the command to make sure everything is correct.

You can also run the commands with the --verbose option to get more information.

Getting a ‘file is too short’ error while restoring a Postgres database

A file is too short error may occur if the input file type is incorrect or if the > character was omitted:

1
pg_restore: error: input file is too short

If you get an error with the message file appears to be a text format, it’s likely that you’re attempting to restore a database with an invalid or corrupt backup file:

1
2
pg_restore: error: input file appears to be a text format dump.
Please use psql.

In this case, you might want to use psql instead of pg_restore.

Restarting the PostgreSQL clusters

When you experience error messages or other issues, you can also try restarting the cluster with the pg_createcluster command. Execute this command followed by the Postgres version number and main. Shown below is an example of how to do this with PostgreSQL v12:

1
pg_createcluster 12 main

If all else fails, you may need to restart the Postgres service or the machine itself and try again.

Conclusion

Safeguarding users’ data is one of the key tasks of a database administrator. If your database gets corrupted, it’s important to have a backup on hand so that your data can be restored. In this article, we showed you how to use PostgreSQL to backup and restore a database. With our examples to guide you, you’ll be able to implement the same techniques in your own PostgreSQL database environment.

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.