This blog is about compiling pg_repack extension on binary format of PostgreSQL installation. Most of you know, pg_repack extension is one of well known PostgreSQL extensions, its especially used for reclaiming space[bloats] ONLINE without holding an EXCLUSIVE LOCK on Tables/Indexes.
To enable pg_repack extension in PostgreSQL database, it should be compiled from sources. Its quite easy and simple to compile from source on any installed variants(source,rpm,binary) of PostgreSQL, however its slightly different if it is with binary format of PostgreSQL [One Click Installer] as they are pre-built binary bundle with dependency libraries. Let's compile and see.
On CentOS 7 Virtual Machine, I have installed binary format of PostgreSQL 9.4(download link) and its home directory "/opt/PostgreSQL/9.4/". Next we need to download pg_repack source from their official site.
Thank you.
--Raghav
To enable pg_repack extension in PostgreSQL database, it should be compiled from sources. Its quite easy and simple to compile from source on any installed variants(source,rpm,binary) of PostgreSQL, however its slightly different if it is with binary format of PostgreSQL [One Click Installer] as they are pre-built binary bundle with dependency libraries. Let's compile and see.
On CentOS 7 Virtual Machine, I have installed binary format of PostgreSQL 9.4(download link) and its home directory "/opt/PostgreSQL/9.4/". Next we need to download pg_repack source from their official site.
[root@localhost ~]# git clone https://github.com/reorg/pg_repack.gitBefore compiling, pg_config of PostgreSQL 9.4 should be set in the PATH.
[root@localhost pg_repack]# export PATH=/opt/PostgreSQL/9.4/bin:$PATH [root@localhost pg_repack]# type pg_config pg_config is /opt/PostgreSQL/9.4/bin/pg_configNow we are good to execute source installation commands "make" & "make install". Let's execute "make"
[root@localhost ~]# cd pg_repack/ [root@localhost pg_repack]# make make[1]: Entering directory `/root/pg_repack/bin' .... .... gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 pg_repack.o pgut/pgut.o pgut/pgut-fe.o -L/opt/PostgreSQL/9.4/lib -lpq -L/opt/PostgreSQL/9.4/lib -L/opt/local/Current/lib -Wl,--as-needed -Wl,-rpath,'/opt/PostgreSQL/9.4/lib',--enable-new-dtags -lpgcommon -lpgport -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm -o pg_repack /usr/bin/ld: cannot find -ledit collect2: ld returned 1 exit status make[1]: *** [pg_repack] Error 1 make[1]: Leaving directory `/root/pg_repack/bin' make: *** [all] Error 2Oops 1...!!, seems there's an error related to -ledit(libedit) library missing in PostgreSQL lib directory. Lets run same command "ld -ledit" in verbose mode (-verbose) for more information, tried and failed by "make" command.
[root@localhost pg_repack]# ld -ledit -verbose GNU ld version 2.20.51.0.2-5.42.el6 20100205 ... ... ... ================================================== attempt to open /usr/x86_64-redhat-linux/lib64/libedit.so failed attempt to open /usr/x86_64-redhat-linux/lib64/libedit.a failed attempt to open /usr/local/lib64/libedit.so failed attempt to open /usr/local/lib64/libedit.a failed attempt to open /lib64/libedit.so failed attempt to open /lib64/libedit.a failed attempt to open /usr/lib64/libedit.so failed attempt to open /usr/lib64/libedit.a failed attempt to open /usr/x86_64-redhat-linux/lib/libedit.so failed attempt to open /usr/x86_64-redhat-linux/lib/libedit.a failed attempt to open /usr/lib64/libedit.so failed attempt to open /usr/lib64/libedit.a failed attempt to open /usr/local/lib/libedit.so failed attempt to open /usr/local/lib/libedit.a failed attempt to open /lib/libedit.so failed attempt to open /lib/libedit.a failed attempt to open /usr/lib/libedit.so failed attempt to open /usr/lib/libedit.a failed ld: cannot find -leditOkay, now its clear that its looking for libedit.so library in PostgreSQL lib directory [/opt/PostgreSQL/9.4/lib]. Lets check for library in that directory.
[root@localhost pg_repack]# cd /opt/PostgreSQL/9.4/lib [root@localhost lib]# ls -l libedit* -rwxr-xr-x. 1 root daemon 254702 Mar 22 23:32 libedit.so.0Ah,we have "libedit.so.0" but not "libedit.so" required by "make" command. Creating a symbolic link should be a quick fix.
[root@localhost lib]# ln -s libedit.so.0 libedit.so [root@localhost lib]# ls -l libedit* lrwxrwxrwx. 1 root root 12 May 19 22:25 libedit.so -> libedit.so.0 -rwxr-xr-x. 1 root daemon 254702 Mar 22 23:32 libedit.so.0Re-run "make" command.
[root@localhost pg_repack]# make make[1]: Entering directory `/root/pg_repack/bin' gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 pg_repack.o pgut/pgut.o pgut/pgut-fe.o -L/opt/PostgreSQL/9.4/lib -lpq -L/opt/PostgreSQL/9.4/lib -L/opt/local/Current/lib -Wl,--as-needed -Wl,-rpath,'/opt/PostgreSQL/9.4/lib',--enable-new-dtags -lpgcommon -lpgport -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm -o pg_repack /usr/bin/ld: warning: libssl.so.1.0.0, needed by /opt/PostgreSQL/9.4/lib/libpq.so, may conflict with libssl.so.10 /lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp' collect2: ld returned 1 exit status make[1]: *** [pg_repack] Error 1 make[1]: Leaving directory `/root/pg_repack/bin' make: *** [all] Error 2Oops 2...!!! interesting, it has passed -ledit error and now its reporting on libldap library [ libldap_r-2.4.so.2 ]. Am not sure why its looking in /lib64 directory when my pg_config points to /opt/PostgreSQL/9.4/lib. Lets check what we have in both the location.
[root@localhost pg_repack]# ls -l /lib64/libldap* lrwxrwxrwx. 1 root root 21 Jan 6 22:05 libldap-2.4.so.2 -> libldap-2.4.so.2.10.2 -rwxr-xr-x. 1 root root 329696 Oct 15 2014 libldap-2.4.so.2.10.2 lrwxrwxrwx. 1 root root 23 May 19 06:43 libldap_r-2.4.so.2 -> libldap_r-2.4.so.2.10.2 -rwxr-xr-x. 1 root root 351920 Oct 15 2014 libldap_r-2.4.so.2.10.2 [root@localhost pg_repack]# ls -l /opt/PostgreSQL/9.4/lib/libldap* -rwxr-xr-x. 1 root daemon 404761 Mar 22 23:32 /opt/PostgreSQL/9.4/lib/libldap-2.4.so.2 -rwxr-xr-x. 1 root daemon 442657 Mar 22 23:32 /opt/PostgreSQL/9.4/lib/libldap_r-2.4.so.2Seems there are two copies of "libldap_r-2.4.so.2", one in the form of symbolic link and another as a hard copy. Am guessing its due to multiple library copies, lets remove symbolic link and retain a hard copy of library and try again.
[root@localhost lib64]# unlink libldap_r-2.4.so.2 [root@localhost pg_repack]# make make[1]: Entering directory `/root/pg_repack/bin' .... .... .... sed 's,REPACK_VERSION,1.3.1,g' pg_repack.sql.in > pg_repack--1.3.1.sql; sed 's,REPACK_VERSION,1.3.1,g' pg_repack.control.in > pg_repack.control make[1]: Leaving directory `/root/pg_repack/lib' make[1]: Entering directory `/root/pg_repack/regress' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/root/pg_repack/regress'wow... finally, it has compiled. Run "make install" for the pg_repack binaries and libraries.
[root@localhost pg_repack]# make install make[1]: Entering directory `/root/pg_repack/bin' /bin/mkdir -p '/opt/PostgreSQL/9.4/bin' /usr/bin/install -c pg_repack '/opt/PostgreSQL/9.4/bin' make[1]: Leaving directory `/root/pg_repack/bin' make[1]: Entering directory `/root/pg_repack/lib' /bin/mkdir -p '/opt/PostgreSQL/9.4/lib/postgresql' /bin/mkdir -p '/opt/PostgreSQL/9.4/share/postgresql/extension' /bin/mkdir -p '/opt/PostgreSQL/9.4/share/postgresql/extension' /usr/bin/install -c -m 755 pg_repack.so '/opt/PostgreSQL/9.4/lib/postgresql/pg_repack.so' /usr/bin/install -c -m 644 pg_repack.control '/opt/PostgreSQL/9.4/share/postgresql/extension/' /usr/bin/install -c -m 644 pg_repack--1.3.1.sql pg_repack.control '/opt/PostgreSQL/9.4/share/postgresql/extension/' make[1]: Leaving directory `/root/pg_repack/lib' make[1]: Entering directory `/root/pg_repack/regress' make[1]: Nothing to be done for `install'. make[1]: Leaving directory `/root/pg_repack/regress'After compilation there will be pg_repack utility in $PGHOME/bin and pg_repack.so library in $PGHOME/lib/postgresql/ directory.
[root@localhost pg_repack]# ls -l /opt/PostgreSQL/9.4/bin/pg_rep* -rwxr-xr-x. 1 root root 84030 May 20 00:07 /opt/PostgreSQL/9.4/bin/pg_repack [root@localhost postgresql]# ls -l /opt/PostgreSQL/9.4/lib/postgresql/pg_rep* -rwxr-xr-x. 1 root root 31028 May 20 00:07 /opt/PostgreSQL/9.4/lib/postgresql/pg_repack.soNow we are set to create pg_repack extension inside the database.
-bash-4.1$ psql Password: psql.bin (9.4.1) Type "help" for help. postgres=# select * from pg_available_extensions where name='pg_repack'; name | default_version | installed_version | comment -----------+-----------------+-------------------+-------------------------------------------------------------- pg_repack | 1.3.1 | 1.3.1 | Reorganize tables in PostgreSQL databases with minimal locks (1 row) postgres=# create extension pg_repack; CREATE EXTENSIONLikewise, I have attempted to compile with EnterpriseDB product PostgresPlus Advanced Server 9.4[PPAS] a pre-built binary package. Faced similar library issues, hence I have used linker option "LDFLAGS" pointing to library directory and compiled.
[root@localhost ~]# export PATH=/opt/PostgresPlus/9.4AS/bin:$PATH [root@localhost ~]# export LDFLAGS=-L/opt/PostgresPlus/9.4AS/lib [root@localhost ~]# cd pg_repack/ [root@localhost pg_repack]# make [root@localhost pg_repack]# make install [root@localhost pg_repack]# ls -l /opt/PostgresPlus/9.4AS/bin/pg_rep* -rwxr-xr-x. 1 root root 201877 May 15 11:06 /opt/PostgresPlus/9.4AS/bin/pg_repack [root@localhost pg_repack]# ls -l /opt/PostgresPlus/9.4AS/lib/pg_rep* -rwxr-xr-x. 1 root root 94516 May 15 11:06 /opt/PostgresPlus/9.4AS/lib/pg_repack.soCool,this too compiled smoothly. Now create extension in PPAS 9.4
-bash-4.1$ psql Welcome psql.bin (9.4.1.4) Type "help" for help. edb=# create extension pg_repack; CREATE EXTENSION edb=#
Thank you.
--Raghav
