Thread: Restoring from SQL dump
Hello!
I am trying to restore data from an SQL dump. I am using Windows 7, PostgreSQL version 9.3, pgAdmin III 1.18.0.
Here is what I did.
1) I used the pgAdmin III interface to create a new database.
2) I selected this db in the console and selected Plugins > PSQL console.
3) Now I have a psql window open with the following prompt:
dbname=#
4) I tried typing the following:
psql –f M:\LongitudinalDB\backups\2013.10.02\uw.sql
and got the following error:
Invalid command \LongitudinalDB. Try \? For help.
5) OK, so maybe the backslashes are the problem. I then tried
psql –f M:/LongitudinalDB/backups/2013.10.02/uw.sql
and got no error.
6) However, none of the data or tables appeared in the db – it looks just as it did as a fresh db.
7) I tried a few other things in the psql window, deliberately typing nonsense. No error messages then, either, just a fresh prompt.
Advice would be much appreciated.
Thanks,
Erin
Hello!
I am trying to restore data from an SQL dump. I am using Windows 7, PostgreSQL version 9.3, pgAdmin III 1.18.0.
Here is what I did.
1) I used the pgAdmin III interface to create a new database.
2) I selected this db in the console and selected Plugins > PSQL console.
3) Now I have a psql window open with the following prompt:
dbname=#4) I tried typing the following:
psql –f M:\LongitudinalDB\backups\2013.10.02\uw.sql
and got the following error:
Invalid command \LongitudinalDB. Try \? For help.5) OK, so maybe the backslashes are the problem. I then tried
psql –f M:/LongitudinalDB/backups/2013.10.02/uw.sql
and got no error.6) However, none of the data or tables appeared in the db – it looks just as it did as a fresh db.
7) I tried a few other things in the psql window, deliberately typing nonsense. No error messages then, either, just a fresh prompt.
Advice would be much appreciated.
Thanks,
Erin
Erin Jonaitis wrote > 3) Now I have a psql window open with the following prompt: > dbname=# > > 4) I tried typing the following: > psql -f M:\LongitudinalDB\backups\2013.10.02\uw.sql > and got the following error: > Invalid command \LongitudinalDB. Try \? For help. #3 - you are now in psql #4 - you are acting as if you are sitting at your OS shell prompt here Either learn the psql to run an external file or get back to your OS shell prompt and run the psql command from there. Not sure why you did't get more errors. Yes, backslashes inside psql denote the intent to use a special command which "Long..." is not. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Restoring-from-SQL-dump-tp5775028p5775031.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
pg_restore M:\LongitudinalDB\backups\2013.10.02\uw.sql
On 10/18/2013 6:57 AM, Erin Jonaitis wrote:
Hello!
I am trying to restore data from an SQL dump. I am using Windows 7, PostgreSQL version 9.3, pgAdmin III 1.18.0.
Here is what I did.
1) I used the pgAdmin III interface to create a new database.
2) I selected this db in the console and selected Plugins > PSQL console.
3) Now I have a psql window open with the following prompt:
dbname=#4) I tried typing the following:
psql –f M:\LongitudinalDB\backups\2013.10.02\uw.sql
and got the following error:
Invalid command \LongitudinalDB. Try \? For help.5) OK, so maybe the backslashes are the problem. I then tried
psql –f M:/LongitudinalDB/backups/2013.10.02/uw.sql
and got no error.6) However, none of the data or tables appeared in the db – it looks just as it did as a fresh db.
7) I tried a few other things in the psql window, deliberately typing nonsense. No error messages then, either, just a fresh prompt.
Advice would be much appreciated.
Thanks,
Erin
Thanks, all. I did also try this from the Windows command prompt -- it gave me the standard error it gives when it has no idea what you're talking about: "'psql' is not recognized as an internal or external command, operable program or batch file." Any Windows PostgreSQL users present? Erin -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of David Johnston Sent: Friday, October 18, 2013 9:19 AM To: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Restoring from SQL dump Erin Jonaitis wrote > 3) Now I have a psql window open with the following prompt: > dbname=# > > 4) I tried typing the following: > psql -f M:\LongitudinalDB\backups\2013.10.02\uw.sql > and got the following error: > Invalid command \LongitudinalDB. Try \? For help. #3 - you are now in psql #4 - you are acting as if you are sitting at your OS shell prompt here Either learn the psql to run an external file or get back to your OS shell prompt and run the psql command from there. Not sure why you did't get more errors. Yes, backslashes inside psql denote the intent to use a special command which "Long..." is not. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Restoring-from-SQL-dump-tp5775028p57 75031.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com. -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Hi Ken,
Thanks for the idea. Tried that too, FWIW (using / instead of \), and it also failed silently, no errors. Pretty unfriendly behavior. I’m used to command lines yelling at me when I do something dumb, but this one just doesn’t.
Erin
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ken Benson
Sent: Friday, October 18, 2013 9:20 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Restoring from SQL dump
Try:
pg_restore M:\LongitudinalDB\backups\2013.10.02\uw.sql
On 10/18/2013 6:57 AM, Erin Jonaitis wrote:
Hello!
I am trying to restore data from an SQL dump. I am using Windows 7, PostgreSQL version 9.3, pgAdmin III 1.18.0.
Here is what I did.
1) I used the pgAdmin III interface to create a new database.
2) I selected this db in the console and selected Plugins > PSQL console.
3) Now I have a psql window open with the following prompt:
dbname=#4) I tried typing the following:
psql –f M:\LongitudinalDB\backups\2013.10.02\uw.sql
and got the following error:
Invalid command \LongitudinalDB. Try \? For help.5) OK, so maybe the backslashes are the problem. I then tried
psql –f M:/LongitudinalDB/backups/2013.10.02/uw.sql
and got no error.6) However, none of the data or tables appeared in the db – it looks just as it did as a fresh db.
7) I tried a few other things in the psql window, deliberately typing nonsense. No error messages then, either, just a fresh prompt.
Advice would be much appreciated.
Thanks,
Erin
Thanks, all. I did also try this from the Windows command prompt -- it gave
me the standard error it gives when it has no idea what you're talking
about: "'psql' is not recognized as an internal or external command,
operable program or batch file." Any Windows PostgreSQL users present?
Erin
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of David Johnston
Sent: Friday, October 18, 2013 9:19 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Restoring from SQL dump
Erin Jonaitis wrote
> 3) Now I have a psql window open with the following prompt:
> dbname=#
>
> 4) I tried typing the following:
> psql -f M:\LongitudinalDB\backups\2013.10.02\uw.sql
> and got the following error:
> Invalid command \LongitudinalDB. Try \? For help.
#3 - you are now in psql
#4 - you are acting as if you are sitting at your OS shell prompt here
Either learn the psql to run an external file or get back to your OS shell
prompt and run the psql command from there.
Not sure why you did't get more errors.
Yes, backslashes inside psql denote the intent to use a special command
which "Long..." is not.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Restoring-from-SQL-dump-tp5775028p57
75031.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
classmarkets GmbH | Schumannstraße 6 | 10117 Berlin | Deutschland
Tel: +49 (0)30 56 59 001-0 | Fax: +49 (0)30 56 59 001-99 | www.classmarkets.com
Amtsgericht Charlottenburg HRB 111815 B | USt-IdNr. DE 260731582
Geschäftsführer: Veit Mürz, Fabian Ströhle
Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sie darf ausschließlich durch den vorgesehenen Empfänger und Adressaten gelesen, kopiert oder genutzt werden. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Jede unerlaubte Nutzung oder Weitergabe des Inhalts dieser Nachricht, sei es vollständig oder teilweise, ist unzulässig. Bitte beachten Sie, dass E-Mail-Nachrichten an den Absender nicht für fristgebundene Mitteilungen geeignet sind. Fristgebundene Mitteilungen sind daher ausschließlich per Post oder per Telefax zu übersenden.
Thanks, all. I did also try this from the Windows command prompt -- it gave
me the standard error it gives when it has no idea what you're talking
about: "'psql' is not recognized as an internal or external command,
operable program or batch file." Any Windows PostgreSQL users present?
1) find where psql is located in your filesystem2) add this to your %PATH%On Fri, Oct 18, 2013 at 4:22 PM, Erin Jonaitis <jonaitis@wisc.edu> wrote:Thanks, all. I did also try this from the Windows command prompt -- it gave
me the standard error it gives when it has no idea what you're talking
about: "'psql' is not recognized as an internal or external command,
operable program or batch file." Any Windows PostgreSQL users present?
Erin
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of David Johnston
Sent: Friday, October 18, 2013 9:19 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Restoring from SQL dump
Erin Jonaitis wrote
> 3) Now I have a psql window open with the following prompt:
> dbname=#
>
> 4) I tried typing the following:
> psql -f M:\LongitudinalDB\backups\2013.10.02\uw.sql
> and got the following error:
> Invalid command \LongitudinalDB. Try \? For help.
#3 - you are now in psql
#4 - you are acting as if you are sitting at your OS shell prompt here
Either learn the psql to run an external file or get back to your OS shell
prompt and run the psql command from there.
Not sure why you did't get more errors.
Yes, backslashes inside psql denote the intent to use a special command
which "Long..." is not.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Restoring-from-SQL-dump-tp5775028p57
75031.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-noviceclassmarkets GmbH | Schumannstraße 6 | 10117 Berlin | Deutschland
Tel: +49 (0)30 56 59 001-0 | Fax: +49 (0)30 56 59 001-99 | www.classmarkets.comAmtsgericht Charlottenburg HRB 111815 B | USt-IdNr. DE 260731582
Geschäftsführer: Veit Mürz, Fabian StröhleDiese Nachricht (inklusive aller Anhänge) ist vertraulich. Sie darf ausschließlich durch den vorgesehenen Empfänger und Adressaten gelesen, kopiert oder genutzt werden. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Jede unerlaubte Nutzung oder Weitergabe des Inhalts dieser Nachricht, sei es vollständig oder teilweise, ist unzulässig. Bitte beachten Sie, dass E-Mail-Nachrichten an den Absender nicht für fristgebundene Mitteilungen geeignet sind. Fristgebundene Mitteilungen sind daher ausschließlich per Post oder per Telefax zu übersenden.
"C:\Program Files\PostgreSQL\9.1\bin\pg_restore" -h {serverIP} -U {UserID} -C -d {targetDB} M:\LongitudinalDB\backups\2013.10.02\uw.sql
But, if you use "C:\Program Files\PostgreSQL\9.1\bin\pg_restore" --help
... at a command prompt, you'll get some more help.
Of course, you need to make sure your installation is in this location...
Ken Benson
On 10/18/2013 7:22 AM, Erin Jonaitis wrote:
Thanks, all. I did also try this from the Windows command prompt -- it gave me the standard error it gives when it has no idea what you're talking about: "'psql' is not recognized as an internal or external command, operable program or batch file." Any Windows PostgreSQL users present? Erin -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of David Johnston Sent: Friday, October 18, 2013 9:19 AM To: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Restoring from SQL dump Erin Jonaitis wrote3) Now I have a psql window open with the following prompt: dbname=# 4) I tried typing the following: psql -f M:\LongitudinalDB\backups\2013.10.02\uw.sql and got the following error: Invalid command \LongitudinalDB. Try \? For help.#3 - you are now in psql #4 - you are acting as if you are sitting at your OS shell prompt here Either learn the psql to run an external file or get back to your OS shell prompt and run the psql command from there. Not sure why you did't get more errors. Yes, backslashes inside psql denote the intent to use a special command which "Long..." is not. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Restoring-from-SQL-dump-tp5775028p57 75031.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com. -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Thanks, Ken, this was helpful.
I tried to follow Anathasios’ advice about setting the path variable, but on Windows7 this seems a bit opaque; I did add my PostgreSQL \bin directory to the Path variable through Control Panel (and I’ve checked my spelling three times), but I still have to be in that directory to run the program, strangely. So Ken’s example here, of how to give a full pathname for a command that isn’t in one of the %path% directories, is super useful.
What wound up working for me is somewhat similar:
“C:\Program Files\PostgreSQL\9.3\bin\psql” –U[username] [targetDB]”
This got me into the DB into which I want to restore the dump. And then once in psql, I used the command
\i ‘[dbfilespace]/[dumpname]’
To import it. (I say “it worked” optimistically because it is actually doing something, but there’ve been some periods where that “something” is “hanging” so we’re not out of the woods yet.)
Glad there is a list for n00bs. Thanks for the quick responses. I’ll try to pay it forward someday, once I know what I’m doing.
Erin
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ken Benson
Sent: Friday, October 18, 2013 9:28 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Restoring from SQL dump
From the command prompt - I've successfully used:
"C:\Program Files\PostgreSQL\9.1\bin\pg_restore" -h {serverIP} -U {UserID} -C -d {targetDB} M:\LongitudinalDB\backups\2013.10.02\uw.sql
But, if you use "C:\Program Files\PostgreSQL\9.1\bin\pg_restore" --help
... at a command prompt, you'll get some more help.
Of course, you need to make sure your installation is in this location...
Ken Benson
On 10/18/2013 7:22 AM, Erin Jonaitis wrote:
Thanks, all. I did also try this from the Windows command prompt -- it gaveme the standard error it gives when it has no idea what you're talkingabout: "'psql' is not recognized as an internal or external command,operable program or batch file." Any Windows PostgreSQL users present?Erin-----Original Message-----From: pgsql-novice-owner@postgresql.org[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of David JohnstonSent: Friday, October 18, 2013 9:19 AMTo: pgsql-novice@postgresql.orgSubject: Re: [NOVICE] Restoring from SQL dumpErin Jonaitis wrote3) Now I have a psql window open with the following prompt:dbname=#4) I tried typing the following:psql -f M:\LongitudinalDB\backups\2013.10.02\uw.sqland got the following error:Invalid command \LongitudinalDB. Try \? For help.#3 - you are now in psql#4 - you are acting as if you are sitting at your OS shell prompt hereEither learn the psql to run an external file or get back to your OS shellprompt and run the psql command from there.Not sure why you did't get more errors.Yes, backslashes inside psql denote the intent to use a special commandwhich "Long..." is not.David J.--View this message in context:http://postgresql.1045698.n5.nabble.com/Restoring-from-SQL-dump-tp5775028p5775031.htmlSent from the PostgreSQL - novice mailing list archive at Nabble.com.--Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To makechanges to your subscription:http://www.postgresql.org/mailpref/pgsql-novice
On Fri, Oct 18, 2013 at 10:58 AM, Erin Jonaitis <jonaitis@wisc.edu> wrote: > I tried to follow Anathasios’ advice about setting the path variable, but on > Windows7 this seems a bit opaque; I did add my PostgreSQL \bin directory to > the Path variable through Control Panel (and I’ve checked my spelling three > times), but I still have to be in that directory to run the program, > strangely. So Ken’s example here, of how to give a full pathname for a > command that isn’t in one of the %path% directories, is super useful. > Forgive me for asking the obvious question, but did you close and re-open your command prompt? Changes in the Path aren't detected until you do that. To check, do "echo %PATH%" (without the double quotes), and it should print what you have for Path in the current command prompt. > To import it. (I say “it worked” optimistically because it is actually doing > something, but there’ve been some periods where that “something” is > “hanging” so we’re not out of the woods yet.) > Depending upon the speed of your computer and the size and schema of your database, the load process can take a long time. On slower disks with a database that was just a few hundred MB when uncompressed, the load process would sit at certain steps for over an hour for me. If you got that far, you're probably fine and all you have to do is wait. -Mike
HAH! Mike got it in one -- I didn't realize that I had to open a new terminal window for changes in %path% to take hold. And here I assumed it was because I wasn't logged in as an admin. Thanks, Mike! My next restore will be less hairy because of you! Erin -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Michael Swierczek Sent: Friday, October 18, 2013 10:20 AM To: Erin Jonaitis Cc: PostgreSQL Novice Subject: Re: [NOVICE] Restoring from SQL dump On Fri, Oct 18, 2013 at 10:58 AM, Erin Jonaitis <jonaitis@wisc.edu> wrote: > I tried to follow Anathasios' advice about setting the path variable, > but on > Windows7 this seems a bit opaque; I did add my PostgreSQL \bin > directory to the Path variable through Control Panel (and I've checked > my spelling three times), but I still have to be in that directory to > run the program, strangely. So Ken's example here, of how to give a > full pathname for a command that isn't in one of the %path% directories, is super useful. > Forgive me for asking the obvious question, but did you close and re-open your command prompt? Changes in the Path aren't detected until you do that. To check, do "echo %PATH%" (without the double quotes), and it should print what you have for Path in the current command prompt. > To import it. (I say "it worked" optimistically because it is actually > doing something, but there've been some periods where that "something" > is "hanging" so we're not out of the woods yet.) > Depending upon the speed of your computer and the size and schema of your database, the load process can take a long time. On slower disks with a database that was just a few hundred MB when uncompressed, the load process would sit at certain steps for over an hour for me. If you got that far, you're probably fine and all you have to do is wait. -Mike -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice