5

I am using this command sudo apt-get install postgresql-11-repack to install pg_repack(default to 1.4.8).

postgresql-11-repack is already the newest version (1.4.8-1.pgdg20.04+1).

when i run "create extension pg_repack" in rds postgres 11, it creates by default with version 1.4.4.

                                        List of installed extensions
     Name     | Version |   Schema   |                         Description
--------------+---------+------------+--------------------------------------------------------------
 pg_repack    | 1.4.4   | public     | Reorganize tables in PostgreSQL databases with minimal locks

so pg_repack fails due to mismatch in version number between ec2 and db.

ERROR: pg_repack failed with error: program 'pg_repack 1.4.8' does not match database library 'pg_repack 1.4.4'
  1. how do i create repack extension with version 1.4.8 in DB?
  2. if above is not possible, how do i downgrade by pg_repack version on ec2 back to 1.4.4 same as my db.
  3. AWS does not allow me to add this extension under "shared_preload_libraries" either.

Error compiling from source:

    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer pg_repack.o pgut/pgut.o pgut/pgut-fe.o  -L/usr/lib/x86_64-linux-gnu -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-10/lib  -L/usr/lib/x86_64-linux-gnu/mit-krb5 -Wl,--as-needed  -L/usr/lib/x86_64-linux-gnu -lpq -L/usr/lib/postgresql/11/lib -lpgcommon -lpgport -lpthread -lssl -lcrypto -lz -lrt -lcrypt -ldl -lm -o pg_repack
/usr/bin/ld: cannot find -lpgcommon
/usr/bin/ld: cannot find -lpgport
collect2: error: ld returned 1 exit status
make[1]: *** [/usr/lib/postgresql/11/lib/pgxs/src/makefiles/pgxs.mk:430: pg_repack] Error 1
make[1]: Leaving directory '/home/user/pg_repack-ver_1.4.4/bin'
make: *** [Makefile:35: all] Error 2
enter code here
Falcon
  • 101
  • 8

3 Answers3

6

First, check to see if the package version exists for the current distro version:

apt list -a postgresql-11-repack

the type of output will look something like this:

postgresql-11-repack/buster-pgdg 1.4.4-2.pgdg110+1 amd64 [upgradable from: 1.4.3-1.pgdg110+1]
postgresql-11-repack/now 1.4.3-1.pgdg110+1 amd64 [installed,upgradable to: 1.4.4-2.pgdg110+1]


If the package version you need is listed, you can then remove the existing package and install the specific version needed by removing the existing package and installing the targeted version as shown below (here is a good reference on why the debian package for the pg_repack extension version 1.4.4 is numbered 1.4.4-1 or 1.4.4-2 and so on)

this is a bit contrived, but in this example I removed 1.4.3 and installed 1.4.4 (the same procedure would apply if you had 1.4.8-1 debian package already installed and you wanted to install 1.4.4-2 specifically):

sudo apt-get --purge remove postgresql-11-repack

sudo apt-get install postgresql-11-repack=1.4.4-2

If the package does not show up, you can alternatively build it from source:

git clone https://github.com/reorg/pg_repack.git
cd pg_repack/
git checkout tags/ver_1.4.4
make
make install

One reason you might want to build from source is because the version of the package you need is from a previous version of the OS and you do not want to deal with that type of joy specifically (example)

justsomeguy
  • 301
  • 1
  • 5
2

The approach I took was to take advantage of these lovely docker containers that someone has gone through the trouble of creating:

https://hub.docker.com/r/cherts/pg-repack

And here's the Github repo if you want to check the code and build out your own container: https://github.com/cherts/pg_repack_docker/

Here's an example from the readme if you wanted to use 1.4.7:

docker run -it --rm --name pg_repack cherts/pg-repack:1.4.7 pgbench -h X.X.X.X -p 5432 -U postgres test -i I d

The original question also asked about using pg_repack in RDS. It is an approved extension in Aurora/RDS at time of writing. Here's the documentation on how to configure it: https://aws.amazon.com/blogs/database/remove-bloat-from-amazon-aurora-and-rds-for-postgresql-with-pg_repack/

It basically boils down to running this database command to enable it:

create extension pg_repack;

Then, you'll want to follow it up with this command (assuming psql) to get the version of pg_repack:

\dx pg_repack

Alternatively they give the following query which does the same thing:

SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE e.extname ~ '^(pg_repack)$'
ORDER BY 1;
shellster
  • 121
  • 1
1

Thanks everyone for your inputs. Here are the steps i followed to setup pg_repack 1.4.7 in EC2 & RDS Postgres

wget https://api.pgxn.org/dist/pg_repack/1.4.7/pg_repack-1.4.7.zip

sudo apt-get install postgresql-server-dev-all
sudo apt-get install postgresql-common
sudo apt-get install postgresql-client
--remove pg 15 from installation ( At present, PG 14 and the latest PG 15 version are not supported, and an error will be reported during installation: pointer type error )


sudo make
sudo make install
sudo cp bin/pg_repack /usr/local/bin/

enter image description here

create extension pg_repack; -- RDS Postgres
Falcon
  • 101
  • 8