{"id":22492,"date":"2023-10-15T04:53:23","date_gmt":"2023-10-15T04:53:23","guid":{"rendered":"https:\/\/linuxways.net\/?p=22492"},"modified":"2023-10-19T02:15:34","modified_gmt":"2023-10-19T02:15:34","slug":"backup-restore-postgresql-database","status":"publish","type":"post","link":"https:\/\/linuxways.net\/de\/ubuntu\/backup-restore-postgresql-database\/","title":{"rendered":"How to Backup & Restore PostgreSQL Database"},"content":{"rendered":"

Protecting the integrity of databases is essential for organizations of all kinds in the data-driven world of today. An open-source, dependable relational database management system called PostgreSQL is widely used in many different sectors. Knowing how to backup and restore a PostgreSQL database is a key skill that protects us from data loosing whether we are a novice or an expert database administrator.<\/p>\n

Let us discuss about how to restore and back up the PostgreSQL databases. We will explain the key principles, approaches, and best practices to ensure the security and availability of our data.<\/p>\n

PostgreSQL Installation on Ubuntu<\/strong><\/h2>\n
$ <\/span>sudo<\/span> apt update<\/div><\/div>\n

Install the most recent PostgreSQL version from the Ubuntu default repository.<\/p>\n

$ <\/span>sudo<\/span> apt install<\/span> postgresql<\/div><\/div>\n

The configuration of PostgreSQL is stored in the \u201c\/etc\/postgresql\/12\/main\u201d directory, while the default data location is \u201c\/var\/lib\/postgresql\/12\/main\u201d.<\/p>\n

$ sudo<\/span> systemctl is-active postgresql
\n
\n$ sudo<\/span> systemctl is-enabled postgresql
\n
\n$ sudo<\/span> systemctl status postgresql<\/div><\/div>\n

Also, validate that the PostgreSQL server is ready to accept the client connections by doing the following:<\/p>\n

$ <\/span>sudo<\/span> pg_isready<\/div><\/div>\n

After that, we can create the database and configure the database.<\/p>\n

Creating a Backup of a Single PostgreSQL Database<\/strong><\/h2>\n

The \u201cpg_dump\u201d program from PostgreSQL makes it easy to back up the databases. With the use of SQL commands, it creates a database file that can later be readily recovered.<\/p>\n

Next, log in as a Postgres user and execute \u201cpg_dump\u201d as shown in the following:<\/p>\n

$ <\/span>pg_dump souvikdb ><\/span> souvikdb.sql<\/div><\/div>\n

Other output formats are also supported by \u201cpg_dump\u201d:<\/p>\n

-F<\/strong>: Output format<\/p>\n

\u201cc\u201d<\/strong>: Custom format archive file<\/p>\n

\u201ct\u201d<\/strong>: Tar format archive file<\/p>\n

All formats are compatible with \u201cpg_restore\u201d.<\/p>\n

Let us see an example:<\/p>\n

$ <\/span>pg_dump -F<\/span> c souvikdb ><\/span> souvikdb.dump<\/div><\/div>\n

Or<\/p>\n

$ <\/span>pg_dump -F<\/span> t souvikdb ><\/span> souvikdb.tar<\/div><\/div>\n

The \u201c-f\u201d options help to save the output.<\/p>\n

$ <\/span>pg_dump -F<\/span> d souvikdb -f<\/span> souvikdumpdir<\/div><\/div>\n

We may restore the dump using \u201cpsql\u201d as demonstrated in the following:<\/p>\n

$ <\/span>psql -f<\/span> all_pg_dbs.sql postgres<\/div><\/div>\n

Recovering or Restoring a PostgreSQL Database<\/strong><\/h2>\n

To restore a PostgreSQL database, we can use the \u201cpsql\u201d or \u201cpg_restore\u201d commands. The \u201cpsql\u201d command recovers the text files created by \u201cpg_dump\u201d, but \u201cpg_restore\u201d recovers a PostgreSQL database from a non-plain-text archive prepared by \u201cpg_dump\u201d (custom, tar, or directory).<\/p>\n

The following is an illustration on how to recover a plain text file dump:<\/p>\n

$ <\/span>psql souvikdb <<\/span> souvikdb.sql<\/div><\/div>\n

A custom-format dump must be restored using \u201cpg_restore\u201d as demonstrated because, as already mentioned, it is not a script for \u201cpsql\u201d. Here, \u201cd\u201d means \u201cdirectory\u201d format archive:<\/p>\n

$ <\/span>pg_restore -d<\/span> souvikdb souvikdb.dump<\/div><\/div>\n

 <\/p>\n

Taking Backup of Large PostgreSQL Database<\/strong><\/h2>\n

We can perform a compressed dump by filtering the output of \u201cpg_dump\u201d through a compression tool like gzip or any of our favourites if the database which we are backing up is huge and we want to create a somewhat smaller output file. So, use the following format:<\/p>\n

$ <\/span>pg_dump souvikdb |<\/span> gzip<\/span> ><\/span> souvikdb.gz<\/div><\/div>\n

Remote PostgreSQL Database Backup<\/strong><\/h2>\n

The \u201cpg_dump\u201d is a standard PostgreSQL client tool that allows us to perform the operations on remote database servers. In addition, use the -U parameter to indicate the database role to connect as:<\/p>\n

Replace the 30.10.20.10 hostip or hostname.<\/p>\n

Replace 5232 with database port.<\/p>\n

Replace the \u201csouvikdb\u201d database name.<\/p>\n

$ <\/span>pg_dump -U<\/span> souvik -h<\/span> 30.10.20.10 -p<\/span> 5232<\/span> souvikdb ><\/span> souvikdb.sql<\/div><\/div>\n

Utilizing the \u201cpg_dump\u201d and \u201cpsql\u201d utilities as demonstrated. It is also feasible to directly dump a database from one server to another.<\/p>\n

$ <\/span>pg_dump -U<\/span> souvik -h<\/span> 30.10.20.10 souvikdb |<\/span> pqsl -U<\/span> souvik -h<\/span> 30.10.20.30 souvikdb<\/div><\/div>\n

Using a Cron Job to Auto Backup a PostgreSQL Database<\/strong><\/h2>\n

Using cron tasks, we can schedule the backups at regular intervals. Cron jobs are a popular method to schedule various types of tasks to execute on a server.<\/p>\n

The following cron job can be used to automate the PostgreSQL database backup. It should be noted that the commands that follow must be run as the PostgreSQL superuser:<\/p>\n

$ mkdir<\/span> -p<\/span> \/<\/span>srv\/<\/span>backups\/<\/span>databases
\n
\n$ crontab -e<\/span><\/div><\/div>\n

This new service is started automatically by the cron service without requiring a restart.<\/p>\n

It automatically takes the backup of our important files.<\/p>\n

Conclusion<\/strong><\/h2>\n

Integrity protection for PostgreSQL databases is essential. A reliable backup and restore plan are necessary. We can successfully handle the backup and restore the difficulties with the help of this guide. Data availability and recoverability are ensured through routine backups, strategic planning, and adherence to best practices to investigate the PostgreSQL backups, rely on tools and methods, and improve our data management.<\/p>","protected":false},"excerpt":{"rendered":"

Tutorial on how to restore and back up the PostgreSQL databases using its key principles, approaches, and best practices to ensure the security of the data.<\/p>","protected":false},"author":111,"featured_media":22495,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-22492","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ubuntu"],"_links":{"self":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/22492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/users\/111"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/comments?post=22492"}],"version-history":[{"count":0,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/22492\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media\/22495"}],"wp:attachment":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media?parent=22492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/categories?post=22492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/tags?post=22492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}