How to find out sizes of PostgreSQL databases and tables

-- list databases and their sizes
SELECT datname, pg_database_size(datname)/1024/1024 AS MegaBytes FROM pg_database ORDER BY MegaBytes DESC; 

-- list tables, their sizes in MB (WITH and WITHOUT size of their toasted tables and indexes), and their index overhead
SELECT
 schemaname, tablename,
 (SELECT pg_total_relation_size(schemaname||'.'||tablename) AS Bytes)/1024/1024 AS MegaBytes_table_plus_all_indexes,
 (SELECT pg_relation_size(schemaname||'.'||tablename) AS Bytes)/1024/1024 AS MegaBytes_table_only,
 ROUND((
  (
   (SELECT pg_total_relation_size(schemaname||'.'||tablename))
   - (SELECT pg_relation_size(schemaname||'.'||tablename))
  )::NUMERIC / (
   SELECT pg_total_relation_size(schemaname||'.'||tablename)
  )
 )*100,2)||'%' AS overhead_all_indexes 
FROM pg_tables
ORDER BY MegaBytes_table_plus_all_indexes DESC;

-- indexes and their sizes, with tables and their sizes
SELECT
 schemaname, tablename,
 (SELECT pg_total_relation_size(schemaname||'.'||tablename))/1024/1024 AS megabytes_table_plus_all_indexes,
 (SELECT pg_relation_size(schemaname||'.'||tablename))/1024/1024 AS megabytes_table_only,
 indexname,
 (
  (SELECT pg_total_relation_size(schemaname||'.'||tablename))
  - (SELECT pg_relation_size(schemaname||'.'||tablename))
 )/1024/1024 AS megabytes_all_indexes,
 ROUND((
  (
   (SELECT pg_total_relation_size(schemaname||'.'||tablename))
   - (SELECT pg_relation_size(schemaname||'.'||tablename))
  )::NUMERIC / (
   SELECT pg_total_relation_size(schemaname||'.'||tablename)
  )
 )*100,2)||'%' AS percentage_all_indexes, 
 (SELECT pg_total_relation_size(schemaname||'.'||"indexname"))/1024/1024 AS megabytes_index,
 ROUND((
  (
   SELECT pg_total_relation_size(schemaname||'.'||"indexname")
  )::NUMERIC / (
   SELECT pg_total_relation_size(schemaname||'.'||tablename)
  )
 )*100,2)||'%' AS percentage_index
FROM pg_indexes
-- HACK: there is probably bug in function pg_total_relation_size - does not work, if name contains uppercase characters
WHERE indexname = LOWER(indexname)
ORDER BY megabytes_all_indexes DESC, schemaname, tablename, megabytes_index DESC;

SQL scripts above inspired by these articles:

http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples

http://www.appdesign.com/blog/2009/05/07/list-of-postres-tables-with-their-sizes-and-indexes/

http://www.postgresonline.com/journal/archives/110-Determining-size-of-database,-schema,-tables,-and-geometry.html

http://archives.postgresql.org/pgsql-admin/2008-07/msg00197.php

http://archives.postgresql.org/pgsql-sql/2005-03/msg00351.php

http://blog.marcua.net/post/65207426/determining-postgres-page-size

How to access WinStrom database directly

WinStrom is software for book-keeping, currently supporting Czech and partially Slovak accounting rules. It is working not only on Windows, but also on Linux and Mac OS X.

It uses PostgreSQL for data storage. Sometimes you could need to access its database directly, not via WinStrom application. Here is how to accomplish this task when using WinStrom version 10.

I found access credentials in these configuration files:
/etc/winstrom/winstrom-server.xml
~/.winstrom/settings.xml

Here are settings for pgAdmin III:

Your specific maintenance DB (database name, in my case system_business__s__r__o_) you can get from this part of settings.xml:

     company-127.0.0.1:5434
    system_business__s__r__o_

Other values are from winstrom-server.xml:

WinStrom server configuration
localhost
5435
dba
Password







Alternatively, you can connect using text-based console (psql):
ostry@elitebook:~$ psql -h 127.0.0.1 -p 5435 -U dba -W -d system_business__s__r__o_
Password for user dba:
psql (8.4.1)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
system_business__s__r__o_=#

Success!

Now you are able to edit also fields, which are not writeable by WinStrom application itself, e.g. "datvyst" field of "dpolobch" table (dates of ordered items).

How to install PostgreSQL module for Python

How to install psycopg2?

On Ubuntu Linux (9.04):
sudo apt-get install python-psycopg2

On Mac OS X (Leopard) - valid for PostgreSQL 8.4:
export PATH="/Library/PostgreSQL/8.4/bin:$PATH"
sudo easy_install -U setuptools
sudo easy_install psycopg2

In case of problems, install PostgreSQL first. Don't know how? Don't worry, it is pretty straightforward. For example on Linux, but also on Mac OS X or Windows.

How to install and configure PostgreSQL server on Ubuntu Linux 9.04

Installation

sudo apt-get install postgresql

Create database

sudo su postgres
psql
postgres=# CREATE DATABASE dsbis_db WITH ENCODING 'utf-8';

where dsbis_db is database name.

Create database user

postgres=# CREATE ROLE dsbis_db_user LOGIN PASSWORD 'ojweod30';
postgres=# ALTER DATABASE dsbis_db OWNER TO dsbis_db_user;

Configure host-based authorization

If you need e.g. allow remote access to this database, you need to edit this configuration file: /etc/postgresql/8.3/main/pg_hba.conf.

To be able to connect locally with your newly added database user, add this line:
local all all md5
possibly as replacement of this line:
local all all ident sameuser
if you do not want to allow connecting to database to all local users of your Linux system.

Restart database server

sudo /etc/init.d/postgresql-8.3 restart

NOTE: Now, you can set your newly created database login/password in your application settings and it should work. But remember, that if you application resides on another host, probably you will need to allow connections from it in pg_hba.conf (see section above, about host baset authorization).