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.

Search Discussions

  • Crombleholme, Roy at Nov 4, 2004 at 10:55 am
    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: 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 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 at Nov 4, 2004 at 12:33 pm
    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.
  • John-Paul Delaney at Nov 4, 2004 at 1:47 pm
    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
    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.
    ********************
  • Tom Lane at Nov 4, 2004 at 3:17 pm

    "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 at Nov 4, 2004 at 8:52 pm
    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
  • Tom Lane at Nov 4, 2004 at 9:06 pm

    "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
  • John-Paul Delaney at Nov 5, 2004 at 5:40 am
    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
  • John-Paul Delaney at Nov 5, 2004 at 9:00 am
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedNov 4, '04 at 10:31a
activeNov 5, '04 at 9:00a
posts9
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase