Check postgres DB size

Posted on Monday, November 25, 2013

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


Make sure it was created by running \l  

> \l

Grant patman privileges to this database


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

Here is my python code

#  Simple Script to insert data

import psycopg2
import sys
import time

con = None

    # 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


       print 'inserting 100 rows of text'

       for x in range(0, 100):
        cur.execute("INSERT INTO test_data(test_text) VALUES ('" + textString + "');")

       cur.execute("SELECT pg_size_pretty(pg_database_size('testdb'));")
       size = cur.fetchone()[0]
       print size

except psycopg2.DatabaseError, e:
    print 'Error %s' % e

Make it executable and run it

> chmod u+x
> ./

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



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)
  sleep 600

Make it executable

> chmod u+x

Run it

> ./ > db-size.csv


No comments:

Post a Comment