How do I restore a plain text postgres .backup file

Question

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?

Answer 1

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.