21 C
Senior Writer and partner

How to backup and restore a database on PostgreSQL?

If we work with the Database, we will have many operations to do frequently. One of them is the backup of the data in it. As you can suppose, it is of vital importance to do it in order to safeguard all the sensitive information of the applications that use it. Of these databases, there are programmers who prefer to make them using PostgreSQL and that is why in this post, I will teach you how to make a backup and restore a database in PostgreSQL.

Install PostgreSQL

Of course, you have to have a fully functional PostgreSQL server before continuing with the post.

On our site, we have written about how to install PostgreSQL on various Linux distributions.

1.- How to install PostgreSQL on Ubuntu 18.04?

- Advertisement -

2. How to install PostgreSQL on Ubuntu 19.04?

3. How to install PostgreSQL on CentOS 7?

4. How to install PostgreSQL on Fedora 30?

5. How to install PostgreSQL on OpenSUSE?

So, check out those tutorials to install PostgreSQL.

Then, we can backup and restore a database on PostgreSQL.

Backup and restore a Database on PostgreSQL

Making a Backup using pg_dump

To backup one or more databases in PostgreSQL, the tool to use is Pg_dump. With them and their multiple options, we can perform without any problems the backups we need.

The most basic operation of this command is the following:

> pg_dump dbname > outfile

The above command performs a backup of an entire database. And although it is effective, we will be able to add options to make it even more complete.

For example, if there is a specific user created in PostgreSQL, we can specify it using the option -U or –username=NAME.

If we want a custom name in the output, we can do it thanks to the -F option.

By default, Pg_dump exports the database in plain text. We will be able to change this if we use the -F option and we could define for example, that the output is compressed in TAR. Ideal for very large databases.

Then, a better command would be the following one.

> pg_dump -U your-user --format=t --file=output.tar dbname

In case you want to backup all databases, you can do it with pg_dumpall. However, this method is not recommended if you have a PostgreSQL cluster. In addition, if they are very large databases, you will also have problems.

> pg_dumpall -U user > output

One of the main disadvantages of this method is that it places all databases in a single file. I repeat, if the databases are very large, then there will be problems.

Restore a database on PostgreSQL

Now that we’ve backed up our database, it’s time to restore it. To do this, the tool is pg_restore.

In this case, using pg_restore is a little simpler than using pg_dump.

> pg_restore -U user -d dbname [output_path]

And that’s it. That’s enough. Remember that dbname is the database where you will perform the restoration. You can enrich the command with the -v option that will show the whole process. If you use the -h and -p parameters you will be able to define the host and the connection port.


In this post you learned how to make a backup and restore on PostgreSQL. The truth is an easy process but important enough to preserve the data from a general failure.

Of course, I recommend you consult the official documentation to broaden your knowledge.

Please share this post with your friends.

- Advertisement -
Everything Linux, A.I, IT News, DataOps, Open Source and more delivered right to you.
"The best Linux newsletter on the web"


Please enter your comment!
Please enter your name here

Latest article