Home / server / Questions / 191499
Accepted
Chase Seibert
Asked: 2010-10-16 12:33:52 +0800 CST

Postgres backup to plaintext, drop data on restore?

  • 772

I'm looking to backup a postgres database to a plaintext file, and then restore that backup. Here is what I tried.

sudo -u postgres pg_dump --create mydatabase > backup.sql
sudo -u postgres psql < backup.sql

I want the database to be completely restored to the state it was in when I backed it up. I'm expecting it to issue a DROP DATABASE in backup.sql, but this is not happening.

Currently, my work-around is to edit backup.sql and insert a DROP DATABASE. Is that really necessary?

postgresql

2 Answers

  1. Best Answer

    It looks like the best you can do with the default pg_dump is --clean (incompatible with --create) which doesn't drop the database, but does add commands to individually drop all of the tables and other objects that are being backed up. If you load a file made this way, if someone's added a new table since the backup that new table will remain in the database.

    You could create a wrapper script:

    #!/bin/sh
echo "DROP DATABASE $1;"
sudo -u postgres pg_dump --create $1

    And then run mybackupscript.sh mydatabase > backup.sql (or move the sudo command out of the script and run the script with sudo, your choice.)

    • 1

  2. In your shell script you can use dropdb command.

    • 0