pg_restore on windows with pipe

From: Ravi Thati <gotothati(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: pg_restore on windows with pipe
Date: 2011-03-29 08:43:10
Message-ID: AANLkTi=qKXYe51jOkDGuvhdg_AJUK=CyKBHfi-4E-_ib@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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
Continuous Efforts with Infinite Faith is evident in all success stories.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Nic Chidu 2011-03-29 15:56:05 full vacuum of a very large table
Previous Message Kevin Grittner 2011-03-28 21:48:50 Re: Wal archives building up in slave pg_xlog dir