PostgreSQL 9.0 - System Administration Functions

PostgreSQL system functions information as follows::

1. getpgusername -- Retrieves the name of the user associated with the current thread

Syntax: select getpgusername();

Ex:-

postgres=# select getpgusername();

getpgusername

---------------

postgres

2. pg_client_encoding -- Retrieves the client encoding of the server

Syntax: select pg_client_encoding();

Ex:-

postgres=# select pg_client_encoding();

pg_client_encoding

--------------------

UTF8

3. pg_reload_conf -- pg_reload_conf sends a SIGHUP signal to the postmaster, causing the configuration files to be reloaded by all server processes.

Syntax: select pg_reload_conf();

Ex:-

Postgres=# Select pg_reload_conf();

pg_reload_conf

----------------

t

(1 row)

4. pg_rotate_logfile -- pg_rotate_logfile signals the log-file manager to switch to a new output file immediately and it works only when logtype is stderr.

Syntax: select pg_ls_dir('<log directory>');

Ex:-

postgres=# select pg_ls_dir('pg_log');

pg_ls_dir

------------------------------------------

postgresql-2010-10-22_221941.log

postgresql-2010-10-23_000000.log

postgresql-2010-10-23_011320.log

postgresql-2010-10-24_000000.log

postgres=# select pg_rotate_logfile();

pg_rotate_logfile

-------------------

t

(1 row)

postgres=# select pg_ls_dir('pg_log');

pg_ls_dir

------------------------------------------

postgresql-2010-10-22_221941.log

postgresql-2010-10-23_000000.log

postgresql-2010-10-23_011320.log

postgresql-2010-10-24_000000.log

postgresql-2010-10-24_135627.log

5. pg_ls_dir -- It shows content of given directory except the special entries “.” and “..”.(it is treated as relative to $PGDATA). It is not possible (or i don’t know how to) to navigate to another directories using this command other than $PGDATA.

Syntax: select pg_ls_dir('<directory name>');

postgres=# select pg_ls_dir('pg_xlog');

pg_ls_dir

--------------------------

000000010000000000000003

000000010000000000000004

000000010000000000000005

archive_status

(4 rows)

6. pg_sleep --It makes the current session’s process sleep until the specified time interval have elapsed.

Syntax: select pg_sleep();

Ex:

postgres=# SELECT CURRENT_TIMESTAMP; SELECT pg_sleep(60); SELECT CURRENT_TIMESTAMP;

now

----------------------------------

2010-10-24 13:51:54.158547+05:30

(1 row)

pg_sleep

----------

(1 row)

now

----------------------------------

2010-10-24 13:52:54.186517+05:30

(1 row)

7. pg_char_to_encoding -- Convert current encoding to another

Ex:

Convert a database in the ASCII format into one of the UTF-8 format

template1=# create database test with encoding='SQL_ASCII';

CREATE DATABASE

template1=# \connect test

You are now connected to database "test".

test=# create table a (x text);

CREATE TABLE

test=# insert into a values ('');

INSERT 33304378 1

test=# select * from a;

x

--

(1 row)

test=# update pg_database set encoding =

pg_catalog.pg_char_to_encoding('UTF8') where datname='test';

UPDATE 1

test=# select * from a;

x

--

(1 row)

test=# \connect template1

You are now connected to database "template1".

template1=# \connect test

You are now connected to database "test".

test=# select * from a;

x

--

(1 row)

8. pg_postmaster_start_time -- Retrieves the postmaster start time

postgres=# select pg_postmaster_start_time();

pg_postmaster_start_time

-------------------------------

2010-09-01 11:39:36.472834-04

(1 row)

9. pg_switch_xlog -- Generates manual switch on the current transaction log.

postgres=# select pg_xlogfile_name(pg_current_xlog_location());

pg_xlogfile_name

--------------------------

000000010000000000000009

(1 row)

postgres=# select pg_switch_xlog();

pg_switch_xlog

----------------

0/A000000

(1 row)

postgres=# select pg_xlogfile_name(pg_current_xlog_insert_location());

pg_xlogfile_name

--------------------------

00000001000000000000000A

(1 row)

10. pg_current_xlog_location -- displays the current transaction log write location

postgres=# select pg_current_xlog_location();

pg_current_xlog_location

--------------------------

0/8000064

(1 row)

11. pg_xlogfile_name -- extract the transaction log file name.

postgres=# select pg_xlogfile_name('0/8000064');

pg_xlogfile_name

--------------------------

000000010000000000000008

(1 row)

12. pg_current_xlog_insert_location -- Retrieves the current transaction log insertion file name.

postgres=# select pg_current_xlog_insert_location();

pg_current_xlog_insert_location

---------------------------------

0/A000064

13. pg_stat_get_numscans -- To view the number of sequential scans occurred on a table

Syntax: select pg_stat_get_numscans(oid);

Ex:

postgres=# select relfilenode,relname from pg_class where relname='test';

relfilenode | relname

-------------+---------

18328 | test

(1 row)

postgres=# select pg_stat_get_numscans(18328);

pg_stat_get_numscans

----------------------

4

(or)

postgres=# select relname,seq_scan from pg_Stat_user_tables;

relname | seq_scan

--------------------+----------

sl_table | 0

sl_setsync | 0

test | 4

14. pg_stat_get_live_tuples -- To view the number of live rows in a table

Syntax: select pg_stat_get_live_tuples(oid);

Ex:

postgres=# select relfilenode,relname from pg_class where relname='test';

relfilenode | relname

-------------+---------

18328 | test

(1 row)

postgres=# select pg_stat_get_live_tuples(18328);

pg_stat_get_live_tuples

-------------------------

10

(1 row)

15. pg_stat_get_dead_tuples -- To view the dead rows in a table.

Syntax: select pg_stat_get_dead_tuples(oid);

postgres=# select pg_stat_get_dead_tuples(18328);

pg_stat_get_dead_tuples

-------------------------

0

(1 row)

16.pg_get_triggerdef -- View the definition of the trigger in the Database.

Syntax: select pg_get_triggerdef(oid);

Ex:-

select pg_get_triggerdef('test.test_trigger'::regtrigger);

17. pg_get_indexdef -- Retrieves the definition of the index.

Syntax: select pg_get_indexdef(oid);

Ex:-

cluster__va_tn=# select pg_get_indexdef('topics_supertaggrouprow_group_id'::regclass);

(or)

cluster__va_tn=# select schemaname,tablename,indexname,indexdef from pg_indexes limit 2;

schemaname | tablename | indexname | indexdef

------------+---------------+--------------------------+--------------------------------------------------------------------------------------

pg_catalog | pg_pltemplate | pg_pltemplate_name_index | CREATE UNIQUE INDEX pg_pltemplate_name_index ON pg_pltemplate USING btree (tmplname)

pg_catalog | pg_tablespace | pg_tablespace_oid_index | CREATE UNIQUE INDEX pg_tablespace_oid_index ON pg_tablespace USING btree (oid)

18. pg_tablespace_database -- List set of database oids that have objects in the namespace

Syntax:select pg_tablespace_database(oid);

19. pg_conf_load_time -- Find the exact time when the PostgreSQL configuration files were last loaded.

Syntax: select pg_conf_load_time();

Ex:-

postgres=# select pg_conf_load_time();

pg_conf_load_time

----------------------------------

2010-10-26 19:40:14.119542+05:30

20. pg_get_keywords -- List parser keywords in the Database.

Syntax: select pg_get_keywords();

postgres=# select pg_get_keywords();

pg_get_keywords

------------------------------------------------------------------

(abort,U,unreserved)

(absolute,U,unreserved)

(access,U,unreserved)

(action,U,unreserved)

21. pg_relation_size -- Size of relation without index.

Syntax: select pg_relation_size(oid);

postgres=# select pg_relation_size('test_slon'::regclass);

pg_relation_size

------------------

8192

22. pg_total_relation_size -- Find the size of relation with indexes

Syntax: select pg_total_relation_size(oid);

postgres=# select pg_total_relation_size('test_slon'::regclass);

pg_total_relation_size

------------------------

49152

23. pg_lock_status -- View the lock status on the database

Syntax: select pg_lock_status();

Ex:

postgres=# select pg_lock_status();

pg_lock_status

----------------------------------------------------------

(virtualxid,,,,,1/2832,,,,,1/2832,17996,ExclusiveLock,t)

24. pg_prepared_xact -- View the prepared transaction in the Database

Syntax: select pg_prepared_xact();

Ex:

postgres=# select pg_prepared_xact();

pg_prepared_xact

------------------

(0 rows)

25. pg_prepared_statement -- View the prepared statements in the Database

Syntax: select pg_prepared_statement();

Ex:-

postgres=# select pg_prepared_statement();

pg_prepared_statement

-----------------------

(0 rows)

26. pg_cursor -- List the cursors in the Database

Syntax: select pg_cursor();

Ex:-

postgres=# select pg_cursor();

pg_cursor

-----------

(0 rows)

27. pg_show_all_settings -- List the configuration parameter values in postgresql.conf file in Database.

Syntax: select pg_show_all_settings();

28. pg_database_size -- Find the size of the database ( value returns in bytes)

Syntax: select pg_database_size(datname);

Ex:-

postgres=# select pg_database_size('dellstore');

pg_database_size

------------------

24707076

To get human readable format use the below system function

postgres=# select pg_size_pretty(pg_database_size('dellstore'));

pg_size_pretty

----------------

24 MB

29. current_database --View the current database

Syntax: select current_database();

postgres=# select current_database();

current_database

------------------

postgres

30. current_schema -- View the current schema

Syntax: select current_schema();

postgres=# select current_schema();

current_schema

----------------

public

31. pg_stat_get_tuples_deleted -- View the deleted rows in a Table

Syntax: select pg_stat_get_tuples_deleted(oid);

Ex:-

postgres=# select pg_stat_get_tuples_deleted('test1'::regclass);

pg_stat_get_tuples_deleted

----------------------------

4

32. pg_stat_get_tuples_updated -- Find the number of rows updated on the Table

Syntax: select pg_stat_get_tuples_updated(oid)

postgres=# select pg_stat_get_tuples_updated('test1'::regclass);

pg_stat_get_tuples_updated

----------------------------

1

33. pg_stat_get_tuples_inserted -- Find the number of rows inserted on the Table

Syntax: select pg_stat_get_tuples_inserted(oid);

postgres=# select pg_stat_get_tuples_inserted('test1'::regclass);

pg_stat_get_tuples_inserted

-----------------------------

8

34. pg_stat_get_last_analyze_time -- View the last analyze time on a table

Syntax: select pg_stat_get_last_analyze_time(oid);

Ex:-

postgres=# select pg_stat_get_last_analyze_time('test1'::regclass);

pg_stat_get_last_analyze_time

----------------------------------

2010-10-29 00:29:55.935538+05:30

35. pg_stat_get_last_vacuum_time -- View the last vacuum time on a table

Syntax: select pg_stat_get_last_vacuum_time(oid);

Ex:-

postgres=# select pg_stat_get_last_vacuum_time('test1'::regclass);

pg_stat_get_last_vacuum_time

----------------------------------

2010-10-29 00:35:42.563424+05:30

Creation Date of PostgreSQL Database

Currently, Database creation date information is not stored in any one of the catalog tables.

There are two way's to find out::

1. Log the Create Database command to Database server log file

psql=# create database systest_live;

2. Get the time-stamp of the Database creation from Operating system and use the below shell script:

#!/bin/sh

# Identifying the Database Creation Date

# ./DB_Creationdate.sh $databasename

PGDATA=/export/raid/pgsql/data

PGHOME=/usr/local/bin

oid=$($PGHOME/psql -d $1 -t -c "select oid from pg_database where datname='systest_live';"|sed '/^$/d'|sed 's/^ //g') 

cmd="ls -ltr $PGDATA/base/$oid/PG_VERSION"

Date=$($cmd|awk '{print $6" "$7" "$8}')

echo "$1" Database Creation Date is :: $Date

PostgreSQL Statistical Information using pg_catalogs

Hi All,

As a PostgreSQL DBA, my major work was retrieving the database information using pg_catalogs. You all know, pg_catalogs in PostgreSQL has a very good informative function,view etc., So, I have come up with few queries which will give a statistical information at first, when you get a hands on to the DB box. I am enclosing few queries which will help you in getting very good information on the PostgreSQL Database Server.

1. Find the number of live rows vs dead rows of a table::

select relname         as "Table",

       n_live_tup      as "Live tuples",

       n_dead_tup      as "Dead tuples"

from pg_stat_user_tables

where relname = 'my_table';

2. Finding the read activity statistics of tables in a database::

select schemaname  as "Schema Name" ,

          relname           as "Table Name",

          seq_tup_read+idx_tup_fetch as " no.of reads”

from pg_stat_all_tables

order by seq_tup_read+idx_tup_fetch;

 

3. Finding the write activity statistics of tables in a database::

select schemaname  as " Schema Name",

          relname           as "Table Name”,

          n_tup_ins+n_tup_upd+n_tup_del  as "no.of writes"

from pg_stat_all_tables

order by n_tup_ins+n_tup_upd+n_tup_del;

4. Find out the I/O statistics of a table::

select relname         as "Table",

       heap_blks_read  as "Heap from disc",

       heap_blks_hit   as "Heap from cache",

       idx_blks_read   as "Index from disc",

       idx_blks_hit    as "Index from cache",

       toast_blks_read as "Toast from disc",

       toast_blks_hit  as "Toast from cache",

       tidx_blks_read  as "Toast index disc",

       tidx_blks_hit   as "Toast index cache"

from pg_statio_user_tables

where relname = 'my_table';

5. Find the size of all databases in a cluster::

select datname  as  "Database name",

          pg_size_pretty(pg_database_size(datname)) as "Size of each database"

from pg_database;

6. Find out the index statistics in a database::

select relname  as  "Tablename",

          indexrelname as  "Index name",

          idx_scan   as "Index lookups",

          idx_tup_read  as "Index entries",

          idx_tup_fetch as "Tuples fetched via index"

from pg_stat_user_indexes

order by relname,indexrelname;

7. Finding the lock statistics in a database::

select pid   as "Process id”,

          mode,

          current_query as " Sql query"

from pg_locks,pg_stat_activity

where granted=false and locktype='transactionid' and pid=procpid order by pid,granted;

8. Finding the sizes of indexes in a database::

select  relname as "Table name",

           indexrelname  as " Index name",

          pg_size_pretty( pg_relation_size( indexrelid ) ) as "Size"

from pg_stat_all_indexes

where schemaname = 'public' and relname='users'

order by pg_relation_size( indexrelid ) desc;

9. Finding the Database statistics in a cluster::

Select    datname             as "Database",

              numbackends      as "Backends",

              xact_commit        as "Xact Committed",

              xact_rollback       as "Xact Rolled Back",

              blks_read            as "Blocks Read",

              blks_hit               as "Blocks Hit",

(pg_database_size(datid) / 1024)::int as "Size (KB)"

FROM pg_stat_database db

WHERE UPPER(db.datname) != 'TEMPLATE0' AND UPPER(db.datname) != 'TEMPLATE1'

ORDER BY "Database";

10. Finding the number of users connected to each Database in a cluster::

SELECT COUNT(datid) as "Count",

               datconnlimit "Connection Limit per Database",

               d.datname as "Database Name"

FROM pg_database d LEFT JOIN pg_stat_activity s ON (s.datid = d.oid)  GROUP BY 2,3 ORDER BY d.datname;

11. Size of each table inside the Database on PostgreSQL 8.4 on-words::

SELECT  tablename as " Table Name",

              pg_size_pretty(pg_total_relation_size(CAST(tablename AS TEXT))) as “Total size of table",

pg_size_pretty((pg_total_relation_size(CAST(tablename AS TEXT))  - pg_relation_size(CAST(tablename AS

TEXT))))  as "Index size”

FROM pg_tables 

WHERE schemaname != 'pg_catalog'  AND schemaname != 'information_schema'  ORDER BY pg_total_relation_size(CAST(tablename AS TEXT));

12.List of tables in a Database::

SELECT relname as  "Table Name”

     FROM pg_class

     WHERE relname !~ '^(pg_|sql_)' AND relkind = 'r';

13.List of sequences::

SELECT relname  as  " Sequence Name”

FROM pg_class

WHERE relkind = 'S' AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema' );

14. List Triggers of a table::

SELECT trg.tgname as "Trigger_name"

FROM pg_trigger trg, pg_class tbl

WHERE trg.tgrelid = tbl.oid AND tbl.relname = 'newtable';

15.Which tables are being updated the most and looking for vacuum ::

select relname, /* pg_size_pretty( pg_relation_size( relid ) ) as table_size,

                        pg_size_pretty( pg_total_relation_size( relid ) ) as table_total_size, */

                        n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum::date, last_autovacuum::date, last_analyze::date, last_autoanalyze::date

from pg_stat_all_tables

where relid in (select oid from pg_class

                       where relnamespace not in

                       (select oid from pg_namespace

                         where nspname in ('information_schema', 'pg_catalog','pg_toast', 'edbhc' ) ) )

order by n_tup_upd desc, schemaname, relname;

16. How to get the indexes scanned less than 200 times and is not unique::

SELECT idstat.relname AS table_name,

     indexrelname AS index_name,

     idstat.idx_scan AS times_used,

pg_size_pretty(pg_relation_size(idstat.relname)) AS table_size,       pg_size_pretty(pg_relation_size(indexrelname)) AS index_size,

n_tup_upd + n_tup_ins + n_tup_del as num_writes, indexdef AS definition

FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname

JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname

WHERE idstat.idx_scan < 200

AND indexdef !~* 'unique'

ORDER BY idstat.relname, indexrelname;

17. Size of each schema in a Database::

SELECT nspname, sum(relpages * cast( 8192 AS bigint )) as "table size", sum( ( select sum(relpages) from pg_class i, pg_index idx where i.oid = idx.indexrelid and t.oid=idx.indrelid ) ) * cast( 8192 AS bigint ) as "index size", sum ( relpages * cast( 8192 AS bigint ) + ( select sum(relpages) from pg_class i, pg_index idx where i.oid = idx.indexrelid and t.oid=idx.indrelid ) * cast( 8192 AS bigint ) ) as "size" FROM pg_class t, pg_namespace WHERE relnamespace = pg_namespace.oid and pg_namespace.nspname not like 'pg_%' and pg_namespace.nspname != 'information_schema' and relkind = 'r' group by nspname;

18. Find out how many pages and tuples are used by a table::

select relname     as "table",

       reltuples       as "number of tuples",

       relpages       as "number of 8kb pages"

  from pg_class

 where relname = 'table';

19.  Find out the Primary key tables in a database::

SELECT tablename FROM pg_tables WHERE tablename IN? (SELECT r.relname FROM pg_class r, pg_constraint c WHERE r.oid = c.conrelid AND c.contype = 'p') AND schemaname = 'public';

20. Which user PID is locking on which transaction process::

select bl.pid as blocked_pid, a.usename as blocked_user,kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement from pg_catalog.pg_locks bl

     join pg_catalog.pg_stat_activity a on bl.pid = a.procpid

     join pg_catalog.pg_locks kl

     join pg_catalog.pg_stat_activity ka on kl.pid = ka.procpid on bl.transactionid = kl.transactionid and bl.pid != kl.pid

     where not bl.granted;

21. Checking the user permissions::

select relname as "Relation", relacl as "Access permissions" FROM pg_class

where  relkind IN ('r', 'v', 'S') AND relname !~ '^pg_' ORDER BY relname;

Will be coming back with some more information posting on PostgreSQL real time informations. Please feel free to post your comments..

Regards

Raghu

http://raghurc.blogspot.com

raghurc.postgresql@gmail.com

Skype ID: raghurc