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:
1. Execute pg_config of PPAS to find the gcc(location) which used to build binary of Advanced Server, as given below:
CC = /opt/gcc-4.4.4/inst/bin/gcc</pre>
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</pre> 3. Modify the Makefile of pg_reorg/bin with following line: <pre class="cpp">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</pre> 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:
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:  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 ----