Fried HD, database backup won't restore

[DerekG]

Yep, same problems.

v8.2 - FATAL: role "postgres82" does not exist
v8.3 - 101-SQLSTATE=08001 could not connect to server: Connection refused

It's as if installing 8.2 not only failed but it also broke v8.3.

[DerekG]

Uninstalled v8.2 completely. Also went into PGAdmin and deleted/dropped the 8.2 server since I have never seen PSQL do this during uninstall and this time was no exception. Reboot. Still will not connect to the only version of PSQL on this computer v8.3. Same 'server doesn't listen' error message. Both in the .conf file are changed from md5 to trust. Disabled firewall and all other security just in case. No help. 8.3 used to work but no longer.

[ptrack pat]

Installing 8.2 should not have messed up 8.3, I've done dozens of install/uninstall/reinstalls and it's never messed up another version before.

Go to the c:\program files\postgresql\8.3\data folder. Is there a file called postmaster.pid there? If so, delete it and then see if you can start the 8.3 service.

What is the size of the .backup file you are trying to restore from?

[DerekG]

No postmaster.pid in the directory, just postmaster.opts.

The .backup file is not very big, about 8MB.

[ptrack pat]

That is a very, very small .backup file. If you had a large database then that is definitely not what the size should be, it would be much larger, which could explain the errors you were getting when you were able to restore it into 8.3 before these problems.

Do you have any way to access any of the files on the drive that crashed? Would you be able to just get the files off of there (don't need to run PostgreSQL on it, just get the files)?

[DerekG]

Yeah, it's a small amt of data and a small database but I think the size was correct. I was making backups monthly and the previous backup was just slightly smaller.

I have tried to get the data using Find and Mount and Testdisk but I really don't know what I'm doing when it comes to fried partitions. Find and Mount never found anything. Testdisk was able to find a couple of partitions but I'm afraid to do anything because I don't know what I'm looking at. My drive had two partitions, F: and H:, the latter contained psql and all the databases.

Results
* HPFS - NTFS 0 1 1 3981 254 63 63970767 [WinXP]
NTFS, 32 GB / 30 GiB
D HPFS - NTFS 3982 1 1 30514 254 63 426252582
NTFS, 218 GB / 203 GiB
D HPFS - NTFS 3982 1 6 30514 254 63 426252577
NTFS found using backup sector!, 218 GB / 203 GiB

D HPFS - NTFS 3982 1 1 30514 254 63 426252582
NTFS, 218 GB / 203 GiB
Can't open filesystem. Filesystem seems damaged.

D HPFS - NTFS 3982 1 6 30514 254 63 426252577
NTFS found using backup sector!, 218 GB / 203 GiB
Can't open filesystem. Filesystem seems damaged.

The first partition on the list is F: and that appears to be ok. The next two on the list have specs matching my H: partition. I believe the 'D' means they were deleted. The examples in the Testdisk instructions make it sound like I need to be able to get a list of files before I attempt to write the partition. I tried to list the files as directed in the instructions but can't get anything as you can see. I sent an email to the creator of Testdisk but still haven't received a response. If you know anything about data recovery I'll give it a try. At this point I'm considering everything a total loss. If I don't get a response soon all I can do is start experimenting on my own.

Since I was making backups monthly I wasn't too concerned about losing the data but now that I was having problems restoring my backup I am starting to worry all these backups I made were worthless.

[ptrack pat]

I'm going to send you a PM with directions on how to FTP that .backup file to me so I can restore it and look at what is in the database.

[DerekG]

Thanks again for helping me get this sorted out.

Do you have any suggestions of what to do about clearing out any PSQL remnants in my system drive before I attempt to install PSQL again on a new hard drive? I'm thinking registry entries, net user postgres /delete, stuff like that. I can remove the installation entry from Add/Remove Programs but I assume I won't be able to uninstall since everything was installed on the fried partition.

[ptrack pat]

An uninstall should delete the registry settings.
You can use Start > Settings > Control Panel > System > Advanced > User Profiles to delete and PostgreSQL created users.
Also, deleting the remnants of the [drive]:\program files\postgresql folder will get rid of any files the uninstall didn't.

[DerekG]

Postgresql was installed on the fried drive so the uninstaller won't run and clear out the registry settings.

I suppose I could make a backup of the registry and go through it line by line and delete anything postgresql related, delete the entries from add/remove programs, then install fresh unless you have a better idea. The only thing I see on my system drive is psqlODBC.