Backup and restore a Postgres database

#Introduction

In this tutorial, How to backup and restore a Postgres database. You have installed the PostgreSQL database system.

Backup and restore a Postgres database

Backup a PostgreSQL Database

PostgreSQL provides the pg_dump utility to help you back up databases.

Example: import and export my-db database as below

Backup and restore a Postgres database

The output format is a plain-text SQL script file ( The default)

root@5cc6d8f56baf:/# pg_dump -h localhost -U devopsroles my-db > my-db.sql

You can compress this data by using the “custom” dump format:

# format tar
root@5cc6d8f56baf:/# pg_dump -h localhost -U devopsroles -F t my-db > my-db.tar

Backup use pg_dump via a compression tool such as gzip

root@5cc6d8f56baf:/# pg_dump -h localhost -U devopsroles my-db | gzip > my-db.gz

Backup Remote PostgreSQL Databases

pg_dump -U devopsroles -h 192.168.1.122 -p 5432 my-db > my-db.sql

Explain:

  • -U: to specify the database role name
  • -h: remote host
  • -p: port a PostgreSQL database

To back up all PostgreSQL databases

root@5cc6d8f56baf:/# pg_dumpall -h localhost -U devopsroles > all_pg_dbs.sql

Restore the dump using the psql command

pgsql -f all_pg_dbs.sql postgres

Restoring a PostgreSQL Database

You can use psql or pg_restore utilities

Example:

root@5cc6d8f56baf:/# psql -h localhost -U devopsroles my-db < my-db.sql
root@5cc6d8f56baf:/# pg_restore -h localhost -U devopsroles -d my-db my-db.tar

Conclusion

You have Backup and restore a Postgres database. I hope will this your helpful. Thank you for reading the DevopsRoles page!

, ,

About HuuPV

My name is Huu. I love technology and especially Devops Skill such as Docker, vagrant, git so forth. I likes open-sources. so I created DevopsRoles.com site to share the knowledge that I have learned. My Job: IT system administrator. Hobbies: summoners war game, gossip.
View all posts by HuuPV →

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.