Some Handy SQLs for PG DBAs

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'