PostgreSQL Dump And Restore

1.Pg_Dump

We can use it to migrate a database or get a backup. This process will continue readers, writers, and other users don’t be blocked from using the database.

1.1 Database Dump

We’re taking a backup of the database with pg_dump. TestDB is my database name

EXAM: pg_dump -Fc — data-only -h<host> -p<port> -U<username> -W -d<database> -t<table> > ~/dump/<filename.dump>

[root@blt ~]# su - postgres
[postgres@blt ~]$ pg_dump -Fc TestDB> TestDB.dump

1.2 Table Dump

We’re taking a backup of the table with pg_dump. t1 is my table name

EXAM: pg_dump -Fc — data-only -h<host> -p<port> -U<username> -W -d<database> -t<table> > ~/dump/<filename.dump>

[root@blt ~]# su - postgres
[postgres@blt ~]$ pg_dump -Fc — data-only -W -dpostgres -tt1 > t1.dump

2.Pg_Restore

The pg_restore command takes a file created by a pg_dump command and restores the selected PostgreSQL database.

2.1 Database Restore

Yes, now we can restore the database file we received from dump

EXAM: pg_restore — data-only -h<host> -p<port> -U<username> -W -d<database> -t<table> ~/Downloads/<filename.dump>

[root@blt ~]# su - postgres
[postgres@blt ~]$ pg_restore -C -d postgres TestDB.dump

2.2 Table Restore

And we now table restore note:The table to be restored, before must be created.

EXAM: pg_restore — data-only -h<host> -p<port> -U<username> -W -d<database> -t<table> ~/Downloads/<filename.dump>

[root@blt ~]# su - postgres
[postgres@blt ~]$ pg_restore — data-only -W -dpostgres -tt1new t1.dump