All we need is an easy explanation of the problem, so here it is.
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"
sudo find / -name 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?
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂