FW: pg_ [dump & restore] invalid archive problem

Hi j-p,

Ok, I just did this (- without outputting to a tar file - thanks for
pointing that out Tom Lane :-) ) dumping from a database on a solaris box
and restoring to a database on a linux box.
Lines beginning "# " are normal shell commands and lines beginning "psql>"
are in postgresql command line.

On my Solaris box:

# pg_dump -U postgres -f /mydumpdir/mydb.dmp mydb

I then copied mydb.dmp to my linux box.

On my linux box I did:

# psql -U postgres template1

psql> CREATE DATABASE mydb;
psql>\q

# psql -U postgres mydb < /mydumpdir/mydb.dmp

This worked without any problems at all.  Just make sure that any users that
owned any tables on your original database exist in your new database.

If you follow this and it still doesn't work then I'm afraid you're own your
own :(

Roy

-----Original Message-----
From: John-Paul Delaney [mailto:John-Paul.Delaney@wfp.org]
Sent: 04 November 2004 13:44
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] pg_ [dump & restore] invalid archive problem

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.
********************






---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster