Database Technologies

In PostgreSQL 9.2, community has added a function pg_xlog_location_diff(), which is very useful for finding the difference between two xlog location in bytes and also useful for monitoring replication. Detail of this function is given in following link: http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP However this function is not available for users of PostgreSQL/PPAS 9.0/9.1 users. So, I thought to write same function plpgsql so, that users can take benefit of same in 9.0/9.1. Before using formula and developing function, lets understand what is xlog and offset. Let's consider user has used function pg_current_xlog_location() function and he gets following information: worktest=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 1/D1012B80 (1 row) In above, first field before forward slash is the hexadecimal value of logical xlog file and second field i.e. D1012B80 is hexadecimal offset inside ...

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...