0

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?

2 Answers 2

1

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.)

0

In your shell script you can use dropdb command.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .