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/