The powerful features associated with PostgreSQL are well-documented. Its extensibility and ability to handle large workloads make it a popular choice as an enterprise-level database platform. However, as you already know, working in a production environment comes with quite a few pitfalls. You need to make sure that your data is safe, and that if anything happens to the database, or the server, you have a backup copy to ensure that there are no interruptions to your business operations. This tutorial will cover some introductory procedures for backing up and restoring your Postgres database.
While your server may be being backed up by a regular process, database files themselves cannot easily be restored from an image. In the event that the server needs to be restored, for your files to be backed up properly, they should be stored as a text file or other storable format.
Backup Procedures
As with any relational database platform, this begins with creating SQL dump files. In Postgres this is handled through the pg_dump process.
For PostgreSQL, dump files can be stored in one of three formats: as a text file (.sql), compressed binary format (.bak), or as a tarball (.tar).
At its most basic level this is handled with a very simple command:
This basically creates a text sql file with the name “mydatabase.sql.”
However in most cases, especially if you are using Postgres as a production server as part of your persistent backups, you will want to dump it as a binary backup, or if the database is particularly large, as a tarball.
To do this, use either of the following commands, with an -Fc operator which tells the dump use a custom function;
Or for a tarball:
It is important, however, to note that if any errors occur during your dump process, these will be ignored, and there is the chance that you could end up with an incomplete backup. As PostgreSQL databases tend to be large, you may wish to have the entire process run as a transaction. To do this, use the -1 option like so:
This will force the running of the entire dump, and will provide the full dump, even in the event of an error at any point.
There are a few other useful operators which will likely come in handy. At some points you may be running a backup that is stored on a remote server. In this case you can provide the host, or the IP address where the database is located with -h, and the listening port for PostgreSQL as -p.
Other useful options include –data-only which is what it sounds like; it only gets the data and not the schema, or —schema which does the opposite. (For a more extensive list of the options including shorthand, you can go to the official website here: https://www.postgresql.org/docs/9.4/static/app-pgdump.html)
Automating Backups
Of course, you could manually do backups if you remember to do this yourself, however, being human, and with all the memory-related flaws associated with this, it makes much more sense to create an automated procedure. You can do this using cronjobs.
First, you’ll need to create a directory for storing these files.
Login as the postgre user with su – postgres and then create the directory:
To enter the crontab file, through your *nix shell, and type crontab -e
In the shell to the full dump run every day at 1AM you can enter the following.
Note that there is a date stamp at the end of the file; that’s to make sure that you can easily identify backups and not overwrite an existing one..
Alternately you can put the entire process into a shell script and run that script through the cronjob. This is the way I tend to like doing it as it allows several jobs to be handled at once, such as for different databases, but how you handle it is up to you.
To do it this way, you can place the entire dump into a script and name it something like “backups.sh” (storing it in /bin/sh) and call that in the cronjob.
0 1 * * * /bin/sh backup.sh
Important: Don’t forget to clean out your backup directories periodically; as Postgres databases tend to be big, even if stored as tarballs, they will fill up the server fairly quickly, and may result in an angry email from your sysadmin!
Restoring your PostgreSQL Database
In the event that the unthinkable (or actually thinkable; would else would you be reading this?) happens. Restoring your database from the created files is simple.
If your database is already in a .sql format, you can simply use the following command.
This will not create a new database; the database named “mydatabase” must already exist for this to work. Note, that the restoration will continue to run even if an error has occurred, so you may end up with a partial restore without knowing, so it’s a good idea to use
In both cases you won’t have a full database, but on the second one you will know that it has stopped for some reason. However, as earlier during the dump process, you can have the entire thing as a transaction by including the -1 option.
To restore the database from the compressed binary format use:
pg_restore -Fc mydatabase.bak
To restore a tarball file, use:
You can also use the -C option to create a brand new database based on the schema and data from your backup file, like so:
for a compressed binary.
and for a tarball,
Conclusion
As PostgreSQL is a powerful platform, this is really only a beginning. For instance, you may wish to learn to use continuous archiving and point-in-time recovery, though this is a bit more complicated and not within the scope of this introductory tutorial.
However, using the above procedures will enable you to create a procedure that will at least at some level keep you safe in the event that your database becomes corrupted. Also The same procedures can be used if you wish to make a copy so that you can quickly build duplicate or backup databases. It may seem complicated, but once you get the hang of it, you can move forward to the next steps.
Related Articles:
MongoDB vs PostgreSQL: A Comparison
