pg_restore

On Sunday 28 October 2007 11:32 am, Bob Pawley wrote:

Please help.

I am attempting to restore a database into PostgreSQL version 8.2 running
on Win XP Professional.

From the 'bin' folder, I am using the command line-
pg_restore psql -h localhost -d PDW -U postgres -f aurel.sql

Try pg_restore psql -h localhost -d PDW -U postgres aurel.sql
No -f switch

I get an error -

pg_restore: cannot specify both -d and -f output.

If the error message is correct how does pg_restore know what to put where?

The -d switch tells pg_restore to the named database. The -f switch tells it
to restore to named file. It won't do both.

I used the same command to successfully install the same pg_dump file into
PostgreSQL 8.1 running on the same computer.

Maybe 8.1 ignored the error.

Any thoughts would be much appreciated.

--
Adrian Klaver
aklaver@comcast.net

Hi Adrian

With pg_restore psql -h localhost -d PDW -U postgres aurel.sql the error
message is -

pg_restore: could not open input file: No such file or directory exists.

I get this message with aurel.sql - or aurel - or the path to aurel
(......8,2\bin) or when aurel is not even mentioned.

This is becoming quite frustrating.

Bob
----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.ca>
Sent: Sunday, October 28, 2007 11:45 AM
Subject: Re: [GENERAL] pg_restore

On Sunday 28 October 2007 2:13 pm, Bob Pawley wrote:

Hi Adrian

With pg_restore psql -h localhost -d PDW -U postgres aurel.sql the error
message is -

pg_restore: could not open input file: No such file or directory exists.

I get this message with aurel.sql - or aurel - or the path to aurel
(......8,2\bin) or when aurel is not even mentioned.

Is this really the path -(......8,2\bin)? Note the ','.

This is becoming quite frustrating.

The other thing to check is whether you have the necessary permissions to read
the file.

--
Adrian Klaver
aklaver@comcast.net

Bob Pawley <rjpawley@shaw.ca> writes:

This is becoming quite frustrating.

The errant "psql" is your problem ... although pg_restore is being
quite unhelpful by not mentioning the filename that it's trying to open.

regards, tom lane

On Sunday 28 October 2007 2:28 pm, Tom Lane wrote:

Bob Pawley <rjpawley@shaw.ca> writes:

This is becoming quite frustrating.

The errant "psql" is your problem ... although pg_restore is being
quite unhelpful by not mentioning the filename that it's trying to open.

regards, tom lane

Well there is your problem. Might help if I was using both eyes. Thanks for
the heads up Tom.
--
Adrian Klaver
aklaver@comcast.net

The latest in the saga -

By using - pg_restore -h localhost -d PDW -U postgres aurel.sql

I get the message - pg_restore: input file does not appear to be a valid
archive.

I get this message when I used the aurel.sql file which I previously loaded
successfully in 8.1 and also when I use an aurel.sql file which I just
successfully dumped a few minutes ago from the 8.1 on my other computer.

Could pg_restore in my 8.2 be corrupted??

Bob
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Adrian Klaver" <aklaver@comcast.net>; <pgsql-general@postgresql.org>
Sent: Sunday, October 28, 2007 2:28 PM
Subject: Re: [GENERAL] pg_restore

On Sunday 28 October 2007 3:01 pm, Bob Pawley wrote:

The latest in the saga -

By using - pg_restore -h localhost -d PDW -U postgres aurel.sql

I get the message - pg_restore: input file does not appear to be a valid
archive.

I get this message when I used the aurel.sql file which I previously loaded
successfully in 8.1 and also when I use an aurel.sql file which I just
successfully dumped a few minutes ago from the 8.1 on my other computer.

Could pg_restore in my 8.2 be corrupted??

Bob

What does your dump command look like? My guess is your are doing a plain text
dump and pg_restore only works with the custom formats. If you want to use
the plain text version than you need to use psql. This maybe how you got to
the point of having both pg_restore and psql on the same line.
--
Adrian Klaver
aklaver@comcast.net

Bob Pawley <rjpawley@shaw.ca> writes:

The latest in the saga -
By using - pg_restore -h localhost -d PDW -U postgres aurel.sql

I get the message - pg_restore: input file does not appear to be a valid
archive.

Oh, I just twigged that you are using a plain-SQL dump file (that is,
you didn't specify -Fc or -Ft to pg_dump). For plain-SQL dumps you
should not use pg_restore at all; you feed those to psql.

This bites enough newbies that I'm thinking the above message ought to
inclue a HINT to use psql directly. We haven't previously used hints
in client-side messages but this seems to need one. Anyone have
thoughts about how to phrase and format it?

(BTW, I just fixed pg_restore to always mention the file name it
attempted to open after getting an fopen failure.)

regards, tom lane

This is the dump command

pg_dump -h localhost -d Aurel -U postgres

Could you suggest a dump command that will match the restore command -

pg_restore -h localhost -d PDW -U postgres aurel.sql

Thanks

Bob

----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Sunday, October 28, 2007 3:15 PM
Subject: Re: [GENERAL] pg_restore

On Sunday 28 October 2007 3:40 pm, Bob Pawley wrote:

This is the dump command

pg_dump -h localhost -d Aurel -U postgres

Could you suggest a dump command that will match the restore command -

pg_restore -h localhost -d PDW -U postgres aurel.sql

Thanks

Bob

It depends on what you want to do. But to use pg_restore you will need to use
one of either -Fc or Ft after the pg_dump command. My concern is that you are
connecting to a different database name in the dump and restore commands.
This may be what you want, but then again it may not.I would suggest reading
the information at the URL below before proceeding further.
http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html

--
Adrian Klaver
aklaver@comcast.net

On 28/10/2007 22:40, Bob Pawley wrote:

pg_dump -h localhost -d Aurel -U postgres

Could you suggest a dump command that will match the restore command -

pg_restore -h localhost -d PDW -U postgres aurel.sql

One thing that caught me, and I suspect may be catching you also -
somewhat confusingly, in pg_restore the -d option specifies the database
to which to restore; in pg_dump it instead causes the data to be dumped
as INSERT statements rather than COPY.

I can see why things may work this way (pg_dump always needs to be
pointed at a database, whereas pg_restore doesn't if the dump is going
to create the database)....but it can trap the unwary.

See the docs at
http://www.postgresql.org/docs/8.2/static/reference-client.html

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Tom Lane wrote on 28.10.2007 23:18:

Oh, I just twigged that you are using a plain-SQL dump file (that is,
you didn't specify -Fc or -Ft to pg_dump). For plain-SQL dumps you
should not use pg_restore at all; you feed those to psql.

While we are on the topic of pg_dump/pg_restore:

Why is it, that pg_dump can use a compressed output directly but pg_dumpall is
always using a SQL (i.e. "plain text") output?

Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:

Why is it, that pg_dump can use a compressed output directly but pg_dumpall is
always using a SQL (i.e. "plain text") output?

The custom and tar formats are not designed to support data from more
than one database. At some point somebody should probably try to
improve that situation, but it's not immediately obvious what the
feature ought to look like.

If all you need is compression it's certainly easy enough:

pg_dumpall | gzip >mydump.gz

zcat mydump.gz | psql

So the argument for doing more hinges around the extra flexibility of
pg_restore to do selective restores and suchlike, and extending those
features to behave sanely for multi-database dumps is not trivial.

regards, tom lane

Tom Lane wrote:

Bob Pawley <rjpawley@shaw.ca> writes:

The latest in the saga -
By using - pg_restore -h localhost -d PDW -U postgres aurel.sql

I get the message - pg_restore: input file does not appear to be a valid
archive.

Oh, I just twigged that you are using a plain-SQL dump file (that is,
you didn't specify -Fc or -Ft to pg_dump). For plain-SQL dumps you
should not use pg_restore at all; you feed those to psql.

This bites enough newbies that I'm thinking the above message ought to
inclue a HINT to use psql directly. We haven't previously used hints
in client-side messages but this seems to need one. Anyone have
thoughts about how to phrase and format it?

Honestly, I would prefer we just fix pg_restore to be able to use plain
text format. The fact that it doesn't is a serious lack of consistency
within our client side apps. E.g; it is completely counter-intuitive to
require our "client user interface" to be used for restoration when
there is a "pg_restore" sitting in the bin directory.

Sincerely,

Joshua D. Drake

Following the examples in the docs I've come to this.

I am attempting to restore the existing sql dump using
psql -d PDW -f aurel.sql

I am then asked for a password.

I try every password that the computer knows with no success.

Funny thing the password cursor doesn't move when inputting the password.

I keep getting authentication failure.

When I attempt to do a new pg_dump with -Fc I also get a request for
password with identical results.

Bob

----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Sunday, October 28, 2007 3:58 PM
Subject: Re: [GENERAL] pg_restore

Bob Pawley <rjpawley@shaw.ca> writes:

Following the examples in the docs I've come to this.

I am attempting to restore the existing sql dump using
psql -d PDW -f aurel.sql

I am then asked for a password.

I try every password that the computer knows with no success.

Funny thing the password cursor doesn't move when inputting the password.

This is standard for Unix command-line applications.

I keep getting authentication failure.

When I attempt to do a new pg_dump with -Fc I also get a request for
password with identical results.

Sounds like you need to fix pg_hba.conf then.

-Doug

Tom Lane wrote on 29.10.2007 00:55:

Thomas Kellerer <spam_eater@gmx.net> writes:

Why is it, that pg_dump can use a compressed output directly but pg_dumpall is
always using a SQL (i.e. "plain text") output?

The custom and tar formats are not designed to support data from more
than one database. At some point somebody should probably try to
improve that situation, but it's not immediately obvious what the
feature ought to look like.

OK, thanks

If all you need is compression it's certainly easy enough:

pg_dumpall | gzip >mydump.gz

That's what I'm currently doing ;)

Thanks