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

From: Dick Visser <visser(at)terena(dot)org>
To: pgsql-admin(at)postgresql(dot)org
Subject: Cannot restore dumps made with -Fc and --column-inserts
Date: 2012-01-04 08:25:36
Message-ID: CAEQQDN=HyD=WWF5FHWxeHpjXnbT==BF0CC788qt2ZuFzB_wnLA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message French, Martin 2012-01-04 12:17:28 Re: Cannot restore dumps made with -Fc and --column-inserts
Previous Message Jeff Rule 2012-01-04 00:19:46 Re: unix domain sockets on Windows.