Restoring a PLAIN backup in PostgreSQL


So um, pgAdmin is nice. It really is. Yeah, it crashes quite often, but UI-wise it’s really handy. For the most part. But this does not apply to backups. The thing is, by default pgAdmin will create backups in PLAIN format. Which is fine… err, would be fine if it could restore PLAIN backups. But guess what – it can’t!

So you have your backup, you’re trying to restore it and you’re staring at pgAdmin which says your backup won’t do shit. And you get really nervous. But relax, your backup is fine – here’s how to restore it.

  1. Create a new, empty database for your backup.
  2. Open the command prompt and add your PostgreSQL\bin folder to PATH.
  3. psql -U postgres new_database < backup_database.sql

Where postgres is the user account for your PostgreSQL installation (this is the default, but YMMV).