How to re-initialize an old DB?

[ddrewno]

I recently had an issue with one of my user-accounts in vista being corrupted and thus forcing me to reinstall vista. Luckily absolutely none of my data was lost, but unfortunately I do have to reinstall Postgres again in order to take what my computer now thinks is just a mound of 0's and 1's and turn it back into an active DB.

Some things about me:

Old DB location: C:/Program Files/Postgresql/8.0
Version: 8.0
Size: ~18GB
PT3 Version: PT3 Beta Build 17

I tried installing the 8.3 version (It came with Build 17), and it successfully linked up with my 8.0 database (It did not give me the "Are you sure you don't want to create a database? We have not detected one on the folder specified" message on install), but I'm getting the same error that effects many people on this forum:

2008/09/09 14:09:46.697: [- Main -][PostgresConnection] Connecting to database: PT3 DB
2008/09/09 14:09:48.690: [- Main -][PostgresConnection (Error)] Unable to login to database (PT3 DB). Reason: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" and accepting TCP/IP connections on port 5432?
20
===================================================

In pgAdmin II I get the following message when attempting manually to connect to the database server:

===================================================
Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?
If you encounter this message, please check if the server you're trying to contact is actually running PostgreSQL on the given port. Test if you have network connectivity from your client to the server host using ping or equivalent tools. Is your network / VPN / SSH tunnel / firewall configured correctly?

For security reasons, PostgreSQL does not listen on all available IP addresses on the server machine initially. In order to access the server over the network, you need to enable listening on the address first.

For PostgreSQL servers starting with version 8.0, this is controlled using the "listen_addresses" parameter in the postgresql.conf file. Here, you can enter a list of IP addresses the server should listen on, or simply use '*' to listen on all available IP addresses. For earlier servers (Version 7.3 or 7.4), you'll need to set the "tcpip_socket" parameter to 'true'.
================================================================================================

I have attempted to follow the instructions given in that message to modify the postgresql.conf file, but have had no improvement after having done so.

The postgresql service is actively running on my computer (named: "PostgreSQL Database Server 8.3), and appears to be operating normally.

I will be attempting to fix this on my own for the next few hours (I have a backup 18gb copy of my DB just in case I mess this up), and I will post back here whenever my issue is solved.

Edit: This is what I get when attempting to create a new database from within PT3:

Unable to create PostgreSQL database: PT3_2008_09_09_144658
Reason: Unable to login to database (template1). Reason: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" and accepting TCP/IP connections on port 5432?

[ddrewno]

Interesting development: I've noticed the PostgreSQL Database Server 8.3 service isn't actually 'startED', but rather it's listing as 'startING'. I'm not sure why it's doing this yet.

[ddrewno]

Update: My problem is half solved now. I realize now that my big issue was that I had an 8.0 database that was trying to be initialized by postgreSQL version 8.3, and obviously that is not possible. I still have the same issue though: Now I have a fully functional PT3 with PostgreSQL 8.3 running ( I can create new DBs just fine), but I still have my data in an 8.0 database. So how do I migrate that up to 8.3, and how do I get it working with PT3?

[ddrewno]

Ok my issue is solved now. I removed 8.3, and reinstalled 8.0.17 from the postgres website. I installed it, and pointed that installation directory towards the location of my DB (Actually I moved my DB to the install directory just to be nice and easy), and it went flawlessly. I then went into the pgadmin III program, and manually connected to the DB. After that I went back into PT3 and went into database management -> new database -> check the "Already exists" box -> Selected the correct DB from the list.

Everything works now!

[WhiteRider]

Glad you got it all worked out.
Updating from 8.0 to 8.3 is a "major version" update (because you're changing from 8.x to 8.y) so the databases are not compatible. To do this upgrade you will have to backup your DBs in 8.0 and then restore them into 8.3.
Please see the end of the Database / Postgres FAQ if you want to attempt this.
Postgres 8.3.3 will give you some performance improvements.

[ddrewno]

I felt it necessary to include one more thing on this:

The reason I had so much trouble is that whenever I installed an 8.x version of PostgreSQL I would not be able to START THE POSTGRES SERVICE either through PGadmin III or through control panel -> administrative tools -> Services. I had no clue on what was causing this problem until I looked at the event viewer (Also in administrative tools), and saw this: "FATAL: syntax error in file "C:/Program Files/PostgreSQL/8.0/data/postgresql.conf" line 48, near token "*"". Sure enough I had edited the postgresql.conf file a LONG time ago, and had somehow accidentally put an asterisk outside of a commented field. This is why the postgres service was not able to start for me, and also why 8.3 would work whereas 8.0.x would not (V 8.3 uses the postgresql.conf file located in c:/program files/postgresql/8.3/data instead of 8.0/data). There was NO other symptom of this problem other then the simple fact that the service would never start (Even though it was clearly listed as "Automatic" in the services list).

After I fixed the single goddamn asterisk in the config file everything started working within about 3 minutes, and I miraculously had all of my data back in good form.

Sometimes the smallest nuisance can be the greatest pain.