Facebook Twitter Instagram
    TecAdmin
    • Home
    • Ubuntu 20.04
      • Upgrade Ubuntu
      • Install Java
      • Install Node.js
      • Install Docker
      • Install LAMP Stack
    • Tutorials
      • AWS
      • Shell Scripting
      • Docker
      • Git
      • MongoDB
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    Home»Databases»PostgreSQL»How to Backup and Restore PostgreSQL Database (5 Tips)

    How to Backup and Restore PostgreSQL Database (5 Tips)

    RahulBy RahulJanuary 22, 20153 Mins ReadUpdated:May 25, 2021

    PostgreSQL database server (also known as Postgres) is an advance database management system. The Postgres server provides psql, pg_dump and pg_dumpall command line utilities to 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 postgres database backup via command line.

    Backup and Restore Database in Postgres

    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. PostgreSQL Backup Single Database

    • Backup a single database in PostgreSQL server. Use “-d” comamnd line option to provide database name to pg_dump command. Make sure to replace your actual database name in place of mydb.
      pg_dump -h localhost -U postgres -W -d mydb > mydb.sql 
      
    • Restore a single database from backup in PostgreSQL. Just use “psql” command to restore PostgreSQL database.
      psql -h localhost -U postgres -W -d mydb < mydb.sql 
      

    2. PostgreSQL Backup All Databases

    • Backup all databases in PostgreSQL using pg_dumpall utility.
      pg_dumpall -h localhost -U postgres -W > alldbs.sql 
      
    • Restore: all database backup using following command.
      psql -h localhost -U postgres -W < alldbs.sql 
      

    3. PostgreSQL Backup Single Table

    • Backup: a single table named mytable from mydb database.
      pg_dump -h localhost -U postgres -d mydb -W -t table_1 > mydb-table_1.sql 
      
    • Restore: single table backup to database. Make sure your backup file contains only single table backup which you want to restore.
      psql -h localhost -U postgres -W -d mydb < mydb-table_1.sql 
      

    4. Compressed Backup and Restore Database

    • Backup PostgreSQL database in compressed format. Use gzip command line utility to accept piped backup data and make archive file.
      pg_dump -h localhost -U postgres -W -d mydb | gzip > mydb.sql.gz 
      
    • Restore database from compressed backup file directly. Here gunzip will extract backup file and send data to psql command via pipe interface.
      gunzip -c mydb.sql.gz | psql -h localhost -U postgres -W -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 -h localhost -U postgres -W -d mydb | split -b 100m – mydb.pql 
      
    • Restore: database backup from multiple splited backup files.
      cat mydb.sql* | psql -h localhost -U postgres -W -d mydb 
      
    • Backup: database in compressed splited files of specified size.
      pg_dump -h localhost -U postgres -W -d mydb | gzip | split -b 100m – mydb.sql.gz 
      
    • Restore: database from multiple files of compressed files.
      cat mydb.sql.gz* | gunzip | psql -h localhost -U postgres -W -d mydb 
      

    Conclusion

    In this tutorial, you have learned about backup and restore of PostgreSQL server database, tables.

    backup database pg_dump PostgreSQL psql restore
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp
    Previous Articlebash scp: command not found
    Next Article How to Increase Tomcat Upload File Size Limit

    Related Posts

    How To Install MySQL Server on Ubuntu 22.04

    Updated:April 6, 20224 Mins Read

    How to Backup Website to Amazon S3 using Shell Script

    Updated:March 24, 20222 Mins Read

    How To Install MariaDB on Debian 11

    4 Mins Read

    How To Configure PostgreSQL to Allow Remote Connections

    3 Mins Read

    How to Install Redis on Debian 11 Linux

    Updated:September 16, 20213 Mins Read

    How to Check the PostgreSQL Version

    Updated:August 6, 20212 Mins Read

    6 Comments

    1. alex on February 21, 2019 1:56 am

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

      Reply
    2. DBA on December 21, 2018 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.

      Reply
    3. Johnson on January 4, 2017 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.

      Reply
    4. Olek on June 17, 2016 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/

      Reply
      • Nick on June 20, 2016 3:41 pm

        Great tool! Thanks!

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

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

      Reply

    Leave A Reply Cancel Reply

    Recent Posts
    • How to Enable / disable Firewall in Windows
    • How to Install JAVA on Ubuntu 22.04
    • Switching Display Manager in Ubuntu – GDM, LightDM & SDDM
    • Changing the Login Screen Background in Ubuntu 22.04 & 20.04
    • How To Install PHP (8.1, 7.4 or 5.6) on Ubuntu 22.04
    Facebook Twitter Instagram Pinterest
    © 2022 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.