how to restore postgreSQL database with template using pg_dump


Import a PostgreSQL database dump
There are two ways to restore a PostgreSQL database:
psql for restoring from a plain SQL script file created with pg_dump,
pg_restore for restoring from a .tar file, directory, or custom format created with pg_dump.

1. Restore a database with psql

If your backup is a plain-text file containing SQL script, then you can restore your database by using PostgreSQL interactive terminal, and running the following command:

psql -U db_user db_name < dump_name.sql

where db_user is the database user, db_name is the database name, and dump_name.sql is the name of your backup file.

2. Restore a database with pg_restore

If you choose custom, directory, or archive format when creating a backup file, then you will need to use pg_restore in order to restore your database:

createdb --user db_user -T template0
pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

f you use pg_restore you have various options available, for example:
-c to drop database objects before recreating them,
-C to create a database before restoring into it,
-e exit if an error has encountered,
-F format to specify the format of the archive.
Use pg_restore -? to get the full list of available options.

Similar Posts:


How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

Scroll to Top