Postgresql – Better way to restore to existing pg database

postgresqlrestore

We want to restore to an existing 9.3 pg database. What we ended up doing was:

1) delete the database and recreate again, 
2) use pg_restore to restore. 
3) reboot the ubuntu server to allow rails app to pick up the new database.

We tried the following pg_restore command and it did not restore to an existing database:

$pg_restore --dbname=existing_database --verbose /home/myname/dbbackup.backup

There was no change of existing_database after pg_restore. Is there a command we can restore to an existing pg database without deleting it first as we did? We have to use command line. Many thanks.

Best Answer

The command you showed looks fine.

pg_restore -d target_database /path/to/the/backup.dbbackup

At a guess, there were errors during the restore but you didn't see them. Most likely you were running with a user ID that did not have the correct permissions to perform the restore.

If you are certain that there is no data of any value in the target database (which will be deleted by the following command) try:

sudo -u postgres pg_restore --create --clean -d postgres /path/to/dbbackup.sql

If you have issues, post the output in an edit to your question, then comment here.

Also: You do not need to restart the server. At most, you should restart your Rails application. Usually that means restarting Apache, but it depends a bit on how you're running Rails. Restarting the whole server is completely unnecessary.

Related Question