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


> 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


No comments:

Post a Comment