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.
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.
Conclusion
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.
