28 C
Senior Writer and partner

Backup and restore databases in PostgreSQL

Hello, friends. Many of our posts are focused on servers and databases and it is very important to know how to backup and restore a database in PostgreSQL. Being this database manager one of the most important and robust there is.

I believe that it is not necessary to go into the term of backups in depth because it is well known that they are vital. Doing them periodically guarantees that if there is a problem with the system or equipment, our data will be safe.

On the other hand, it is not only necessary to know how to do it, but also to restore it. So if you are starting with PostgreSQL this post is for you. Besides, it is always good to have this information at hand to solve any problem.

Learning how to backup and restore databases in PostgreSQL

Obviously, the first thing we have to have is PostgreSQL installed and running on our computer. Whether in Debian 10, CentOS 8, or Ubuntu 20.04 we have tutorials about it.

- Advertisement -

Then, we have to log into the PostgreSQL console.

Open a terminal and run:

su - postgres

After you are in you can make a backup to a database in the following way:

pg_dump [database] > [name].bak

In my case, it would look like this:

pg_dump user > user-backup.bak

This will copy all the contents of the user database into the file user-backup.bak.

So as you can see it’s quite simple to do.

Now to do the reverse process i.e. restore a backup, just follow this syntax:

psql [database] < [backup-name].bak

For example:

psql user < user-backup.bak

You can easily backup and restore databases in PostgreSQL.

Backup and restore all cluster databases

It is also possible to make a backup of all PostgreSQL databases including those of the cluster. Therefore, we can use the pg_dumpall command. This command allows not only to store data but also database configurations such as users and roles.

So, to make a backup of all the databases, follow this syntax:

pg_dumpall > [backup_name].bak

And to restore them:

psql -f [backup_name].bak postgres

So, it is very easy.


The process of making a database backup is something quite important and that allows us to be sure of our data. Once the backup is done, another important part is to know how to restore it. And now you know how to do it in PostgreSQL.

So, share this post and join our Telegram Channel.

- 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