PostgreSQL Tutorial SQL -ing and Select -ing around Part1 for those coming from other worlds such Oracle, SQLServer, etc.
If you are like me one of the items on my to do list is get familiar with my db environment as soon as possible and be able to tell statistical information in space utilization and one of quickies is to get this at the top level first of anything else, I’m talking about get to know how big are your databases before you even go down to the table/row/segments level.
Here are quick select that will help you to get just that, later when I get the time and energy to keep sharing I will writer more tutorials with more advance reporting and select script. Also I’ll try to write about the many other cool features that PostgreSQL have.
Please also note: While there are several other ways to accomplish these results published in the net and books, I’m only publishing here what actually worked for me after scientifically test and tied my self on my own systems. There a lot fancy and pretty functions to use but I like to be simple since my early days in the db world. Your comments and corrections are always welcome.
Have said that, let’s get in, shall we?
postgres@opsujira:~> psql psql (9.1.3) Type "help" for help. postgres=#
Then let’s pick around simply: Get Database size
jiradb=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as "The Dbs have up for:";
The Dbs have up for:
----------------------
2 days 09:22:47
(1 row)
jiradb=#
This tell for how long the dbs on this server have been up.
postgres=# select round(pg_database_size(current_database())/1024,2) db_size_MBs;
db_size_mbs
-------------
6080.00
(1 row)
postgres=#
Ok, this gives the size of our current db and notice that I’m rounding up to KBs and MBs. You can also use the “pg_size_pretty” function as well.
postgres=# SELECT datname, sum(round(pg_database_size(datname)/1024/1024,2)) db_size_GBs FROM pg_database GROUP BY 1 ORDER BY 1 ASC; datname | db_size_gbs -----------+------------- jiradb | 11.00 postgres | 5.00 template0 | 5.00 template1 | 5.00 (4 rows) postgres=#
WOW! That’s cool but let’s keep doing more cool stuff, now let’s get the size for the databases on the server using some other functionally then SUM or ROUND
postgres=# SELECT datname, round(pg_database_size(datname)/1024/1024,2) db_size_GBs postgres-# FROM pg_database postgres-# UNION ALL postgres-# SELECT datname, round(pg_database_size(datname)/1024/1024,2) all_dbs_GBs postgres-# FROM pg_database; datname | db_size_gbs -----------+------------- template1 | 5.00 template0 | 5.00 postgres | 5.00 jiradb | 11.00 template1 | 5.00 template0 | 5.00 postgres | 5.00 jiradb | 11.00 (8 rows) postgres=#
That was cool but could we do a better use of this UNION ALL operation, let’s compute the total size of all the databases at the end or last row of the result set.
postgres=# SELECT datname, round(pg_database_size(datname)/1024/1024,2) db_size_GBs
postgres=# FROM pg_database
postgres=# UNION ALL
postgres=# SELECT 'The are a total of '|| count(1) || ' Dbs, the total size is: ',
postgres=# sum(round(pg_database_size(datname)/1024/1024,2)) all_dbs_GBs
postgres=# FROM pg_database;
datname | db_size_gbs
----------------------------------------------+-------------
template1 | 5.00
template0 | 5.00
postgres | 5.00
jiradb | 11.00
----------------------------------------------+-------------
The are a total of 4 Dbs, the total size is: | 26.00
(5 rows)
postgres=#
Let’s select the take a look at the biggest tables on my Jira db.
jiradb=# select table_catalog Datatbase, table_schema As Schema, table_name, table_type,
jiradb-# round(pg_total_relation_size(table_schema || '.' || table_name)/1024) as Sch_Tbls_size_MB
jiradb-# from information_schema.tables
jiradb-# WHERE table_schema NOT IN ('information_schema','pg_catalog') and
jiradb-# table_catalog = 'jiradb' and
jiradb-# round(pg_total_relation_size(table_schema || '.' || table_name)/1024) > 0
jiradb-# limit 15;
datatbase | schema | table_name | table_type | sch_tbls_size_mb
-----------+--------+-------------------------+------------+------------------
jiradb | public | jiraaction | BASE TABLE | 40
jiradb | public | cwd_application | BASE TABLE | 48
jiradb | public | avatar | BASE TABLE | 48
jiradb | public | changegroup | BASE TABLE | 16
jiradb | public | changeitem | BASE TABLE | 32
jiradb | public | columnlayout | BASE TABLE | 24
jiradb | public | columnlayoutitem | BASE TABLE | 8
jiradb | public | component | BASE TABLE | 16
jiradb | public | configurationcontext | BASE TABLE | 72
jiradb | public | customfield | BASE TABLE | 16
jiradb | public | customfieldoption | BASE TABLE | 16
jiradb | public | customfieldvalue | BASE TABLE | 24
jiradb | public | cwd_directory | BASE TABLE | 96
jiradb | public | cwd_directory_attribute | BASE TABLE | 32
jiradb | public | cwd_directory_operation | BASE TABLE | 24
(15 rows)
jiradb=#
Let’s create and manage some users:
jiradb=# CREATE ROLE jira LOGIN jiradb=# ENCRYPTED PASSWORD 'md5a627d0b1dd89b994' jiradb=# SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION VALID UNTIL '2012-07-08 00:00:00'; jiradb=# COMMENT ON ROLE jira IS 'Jira_Admin Application'; jiradb=# ALTER ROLE jira WITH ENCRYPTED PASSWORD 'xxxxx'; jiradb=# ALTER ROLE jira VALID UNTIL 'infinity';
We could accomplish the above with this less steps:
jiradb=# CREATE ROLE urjira LOGIN jiradb=# ENCRYPTED PASSWORD 'urxoxoa' jiradb=# SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity'; jiradb=# COMMENT ON ROLE urjira IS 'Jira is another admin user of the Jira App';
Ok, this was fun, you might want to take a quick view to other PostgreSQL Tutorials:
Tutorial to Install and Configure PostgreSQL in OpenSUSE 12 Part1