Thread: 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.
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 [mailto:John-Paul.Delaney@wfp.org]
Sent: 04 November 2004 10:29
To: pgsql-novice@postgresql.org
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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
********************
This e-mail contains information intended for the addressee only.
It may be confidential and may be the subject of legal and/or professional privilege.
If you are not the addressee you are not authorised to disseminate, distribute, copy or use this e-mail or any
attachmentto it
The content may be personal or contain personal opinions and unless specifically stated or followed up in writing, the
contentcannot be taken to form a contract or to be an expression of the County Council's position.
LCC reserves the right to monitor all incoming and outgoing email
LCC has taken reasonable steps to ensure that outgoing communications do not contain malicious software and it is your
responsibilityto carry out any checks on this email before accepting the email and opening attachments.
********************
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"
<Roy.Crombleholme@its.lanc To: "'John-Paul Delaney'" <John-Paul.Delaney@wfp.org>
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 [mailto:John-Paul.Delaney@wfp.org]
Sent: 04 November 2004 12:30
To: pgsql-novice@postgresql.org
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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
********************
This e-mail contains information intended for the addressee only.
It may be confidential and may be the subject of legal and/or professional
privilege.
If you are not the addressee you are not authorised to disseminate,
distribute, copy or use this e-mail or any attachment to it
The content may be personal or contain personal opinions and unless
specifically stated or followed up in writing, the content cannot be taken
to form a contract or to be an expression of the County Council's position.
LCC reserves the right to monitor all incoming and outgoing email
LCC has taken reasonable steps to ensure that outgoing communications do
not contain malicious software and it is your responsibility to carry out
any checks on this email before accepting the email and opening
attachments.
********************
"John-Paul Delaney" <John-Paul.Delaney@wfp.org> writes:
> 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"
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
<tgl@sss.pgh.pa.u To: "John-Paul Delaney" <John-Paul.Delaney@wfp.org>
s> cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] pg_ [dump & restore] invalid archive
04/11/2004 16:17 problem
"John-Paul Delaney" <John-Paul.Delaney@wfp.org> writes:
> 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"
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
"John-Paul Delaney" <John-Paul.Delaney@wfp.org> writes:
> Thanks Tom... I'm hoping to move the db from a version 7.3.3 to 7.2.4
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
<tgl@sss.pgh.pa.us> To: "John-Paul Delaney" <John-Paul.Delaney@wfp.org>
Sent by: cc: pgsql-novice@postgresql.org
pgsql-novice-owner@pos Subject: Re: [NOVICE] pg_ [dump & restore] invalid archive
tgresql.org problem
04/11/2004 22:06
"John-Paul Delaney" <John-Paul.Delaney@wfp.org> writes:
> Thanks Tom... I'm hoping to move the db from a version 7.3.3 to 7.2.4
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
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 <tgl@sss.pgh.pa.us>
05/11/2004 06:36 cc: pgsql-novice@postgresql.org
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
<tgl@sss.pgh.pa.us> To: "John-Paul Delaney" <John-Paul.Delaney@wfp.org>
Sent by: cc: pgsql-novice@postgresql.org
pgsql-novice-owner@pos Subject: Re: [NOVICE] pg_ [dump & restore] invalid
tgresql.org archive problem
04/11/2004 22:06
"John-Paul Delaney" <John-Paul.Delaney@wfp.org> writes:
> Thanks Tom... I'm hoping to move the db from a version 7.3.3 to 7.2.4
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings