Trying to restore Database

PostgreSQL is the database server used to store information. Do you have a question or are you having problem with PostgreSQL? If so, post them here.

Moderator: Moderators

Trying to restore Database

Postby PokerBot » Fri Sep 19, 2008 3:54 pm

I made a back up of my database which is an SQL file, but looks like a note pad file.

Please can you tell me how to restore my old database, when i try to find an existing database it does not find any and when i try to access it through the SQL admin program, when searching for it i have to scroll down on the option of "back up file" to "all files" so that it can see it, when i select it the "OK" button does not become solid, ie i cannot click on it.

Do i have to back it up this way, can i just copy and paste the program directory into the new instillation folder?

Please advice me with as much detail as possible

Thanks
PokerBot
 
Posts: 259
Joined: Mon Feb 18, 2008 9:02 am

Re: Trying to restore Database

Postby WhiteRider » Fri Sep 19, 2008 5:28 pm

The command line you need to use to restore a database is given near the end of the Database / Postgres FAQ.
WhiteRider
Moderator
 
Posts: 53416
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Trying to restore Database

Postby PokerBot » Fri Sep 19, 2008 6:11 pm

I apologize for having to be spoon fed but please can you tell me EXACTLY what to do,

i read the article you advised but it says could not open input file... no such file or directory exists

i tried different names and renaming the back up to "tar" at the end

the file is located hear: C:\Program Files\PostgreSQL\8.3\bin File name is "PT3 DB" (SQL file)

1) What should i name the back up file, including the file extension, (note it looks like a note pad file, is this okay?)

2) What should i type into the command line EXACTLY please: pg_restore -U postgres -d yournewdb databasename.tar

I have tried to supstitue every part of the line with my databse name to no avail, please write exactly what i should copy and paste into the command line

Thanks
PokerBot
 
Posts: 259
Joined: Mon Feb 18, 2008 9:02 am

Re: Trying to restore Database

Postby WhiteRider » Sat Sep 20, 2008 4:08 am

You can't just change the file extension, that doesn't change the type of file it is.
How did you generate the backup file?
WhiteRider
Moderator
 
Posts: 53416
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Trying to restore Database

Postby PokerBot » Sat Sep 20, 2008 7:44 am

Via the postgres admin program as advised on the page above...

Pgadmin111, double click database server, entered password, clicked on PT3 DB and clicked back up, clicked the "plain" option for format, left all the other buttons and options alone. The instructions does not give any advice on what options to use so i assumed that plain would be the safest one. It is a SQL file but the icon is of a note pad file, is this okay? Also it is 8GB in size with about 1.5 million hands in it

1) Does the new database that we create have to have the same name as the file we are backing up, or does it have to be different?

2) Can i not use this file because it is not a correct back up file? If so can i at least either convert it to work or some how export all the hands from the file and then import them into a new database manually?

3) I had a windows reinstall, so perhaps an issue lies with this? A different version of SQL? I downloaded the file from PT3 both times, the one saying "with Postgress"

I don't mean to sound rude but i am from UK and will have to wait another day, or at least half a day before i get a reply because of the time differences so could you be as comprehensive with your replies as possible, answering all the questions and offering ANY advice that my help thanks
PokerBot
 
Posts: 259
Joined: Mon Feb 18, 2008 9:02 am

Re: Trying to restore Database

Postby WhiteRider » Sat Sep 20, 2008 9:44 am

I'm in the UK as well, but I'm afraid I don't know everything there is to know about postgres..

I assume your backup file has no extension because you have file extensions turned off in windows?
It may be associated with notepad because windows doesn't know what the extension means, or maybe you tried to open a file of that type in notepad before.
You should just leave it with the name it was created with when you backed it up.

If you created the database backup using PGAdmin, then you should be able to use the restore feature in PGAdmin to restore it.
Have you tried that?

1. I would have thought the restore would recreate the DB for you, and probably with the same name it had when you backed it up.
2. If you haven't done anything to the file, then you should be able to restore from it, assuning that the backup worked correctly.
3. If you re-installed postgres using the PT3 installer, then you will have the same version.

I'll see if I can get one of our postgres experts to take a look at this thread..
(they ARE in the US, but hopefully we can get some help for you this afternoon)
WhiteRider
Moderator
 
Posts: 53416
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Trying to restore Database

Postby kraada » Sat Sep 20, 2008 9:51 am

The backup being a text file is completely normal.

I personally am more of a command line guy, so you'll excuse the reversion to the command line, I hope. I don't have as much experience with PGAdmin.

Start --> Programs --> PostgreSQL --> Command Prompt.

at the command prompt type:
Code: Select all
pg_restore -U postgres <filename>


where <filename> is the full name and location of your backup file (C:\...etc).

That should be all you need to do.

If that doesn't work let me know what error you get and we'll work from there.
kraada
Moderator
 
Posts: 54435
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Trying to restore Database

Postby PokerBot » Sat Sep 20, 2008 3:42 pm

I keep getting the same errors "pg_restore: (archiver) could not open input file "c:\program": no such file or directory"

I tried to move the file to the bin folder in the SQL program files but that did not chance anything

Please can you help me resolve this issue ASAP as cannot really play until it is all ready to go.

Perhaps finding out how to do it through the PG admin program is easier?

Also should PT3 be running?

Do i have to have the database name that i just created the same as the restore name file?

I am more than happy to use alternative means of communication like telephone if you are okay with that and if it will make things easier to do

Thanks
PokerBot
 
Posts: 259
Joined: Mon Feb 18, 2008 9:02 am

Re: Trying to restore Database

Postby WhiteRider » Sat Sep 20, 2008 4:03 pm

PokerBot wrote:I keep getting the same errors "pg_restore: (archiver) could not open input file "c:\program": no such file or directory"

You will need to put the path in quotes since it has spaces in it.

[quote]Please can you help me resolve this issue ASAP as cannot really play until it is all ready to go.[quote]
Please don't be impatient - you got the last reply to help you 6 hours before you replied..
WhiteRider
Moderator
 
Posts: 53416
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Trying to restore Database

Postby PokerBot » Sat Sep 20, 2008 4:50 pm

My apologize for my impatience but you can imagine how eager i am to get this fixed.

After i typed in EXACTLY this: "pg_restore -U postgres C:\Program Files\PostgreSQL\8.3\bin\PT3 DB2.sql"

This message came up: "pg_restore: (archiver) input file does not appear to be a valid archive"

1) How can we be sure that it is talking about the correct file hear?

2) The back up file must be correct because i followed the instructions on the SQL FAQ web page on this site

3) If this is not the correct format how can i get my histories out of it, if need be i am happy to export the hands from the file somehow and then manually import into a new database again.

4) What other options are available? Perhaps using the PG admin program? Or moving the file to a different sub folder? There must be a way to access the data on the file?

Thanks
PokerBot
 
Posts: 259
Joined: Mon Feb 18, 2008 9:02 am

Next

Return to PostgreSQL [Read Only]

Who is online

Users browsing this forum: No registered users and 4 guests

cron