Compiling pg_reorg with Advanced Server 8.4AS

pg_reorg is a utility which I had mentioned in my previous Blog.

This utility can be use for :
1. Online Reorganising of Tables
2. Online VACUUM FULL (i.e removing bloats) (if table is having primary key)
3. Online Clustering of tables

Since, in production environment, we cannot perform VACUUM FULL/CLUSTER, because it locks the table, therefore DBAs always need a way which they can use to perform maintenance activity without locking. So, I thought to use pg_reorg and compile it against Advanced Server.

Advanced Server is a prebuilt binary, so we cannot compile any module or tool with it, without modifying the makefile.

Following are some steps, if someone wants can try, to compile pg_reorg with Advanced Server.
Donwload pg_reorg utility from following location:
http://pgfoundry.org/frs/?group_id=1000411&release_id=1721

1. Execute pg_config of PPAS to find the gcc(location) which used to build binary of Advanced Server, as given below:

pg_config |grep "CC ="
CC = /opt/gcc-4.4.4/inst/bin/gcc

2. Next, user has to create a soft link of gcc installed on their machine as I have done on my local machine:

mkdir -p /opt/gcc-4.4.4/inst/bin/
ln -s /usr/bin/gcc /opt/gcc-4.4.4/inst/bin/gcc

3. Modify the Makefile of pg_reorg/bin with following line:

LIBS := $(filter-out -lxml2 -lmemcached -ledit, $(LIBS))
LIBS := $(filter-out -lxslt -lmemcached -ledit, $(LIBS))

If someone want, they can use following Makefile, which I have already modified:

#
# pg_reorg: bin/Makefile
#
#    Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
#
SRCS = pg_reorg.c pgut/pgut.c pgut/pgut-fe.c
OBJS = $(SRCS:.c=.o)
PROGRAM = pg_reorg
REGRESS = init reorg

ifdef DEBUG_REORG
PG_CPPFLAGS = -I$(libpq_srcdir) -DDEBUG_REORG
else
PG_CPPFLAGS = -I$(libpq_srcdir)
endif
PG_LIBS = $(libpq)

ifdef USE_PGXS
PGXS := $(shell pg_config --pgxs)
include $(PGXS)
else
subdir = contrib/pg_reorg
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

# remove dependency to libxml2 and libxslt
LIBS := $(filter-out -lxml2 -lmemcached -ledit, $(LIBS)) # Modified by Vibhor to remove dependency of memcached and edit
LIBS := $(filter-out -lxslt -lmemcached -ledit, $(LIBS)) # Modified by Vibhor to remove dependency of memcached and edit

Now, you are ready to compile the pg_reorg.

Compile the pg_reorg as given below:

cd /home/edb/pg_reorg-1.1.5
make USE_PGXS=1 install

Please note: before executing above command, user has to include the 8.4AS bin directory in PATH environment variable as given below:

export PATH=/opt/PostgresPlus/8.4AS/bin:$PATH

After compiling the pg_reorg, user would be able to use pg_reorg to do the important maintenance activity like, removing bloats and Clustering database without locking it.
To use the pg_reorg, user has to create necessary functions/schema in database, which will be use by pg_reorg.

 psql -f $PGSHARE/contrib/pg_reorg.sql your_database

Following is an example of performing online VACUUM FULL on table t1:

[edb@localhost pg_reorg-1.1.5]$ pg_reorg -E DEBUG -n -t enterprisedb.t1
DEBUG: ---- reorg_one_table ----
DEBUG: target_name    : enterprisedb.t1
DEBUG: target_oid     : 50760
DEBUG: target_toast   : 0
DEBUG: target_tidx    : 0
DEBUG: pkid           : 50763
DEBUG: ckid           : 0
DEBUG: create_pktype  : CREATE TYPE reorg.pk_50760 AS (id integer)
DEBUG: create_log     : CREATE TABLE reorg.log_50760 (id bigserial PRIMARY KEY, pk reorg.pk_50760, row enterprisedb.t1)
DEBUG: create_trigger : CREATE TRIGGER z_reorg_trigger BEFORE INSERT OR DELETE OR UPDATE ON enterprisedb.t1 FOR EACH ROW EXECUTE PROCEDURE reorg.reorg_trigger('INSERT INTO reorg.log_50760(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::reorg.pk_50760) END, $2)')
DEBUG: create_table   : CREATE TABLE reorg.table_50760 WITH (oids=false) TABLESPACE pg_default AS SELECT * FROM ONLY enterprisedb.t1
DEBUG: delete_log     : DELETE FROM reorg.log_50760
DEBUG: lock_table     : LOCK TABLE enterprisedb.t1 IN ACCESS EXCLUSIVE MODE
DEBUG: sql_peek       : SELECT * FROM reorg.log_50760 ORDER BY id LIMIT $1
DEBUG: sql_insert     : INSERT INTO reorg.table_50760 VALUES ($1.*)
DEBUG: sql_delete     : DELETE FROM reorg.table_50760 WHERE (id) = ($1.id)
DEBUG: sql_update     : UPDATE reorg.table_50760 SET (id) = ($2.id) WHERE (id) = ($1.id)
DEBUG: sql_pop        : DELETE FROM reorg.log_50760 WHERE id <= $1
DEBUG: ---- setup ----
DEBUG: ---- copy tuples ----
DEBUG: ---- create indexes ----
DEBUG: [0]
DEBUG: target_oid   : 50763
DEBUG: create_index : CREATE UNIQUE INDEX index_50763 ON reorg.table_50760 USING btree (id)
DEBUG: ---- swap ----
DEBUG: ---- drop ----
DEBUG: ---- analyze ----