Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group