Home / server / Questions / 176661
Accepted
aleroot
Asked: 2010-09-01 11:07:21 +0800 CST

psql empty database before restore

  • 772

I want to restore a database of postgreSQL, but before do it i need to empty the restore target database.

Is there an option of psql to do it ?

Now i use a command line as the following : 

psql -U postgres db_test < testdb.sql
postgresql psql

2 Answers

  1. Best Answer

    There are two options here - if your backup is plain text then you add the -c option to pg_dump - ie

    pg_dump -c mydb > mydb.sql

    Otherwise if your using -Fc or -Ft on pg_dump then you use the -c flag on pg_restore

    pg_dump -Ft -b mydb > db.tar

    pg_restore -c -d newdb db.tar

    • 12

  2. Assuming you're talking about restoring from a pg_dump backup the answer is "it depends on how the backup was made", but typically yes: You must empty the database before restoring the backup.

    The fastest way to empty out a database in Postgres is to drop and re-create the database (you'll probably need superuser privileges for that), otherwise you have to manually drop each table and any stored procedures you may have (CASCADE is your friend here).

    • 1