Quick How To: Record SQL Results to Text File in PostgreSQL

Once you connected using psql,

# \o  /tmp/myrecord.txt

Then run any SQL queries you want.  They wont be visible on screen But will be recored to /tmp/myrecord.txt

#  SELECT * FROM my_super_dummy_table;

#\q

Moving Postgresql Data Folder

We had a old server which ran out space for partition where postgres was running… we had to move the data folder to a bigger partition… and here is how I did it…

Step 0:  Stop the Postgres if running

$service postgresql stop

Step 1 :  Copy  the whole  data/  folder  to  new parition

say  we have  /usr/local/pgsql/data   and wants to move to /home2/ partition

$cp -R /usr/local/pgsql/data  /home2/postgres/data

Step 2 :  Modify the Startup script to point to correct data directory

In  /etc/init.d/postgresql  file,  change the value of  PGDATA  variable to new location  which is /home2/postgres/data

Step 3 : Start the db server

$ service postgresql start

PostgreSQL Log Analysis using Ruby’s PQA

Lastweek I had to look at a slow postgreSQL server to figure out what queries taking long time and identify bottlenecks. So I thought of trying out Ruby’s Practical Query  Analysis (PQA). So here is the way to install and use it on a RedHat Linux box already running PostgreSQL 7/8.

Step1 : Install Ruby

Download Source ruby-1.8.5.tar.gz

$ tar -xvfz ruby-1.8.5.tar.gz

$ cd ruby-1.8.5

$ configure

$ make

$ make install

Step 2: Install Ruby gems

Download rubygems-0.9.4.tgz

$ tar -xvfz rubygems-0.9.4.tgz

$ cd rubygems-0.9.4

$ ruby setup.rb

Step 3: Install Practical Query Analysis

Download pga gem

$ gem install pqa-1.6.gem

Step 4: Configure PostgreSQL 

In postgresql.conf file, makesure these settings are on

log_statement  true

log_duration   true

log_pid        true

Restart PostgreSQL to make these changes in effect.In my case postgresql is setup as a service to start at boot. so restarting is easy.

service postgresql restart

Step 5:   Use PQA to anlayze log

pqa -file /path/to/your/logfile -normalize -top 5

To generate a HTML Report

pqa -file /path/to/your/logfile -normalize -top 5 -format html > /tmp/myReport.html

Thats it!. You could find slowest queires, most run queries and other cool stuff from this great tool.  you could out details and detailed documentation at http://pgfoundry.org/projects/pqa/

MySql vs Postgres

Wait… Its not one of many arguments over which one is better.  Both are excellent open source database systems  with rich set of features competing commmercial databases. (it look like MySQL has more marketing power than postgres though….) . The point I want to make is how both are different.  I been using Postgresql from almost 5 years.  When we tried to pick between MySQL and Postgres back then, I picked Postgresql as it has support for Foriegn Keys, triggers and stored procedures. So in the last five or so years when people asked me why Postgres over MySQL, I could proudly point to those features MySQL lacked. But NOT anymore!  Looks like MySQL 5 has all these covered. Now it has support for Views, Stored Procedures and Triggers.  ( See http://dev.mysql.com/doc/refman/5.0/en/mysql-5-0-nutshell.html)  More over they added more features from MaxDB (SA- DB from MySQL AB) like Event Scheduler in 5.1 version.  So now its purely personal preference of which one to pick.