For using these query DBA has to install the adminpack contrib module of PostgreSQL. If you are using one-click installer then user can find the adminpack.sql in /PostgreSQL Installation Directory/share/postgresql/contrib directory.

Following are some SQLs Based on adminpack contrib module DBA can use to find the last logfile, size of the logfile and can ready using PG Client applications (psql,pgAdmin etc.)

SELECT * FROM ( SELECT pg_ls_dir('pg_log')) AS t (filename)ORDER BY 1 DESC LIMIT 1   
  
SELECT size FROM pg_stat_file('pg_log/postgresql-2010-05-13.log')  
  
SELECT pg_read_file('pg_log/postgresql-2010-05-13.log', 0, 21604)

Sometimes, user doesn’t have the access to the Server and for reading the PG logfile user has to login on the server. Now they don;t have to. If you have adminpack.sql installed in PostgreSQL user can easily read the last logfile by creating following function:

CREATE OR REPLACE FUNCTION pg_read_last_logfile() returns text  
AS  
$$  
   DECLARE  
     fname text;  
     fsize integer;  
   BEGIN  
        SELECT filename into fname FROM ( SELECT pg_ls_dir('pg_log')) AS tmp (filename)ORDER BY 1 DESC LIMIT 1; -- to get the latest filename  
        fname='pg_log/'||fname;  
        SELECT size into fsize FROM pg_stat_file(fname); -- to get the size of the latest filename  
   RETURN pg_read_file(fname, 0, fsize);  
  END;  
$$ language plpgsql; 

Output is given below:

postgres=# select pg_read_last_logfile();  
                                                                                                                                                                                                                                                                                                                   pg_read_last_logfile                                                                                                                                                                                                                                                                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  2010-11-28 02:16:35 IST 3965   DBMESSAGELOG:  database system was shut down at 2010-11-24 03:31:24 IST  
  2010-11-28 02:16:35 IST 3968   DBMESSAGELOG:  autovacuum launcher started  
  2010-11-28 02:16:35 IST 3959   DBMESSAGELOG:  database system is ready to accept connections  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  statement: /* $PostgreSQL: pgsql/contrib/adminpack/adminpack.sql.in,v 1.6 2007/11/13 04:24:27 momjian Exp $ */  
         /* *********************************************** 
          * Administrative functions for PostgreSQL 
          * *********************************************** */  
         /* generic file access functions */  
         CREATE OR REPLACE FUNCTION pg_catalog.pg_file_write(text, text, bool)  
         RETURNS bigint  
         AS '$libdir/adminpack', 'pg_file_write'  
         LANGUAGE C VOLATILE STRICT;  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  duration: 332.562 ms  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  statement: CREATE OR REPLACE FUNCTION pg_catalog.pg_file_rename(text, text, text)  
         RETURNS bool  
         AS '$libdir/adminpack', 'pg_file_rename'  
         LANGUAGE C VOLATILE;  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  duration: 1.004 ms  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  statement: CREATE OR REPLACE FUNCTION pg_catalog.pg_file_rename(text, text)  
         RETURNS bool  
         AS 'SELECT pg_catalog.pg_file_rename($1, $2, NULL::pg_catalog.text);'  
         LANGUAGE SQL VOLATILE STRICT;  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  duration: 2.472 ms  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  statement: CREATE OR REPLACE FUNCTION pg_catalog.pg_file_unlink(text)  
         RETURNS bool  
         AS '$libdir/adminpack', 'pg_file_unlink'  
         LANGUAGE C VOLATILE STRICT;  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  duration: 0.575 ms  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  statement: CREATE OR REPLACE FUNCTION pg_catalog.pg_logdir_ls()  
         RETURNS setof record  
         AS '$libdir/adminpack', 'pg_logdir_ls'  
         LANGUAGE C VOLATILE STRICT;  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  duration: 41.991 ms  
  2010-11-28 02:16:40 IST 3973 postgres postgres DBMESSAGELOG:  statement: /* Renaming of existing backend functions for pgAdmin compatibility */  
         CREATE OR REPLACE FUNCTION pg_catalog.pg_file_read(text, bigint, bigint)  

Some times DBA has to re-verify the Parameters and they stick to “show all” command or if they know the parameter name, then they can use show parametername command.

Best part of PostgreSQL is that it has catogerized parameters. To view the parameter category-wise, DBA can use following SQL:

SELECT name, setting, unit, short_desc, extra_desc, boot_val, reset_val, sourcefile, sourceline, context, vartype, source, min_val, max_val FROM pg_settings WHERE category='Version and Platform Compatibility / Previous PostgreSQL Versions' ORDER BY name;  

where category of postgresql.conf are following:

Write-Ahead Log / Settings  
Client Connection Defaults / Locale and Formatting  
Version and Platform Compatibility / Other Platforms and Clients  
Query Tuning / Genetic Query Optimizer  
Resource Usage / Memory  
Statistics / Monitoring  
Reporting and Logging / Where to Log  
Resource Usage / Kernel Resources  
Preset Options  
Reporting and Logging  
Resource Usage  
Reporting and Logging / What to Log.  
Lock Management  
Connections and Authentication / Security and Authentication  
Autovacuum  
Write-Ahead Log / Checkpoints  
Query Tuning / Planner Method Configuration  
Statistics / Query and Index Statistics Collector  
Developer Options  
Write-Ahead Log / Replication  
Customized Options.  
Query Tuning / Other Planner Options  
File Locations  
Client Connection Defaults / Statement Behavior  
Reporting and Logging / When to Log  
Query Tuning / Planner Cost Constants  
Client Connection Defaults / Other Defaults  
Version and Platform Compatibility / Previous PostgreSQL Versions.  

Following are some more handy PG SQLs:
To get the detail information of Database User can use following command:

SELECT datname as Database, pg_get_userbyid(datdba) AS dba, pg_catalog.pg_encoding_to_char(encoding) AS encoding, datcollate as Collate, datctype as CTYPE, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, spcname as tablespace, pg_size_pretty(pg_database_size(datname)) AS size, datacl, age(datfrozenxid) AS freezeage, ROUND(100*(age(datfrozenxid)/freez::float)) AS perc FROM pg_database, pg_tablespace JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS param ON (true) WHERE dattablespace = pg_tablespace.oid ORDER BY datname  

To get the details of Non Idle Processes:

SELECT extract(epoch FROM (now() - query_start))::numeric(10,2) AS age, procpid, usename, client_addr, application_name, current_query FROM pg_stat_activity WHERE current_query <> '' ORDER BY 1  

Cursors in Use:

SELECT name, statement, is_holdable, is_binary, is_scrollable, creation_time FROM pg_cursors ORDER BY name  

To get the Prepared Statements information:

SELECT name, statement, prepare_time, from_sql FROM pg_prepared_statements ORDER BY name  

Prepare Transactions Query:

SELECT transaction, gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY owner, database  

All Locks Information of a Database:

SELECT locktype, CASE WHEN datname IS NOT NULL THEN datname ELSE database::text END AS database, nspname, relname, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted FROM pg_locks LEFT JOIN pg_database ON pg_database.oid = database LEFT JOIN pg_class ON pg_class.oid = relation LEFT JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace  

Get Exclusive Lock Information:

SELECT locktype, CASE WHEN datname IS NOT NULL THEN datname ELSE database::text END AS database, nspname, relname, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, granted FROM pg_locks LEFT JOIN pg_database ON pg_database.oid = database LEFT JOIN pg_class ON pg_class.oid = relation LEFT JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE mode='ExclusiveLock' AND locktype NOT IN ('virtualxid', 'transactionid' 

2 Comments

  1. Quite useful. One more from me, to know which PID is locking on Which transaction process. Here is the query:

    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;
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s