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