Postgresql – get results from a Postgres query that I got disconnected from

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.

Use the -e flag when you start psql.
For example:

psql -e mydatabase

Or, in case of using a script, try the -a flag.
Details in the manual.