Some PostgreSQL stuff

Just some bits that might be useful when working with PostgreSQL databases. Everything is working with version 8.x, I can't say anything about the compatibility with older versions.

Display the sizes all of your databases running on a particular server:

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

Show running PostgreSQL thread on Linux:

ps auxww | grep ^postgres

When upgrading to a new version (usually major or secondary), you have to do a full dump and restore. This one works well for me for some time now:

    * pg_dumpall -o -U postgres > [dump file]
    * stop postgresql server
    * you may want to save your configuration files (e.g. postgresql.conf, pg_hba.conf)
    * now you may safely remove your old postgresql installation
    * rm -rf /var/lib/postgresql/data
    * install new postgresql
    * merge changes from postgresql.conf and pg_hba.conf
    * start new postgresql server
    * psql -f [dumpfile] -U postgres