Skip site navigation (1) Skip section navigation (2)

Logging just SQL commands in an SQL script format

From: "Campbell, Lance" <lance(at)uiuc(dot)edu>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Logging just SQL commands in an SQL script format
Date: 2007-01-24 20:45:26
Message-ID: A3AC4FA47DC0B1458C3E5396E685E63302395C55@SAB-DC1.sab.uiuc.edu (view raw or flat)
Thread:
Lists: pgsql-admin
I want to log all SQL commands as an executable script file for use in
performance testing.  Currently there is a lot of information in the log
that is generated that would not be executable.  Below is an example of
what I would want to see; which is just the SQL.

 

Example of log output:

Select a,b,c from calendar where a=12;

Select a,b,c from calendar where a=13;

 

Example:

1) I would take a snap shot of our production database.  

2) I would then turn on SQL logging.  

3) I would put the copy of the database onto a test server.  

4) After a few days of logging the SQL commands I would copy the file to
the test server.

5) I would then have a basic script that would display the start time,
execute the postgres SQL log (pgsql -d database_name -f
sql_log_file_name), and then display the end time.

 

Now I can test configuration changes and index modifications on real
world data to see what impact the changes would have.  I just keep
reloading the database, change the configurations settings, and then
execute the script again.

 

The key is that the SQL log be in a format that can be executed as a
postgres SQL file.

 

How do I do this?

 

Thanks,

 

 

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

 

Responses

pgsql-admin by date

Next:From: Ryan UnderwoodDate: 2007-01-24 20:52:30
Subject: kerberos 5: wrong principal
Previous:From: Bruno Wolff IIIDate: 2007-01-24 20:26:36
Subject: Re: Print command equivalent in postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group