www.tecmint.com Open in urlscan Pro
2606:4700:20::ac43:48cf  Public Scan

Form analysis
2 forms found in the DOM

GET https://www.tecmint.com/

<form method="get" class="search-form navigation-search" action="https://www.tecmint.com/">
  <input type="search" class="search-field" value="" name="s" title="Search">
</form>

POST https://www.tecmint.com/wp-comments-post.php

<form action="https://www.tecmint.com/wp-comments-post.php" method="post" id="commentform" class="comment-form" novalidate="">
  <p> Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published. </p>
  <p class="comment-form-comment"><label for="comment" class="screen-reader-text">Comment</label><textarea autocomplete="new-password" id="comment" name="b311dc7799" cols="45" rows="8" aria-required="true"></textarea><textarea
      id="a93bf2e3eae6d41a170d8f5d4d816f78" aria-label="hp-comment" aria-hidden="true" name="comment" autocomplete="new-password"
      style="padding:0 !important;clip:rect(1px, 1px, 1px, 1px) !important;position:absolute !important;white-space:nowrap !important;height:1px !important;width:1px !important;overflow:hidden !important;" tabindex="-1"></textarea>
    <script data-noptimize="" type="text/javascript">
      document.getElementById("comment").setAttribute("id", "a93bf2e3eae6d41a170d8f5d4d816f78");
      document.getElementById("b311dc7799").setAttribute("id", "comment");
    </script>
  </p><label for="author" class="screen-reader-text">Name</label><input placeholder="Name *" id="author" name="author" type="text" value="" size="30">
  <label for="email" class="screen-reader-text">Email</label><input placeholder="Email *" id="email" name="email" type="email" value="" size="30">
  <p class="comment-form-cookies-consent"><input id="wp-comment-cookies-consent" name="wp-comment-cookies-consent" type="checkbox" value="yes"> <label for="wp-comment-cookies-consent">Save my name, email, and website in this browser for the next time
      I comment.</label></p>
  <p class="form-submit"><input name="submit" type="submit" id="submit" class="submit" value="Post Comment"> <input type="hidden" name="comment_post_ID" value="40286" id="comment_post_ID">
    <input type="hidden" name="comment_parent" id="comment_parent" value="0">
  </p>
</form>

Text Content

WE VALUE YOUR PRIVACY

We and our partners store and/or access information on a device, such as cookies
and process personal data, such as unique identifiers and standard information
sent by a device for personalised ads and content, ad and content measurement,
and audience insights, as well as to develop and improve products.With your
permission we and our partners may use precise geolocation data and
identification through device scanning. You may click to consent to our and our
partners’ processing as described above. Alternatively you may click to refuse
to consent or access more detailed information and change your preferences
before consenting.Please note that some processing of your personal data may not
require your consent, but you have a right to object to such processing. Your
preferences will apply to this website only. You can change your preferences at
any time by returning to this site or visit our privacy policy.
AGREE DISAGREE MORE OPTIONS
Skip to content
Linux Foundation LFCS and LFCE Certification Preparation Guide - Get This Book
Menu
 * Home
 * About
 * eBooks
 * Shop
 * Donate
 * Linux Online Courses
 * Subscribe to Newsletter
 * Linux Hosting
 * A-Z Linux Commands
 * Get Involved
   * Testimonials
   * We are Hiring!
   * Submit Article Request
   * Suggest an Update



Menu
 * Linux Distro’s
   * CentOS Alternatives
   * Best Linux Distros
   * RedHat Distributions
   * Linux Distros for Beginners
   * Linux Server Distros
   * Debian Distributions
   * Ubuntu Distributions
   * Arch Linux Distros
   * Rolling Linux Distros
   * KDE Linux Distros
   * Secure Linux Distros
   * Linux Distros for Old PC
   * New Linux Distros
   * Linux Distros for Kids
   * Linux Distros for Windows Users
 * FAQ’s
 * Programming
   * Shell Scripting
   * Learn Python
   * Learn Awk
 * Linux Commands
 * Linux Tricks
 * Best Linux Tools
 * Certifications
   * RHCE Exam
   * RHCSA Exam
   * LFCE Exam
   * LFCS Exam
   * LFCA Exam
   * Ansible Exam
 * Guides
   * Hadoop Series
   * Docker Series
   * Postfix Mail
   * XenServer Series
   * RHEV Series
   * Clustering Series
   * LVM Series
   * RAID Series
   * KVM Series
   * iSCSI Series
   * Zentyal Series
   * Ansible Series
   * Django Series
   * Create GUI Apps
 * Monitoring Tools
   * Nagios
   * Zabbix
   * Cacti
   * Observium
   * Monitorix
   * Collectd
   * Collectl
   * MySQL Monitoring
 * 


HOW TO BACKUP AND RESTORE A POSTGRESQL DATABASE

Aaron KiliOctober 15, 2020October 15, 2020 Categories Databases, PostgreSQL 3
Comments


In a production environment, no matter how large or small your PostgreSQL
database may be, regular back is an essential aspect of database management. In
this article, you will learn how to backup and restore a PostgreSQL database.

We assume that you already have a working installation of the PostgreSQL
database system. If not, read our following articles to install PostgreSQL on
your Linux distribution.

 * How to Install PostgreSQL and pgAdmin4 in Ubuntu 20.04
 * How to Install PostgreSQL and pgAdmin in CentOS 8
 * How to Install PostgreSQL and pgAdmin in RHEL 8

Let’s get started…


BACKUP A SINGLE POSTGRESQL DATABASE

PostgreSQL provides the pg_dump utility to help you back up databases. It
generates a database file with SQL commands in a format that can be easily
restored in the future.

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). By default, the
output format is a plain-text SQL script file.

$ pg_dump tecmintdb > tecmintdb.sql




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.

For example:

$ pg_dump -F c tecmintdb > tecmintdb.dump
OR
$ pg_dump -F t tecmintdb > tecmintdb.tar


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. The
directory which will be created by pg_dump must not exist.

$ 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


You can restore the dump using psql as shown.

$ pgsql -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 tecmintdb < tecmintdb.sql


As mentioned above, a custom-format dump is not a script for pgsql, 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	



BACKUP LARGE POSTGRESQL DATABASES

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


If the database is extremely large, you can dump in parallel by dumping
number_of_jobs tables simultaneously using the -j flag, as shown.

$ pg_dump -F d -j 5 -f tecmintdumpdir


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.


BACKUP REMOTE POSTGRESQL DATABASES

pg_dump is a regular PostgreSQL client tool, it supports operations on remote
database servers. 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. Besides, use the -U flag to
specify the database role name to connect as.

Remember to replace 10.10.20.10 and 5432 and tecmintdb with your remote host IP
address or hostname, database port, and database name respectively.

$ pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sql


Ensure 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.

PostgreSQL Database Connection Error

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 tecmintdb | pqsl -U tecmint -h 10.10.20.30 tecmintdb



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.

For more information on how to schedule cron jobs, see: How to Create and Manage
Cron Jobs on Linux

That’s it for now! It’s a good idea to make backing up data a part of your
database management routine. To reach us for any questions or comments, use the
feedback form below. For more information, see the pg_dump and pg_restore
reference pages.

Tags PostgreSQL Tips
Post navigation
How to Share a Local Folder with a Remote Host Running on VMWare
How to Resolve “Temporary failure in name resolution” Issue
If you liked this article, then do subscribe to email alerts for Linux
tutorials. If you have any questions or doubts? do ask for help in the comments
section.



IF YOU APPRECIATE WHAT WE DO HERE ON TECMINT, YOU SHOULD CONSIDER:

TecMint is the fastest growing and most trusted community site for any kind of
Linux Articles, Guides and Books on the web. Millions of people visit TecMint!
to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as
a token of appreciation.



We are thankful for your never ending support.


RELATED POSTS

How to Setup MySQL Replication in RHEL, Rocky and AlmaLinux

How to Install MySQL 8.0 on RHEL/CentOS 8/7 and Fedora 35

How to Install SQLite and SQLite Browser in Ubuntu

How to Install CouchDB on Debian 10

How to Install Apache CouchDB in Ubuntu 20.04

How To Create a New User and Grant Permissions in MySQL




3 THOUGHTS ON “HOW TO BACKUP AND RESTORE A POSTGRESQL DATABASE”

 1. Craig Weber
    May 6, 2022 at 8:09 pm
    
    I’m pretty sure the restore is incorrect. You need to first reset the
    database instance or else you’re just going to get a bunch of “table already
    exists” and “role already exists” and so on.
    
    Reply
    
 2. Mohit Singh
    March 19, 2021 at 2:28 pm
    
    Excellent !!!
    
    Reply
    
 3. Andreas S.
    October 18, 2020 at 12:52 am
    
    Your cron job example has a problem: during the time of the backup, you have
    no working backup because you overwrite the sole existing backup.
    
    Also if something goes wrong during the backup, you are left with no backup.
    
    Reply
    


GOT SOMETHING TO SAY? JOIN THE DISCUSSION. CANCEL REPLY

Have a question or suggestion? Please leave a comment to start the discussion.
Please keep in mind that all comments are moderated and your email address will
NOT be published.

Comment

Name Email

Save my name, email, and website in this browser for the next time I comment.



Over 3,500,000+ Readers

A Beginners Guide To Learn Linux for Free [with Examples]

Red Hat RHCSA/RHCE 8 Certification Study Guide [eBooks]
Linux Foundation LFCS and LFCE Certification Study Guide [eBooks]


LEARN LINUX COMMANDS AND TOOLS

10 tr Command Examples in Linux



6 WC Command Examples to Count Number of Lines, Words, Characters in Linux



10 Commands to Collect System and Hardware Info in Linux



Gogo – Create Shortcuts to Long and Complicated Paths in Linux



Easily Correct a Typo of Previous Command Using Carat (^) Symbol



Exploring Linux Shell (Terminal) Remotely Using PHP Shell



If You Appreciate What We Do Here On TecMint, You Should Consider:







LINUX SERVER MONITORING TOOLS

How to Test Network Throughput Using iperf3 Tool in Linux



How to Install Zabbix on RHEL/CentOS and Debian/Ubuntu – Part 1



CBM – Shows Network Bandwidth in Ubuntu



HardInfo – Check Hardware Information in Linux



Installing “PHP Server Monitor” Tool using LEMP or LAMP Stack in Arch Linux



Install Cacti (Network Monitoring) on RHEL/CentOS 8/7 and Fedora 30




LEARN LINUX TRICKS & TIPS

How to Boot into Single User Mode in CentOS/RHEL 7



Bash-it – Bash Framework to Control Your Scripts and Aliases



How to Download MP3 Tracks from a YouTube Video Using YouTube-DL



fdupes – A Command Line Tool to Find and Delete Duplicate Files in Linux



How to Find Linux Server Geographic Location in Terminal



How to Find MySQL, PHP and Apache Configuration Files




BEST LINUX TOOLS

8 Top Open Source Reverse Proxy Servers for Linux



6 Best PDF Page Cropping Tools For Linux



10 Best File and Disk Encryption Tools for Linux



10 Best Free and Open Source Software (FOSS) Programs I Found in 2020



3 Useful GUI and Terminal Based Linux Disk Scanning Tools



The 8 Best Free Anti-Virus Programs for Linux


 * Donate to TecMint
 * Contact Us
 * Advertise on TecMint
 * Linux Services
 * Copyright Policy
 * Privacy Policy
 * Career
 * Sponsored Post

Tecmint: Linux Howtos, Tutorials & Guides © 2022. All Rights Reserved.

The material in this site cannot be republished either online or offline,
without our permission.

Hosting Sponsored by : Linode Cloud Hosting

Scroll back to top