Thread: pg_restore on windows with pipe

Hi All,

I am trying to restore a dump (custom archive format) using pg_restore like this:

type C:\testdump | pg_restore   -Fc -C -U postgres -w -d postgres

where testdump contains the database dump from the following commmand:
pg_dump -U postgres -Fc test > C:\testdump


the restore command fails :

C:\Program Files (x86)\PostgreSQL\8.4\bin>type C:\testdump | pg_restore   -Fc -C -v -U postgres -w -d postgres
pg_restore: connecting to database for restore
pg_restore: creating DATABASE test
pg_restore: connecting to new database "test"
pg_restore: connecting to database "test" as user "postgres"
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: creating TABLE tb1
pg_restore: restoring data for table "tb1"
pg_restore: [custom archiver] could not find block ID 1770 in archive -- possibly corrupt archive
pg_restore: *** aborted because of error

I have read about Custom format behavior that restore involves going back & front inside the dump for the entire database restore.

But the same redirection works in Unix very well.
cat /test/dump > pg_restore   -Fc -C -U postgres -w -d postgres
completely the restore correctly.

The dump header is :

;
; Archive created at Tue Mar 29 10:36:58 2011
;     dbname: test
;     TOC Entries: 9
;     Compression: -1
;     Dump Version: 1.11-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 8.4.7
;     Dumped by pg_dump version: 8.4.7
;
;
; Selected TOC Entries:
;
1771; 0 0 ENCODING - ENCODING
1772; 0 0 STDSTRINGS - STDSTRINGS
1773; 1262 16395 DATABASE - test postgres
3; 2615 2200 SCHEMA - public postgres
1774; 0 0 COMMENT - SCHEMA public postgres
;       depends on: 3
1775; 0 0 ACL - public postgres
;       depends on: 3
304; 2612 16386 PROCEDURAL LANGUAGE - plpgsql postgres
1491; 1259 16396 TABLE public tb1 postgres
;       depends on: 1769 3
1770; 0 16396 TABLE DATA public tb1 postgres
;       depends on: 1491


One might want to ask why do you want to use type OR cat commands when you have a dump file already, but this dump file comes in a buffer stream which i will read and apply to pg_restore using pipes both in Unix & Windows. For the test purpose i have write the stream into file and trying restore.

can someone help me achieve this?


--

Ravi Thati <gotothati@gmail.com> wrote:

> type C:\testdump | pg_restore -Fc [...]

> pg_restore: [custom archiver] *could not find block ID 1770* in
> archive -- possibly corrupt archive
> pg_restore: *** aborted because of error

Does the type command treat the stream as characters?  (You could
try putting a ^Z end of file character into a small text file and
see how much of it you see if you run type against it.)

-Kevin

Have you tried a non redirect? It could be that the "type" command
mangles the file (MS software is good at this)
pg_restore -Fc -C -U postgres -w -d postgres C:\testdump

or even with standard redirect
pg_restore -Fc -C -U postgres -w -d postgres < C:\testdump

I've had some issues with pg_restore through pgAdmin III where the
command passed to the windows shell will just plain not work unless the
form of the command is absolutely perfect, it seems that windows doesn't
like the form sometimes.

M

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner
Sent: 29 March 2011 18:41
To: Ravi Thati; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_restore on windows with pipe

Ravi Thati <gotothati@gmail.com> wrote:

> type C:\testdump | pg_restore -Fc [...]

> pg_restore: [custom archiver] *could not find block ID 1770* in
> archive -- possibly corrupt archive
> pg_restore: *** aborted because of error

Does the type command treat the stream as characters?  (You could
try putting a ^Z end of file character into a small text file and
see how much of it you see if you run type against it.)

-Kevin

--
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.

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
__________________________________________________

Thank You Kevin & Martin.

I have tried the two ways:
1.pg_restore -Fc -C -U postgres -w -d postgres C:\testdump
2.pg_restore -Fc -C -U postgres -w -d postgres < C:\testdump

Both work very well.

Let me put my program here to understand where i started & where I landed.

Backup:
my pg_dump result is redirected to a pipe which read and pushed to a socket.
on the other(m/c with enough space for backups) end i receive this buffer stream and store as a backup.

Restore:
on the storage end, my program will read and send the dump file over a socket.
on the postgres running machine, my program reads from this socket & writes to the process pg_restore.

how is done is like this:
CreateProcess(pg_restore with arguments ) whose STDIN/STDOUT/STDERR supplied with pipes.

now as when i receive the buffer i will write to STDIN handle of pg_restore process.
This program is failing to restore the dump with the same error :
pg_restore: [custom archiver] *could not find block ID 1770* in
 archive -- possibly corrupt archive

This case, I could see it reproducible using the "type" command line utility as in my first email.

my doubts:
1) does the custom archive format of pg_dump needs to be in a file (not pipe) for the pg_restore to seek back  &forth ?
  If yes, how is it working in Linux.
2) will pg_restore try to do parallel restore of custom archive format dump by default?
 on the pg_restore man page http://developer.postgresql.org/pgdocs/postgres/app-pgrestore.html

--jobs=number-of-jobs

Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine.

Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server.

The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreased performance because of thrashing.

Only the custom archive format is supported with this option. The input file must be a regular file (not, for example, a pipe). This option is ignored when emitting a script rather than connecting directly to a database server. Also, multiple jobs cannot be used together with the option --single-transaction.

   how is this behavior working in Linux?

any suggestions would be of great help to make the same working on Windows too.







--

___________________________________________________

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.

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
__________________________________________________