pg_ [dump & restore] invalid archive problem

Hi,

I had a similar problem a while back,

I think the way you've dumped them is fine, to restore them however you need
to use the psql command

psql -U postgres YourDatabase < mydb.tar

make sure YourDatabase is created and empty before you run this command, and
change postgres to whatever user you have.

Hope this helps

Roy

-----Original Message-----
From: John-Paul Delaney
Sent: 04 November 2004 10:29
To: [email protected]
Subject: [NOVICE] pg_ [dump & restore] invalid archive problem

Hello List... I'd like to move a db containing large objects from one db to
another.
Using the command syntax directly from man pg_dump:

pg_dump -Ft -b mydb > mydb.tar

pg_restore -d mydb mydb.tar

I invariably (tried various attempts) get the following error:
"pg_restore: [archiver] input file does not appear to be a valid archive"

Any help much appreciated.
/j-p.

Thanks Roy...

Unfortunately that parsed the file and output lot's of invalid command
strings with nothing written to the db in the end.
What's the pg_restore command used for then, if not to restore from a
pg_dump, I wonder?

regards
/j-p.

Hello Roy et al...

Just following on the pg_restore thing, I tried it with the --format=t
option, and this time the error is:

pg_restore: [archiver] unsupported version (1.7) in file header. I had
also attemped the dump with the -i option to ignore version (?), but the
result is still the same.

Googling around doesn't give me much to work on.

Using the redirection as you describe, it seems to interpret the binary
contents of the tables as text commands and hence scrolls off for some time
with "invalid command", leaving a file with a name of >100 gibberish
characters on the filesystem. I think this may work for dumps containing
text-only tables?

Regards,
/j-p.






"Crombleholme, Roy"
<[email protected] To: "'John-Paul Delaney'" <[email protected]>
scc.gov.uk> cc:
Subject: RE: [NOVICE] pg_ [dump & restore] invalid archive problem
04/11/2004 14:05






HI j-p,

From what I can remember, (anybody feel free to correct me on this if I'm
wrong), pg_restore expects a different format to that output from pg_dump,
Not very logical I know but I was doing a similar thing a while back but it
was a few versions back now. I was confused by the fact pg_restore did not
restore a pg_dump. I am pretty sure that the psql < databasedump is the
way
to do it.

Did you make sure that the database you were trying to recreate was a brand
new one and owned by the same user as the original?

Maybe you could post a few error lines from the psql < databasedump command
that you ran and I'll see if I recognize anything there.

Roy

-----Original Message-----
From: John-Paul Delaney
Sent: 04 November 2004 12:30
To: [email protected]
Subject: Re: [NOVICE] pg_ [dump & restore] invalid archive problem

Thanks Roy...

Unfortunately that parsed the file and output lot's of invalid command
strings with nothing written to the db in the end.
What's the pg_restore command used for then, if not to restore from a
pg_dump, I wonder?

regards
/j-p.

Which PG version are you using? There was a bug for awhile that would
cause pg_restore to fail to identify tar-format archives automatically,
so you had to specify -Ft at restore time too:
pg_restore -Ft -d mydb mydb.tar

Other than that, this looks right to me. The nearby suggestion to use
psql is definitely wrong --- you use psql for plain-text dumps, but
pg_restore for -Ft and -Fc dumps.

(BTW, in general I'd recommend -Fc over -Ft, unless you have some
compelling reason to want to look at the dump with tools other than
pg_restore. The custom-format code is better tested, for one thing, as
evidenced by the fact that this bug went undetected for a good while.)

regards, tom lane

Thanks Tom... I'm hoping to move the db from a version 7.3.3 to 7.2.4
though even trying with a format custom 'c' I get the usual error:

pg_restore: [archiver] unsupported version (1.7) in file header

The commands I'm using are:

[On version 7.3.3] pg_dump -Fc -b -i -v mydb > mydb.pg_dump (completes
successfully)

[On version 7.2.4] pg_restore -d mydb -Fc -i -v mydb.pg_dump (immediate
return with error)

/j-p.






Tom Lane
<[email protected] To: "John-Paul Delaney" <[email protected]>
s> cc: [email protected]
Subject: Re: [NOVICE] pg_ [dump & restore] invalid archive
04/11/2004 16:17 problem






"John-Paul Delaney" <[email protected]> writes:
Which PG version are you using? There was a bug for awhile that would
cause pg_restore to fail to identify tar-format archives automatically,
so you had to specify -Ft at restore time too:
pg_restore -Ft -d mydb mydb.tar

Other than that, this looks right to me. The nearby suggestion to use
psql is definitely wrong --- you use psql for plain-text dumps, but
pg_restore for -Ft and -Fc dumps.

(BTW, in general I'd recommend -Fc over -Ft, unless you have some
compelling reason to want to look at the dump with tools other than
pg_restore. The custom-format code is better tested, for one thing, as
evidenced by the fact that this bug went undetected for a good while.)

regards, tom lane

Why?

Archive header incompatibility is going to be the *least* of your
worries. pg_dump output is not designed to be loaded into old versions
without problems, and in this case you are talking about going backwards
over the addition of schemas. You'll have all kinds of syntax errors.

I can't imagine why you'd be wanting to go back to 7.2.anything anyway.

regards, tom lane

Oh.. It's just that they are the versions running on the particular
machines I have to swap between. Then what I'm looking at is really an
upgrade of the 7.2.4 version, before I can consider installing the db? I
hadn't realized there was such a difference that it would force an upgrade.
Thanks for the clarification.

/j-p.




Tom Lane
<[email protected]> To: "John-Paul Delaney" <[email protected]>
Sent by: cc: [email protected]
[email protected] Subject: Re: [NOVICE] pg_ [dump & restore] invalid archive
tgresql.org problem


04/11/2004 22:06






"John-Paul Delaney" <[email protected]> writes:
Why?

Archive header incompatibility is going to be the *least* of your
worries. pg_dump output is not designed to be loaded into old versions
without problems, and in this case you are talking about going backwards
over the addition of schemas. You'll have all kinds of syntax errors.

I can't imagine why you'd be wanting to go back to 7.2.anything anyway.

regards, tom lane

Tom... just to confirm your solution to the list, I did a quick test
postgres rpm install (7.3.4) on a separate machine and the pg_restore
worked a charm.

Thanks to Tom Lane & Roy Crombleholme for kind assistance.

/j-p.

----- Forwarded by John-Paul Delaney/ICT/WFP on 05/11/2004 09:53 -----

John-Paul Delaney
To: Tom Lane <[email protected]>
05/11/2004 06:36 cc: [email protected]
Subject: Re: [NOVICE] pg_ [dump & restore] invalid
archive problem(Document link: Delaney)




Oh.. It's just that they are the versions running on the particular
machines I have to swap between. Then what I'm looking at is really an
upgrade of the 7.2.4 version, before I can consider installing the db? I
hadn't realized there was such a difference that it would force an upgrade.
Thanks for the clarification.

/j-p.




Tom Lane
<[email protected]> To: "John-Paul Delaney" <[email protected]>
Sent by: cc: [email protected]
[email protected] Subject: Re: [NOVICE] pg_ [dump & restore] invalid
tgresql.org archive problem


04/11/2004 22:06






"John-Paul Delaney" <[email protected]> writes:
Why?

Archive header incompatibility is going to be the *least* of your
worries. pg_dump output is not designed to be loaded into old versions
without problems, and in this case you are talking about going backwards
over the addition of schemas. You'll have all kinds of syntax errors.

I can't imagine why you'd be wanting to go back to 7.2.anything anyway.

regards, tom lane