The two constraints that you have specified:
- Very low downtime
- Limited disk space use
conflict rather seriously.
You have (wisely, IMO) excluded the option of using pg_upgrade
with a custom rebuilt Pg, which is the only option I see that'd satisfy both those constraints.
I suspect you'll have to drop the disk space constraint. The only way out of this that I see is to configure Slony-I to replicate from your 8.3 database to a new 9.2 instance deployed either on new storage on the same machine, or on a separate machine. Allow Slony-I to bring the replica up to date, then shut the old server down, disable replication and cut over to the new server. "New server" here can be a new 9.2 cluster running on a different port on the existing server hardware, it doesn't have to be a new physical or virtual machine.
At this point you can use streaming replication - or Slony-I again - to replicate the data back to a 9.2 instance on the original server and fail back to it. The temporary server used for migration can be retired, or (preferably, if it's real hardware) configured to operate as a streaming replication slave and WAL archiving repository for PITR and failover purposes.
Slony-I is a bit of an experience to work with and places some constraints on how you can use the DB (mainly: All DDL must go through Slony, not direct DDL commands), so I recommend taking a dump of the 8.3 server, restoring it to a test workstation, and testing the proposed replication setup there before attempting it in production. You will need to test your application on 9.2 anyway, so this is a good chance to do both. Test it on 8.3-with-slony, and on 9.2.
This might be a good opportunity to see professional support from people who have experience dealing with these issues.
I don't think these are issued by PGPool. I think they are instigated by your application (which I am guessing is written in PHP and running over some sort of ORM).
The first entry is DEALLOCATE
which means effectively to tell the server to free up memory from a prepared statement. These look like they are issued through some PDO module.
The second and third queries look like they are ORM-related mapping queries. Your application is probably unware that these queries are being issued by the framework it is running on, but these queries only make sense really in an ORM or similar environment.
Best Answer
netstat -an | grep LISTEN | grep 5444
will show if there's a process listening on that port.netstat -an | grep LISTEN
will show you all ports with a process listening on them. You can then uselsof
to see which processes are actually using a given port.There's no such concept of "find out the application port even the application was shutdown", as anything can listen on any port (with certain restrictions if not running as
root
). The closest thing to this is the/etc/services
file, which documents ports that are "well known"/assigned by IANA. Also see http://www.iana.org/assignments/port-numbers. But, as I said, there's nothing to stop anything from running on one of these ports.To be honest, you should know what's running on a production box that you have root access to?