Postgresql db restoring from dump fails

postgresql

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(?).

Best Answer

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.

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

OK, got it from someone else. This query should do the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

Postgresql – Merging Postgresql dump with production database

patch could be you friend could try creating a new dump, we'll call it current_dump and do a diff against old_dump then use

   diff old_dump new_dump > patch.input
    (copy the new_dump) to another file at this point new.db.dump)
    patch new.db.dump patch.input

You might have to sort the sort the dump files before you do the diff and depending on how big your database is this might not really be practical.

Also be very careful and do this against a backup until you are sure its right.

Otherwise, maybe there are ETL type tools that can do this.

Best Answer

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

Postgresql – Merging Postgresql dump with production database

Related Topic