Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is not valid archive file. How do I restore the db back?

share|improve this question
up vote 1 down vote accepted

It would appear that the PLAIN format is SQL. To Quote the documentation:

pg_dump does not support all options for all backup file formats. Particularly, to backup blobs the PLAIN format can not be used. Also, a PLAIN file can not be interpreted, and can not be restored using pgAdmin. The PLAIN format will create an SQL script that can be executed using the psql tool. For standard backup and restore purposes, the COMPRESS and TAR options are recommended.

I'm assuming you could restore using something like (on unix):

psql < backup-file

or from inside psql prompt (newdb is an empty db)

psql newdb
newdb=# \i backup-file

You might have to run as privileged user 'postgres' (on unix, say) or pass the appropriate credentials for the role to psql so that it can create the db and roles and other entities indicated by the backup-file. The create database and role statements might not be present in the backup depending on whether it has the entire server (it has) or just one database (you should create the db and role(s)). So you'd have to create them before restoring it.

share|improve this answer
I am on windows – yankitwizzy Feb 21 '12 at 11:52
You should still be able to do it using the psql.exe bundled with pgadmin. The default installation should put it some where like C:\Program Files\pgAdmin III\1.14> – Anil Feb 21 '12 at 12:07
Just tried it myself and would like to point out two nuances with the psql.exe on windows. You might have to set the environment variable PGCLIENTENCODING to the encoding your (empty) db is created with. Also, I had to specify the backup file path using '/' (forward slashes), i.e. db-# \i C:/Temp/db.backup – Anil Feb 21 '12 at 12:38

You must log in to answer this question.

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