• Home
  • Ubuntu 20.04
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us
TecAdmin
Menu
  • Home
  • Ubuntu 20.04
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us

5 Tips to Backup and Restore Database in PostgreSQL

Written by Rahul, Updated on April 5, 2017

PostgreSQL database server provides pg_dump and psql utilities for backup and restore databases. This article will describe various ways to use of pg_dump command to backup database. Also you will learn how to restore datbase backup.

Backup and Restore Database in PostgreSQL

Below is some connections options which you can use for connecting remote server or authenticated server with all queries given in this article.

  • -d, –dbname=DBNAME database name
  • -h, –host=HOSTNAME database server hostname or ip
  • -p, –port=PORT database server port number (default: 5432)
  • -U, –username=NAME connect as specified database user
  • -W, –password force password prompt
  • –role=ROLENAME do SET ROLE before dump

1. Backup and Restore Single Database

Backup: single database in PostgreSQL. Replace your actual database name with mydb.

$ pg_dump -U postgres -d mydb > mydb.pgsql

Restore: single database backup in PostgreSQL.

$ psql -U postgres -d mydb < mydb.pgsql

2. Backup and Restore All Databases

Backup: all databases in PostgreSQL using pg_dumpall utility.

$ pg_dumpall -U postgres > alldbs.pgsql

Restore: all database backup using following command.

$ psql -U postgres < alldbs.pgsql

3. Backup and Restore Single Table

Backup: a single table named mytable from mydb database.

$ pg_dump -U postgres -d mydb -t mytable > mydb-mytable.pgsql

Restore: single table backup to database. Make sure your backup file contains only single table backup which you want to restore.

$ psql -U postgres -d mydb < mydb-mytable.pgsql

4. Compressed Backup and Restore Database

Backup: PostgreSQL database in compressed format.

$ pg_dump -U postgres -d mydb | gzip > mydb.pgsql.gz

Restore: database from compressed backup file directly.

$ gunzip -c mydb.pgsql.gz | psql -U postgres -d mydb

5. Split Backup in Multiple Files and Restore

Backup: PostgreSQL database and split backup in multiple files of specified size. It helps us to backup a large database and transfer to other host easily. As per below example it will split backup files of 100mb in size.

$ pg_dump -U postgres -d mydb | split -b 100m – mydb.pgsql

Restore: database backup from multiple splited backup files.

$ cat mydb.pgsql* | psql -U postgres -d mydb

Backup: database in compressed splited files of specified size.

$ pg_dump -U postgres -d mydb | gzip | split -b 100m – mydb.pgsql.gz

Restore: database from multiple files of compressed files.

$ cat mydb.pgsql.gz* | gunzip | psql -U postgres -d mydb

Share it!
Share on Facebook
Share on Twitter
Share on LinkedIn
Share on Reddit
Share on Tumblr
Share on Whatsapp
Rahul
Rahul
Connect on Facebook Connect on Twitter

I, Rahul Kumar am the founder and chief editor of TecAdmin.net. I am a Red Hat Certified Engineer (RHCE) and working as an IT professional since 2009..

6 Comments

  1. Avatar alex Reply
    February 21, 2019 at 1:56 am

    por que me sale (bash: jampy db.bk: permised denied

  2. Avatar DBA Reply
    December 21, 2018 at 8:58 am

    Great post. Articles that have meaningful and insightful comments are more enjoyable, at least to me. It’s interesting to read what other people thought and how it relates to them or their clients, as their perspective could possibly help you in the future.

  3. Avatar Johnson Reply
    January 4, 2017 at 7:13 am

    Nice blog help me lot. There i found an interesting article, giving brief description on restoring a single table from the backup in sql server. You may also have a look:
    http://www.sqlmvp.org/restore-single-table-from-backup/

    Hope! you also like this article.

  4. Avatar Olek Reply
    June 17, 2016 at 9:15 am

    Nice tips but I prefer to make PostgreSQL backup with the help of 3rd party tools like Postgresql-Backup http://postgresql-backup.com/

    • Avatar Nick Reply
      June 20, 2016 at 3:41 pm

      Great tool! Thanks!

  5. Avatar Prem Reply
    June 8, 2016 at 6:46 am

    Compressed Backup and Restore Database process helped me a lot. Thanks

Leave a Reply Cancel reply

Popular Posts

  • How To Install Python 3.9 on Debian 10
  • Download Ubuntu 20.04 LTS – DVD ISO Images
  • Linux Run Commands As Another User
  • How to Check PHP Version (Apache/Nginx/CLI)
  • How To Install and Configure GitLab on Ubuntu 20.04
  • How to Install PyCharm on Ubuntu 20.04
  • How to Check Ubuntu Version with Command or Script
  • How to Set all directories to 755 And all files to 644
© 2013-2021 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy