Use PostgreSQL to Backup and Restore Data
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.
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:
-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:
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:
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:
If you’re not sure which database to access, use the following command to have psql return all of your databases:
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:
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:
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:
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
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:
name TEXT PRIMARY KEY,
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:
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.
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
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:
This command should return something similar to the following if it’s successful:
-- 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)
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
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:
This SQL statement should return a response of
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:
--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:
This command should get a response that looks like the following:
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
file is too short error may occur if the input file type is incorrect or if the
> character was omitted:
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:
Please use psql.
In this case, you might want to use
psql instead of
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:
If all else fails, you may need to restart the Postgres service or the machine itself and try again.
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