Upgrading 8.2 to 8.4: pg_restore: did not find magic string in file header\n

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