PostgreSQL - pg_repack Installation

Overview

This document explains how to install the pg_repack utility on a Linux system and enable the pg_repack extension in a PostgreSQL database. All database, schema, and table names are kept generic for reuse across environments.

 pg_repack is used to remove table and index bloat without requiring long locks, making it suitable for production systems.

Step 1: Check Installed PostgreSQL Packages

dpkg -l | grep postgresql

 

Explanation:

This command lists all installed PostgreSQL-related packages on the server.

Step 2: Configure PostgreSQL Repository

sudo rm -f /etc/apt/sources.list.d/pgdg.list

 

echo "deb http://apt-archive.postgresql.org/pub/repos/apt focal-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

 

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg-archive.gpg

 

sudo apt update

 

Explanation:

These steps configure the PostgreSQL package repository and refresh package metadata.

Step 3: Install pg_repack Package

sudo apt install postgresql-XX-repack

 

Explanation:

Replace XX with your PostgreSQL major version (for example 14, 15, etc.).

Alternative: Build pg_repack from Source (If Package Installation Pulls PostgreSQL Server)

sudo apt update

sudo apt install build-essential libpq-dev postgresql-server-dev-XX

 

cd /tmp

git clone https://github.com/reorg/pg_repack.git

cd pg_repack

make

sudo make install

 

Explanation:

This method compiles pg_repack manually when package dependencies are not suitable.

Step 4: Verify Installation

pg_repack --version

 

Expected Output:

pg_repack X.X.X

Step 5: Enable pg_repack Extension in Database

sudo su - postgres

psql

\c database_name

 

CREATE EXTENSION pg_repack;

 

Explanation:

This creates the pg_repack extension inside the selected database so it can be used.

Step 6: Verify Extension Installation

SELECT * FROM pg_extension WHERE extname = 'pg_repack';

 

Explanation:

This query confirms that the extension is successfully installed.

Common pg_repack Usage Example

pg_repack -h hostname -p port -U username -d database_name -t schema_name.table_name

 

Explanation:

This command rebuilds a specific table to remove bloat.

Prerequisites and Notes

• Database user must have sufficient privileges

• Tables should have a primary key or unique index

• Ensure enough disk space for temporary objects

• Monitor locks during execution

Conclusion

pg_repack is a powerful tool for online table maintenance in PostgreSQL environments. Proper installation and verification ensure safe execution in production systems.