How to find the PostgreSQL database size

PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.
If you want to find the size of a database, in our example the sqview database, please tape:
sqview-# SELECT pg_database_size(‘sqview’);
(1 ligne)

The result in octal, for a pretty print we will use the pg_size_pretty function which converts the size in bytes to human understandable format.
sqview-# SELECT pg_size_pretty(pg_database_size(‘sqview’));
106 MB
(1 ligne)

Well if we need to get the size of all databases
sqview-# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
datname | size
postgres | 3537 kB
template1 | 3537 kB
template0 | 3480 kB
freedom | 25 MB
anakeen | 6081 kB
sqview | 106 MB
rt | 8201 kB
(7 lignes)

PostgreSQL can also give the size of a table ‘users’ like this

sqview-# SELECT pg_size_pretty(pg_relation_size(‘users’));
64 kB
(1 ligne)

This value exclude indexes and some auxiliary data.
If you want to include them use pg_total_relation_size instead of pg_relation_size as shown below.
sqview-# SELECT pg_size_pretty(pg_total_relation_size(‘users’));
152 kB
(1 ligne)

to find the largest table in the postgreSQL database.
sqview-# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
relname | relpages
sqview | 9666
(1 ligne)


Plotting CPU temperature with gnuplot

Good sysadmins take a look daily to log, and they collect a lot of logs, the most of the information collected consists of time series data:
a set of numerical values associated with a sequence of discrete time values.
There are many number of tools for monitoring this data visually as it is collected, most of them are built using the exellent RRDTool, like cacti …
In this article, I’ll introduce you to Gnuplot, focusing specifically on how to plot time series data.
Gnuplot is a portable command-line driven interactive data and function plotting utility.
It has been supported and under development since 1986. Gnuplot supports many types of plots in either 2D and 3D.
It can draw using lines, points, boxes, contours, vector fields, surfaces, and various associated text. It also supports various specialized plot types.

Let’s start our example that is based on reading CPU Temperature Sensor.
We will use lm_sensor a hardware monitoring tool.
Create a cron entry that collect continuously the CPU temperature into a log file /var/log/sensors.log.
(echo `date +%b-%d-%H:%M:%S` | tr -d ‘\ 012’ ; echo -n ‘ ‘; sensors | awk ‘/CPU Temp:/{ print $3 }’ )>> /var/log/sensors.log

After create a file with this contents.
$ cat > temper.conf
set terminal png size 620,360
set xdata time
set timefmt “%b-%d-%H:%M:%S”
set output “temper.png”
# time range must be in same format as data file
set xrange [“AUG-25-00:00:00″:”SEP-05-00:00:00”]
set yrange [0:60]
set grid
set xlabel “Date-Time”
set ylabel “°C”
set title “CPU temperature”
set key left box
plot “/var/log/sensors.log” using 1:2 index 0 title “aldebaran” with lines

Wait for a while for collecting data and tape the following command that will end up with a file called ‘temper.png’.
Use your favorite image viewer to take a look.
$ cat temper.conf | gnuplot