Saturday, January 7, 2012

Postgresql - Foreign tables. file_fdw wrapper. querying the log file

The foreign table functionality has been around for quite some time in the SQL Standard. A foreign table derives from the concept of External Data Management (SQL/MED) .

SQL/MED is Management of External Data, a part of the SQL standard that deals with how a database management system can integrate data stored outside the database. There are two components in SQL/MED:

Foreign Table
a transparent access method for external data


http://wiki.postgresql.org/wiki/SQL/MED


Another reference:

In 2003, a new extension called SQL/MED ("SQL Management of External Data") was added to the SQL standard. It is a standardized way of handling access to remote objects in SQL databases. In 2011, PostgreSQL 9.1 was released with a great support of this standard.

In a nutshell, you can now use various Foreign Data Wrappers (FDW) to connect a PostgreSQL Server to remote data stores.

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

In this post I'll be using the file_fdw extension:

What does it do?
The file_fdw module provides the foreign-data wrapper file_fdw, which can be used to access data files in the server's file system. Data files must be in a format that can be read by COPY FROM; see COPY for details.

Case scenario. Setup a foreign table to read the server log directly within the database manager.
Postgresql version 9.1.1 on Debian squeeze
Step 0. Install if you haven't done it, the contrib module
root@debian1:~# apt-get install postgresql-contrib-9.1

Step 1. Reconfigure your postgresql.conf file so that logs are generated in a csv format and appropriate rotation of the log is chosen, and reload the configuration.

I though of these parameters (note that a static name for the generated log file is required for obvious reasons).

# - Where to Log -

log_destination = 'csvlog'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on                  # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'                # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql_log' # log file name pattern,
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 5d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.




Step 2. Read through and follow along the example depicted in this link (the only change needed is the path to your logfile):
http://www.postgresql.org/docs/devel/static/file-fdw.html

Step 3. Query the log in whatever fashion you like.
For example:
postgres@debian1:~/data_sr$ psql -p 5432 pgbenchdb
psql (9.1.1)
Type "help" for help.
pgbenchdb=# select log_time, error_severity, message from pglog order by log_time desc;

Output
log_time          | error_severity |                                                         message                                                         
----------------------------+----------------+-------------------------------------------------------------------------------------------------------------------------
 2012-01-07 20:00:43.223-06 | ERROR          | column "logtime" does not exist
 2012-01-07 19:58:57.02-06  | WARNING        | transaction log file "000000020000000000000041" could not be archived: too many failures
 2012-01-07 19:58:57.02-06  | LOG            | archive command failed with exit code 1
 2012-01-07 19:58:56.009-06 | LOG            | archive command failed with exit code 1
 2012-01-07 19:58:54.998-06 | LOG            | archive command failed with exit code 1
 2012-01-07 19:57:54.93-06  | LOG            | archive command failed with exit code 1
 2012-01-07 19:57:54.93-06  | WARNING        | transaction log file "000000020000000000000041" could not be archived: too many failures
 2012-01-07 19:57:53.92-06  | LOG            | archive command failed with exit code 1
 2012-01-07 19:57:52.91-06  | LOG            | archive command failed with exit code 1
 2012-01-07 19:56:52.837-06 | WARNING        | transaction log file "000000020000000000000041" could not be archived: too many failures


On that warning:the transaction log file couldn't be archived, as the target machine where I was sending it to, was down. =-)
Publishing from my fortress of solitude, they gave me back my keys. Yay!!!!

Other references:
http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html
http://www.depesz.com/index.php/2011/03/14/waiting-for-9-1-foreign-data-wrapper/
http://pgxn.org/
http://vibhorkumar.wordpress.com/2011/12/06/postgresql-9-1-new-in-object-manipulation/

No comments:

Post a Comment