Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D67229FB487 for ; Thu, 25 Jan 2007 10:56:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 14660-09 for ; Thu, 25 Jan 2007 10:56:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.230]) by postgresql.org (Postfix) with ESMTP id 752569FB20B for ; Thu, 25 Jan 2007 10:56:09 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id i21so424834wra for ; Thu, 25 Jan 2007 06:56:02 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=JV03K5QTv26L7dSdlNKJw3PHc3qgLQO7dm7pnUtdpfQJA5Te9VQ7CubjKdRV3dmr36627PD1ralXw+iZ0vCddJiAL8DaMz0MLTemuXPi1895SgHwQ4ojJNy6A+b2MbCFASEIKwG5umG6e9PWokNBNWnyUdUzr/FzYatJndx79xE= Received: by 10.78.201.10 with SMTP id y10mr1709899huf.1169736960927; Thu, 25 Jan 2007 06:56:00 -0800 (PST) Received: by 10.78.192.12 with HTTP; Thu, 25 Jan 2007 06:56:00 -0800 (PST) Message-ID: <48d0cacb0701250656r3af8e26bvf70274a823205e78@mail.gmail.com> Date: Thu, 25 Jan 2007 12:56:00 -0200 From: "Daniel Cristian Cruz" Subject: Re: Logging just SQL commands in an SQL script format Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/244 X-Sequence-Number: 24400 Look at pgfouine. It can extract SQL commands from log, and build a xml file to be used with tsung to generate some load testing. Kind regards... On 1/24/07, Campbell, Lance wrote: > > > > > 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 w= hat > I would want to see; which is just the SQL. > > > > Example of log output: > > Select a,b,c from calendar where a=3D12; > > Select a,b,c from calendar where a=3D13; > > > > 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 =96d database_name =96f sql_log_file_= name), > and then display the end time. > > > > Now I can test configuration changes and index modifications on real worl= d > data to see what impact the changes would have. I just keep reloading th= e > 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 > > --=20 Daniel Cristian Cruz Analista de Sistemas Especialista postgreSQL e Linux Instrutor Certificado Mandriva