PostgreSQL: database restore from dump - syntax error

Question

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

Loraleeloralie

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 -d dbname -U username -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.

Reeding

This worked for me when trying to import a dump with Navicat that was throwing all sorts of syntax errors. It worked just fine from OS X's terminal. – Annihilation

Answer 2

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

Lamothe

Answer 3

I think this syntax error is sometimes a red herring. If there's some other problem with your dump, or with that particular copy command, psql may ignore the start of the COPY (because of the error), and then the following syntax is only valid inside COPY, so when the start is ignored it leads to a syntax error.

Check if the error quoted is the first error encountered; if not, try resolving all other errors first.

Dumbhead