Skip to content

๐Ÿ“š Using PG_Dumpall to backup and restore the DBยถ

1. Using PG_Dumpall to backup and restore the DBยถ

  • 1.1 create account information into the .pgpass in your Postgres Home directory

Format will be like IP:port:database:username:password

Add below information 

vi ~/.pgpass
127.0.0.1:5438:*:postgres:password

  • 1.2 Doing the backup for pg_dumpall
pg_dumpall -h 127.0.0.1 -U postgres -p 5438 -c --inserts -f /tmp/backup1_all.sql
ll /tmp/backup1_all.sql

alt text

SQL content will like this

alt text

  • 1.3 Before restoring, we check the DB status

alt text

Delete the one table space and schema

alt text

  • 1.4 Do the restore from our backup sql file

    psql -U postgres -p 5438 -f backup1_all.sql

    alt text

    alt text

  • 1.5 Go to check DB status again. You will find the delete tablespace and schema back

    alt text