Check postgres DB size

Just to help myself out I am first going to make some notes for installing and setting up postgres 9.1 on an Ubuntu 12.04 server

Run the following command line commands to install postgres 9.1 on Ubuntu 12.04

> sudo apt-get install python-software-properties > sudo add-apt-repository ppa:pitti/postgresql > sudo apt-get update > sudo apt-get install postgresql-9.1 libpq-dev

Log into the DB

> sudo su postgres > psql -d postgres -U postgres

Create a test user

> CREATE USER patman WITH PASSWORD 'myPassword';

Create the Database

> CREATE DATABASE testdb;

Make sure it was created by running \l  

> \l

Grant patman privileges to this database

> GRANT ALL PRIVILEGES ON DATABASE testdb to patman;

Now let’s test it quit out of postgres and try

> \q

As your user run the following from the command line

> psql -d testdb -U patman

Create a simple table that I can send updates to so I can check the size. 

> CREATE TABLE test_data(      id serial primary key not null,      time timestamp not null default CURRENT_TIMESTAMP,      test_text text not null   );

Examine the table you just created

> \d test_data

Insert some test data

> INSERT INTO test_data(test_text) VALUES ('this is my test text!!');

Then check it

> select * from test_data;

Create a simple python program to insert data

To better check the growth rate of a database I created a simple python program to insert data into it.

> sudo apt-get install python-psycopg2 > vi insertDB.py

Here is my python code

#!/usr/bin/python # # #  Simple Script to insert data # import psycopg2 import sys import time con = None try:     # First get the current maximum     con = psycopg2.connect(database='testdb', user='patman', password='myPassword')     cur = con.cursor()     textString = "this is the text string for the database test "     for x in range(0, 16):        textString += textString     while(True):        print 'inserting 100 rows of text'        for x in range(0, 100):         cur.execute("INSERT INTO test_data(test_text) VALUES ('" + textString + "');")         con.commit()        cur.execute("SELECT pg_size_pretty(pg_database_size('testdb'));")        size = cur.fetchone()[0]        print size        time.sleep(10) except psycopg2.DatabaseError, e:     print 'Error %s' % e     sys.exit(1)

Make it executable and run it

> chmod u+x insertDB.py > ./insertDB.py

Now for the different ways to get the Database size

Get into postgres with psql

> psql -d testdb -U patman

This command will show you the size of each database

> \l+

This command will show you the size  of each table in the database

> \d+

This command will show you the size of the database and display it in a nicer way, like using ls -h

> SELECT pg_size_pretty(pg_database_size('testdb'));

Here is a very detailed query that will list all the tables and their sizes listing the largest one at the top.

> SELECT nspname || '.' || relname AS "relation",     pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"   FROM pg_class C   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)   WHERE nspname NOT IN ('pg_catalog', 'information_schema')     AND C.relkind <> 'i'     AND nspname !~ '^pg_toast'   ORDER BY pg_total_relation_size(C.oid) DESC   LIMIT 20;

Finally here is a command to use on the linux command line to get the size.

> psql -d testdb -U patman --pset=format=unaligned -c "SELECT pg_size_pretty(pg_database_size('testdb'));"

Little bash Script

Using this command I created a little bash script to monitor the size increase of a database.  It runs a check every 10 minutes and puts the data in a csv friendly format.  Simply pipe it out to a csv file.

> vi db-test-size.sh

#!/bin/bash DB_NAME='testdb' DB_USER='patman' echo Database Size, timestamp while [ true ]; do   OUTPUT=$(psql -d $DB_NAME -U $DB_USER -t -c "SELECT pg_database_size('$DB_NAME');")   TIMESTAMP=$(date +%s)   echo $OUTPUT, $TIMESTAMP   sleep 600 done

Make it executable

> chmod u+x db-test-size.sh

Run it

> ./db-test-size.sh > db-size.csv

References