PostgreSQL: database restore from dump - syntax error

Question

Hi folks,

I'm trying to restore a PostgreSQL database by executing the SQL that pg_dump created, on an empty database.

I'm getting this error:

ERROR:  syntax error at or near "\"
LINE 5211: \.

lines 5210 and 5211 read:

COPY auth_group (id, name) FROM stdin;
\.

It works fine on my Linux server, where I use this command:

psql -U dbname < dumpfile.sql

but on Windows, I'm not sure how to do the same, so I've been trying to run the dumpfile's sql from pgAdminIII query utility.

What the recommended way of importing a db from a dump on Windows? Or: how can that syntax issue be resolve?

Any help is much appreciated!

Martin

Answer 1

The -f filename argument on psql will read in the file, so you don't have to pipe it in. psql should be included in PostgreSQL's bin directory in windows. Like so:

psql -U dbname -f dumpfile.sql

You may have to include a full path to the psql executable if it is not on your path, and possible add ".exe" to psql, so it is "psql.exe".

Also make sure you are not going down in version numbers, I've run into syntax issues before (e.g don't export an 8.4 database and try to load it into an 8.1 database). If so, you may have to manually edit the dump file.

Answer 2

try with psql -d dbname -U user -f dumpfile.sql