pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCannot restore dumps made with -Fc and --column-inserts
4+ messages / 3 participants
[nested] [flat]
* Cannot restore dumps made with -Fc and --column-inserts
@ 2012-01-04 08:25 Dick Visser <visser@terena.org>
2012-01-04 12:17 ` Re: Cannot restore dumps made with -Fc and --column-inserts French, Martin <frenchm@cromwell.co.uk>
2012-01-04 18:14 ` Re: Cannot restore dumps made with -Fc and --column-inserts Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Dick Visser @ 2012-01-04 08:25 UTC (permalink / raw)
To: 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@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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cannot restore dumps made with -Fc and --column-inserts
2012-01-04 08:25 Cannot restore dumps made with -Fc and --column-inserts Dick Visser <visser@terena.org>
@ 2012-01-04 12:17 ` French, Martin <frenchm@cromwell.co.uk>
1 sibling, 0 replies; 4+ messages in thread
From: French, Martin @ 2012-01-04 12:17 UTC (permalink / raw)
To: Dick Visser <visser@terena.org>; pgsql-admin
As far as I am aware, you're right and they ARE mutally exclusive.
Cheers
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Dick Visser
Sent: 04 January 2012 08:26
To: pgsql-admin@postgresql.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@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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.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
__________________________________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cannot restore dumps made with -Fc and --column-inserts
2012-01-04 08:25 Cannot restore dumps made with -Fc and --column-inserts Dick Visser <visser@terena.org>
@ 2012-01-04 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-01-04 20:10 ` Re: Cannot restore dumps made with -Fc and --column-inserts Dick Visser <visser@terena.org>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2012-01-04 18:14 UTC (permalink / raw)
To: Dick Visser <visser@terena.org>; +Cc: pgsql-admin
Dick Visser <visser@terena.org> writes:
> 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:
Ugh ... I think I broke this :-(.
As a workaround, you can ask pg_restore for plain text output and
pipe that to psql.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cannot restore dumps made with -Fc and --column-inserts
2012-01-04 08:25 Cannot restore dumps made with -Fc and --column-inserts Dick Visser <visser@terena.org>
2012-01-04 18:14 ` Re: Cannot restore dumps made with -Fc and --column-inserts Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-01-04 20:10 ` Dick Visser <visser@terena.org>
0 siblings, 0 replies; 4+ messages in thread
From: Dick Visser @ 2012-01-04 20:10 UTC (permalink / raw)
To: pgsql-admin
On 4 January 2012 19:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dick Visser <visser@terena.org> writes:
>> 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:
>
> Ugh ... I think I broke this :-(.
>
> As a workaround, you can ask pg_restore for plain text output and
> pipe that to psql.
Pfew, that worked, I actually didn't know about this SQL mode of pg_restore.
At least my backups are not useless :-)
--
Dick Visser
System & Networking Engineer
TERENA Secretariat
Singel 468 D, 1017 AW Amsterdam
The Netherlands
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-01-04 20:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-04 08:25 Cannot restore dumps made with -Fc and --column-inserts Dick Visser <visser@terena.org>
2012-01-04 12:17 ` French, Martin <frenchm@cromwell.co.uk>
2012-01-04 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-01-04 20:10 ` Dick Visser <visser@terena.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox