There are many guides already explaining how to backup and restore PostgreSQL databases. What makes this one different is that explains the fundamentals of importing and exporting using a simple language that beginners can understand and also knowledgeable people can use as reference.
By the moment you reach the end of this guide you will have a better understanding in how to manage your PostgreSQL data confidently. You'll learn the most efficient methods to both export and restore your databases.
A dump is simply a copy of a database. Typically, it is just a file. It contains the structure of your database, its collection of data stored, or both. It is structured in a way that is easy to restore it back at any time from any machine. It is the most convenient way to create consistent database backups and move its copies around different machines.
When exported correctly, a dump contains all what is needed to re-create a database from scratch including the database structure called schema and all its data.
There are multiple formats in which you can export a PostgreSQL database. We will focus on two of them in this guide. The most common is the standard SQL format. The second one is called custom. This is the most modern and reliable format and our preferred choice.
Plain-text SQL is arguably the most common output format for exporting PostgreSQL data. It is also the default one.
SQL dumps are usually exported having a .sql extension and imported using the psql command tool. Dumps are slower to import with this command because psql can't take advantage of using concurrent jobs to speed up the process.
The performance impact may be significant as your database grows beyond hundreds of thousands of records and many indexes. I have seen quite often SQL dumps of a few gigabytes taking many hours to restore.
If you find yourself in a situation you need to recover your database quickly, this may not be the most efficient format and you will be better off choosing the custom format instead described below.
To its justice, the biggest advantage is likely that it is easier to manipulate into different architectures given its plain SQL nature. If you ever need to convert data to MySQL or to older versions of PostgreSQL this format will be the way to go. This may be in fact, one of the few reasons why it still remains as the default exporting format.
In most cases there is no compelling reason not to use the newer and faster custom format.
With recent versions of PostgreSQL you can export and restore databases using a faster and more reliable format called simply custom. This is our format of choice and the one that we will use in the examples below.
What makes custom dumps a preferred alternative is that you can run concurrent jobs when restoring a database, significantly speeding up the process. This is further explained below.
Typically these dumps are exported having a .dump or .backup extension to differentiate them from plain text SQL dumps. However, this is simple a convention and not enforced by PostgreSQL. Therefore you can not always relay simply by the extension to guess its format.
If you open the file and you can read SQL statements it is a plain SQL file. If it looks more of a binary, then it is probably a custom format file.
Custom format files are also compressed. This becomes handy if you need to move the files to another web-server saving disk space, bandwidth and time without needing to gzip and ungzip the file.
The easiest way to export a dump to a file is to run the following line in a Terminal console.
$ pg_dump -Fc database_name > backup.dump
You would replace backup.dump with the desired name for your dump file and database_name with the database name you want to export.
Running this command will not affect your current database in anyway and your database will be continue to work normally while the exporting is being done.
As you may note, we will use custom format for exporting backups. By default pg_dump exports in plain .sql format. To export as custom format instead, simply specify its format by passing the parameter and value -Fc for custom format.
The command will attempt to connect to the database using the privileges for the current user. If your database is owned by a different user, you can pass -U username with the corresponding name.
If everything is good now you should have your backup done.
Let's say you now want to move this backup file to another server for restoring it.
You can do this easily with the scp command. Run the following command in a Terminal console. Keep in mind this example assumes you have access to the remote server. If this is not the case then make sure to have proper SSH access to your remote host before proceed.
$ scp backup.dump user@remote_host:/remote/directory
Replace backup.dump with the name of your desired dump file, user with the username in your remote host, remote_host with the IP or hostname of the host server and /remote/directory with the directory where you want the file to be copied.
When complete, you should see an output like this.
backup.dump 100% 0 0.0KB/s 00:00
An alternative solution is to run the pg_dump command from the remote host where you want the dump to be stored and passing the username and host of your current database there. This is explained below. This will assume your database is accessible from the outside, which is not a configuration usually by default.
Alternately you can also export all your databases using pg_dumpall. You would do it simply running the following command in a terminal console.
$ pg_dumpall > backup.sql
Keep in mind that even if this command call pg_dump internally it only exports plain SQL and does not allow to change the format output.
You need to restore this dump using psql which doesn't support simultaneous jobs to speed up the process. This significantly slows down the import process when big databases are involved.
If this is the case, consider export your databases individually using the pg_restore command described above.
To get a list of all your databases, you can simply run the following command.
$ psql -l
You want to restore a PostgreSQL database from a backup file. This process that can be performed fairly easy.
Assuming you have a PostgreSQL backup file and you want to import it, the simplest and fastest way would be to run the following command from a Terminal console.
$ pg_restore -j 8 backup.dump > database_name
Replace backup.dump with the name of the backup file and database_name with the database name to import it. This database should be created previously. If your database is not already created you can do it simply with this command.
$ createdb database_name
Depending on the size of your dump the restore process can take from a few seconds to many hours. Big files containing databaes with many indexes may take a lot of time to import
When using pg_restore you can take advantage of using multiple concurrent jobs to speed up the process when importing dumps previously exported with custom format.
A good rule of thumb is to use between one or two jobs per available processor in your server. If you server has 4 CPUs, you can try with either 4 or 8 jobs. Launching too many jobs may have a negative impact with a decrease in performance.
Taking advantage of jobs when importing big files makes a huge difference by speeding up the process significantly.
If the restore process is interrupted for any reason you will now have a database that is partially restored. If you try to restore it again you will find yourself with duplicate records. This is certainly not a good place to be as your database will not end up like you have it before.
You have two options here. If your restoring process was interrupted you can simply drop the database and create it again before restoring again. You would do something like this.
$ dropdb database_name
$ createdb database_name
And alternative is to use the --single-transaction parameter when restoring the database.
$ pg_restore -j 8 --single-transaction backup.dump > database_name
This executes the command as a single transaction and ensures that the whole restoration is completed successfully or no changes are applied to the database at all. Keep in mind that a single error may cause a big database restoration to abort. However, this is what you most likely want anyways.
It is a good idea after restoring to run ANALYZE and check the output. You can do this simply running the following command in a terminal console.
$ psql -d database_name -c 'ANALYZE VERBOSE'
Remember to replace database_name with the name of your database.
You can also import a specific table by adding the t parameter and the name of the database as follows.
$ pg_restore -j 8 backup.dump > database_name -t table_name
If your dump is a plain-text SQL file you should use the psql tool instead. The pg_restore command does not support importing standard plain-text SQL files.
It is very easy to import a database using psql. Simply run the following command from a terminal console.
$ psql database_name < backup.sql
Replace backup.dump with the name of your file and database_name with the name of the database to import it.
Yorokobi automatically makes your backups using the
pg_dumptool and ready to be restored withpg_restoretool. You won't need to usepsqltool with Yorokobi backups.
Both pg_dump and pg_restore allow you to dump and restore remote databases. This is very helpful when your need to restore a database from another server.
You can create a dump file locally using pg_dump and connect to a remote PostgreSQL database. You can do it as follows.
$ pg_dump -h host -p 5432 -U username -Fc database_name > backup.dump
Replace host with the IP address or hostname of your remote host where the PostgreSQL server is running. Make sure to pass the same port as the database is running. Lastly, replace username with the username in the remote host that has access to your PostgreSQL database.
If everything is working smoothly you will have a backup.dump file locally containing the database from the remote host.
You can now restore this database using pg_restore locally as described above.
If you made a dump locally you can easily restore it into another machine directly from pg_restore tool. You would do something like this.
$ pg_dump -h host -p 5432 -U username -j 8 backup.dump > database_name
Replace host with the IP address or hostname of your remote host where the PostgreSQL server is running. Make sure to pass the same port as the database is running. Lastly, replace username with the username in the remote host that has access to your PostgreSQL database.
Remember that the database needs to be previously created. You can do this also remotely, running this command before pg_restore.
$ createdb -h host -p 5432 -U username database_name
Please note PostgreSQL needs to be configured in the remote server to work on the outside world. For security measures, connections from the outside are disabled by default.
Make sure incoming connections are allowed for the port in which PostgreSQL is running. Typically is 5432 but it can be different. You can check in which port is running by looking at postgresql.conf file typically at /etc/postgresql/11/main/postgresql.conf and find the value for the attribute port.
You may need to edit the file /etc/postgresql/11/main/pg_hba.conf and add the following line at the end host all all 0.0.0.0/0 md5. This can open access to any computer from the outside world. You can optionally Restart PostgreSQL after this change. Run /etc/init.d/postgresql restart.
Make sure you are connecting with the same username PostgreSQL is running. This is typically postgres.
If you don't have a good reason to keep PostgreSQL open to the outside world after performing your imports and exports we recommend it to block incoming connections for PostgreSQL back again removing or commenting the line you added on pg_hba.conf file and restarting PostgreSQL again.
If you got through this guide you should have by now a deeper understanding in how to backup and restore databases with confidence both locally and with remote databases using the most reliable and fastest methods available.
If you are interested in a comprehensive backup solution for your PostgreSQL databases Yorokobi does automatic daily backups and store them safely in the cloud. Learn more about what Yorokobi can do for you.