pg_reorg

In any OLTP 24*7 database there is a constant need to maintain the database to meet performance metrics. As the database and tables grow in size it gets difficult to maintain the tables while production is on. As DBA’s we try to script vacuum analyze on tables during off peak hours and that does update the pg_stats to help in performance. But how do we get rid of all the bloat due to updates and rollbacks? Also how do we rebuild our indexes in such a scenario when downtime is not possible at all?

Well the answer is pg_reorg. pg_reorg re-organizes tables on a postgres database without any locks so that you can retrieve or update rows in tables being reorganized. It places the data of a table in a temp table and then recreates the indexes on the temp table. Thus, leaving the primary production table unlocked for production use. This is a very useful tool that reorganizes the tables in an instant without impacting production. Make sure you have twice the space available for the table that you are performing a pg_reorg on.

pg_reorg can be download from here. They maintain rpm and source installers.

Once you have pg_reorg downloaded follow the steps below to install it:

$ cd pg_reorg
$ make
$ su
$ make install

Next you need to run the following pg_reorg script to register it with your database

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

Now you are all set and let the games begin:

$ pg_reorg --no-order --table test1 -d mydb

This will reorg the test1 table in the database mydb

Advertisements

, , ,

  1. #1 by Creusa on June 14, 2012 - 6:25 am

    nice post! i have read your article and it is really amazing.http://www.paredesdecoradas.org

  2. #2 by Vikram Rai on June 14, 2012 - 2:09 pm

    Thanks for the encouragement. I will write more soon. Stay tuned!!

  3. #3 by autoayuda on June 26, 2012 - 1:42 am

    You are a very capable person!

  4. #5 by Nelson Rivas on July 5, 2013 - 1:52 pm

    Hello Vikram.

    I’m absolutely new in pg_reorg and an unusual user of Linux.

    I have a Postgres database installed on a CentOS. I need to use pg_reorg, accesing from a Windows 7 and using the postgres user to login PuTTY via.

    I downloaded the 1.1.7 version of pg_reorg with the sources and tried to follow the steps you pointed out to install it. But when I’m inside the “pg_reorg” folder and try to run the “make” command, I get an error in Spanish (I’m Paraguayan) that goes:

    make[1]: se ingresa al directorio `/home/postgres/pg_reorg-1.1.7-src/pg_reorg/bin’
    gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -I/usr/local/pgsql/include -I. -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal -D_GNU_SOURCE -c -o pg_reorg.o pg_reorg.c
    make[1]: gcc: No se encontró el programa
    make[1]: *** [pg_reorg.o] Error 127
    make[1]: se sale del directorio `/home/postgres/pg_reorg-1.1.7-src/pg_reorg/bin’
    make: *** [all] Error 2

    Basically it says that didn’t find the program. The Postgres version is 8.1.4. I’ve the read that the pg_reorg works on 8.2 and above.

    How can I solve this problem or what do you recommend me to do?

    Regards.

    Nelson.

    • #6 by Vikram Rai on July 10, 2013 - 8:29 pm

      Hello Nelson,

      Sorry about the super late reply. I was on vacation. About the error from what I can see make is not able to find postgres install location. You may want to set your environment variables for the postgres user to set some of the parameters that would help. pg_config location would be helpful too.

      export PATH=location of postgres binaries
      export LD_LIBRARY_PATH=location of postgres lib
      PGPORT and PGDATA can also be set.

      And yes pgreorg currently requires postgres 8.2 and higher if its the community release or enterprisedb version of postgres 8.1.4 or higher. You may want to take use of the latest versions of postgres. If you choose to upgrade to 9.2 then you would need pg_repack to make use of the pg_reorg functionality.

      -Vikram

      • #7 by Nelson Rivas on August 27, 2013 - 4:02 pm

        Hi Vikram.

        I tried to do what you told me, but I’m still getting the same error.

        I entered the following commands before running the make command:

        $ export PATH=/usr/local/pgsql/bin:$PATH
        $ export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LB_LIBRARY_PATH
        $ export PGPORT=5432
        $ export PGDATA=/usr/local/pgsql/data
        $ export PG_CONFIG=/usr/local/pgsql/bin

        I don’t know if I’m doing it well.

        Regards.

        Nelson.

  5. #8 by Nelson Rivas on August 27, 2013 - 8:05 pm

    Actually, this is what I get:

    pgut/pgut.o: In function `pgut_fopen’:
    pgut.c:(.text+0x885): undefined reference to `strlcpy’
    pgut.c:(.text+0x906): undefined reference to `strlcpy’
    collect2: ld devolvio el estado de salida 1
    make[1]: *** [pg_reorg] Error 1
    make[1]: se sale del directorio `/root/reorg/pg_reorg/bin’
    make: *** [all] Error 2

    Nelson.

    • #9 by Vikram Rai on August 30, 2013 - 6:32 pm

      Nelson,

      Looking at this recent error that you have sent. I wanted to confirm if u have gcc and make installed on that centos? Also is this postgres version installed using source or RPM?

      -Vikram

      • #10 by Nelson Rivas on August 30, 2013 - 9:47 pm

        Vikram, thank you very much for your reply.

        I have both of them installed: gcc (GCC) 4.1.2 and GNU Make 3.81. The postgres version installed is using RPM.
        I thought it was a matter of user, so now I’m logging in using the root user, but the same error appears.

        Nelson.

      • #11 by nrivas on September 1, 2013 - 4:40 am

        Hi Vikram.

        Apparently the problem was the postgres version. I’ve installed a 9.2.4 postgres version on a debian virtual machine and the 1.1.8 pg_reorg version as well and it worked!

        Now, I have a question. I run the following command: “pg_reorg –no-order –table test1 -d mydb”. The command runs normally but no messages appear, is that ok? I have pg_repack too so, do I need to uninstall pg_reorg?

        And again, thank you so much for your help!

        Nelson.

      • #12 by Vikram Rai on September 12, 2013 - 9:03 pm

        If you are running postgres 9.2 then I would suggest looking into pg_repack instead and you can remove pg_reorg. Also, in order to see whats going on when you use pg_repack or reorg you can use -e in that command syntax and it will echo what table its working on with more information. Good luck, let me know how it goes !!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: