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.
Only the same major version can read the data files.
You need to upgrade properly. To do that you'll have to install 8.4, then use pg_upgrade
, or use pg_dump
and pg_restore
. See the manual for more information.
That said, the error actually has the versions the other way around - it says you're running 8.4 binaries and trying to point them at a datadir from 9.2. Verify this with postgres --version
. If you turn out to have 8.4 binaries and a 9.2 datadir then pg_upgrade won't do you any good - you just need to install 9.2 (or use the correct PATH
if it's already installed) and run the 9.2 datadir using 9.2 binaries.
Update given extra info in question:
It is very likely that both 8.4 and 9.2 are running fine on your machine, just on different ports.
sudo netstat -ltnp |grep postgres
will show you listening PostgreSQL servers, and
psql -p 5433
(or whatever) will connect to a different port.
The databases appeared to vanish because when you installed the new PostgreSQL it became the default version that's run.
If you find that the 8.4 install is in fact not running, you probably just need to change the port
it starts on in its configuration so it runs on a different port, then connect to it as shown above.
Best Answer
I've run 8.3, 8.4, 9.0, and 9.1 at the same time on a single Windows server. You just need each PostgreSQL server listening on a different port. Choosing the port is part of the installation process.
I'm far to cynical to believe that PostgreSQL will never overwrite an existing data directory, but in my experience (none of it on OS/X), each version installs its own data directory.