Install pg_repack on Amazon EC2 for RDS PostGresql Instances

On a Amazon linux EC2 instance,

As ec2-user,

sudo yum install -y openssl-devel readline-devel

sudo yum install postgresql96-static -y

wget http://api.pgxn.org/dist/pg_repack/1.4.2/pg_repack-1.4.2.zip pg_repack-1.4.2.zip

unzip pg_repack-1.4.2.zip

cd pg_repack-1.4.2

make

sudo make install

Ensure you are in /home/ec2-user/pg_repack-1.4.2 directory

sudo cp bin/pg_repack /usr/local/bin/
Now, create a password file using PGPASSFILE param
PGPASSFILE=/tmp/pgpasswd
echo “HostEndPoint:5432:DBNAME:UserName:UserPassword” > $PGPASSFILE
chmod 600 $PGPASSFILE
export PGPASSFILE=/tmp/pgpasswd

Connect to RDS Instance as Master User. Ensure the extension version is 1.4.2
Connect to the Database of interest

CREATE EXTENSION pg_repack;
TestDB=> select * from pg_extension where extname=’pg_repack’;TestDB=> select * from pg_extension where extname=’pg_repack’;   extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ———–+———-+————–+—————-+————+———–+————– pg_repack |       10 |         2200 | f              | 1.4.2      |           |
(1 row)

Run pg_repack against the RDS instance from the EC2 instance as ec2-user
pg_repack –no-superuser-check –table=schemaname.tablename –dbname=DBNAME –host=HostEndPoint(Rte53 entry is also fine) –port=5432 –username=adminpostgres –password –elevel=DEBUG                                                                                                                     Example:
pg_repack –no-superuser-check –table=public.s3_location –dbname=TestDB –host=test-inst-1.cmqnpz2prft5yf.us-east-1.rds.amazonaws.com –port=5432 –username=adminuser –password –elevel=DEBUG
Note: password to this command comes from PGPASSFILE param