Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

How to Backup and Restore a PostgreSQL Database in Linux​

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Backup-restore postgreSQL database - Learnomate
  • User AvatarAshiwini
  • 23 May, 2024
  • 0 Comments
  • 3 Mins Read

How to Backup and Restore a PostgreSQL Database in Linux​

 

Backup a Single PostgreSQL Database

  • In a production environment, no matter how large or small your PostgreSQL database may be, regular backup is an essential aspect of database management. In this article, we  will learn how to take a  backup and restore a PostgreSQL database.
  • Backing up and restoring a PostgreSQL database in Linux is a straightforward process using the command-line tools pg_dump for backups and psql for restoration.
pg_dump -U [username] [database_name] > [backup_file].sql
  • PostgreSQL allows you to create backups in a custom format, which can be useful for more complex backup and restore scenarios. Use the -F option to specify the format (c for custom):
  • Replace [username] with your PostgreSQL username, [database_name] with the name of the database you want to back up, and [backup_file] with the desired name for your backup file.
pg_dump -U [username] -F c [database_name] -f [backup_file].dump
  • The pg_dump supports other output formats as well.
  • 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.
$ pg_dump -U postgres -F c tecmintdb > tecmintdb.dump
OR
$ pg_dump -U postgres -F t tecmintdb > tecmintdb.tar
  • PostgreSQL allows you to create backups in a custom format, which can be useful for more complex backup and restore scenarios. Use the -F option to specify the format (c for custom):
$ pg_dump -F d tecmintdb -f tecmintdumpdir
  • To back up all PostgreSQL databases, use the pg_dumpall tool as shown.
$ pg_dumpall > all_pg_dbs.sql

For Example:

$ psql -f all_pg_dbs.sql postgres

Restoring a PostgreSQL Database

  • To restore a PostgreSQL database, you can use the psql or pg_restore utilities. 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).
  • Here is an example of how to restore a plain text file dump:
$ psql -U postgres tecmintdb < tecmintdb.sql
  • As mentioned above, a custom-format dump is not a script for psql, so it must be restored with pg_restore as shown.
$ pg_restore -d tecmintdb tecmintdb.dump
OR
$ pg_restore -d tecmintdb tecmintdb.tar
OR
$ pg_restore -d tecmintdb tecmintdumpdir

Compressed PostgreSQL Database Backup

  • If the database you are backing up is large and you want to generate a fairly smaller output file, then you can run a compressed dump where you have to filter the output of pg_dump via a compression tool such as gzip or any of your favorite:
$ pg_dump tecmintdb | gzip > tecmintdb.gz
$ pg_dump -F d -j 5 -f tecmintdumpdir

 Backup Remote PostgreSQL Databases

  • Use the pg_dump command with the -h flag to specify the remote host, -p to specify the port, and -U to specify the username. You’ll also need to provide the name of the database you want to back up.
  • Replace [remote_host] with the hostname or IP address of the remote server, [port] with the port number of the PostgreSQL server (usually 5432), [username] with the username used to connect to the database, [database_name] with the name of the database you want to back up, and [backup_file] with the desired name for your backup file.
pg_dump -h [remote_host] -p [port] -U [username] [database_name] > [backup_file].sql
pg_dump -h example.com -p 5432 -U postgres mydatabase > mydatabase_backup.sql

Auto Backup PostgreSQL Database Using a Cron Job

  • You can perform backups at regular intervals using cron jobs. Cron jobs are a commonly used means for scheduling various kinds of tasks to run on a server
  • You can configure a cron job to automate PostgreSQL database backup as follows. Note that you need to run the following commands as the PostgreSQL superuser:
$ mkdir -p /srv/backups/databases
  • Next, run the following command to edit the crontab to add a new cron job.
$ crontab -e
  • Copy and paste the following line at the end of the crontab. You can use any of the dump formats explained above.
0 0 * * * pg_dump -U postgres tecmintdb > /srv/backups/postgres/tecmintdb.sql
  • Save the file and exit.
  • The cron service will automatically start running this new job without a restart. And this cron job will run every day at midnight, it is a minimum solution to the backup task.