psql Configuration

Firstly, configure the the psql tool using a ~/.psqlrc file or put a system-wide configuration in /etc/postgresql-9.1/.

~ # cat ~/.psqlrc

-- use this text for null values
\pset null '[NULL]'
-- Paginate
\pset pager off
-- Show timing
-- Better prompt
\set PROMPT1 '(%n@%M:%>) [%/] > '
-- ensure unicode
\encoding unicode

Show Database Site

SELECT pg_database_size('mydatabasename') AS size;
SELECT pg_size_pretty(pg_database_size('mydatabasename')) AS size;

Show Runing Queries

First, update the postgresql.conf file to include these two options.

stats_start_collector = true
stats_command_string = true

You'll have to restart PostgreSQL when changing those values

~ $ psql -U postgres template1 -c "select * from pg_stat_activity"