Help - lost database

[Ben Attenborough]

I recently had a problem with my computer which meant I had to reinstall windows afresh. I made a backup of all my stuff and reinstalled PT3 and postgreSQL. I foolishly started a new database (which works fine BTW) but I don't now where my old PT3 database is or how to connect to it. Any ideas???

[WhiteRider]

I assume you mean that you just copied the folders, you didn't actually "backup" the database?

If you re-installed the same major version (8.3.x) then you should be able to recover your old database, but I'll have to get someone else to help you.

[Ben Attenborough]

Ok cool, there's no immediate hurry - I'll have to remember to make a backup next time!

[kraada]

First, stop the running version of PostgreSQL:
Start --> Programs --> PostgreSQL --> Stop Service

Then:

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

At the command prompt type:

Code: Select all

pg_resetxlog <directory>

where <directory> is the directory you copied the PostgreSQL folders to.
Then:

Code: Select all

pg_ctl start -D <directory>

.

This restarts PostgreSQL on that data directory, but since we don't want the data there we're going to back that database up and restore it to your other data directory. You won't want two PostgreSQL servers running at once when you can have one

So, the next step will tell you the name of your PostgreSQL database that you moved. If you already know the name of the database you can skip this step.

Code: Select all

psql

The command prompt will change, then:

Code: Select all

\l

This will list the databases on the list. Note the one that has your PT3 data, then:

Code: Select all

\q

to exit.

I'll use <database> to refer to the database name of your old PT3 data.

Code: Select all

pg_dump <database> -f C:\Backup.sql

This will dump your old database data to a file called Backup.sql. Once it finishes, we can now shut the PostgreSQL process running on the copied data directory down:

Code: Select all

pg_ctl stop -D <directory>

.

You can now restart your normal PostgreSQL service on the normal data directory via:
Start --> Programs --> PostgreSQL --> Start Services.

Then:

Code: Select all

pg_restore -C -d PT3RestoredDb C:\Backup.sql

Will restore the data from the old database as PT3RestoredDb. If you want to call it something else (PT3OldDb), go for it. Just don't use anything that you've already got in use.

Once it finishes, you can connect to that database via PT3: Database --> Database Management --> New, then click "Browse Databases", select the database in question, then click connect.

Voila, the database has been ported over. Please note that if your copy of the database directory originally was taken while the database was running there may be some corruption of the database. Unfortunately there isn't much that can be done for that. Worst case, export the hand histories once you've gotten to this point and reimport them either into the new DB you created or into another new DB. Hopefully the damage to the database will be minimal to nonexistent since I doubt you took the copy while you were auto-importing

Once you've confirmed that everything works happily, you can delete the old copy of the database directory and the Backup.sql at your leisure as they won't be necessary any longer.

[Ben Attenborough]

Thanks for spending the time doing all that, I'll try it out soon and let you know if it works. Cheers!

[Ben Attenborough]

Got as far as psql and it comes up with "FATAL: database "<username>" does not exist"
If I try psql "PT3 DB" (the name of the database it says "FATAL: role "<username>" does not exist"

[kraada]

In that case, add:

Code: Select all

-U postgres

to any command giving you that error. If you used a username other than postgres, use it here instead of postgres as well. That error is saying it can't log in as your user to the database, so you have to tell it to use the postgres user.