Hi guys
Running pg on Ubuntu 10.04 64bit, which is 8.4.9 today.
When I do a pg_dump using --column-inserts, the subsequent restore fails:
postgres@filesender:~$ pg_dump -Fc --column-inserts filesender -f
filesender_backup.pgdump
postgres@filesender:~$ createdb -T template0 fstest
postgres@filesender:~$ pg_restore -d fstest filesender_backup.pgdump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1785; 0 16390 TABLE
DATA files fs_user
pg_restore: [archiver (db)] could not execute query: ERROR: syntax
error at end of input
LINE 6: ...lesubject, filevoucheruid, filemessage, filefrom, filesize,
^
Command was: INSERT INTO files (fileto, filesubject,
filevoucheruid, filemessage, filefrom, filesize, fileoriginalname,
filestatus, filei...
pg_restore: [archiver (db)] could not execute query: ERROR: syntax
error at or near "fileoriginalname"
LINE 1: fileoriginalname, filestatus, fileip4address, fileip6address...
etc etc
Without the option it works fine:
postgres@filesender:~$ pg_dump -Fc filesender -f filesender_backup.pgdump
postgres@filesender:~$ createdb -T template0 fstest
postgres@filesender:~$ pg_restore -v -d fstest filesender_backup.pgdump
(database restored)
The column-inserts option does work with the default format (i.e. plain SQL):
pg_dump --column-inserts filesender -f filesender_backup.sql
createdb -T template0 fstest
psql fstest < filesender_backup.sql
(database restored)
I guess the -Fc and --column-inserts are mutually exclusive.
Which makes sense because according to the man page, since -Fc yields
a dump that is ONLY suitable for pg_restore, while --colum-inserts is:
"mainly useful for making dumps that can be loaded into non-PostgreSQL
databases".
In any case, it would it be great to raise an error when calling
pg_dump with both options.
Finding out that dumps are unusable at restore time is not very nice ;-)
THanks!!
--
Dick Visser
System & Networking Engineer
TERENA Secretariat
Singel 468 D, 1017 AW Amsterdam
The Netherlands