logo

How to take database dump and restore in PostgreSQL

gcptutorials.com PostgreSQL

Dump method is to generate a file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose.

for this tutorial we already have two dbs created with with name "test_db" and "test_user_db_restore". "test_db" is having some tables in it which would be restored in "test_user_db_restore".
if you don't have dbs created than follow the link How to create database and tables in PostgreSQL.to create dbs and tables.

  • Switch to postgres user

    • [gcptutorial@localhost ~]$ sudo su - postgres

  • Execute the dump command as shown below and check created dump

    • -bash-4.2$ pg_dump test_db > dumpfile
    -bash-4.2$ ls -lrt

  • Restoring the Dump

    • -bash-4.2$ psql test_user_db_restore < dumpfile

    Category: PostgreSQL