Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a dump file from a Postgresql db. However, If i run psql -U user < db.sql under Windows, i get

 ERROR:  missing data for column "x"

for certain lines. Thing is, the data for "x" is not missing. I'm guessing there are some characters in the column preceding "x" that somehow corrupt the import, but i have no idea what it might be.

Any ideas? Postgre version is 8.3.7

Edit: It would be fine to omit the offending lines, if restoring process could continue gracefully after encountering the line.

Edit2: It appears the culprit is the Ctrl-Z chracters (ascii 26) in the dump, which are interpreted as end-of-file under windows(?).

share|improve this question

4 Answers 4

You shouldn't have any trouble with characters-if they were exported successfully they should import fine. Is it possible you need the schema from the old database server as well?

If you look in the dump file can you find the line that its choking on? Is there anything odd there?

share|improve this answer
    
It appears the culprit is the Ctrl-Z chracters (ascii 26) in the dump, which are interpreted as end-of-file under windows(?) –  Ash Oct 12 '09 at 13:49
    
Ash-did you try the -f flag that Topher suggested above? It may help. –  Josh Budde Oct 12 '09 at 18:29

Have you tried passing the file using the -f flag instead of redirecting the input? Perhaps your console's redirection is acting weird:

psql -U user -f db.sql

Considering it's on Windows, I wouldn't be surprised if this was the case.

share|improve this answer
    
Good call-I forgot about the f flag –  Josh Budde Oct 12 '09 at 13:48
    
Or even better, use pg_dump with the custom format, and then pg_restore. That brings several other advantages as well, such as selective restoring. –  Magnus Hagander Oct 12 '09 at 14:52

Try dumping in custom format (-Fc) and then use pg_restore to connect directly to the database to restore it.

share|improve this answer

I could not restore from .sql dump (got the same error) because my dump was not correctly completed (errors arose when I generated the dump, but somehow I didn't notice them). So always double-check if you have a valid dump file!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.