Thread: pg_ [dump & restore] invalid archive problem

pg_ [dump & restore] invalid archive problem

From
"John-Paul Delaney"
Date:
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.



Re: pg_ [dump & restore] invalid archive problem

From
"Crombleholme, Roy"
Date:
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. 
********************


Re: pg_ [dump & restore] invalid archive problem

From
"John-Paul Delaney"
Date:
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.





Re: pg_ [dump & restore] invalid archive problem

From
"John-Paul Delaney"
Date:
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.
********************






Re: pg_ [dump & restore] invalid archive problem

From
Tom Lane
Date:
"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

Re: pg_ [dump & restore] invalid archive problem

From
"John-Paul Delaney"
Date:
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





Re: pg_ [dump & restore] invalid archive problem

From
Tom Lane
Date:
"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

Re: pg_ [dump & restore] invalid archive problem

From
"John-Paul Delaney"
Date:
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





Re: pg_ [dump & restore] invalid archive problem

From
"John-Paul Delaney"
Date:
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