Using the correct postgres server

Question

I'm not sure what happened but I logged in on friday and did psql -d name to access a db that was perfectly running just fine. Then I log in this monday morning and try to access the same database with psql -d dbname and this is what I got:

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "johndoe", database "db", SSL off

I try sudo su postgres then I got this:

psql: FATAL:  password authentication failed for user "postgres"

After running

 sudo find / -name pg_hba.conf 

I get:

/var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/9.2/data/pg_hba.conf

Modifying the 1st pg_hba.conf and allowing access to postgres allowed me to login through psql. With a \l command there showed no databases or users. This confirms my suspicion that the postgres install I did last Friday did not update the old version I had but installed a new copy of it onto the server.

IS there anyway of logging into the old postgres server or retrieving those databases? I know for a fact they did not get wiped with the new install of postgres as there are two pga_hba.conf files on the box.

If it did get wiped, is there any way of restoring my Centos 6.5 server to a past date?

EDIT:

Afer stopping the new running server with:

service postgres stop

I type postgres into the terminal and I get:

postgres does not know where to find the server configuration file.
You must specify the --config-file or -D invocation option or set the PGDATA environment variable.

So I point to the correct server config with:

postgres -D /var/lib/pgsql/9.2/data

after switching with su postgres and I get this error:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 8.4.13.

This used to work where it would just give me a warning saying that the db version was 8.4 and server version was 9.2 so some features might not work.

Answer 1

Most likely, you have both Postgresql 8.4 and Postgresql 9.2 installed on this server.

Prior to pg9.0, the RPM packages for postgresql were configured such that you could only have one version installed at a time. Upgrading the RPM replaced the old version with the new.

From pg 9.0, the RPM packages have been configured to allow installation of multiple versions side by side. This is to facilitate in-place upgrade of databases (which requires both versions installed during the conversion). The packages are names postgresql92, postgresql93, etc.

Also note that it would not be possible for both versions to run simultaneously on the same port.

My guess is that your server was rebooted, and when it came up, either they were both configured to start and the 8.4 version started first, or perhaps the 9.2 version is not configured to start at boot at all.

You can confirm this with:

yum list installed "postgres*"

to see that you have both versions installed. You can check which versions are configured to start at boot with:

chkconfig --list | grep postgres

To stop the 8.4 version and start the 9.2 version, so that you can access your data:

service postgresql stop
service postgresql-9.2 start

To make sure that 9.2 starts up next time you boot, and 8.4 does not:

chkconfig postgresql-9.2 on
chkconfig postgresql off

All the above commands executed as root.

If you do not specifically need the 8.4 postgresql installed on your server I would recommend you remove it.