Note that you need to run the following commands as the PostgreSQL superuser: $ mkdir -p /srv/backups/databases You can configure a cron job to automate PostgreSQL database backup as follows. Cron jobs are a commonly used means for scheduling various kinds of tasks to run on a server. You can perform backups at regular intervals using cron jobs. $ pg_dump -U tecmint -h 10.10.20.10 tecmintdb | pqsl -U tecmint -h 10.10.20.30 tecmintdbĪuto Backup PostgreSQL Database Using a Cron Job It is also possible to dump a database directly from one server to another, use the pg_dump and psql utilities as shown.
$ pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sqlĮnsure that the user connecting remotely has the required privileges to access the database, and the appropriate database authentication method is configured on the database server, otherwise, you will get an error like the one shown in the following screenshot. and tecmintdb with your remote host IP address or hostname, database port, and database name respectively. Besides, use the -U flag to specify the database role name to connect as. To specify the remote database server pg_dump should contact, use the command-line options -h to specify the remote host and -p specifies the remote port the database server is listening on. Pg_dump is a regular PostgreSQL client tool, it supports operations on remote database servers. It is important to note that the parallel dump option reduces the time of the dump, but on the other hand, it also increases the load on the database server. If the database is extremely large, you can dump in parallel by dumping number_of_jobs tables simultaneously using the -j flag, as shown.
Backup and restore how to#
Here is an example of how to restore a plain text file dump: $ psql tecmintdb tecmintdb.gz
Backup and restore archive#
psql is used to restore text files created by pg_dump whereas pg_restore is used to restore a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats (custom, tar, or directory). To restore a PostgreSQL database, you can use the psql or pg_restore utilities. You can restore the dump using psql as shown. To back up all PostgreSQL databases, use the pg_dumpall tool as shown. $ pg_dump -F d tecmintdb -f tecmintdumpdir The directory which will be created by pg_dump must not exist. To dump output in the directory output format, use the -f flag (which is used to specify the output file) to specify the target directory instead of a file. You can specify the output format using the -F option, where c means custom format archive file, d means directory format archive, and t means tar format archive file: all formats are suitable for input into pg_restore.įor example: $ pg_dump -F c tecmintdb > tecmintdb.dump The pg_dump supports other output formats as well. By default, the output format is a plain-text SQL script file. To back up, a PostgreSQL database, start by logging into your database server, then switch to the Postgres user account, and run pg_dump as follows (replace tecmintdb with the name of the database you want to backup). It generates a database file with SQL commands in a format that can be easily restored in the future. PostgreSQL provides the pg_dump utility to help you back up databases. Let’s get started… Backup a Single PostgreSQL Database
Backup and restore install#
How to Install PostgreSQL and pgAdmin in RHEL 8.How to Install PostgreSQL and pgAdmin in CentOS 8.How to Install PostgreSQL and pgAdmin4 in Ubuntu 20.04.If not, read our following articles to install PostgreSQL on your Linux distribution.
We assume that you already have a working installation of the PostgreSQL database system. In this article, you will learn how to backup and restore a PostgreSQL database. In a production environment, no matter how large or small your PostgreSQL database may be, regular back is an essential aspect of database management.