I found that it was actually a '\\N' value only that causes
dumps to dump successfully, but fail on import (When using COPY),
because both '\N' and '\\N' are seen as null by the COPY statement.
I just happened to have '\\N' values in my NOT NULL text field.
I now manually use this dump command :
pg_dump | sed 's:\\\\N:¬:g' | gzip > dump.gz
and do the reverse sed to restore.
Stef mentioned :
=> mike g mentioned :
=> => That could be a bug. How are you dumping the data? pg_dump? Select
=> => query? How are you restoring the data? psql?
=>
=> Dumping:
=> pg_dump -Ft | gzip > dump.tgz
=>
=> Restoring:
=> zcat dump.tgz | pg_restore -Ft |psql
=> OR
=> tar xvfz dump.tgz
=> perl -pi -e 's/\$\$PATH\$\$/$ENV{PWD}/g' restore.sql
=> psql -f restore.sql
=>
=> Both these methods, produce the same result.
=>