Re: Cannot restore dumps made with -Fc and --column-inserts

As far as I am aware, you're right and they ARE mutally exclusive.

Cheers

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Dick Visser
Sent: 04 January 2012 08:26
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] Cannot restore dumps made with -Fc and --column-inserts

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(at)filesender:~$ pg_dump -Fc --column-inserts filesender -f
filesender_backup.pgdump
postgres(at)filesender:~$ createdb -T template0 fstest
postgres(at)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(at)filesender:~$ pg_dump -Fc filesender -f filesender_backup.pgdump
postgres(at)filesender:~$ createdb -T template0 fstest
postgres(at)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

--
Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

___________________________________________________

This email is intended for the named recipient. The information contained
in it is confidential. You should not copy it for any purposes, nor
disclose its contents to any other party. If you received this email
in error, please notify the sender immediately via email, and delete it from
your computer.

Any views or opinions presented are solely those of the author and do not
necessarily represent those of the company.

PCI Compliancy: Please note, we do not send or wish to receive banking, credit
or debit card information by email or any other form of communication.

Please try our new on-line ordering system at http://www.cromwell.co.uk/ice

Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900
__________________________________________________