Обсуждение: Upgrading 8.2 to 8.4: pg_restore: did not find magic string in file header\n

  Hi list,

i cannot restore my data from 8.2 to 8.4 because i always get this error
message, does anyone know what to do??

hasta luego        Andreas


--
Dipl. Geoökologe Andreas Laggner

Institut für Agrarrelevante Klimaforschung (AK) des vTI
Arbeitsgruppe Emissionsinventare
Johann Heinrich von Thünen-Institut (vTI),
Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei

Institute of Agricultural Climate Research (AK) of the vTI
Johann Heinrich von Thünen-Institute (vTI),
Federal Research Institute for Rural Areas, Forestry and Fisheries

Bundesallee 50
D-38116 Braunschweig

Tel.: (+49) (0)531 596 2636
Fax : (+49) (0)531 596 2645
E-mail: andreas.laggner@vti.bund.de
Homepage: http://www.vti.bund.de

On Friday 19 November 2010 8:02:55 am Andreas Laggner wrote:
>   Hi list,
>
> i cannot restore my data from 8.2 to 8.4 because i always get this error
> message, does anyone know what to do??
>
> hasta luego        Andreas
>
>
>

Two questions:
Are you using the 8.2 or 8.4 version of pg_dump/pg_restore?
Are you using pl/sh by any chance?

--
Adrian Klaver
adrian.klaver@gmail.com

Andreas Laggner <andreas.laggner@vti.bund.de> writes:
> i cannot restore my data from 8.2 to 8.4 because i always get this error
> message, does anyone know what to do??

It sounds like you're trying to use pg_restore on a plain-text (SQL
script) dump file.  Run it through psql, instead.

            regards, tom lane

On Friday 19 November 2010 8:29:38 am Tom Lane wrote:
> Andreas Laggner <andreas.laggner@vti.bund.de> writes:
> > i cannot restore my data from 8.2 to 8.4 because i always get this error
> > message, does anyone know what to do??
>
> It sounds like you're trying to use pg_restore on a plain-text (SQL
> script) dump file.  Run it through psql, instead.
>
>             regards, tom lane

Out of curiosity what would trigger this? When I have tried to run a plain text
file through pg_restore I get:

aklaver@tucker:~$ pg_restore -d test -U postgres test.sql
pg_restore: [archiver] input file does not appear to be a valid archive



--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> On Friday 19 November 2010 8:29:38 am Tom Lane wrote:
>> It sounds like you're trying to use pg_restore on a plain-text (SQL
>> script) dump file.  Run it through psql, instead.

> Out of curiosity what would trigger this? When I have tried to run a
> plain text file through pg_restore I get:

> aklaver@tucker:~$ pg_restore -d test -U postgres test.sql
> pg_restore: [archiver] input file does not appear to be a valid archive

Yeah, that's what you get if you let pg_restore try to determine the
file type.  If you tell it you know that the file is an archive (-Fc)
then it believes you, and you get the lower-level failure.

I'm not real sure why we honor -Fc and -Ft in pg_restore anyway;
skipping the file type check couldn't possibly save enough to be
worth the trouble ...

            regards, tom lane

On 11/19/2010 09:54 AM, Tom Lane wrote:
> Adrian Klaver<adrian.klaver@gmail.com>  writes:
>> On Friday 19 November 2010 8:29:38 am Tom Lane wrote:
>>> It sounds like you're trying to use pg_restore on a plain-text (SQL
>>> script) dump file.  Run it through psql, instead.
>
>> Out of curiosity what would trigger this? When I have tried to run a
>> plain text file through pg_restore I get:
>
>> aklaver@tucker:~$ pg_restore -d test -U postgres test.sql
>> pg_restore: [archiver] input file does not appear to be a valid archive
>
> Yeah, that's what you get if you let pg_restore try to determine the
> file type.  If you tell it you know that the file is an archive (-Fc)
> then it believes you, and you get the lower-level failure.
>
> I'm not real sure why we honor -Fc and -Ft in pg_restore anyway;
> skipping the file type check couldn't possibly save enough to be
> worth the trouble ...
>
>             regards, tom lane

Now I understand. I wonder if this would be a good time to ask about
whether pg_restore could be made to work with plain-text files:)

Maybe use the below for the magic string?

--
-- PostgreSQL database dump
--

I realize some of the options to pg_restore would be no-ops in this
case. Though I could see a shortcut that passes a plain text file to
psql or at least creates an error message that says:

"Please use psql with the -f option to restore this file"

It just seems that quite a few people are caught by the asymmetrical
nature of pg_dump/pg_restore i.e. pg_dump can create a file that
pg_restore cannot restore.

Thanks,
--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> Now I understand. I wonder if this would be a good time to ask about
> whether pg_restore could be made to work with plain-text files:)

Not in any particularly useful way --- the plain text dump wouldn't
really support selective restore, etc etc.

> ... Though I could see a shortcut that passes a plain text file to
> psql or at least creates an error message that says:

> "Please use psql with the -f option to restore this file"

Yeah, I was thinking a hint might be the most useful fix too.

            regards, tom lane

On Fri, 2010-11-19 at 17:45 -0500, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
> > Now I understand. I wonder if this would be a good time to ask about
> > whether pg_restore could be made to work with plain-text files:)
>
> Not in any particularly useful way --- the plain text dump wouldn't
> really support selective restore, etc etc.

It would however lend to consistency in our user interface. It is really
quite silly that pg_restore can't restore the default backup method.

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

"Joshua D. Drake" <jd@commandprompt.com> writes:
> On Fri, 2010-11-19 at 17:45 -0500, Tom Lane wrote:
>> Not in any particularly useful way --- the plain text dump wouldn't
>> really support selective restore, etc etc.

> It would however lend to consistency in our user interface. It is really
> quite silly that pg_restore can't restore the default backup method.

I don't think it would be "consistent" for pg_restore to run, but have
most of its useful switches either be ignored or cause failures.

            regards, tom lane

On Friday 19 November 2010 2:45:33 pm Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
> > Now I understand. I wonder if this would be a good time to ask about
> > whether pg_restore could be made to work with plain-text files:)
>
> Not in any particularly useful way --- the plain text dump wouldn't
> really support selective restore, etc etc.

True, but I was thinking of something like:

  if $FILE_TYPE = plain_text
      echo 'This is a plain text file it will be passed to psql'
      echo 'It does not support all the features of a binary restore'
      dialog 'Do you wish to continue yes/no>'
      if yes
          psql $OPTIONS -f $FILE_NAME
      else
         exit
      # Where $OPTIONS are the -d -p -U and -h switches
  else
      continue with existing code path

>
> > ... Though I could see a shortcut that passes a plain text file to
> > psql or at least creates an error message that says:
> >
> > "Please use psql with the -f option to restore this file"
>
> Yeah, I was thinking a hint might be the most useful fix too.

That would help also.

>
>             regards, tom lane


Thanks,
--
Adrian Klaver
adrian.klaver@gmail.com