Thread: pg_restore
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 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? I used the same command to successfully install the same pg_dump file into PostgreSQL 8.1 running on the same computer. Any thoughts would be much appreciated. Bob Pawley
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 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
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 > 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 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: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 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
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 > > (BTW, I just fixed pg_restore to always mention the file name it > attempted to open after getting an fopen failure.) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: 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 >
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 > 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
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