Installation Guide

This guide covers all installation methods for the pg_ttl_index extension.

Prerequisites

Before installing, ensure you have:

  • PostgreSQL: Version 12.0 or higher
  • Development tools:
    • make
    • gcc or compatible C compiler
    • postgresql-server-dev package (for building extensions)
  • Superuser privileges: Required for creating the extension
  • Database restart capability: Needed for loading shared libraries

Check Your PostgreSQL Version

psql --version
# or
SELECT version();

Installation Methods

Option 1: Install via PGXN (Recommended)

The easiest way to install is through the PostgreSQL Extension Network (PGXN):

# Install using PGXN client
pgxn install pg_ttl_index

Installing PGXN Client

If you don’t have the PGXN client installed:

# Ubuntu/Debian
sudo apt-get install pgxnclient

# macOS (Homebrew)
brew install pgxnclient

# Or install via pip
pip install pgxnclient

Option 2: Install from Source

Clone the Repository

git clone https://github.com/ibrahimkarimeddin/postgres-extensions-pg_ttl.git
cd postgres-extensions-pg_ttl

Build and Install

# Build the extension
make

# Install (requires sudo/superuser)
sudo make install

Verify Installation

# Check if extension files are installed
ls -la $(pg_config --sharedir)/extension/pg_ttl_index*

PostgreSQL Configuration

After installing the extension files, you need to configure PostgreSQL to load the extension.

Step 1: Edit postgresql.conf

Add pg_ttl_index to the shared_preload_libraries parameter:

# Find your postgresql.conf location
psql -c "SHOW config_file;"

# Edit the file (Ubuntu/Debian example)
sudo nano /etc/postgresql/16/main/postgresql.conf

Add or modify this line:

shared_preload_libraries = 'pg_ttl_index'

:::tip Multiple Extensions If you already have other extensions loaded, separate them with commas: conf shared_preload_libraries = 'pg_stat_statements,pg_ttl_index' :::

Step 2: Restart PostgreSQL

The extension must be loaded at server startup:

# Ubuntu/Debian
sudo systemctl restart postgresql

# macOS (Homebrew)
brew services restart postgresql

# Or using pg_ctl
pg_ctl restart -D /path/to/data/directory

Step 3: Verify Server Startup

Check that PostgreSQL started successfully and loaded the extension:

# Check server status
sudo systemctl status postgresql

# Check PostgreSQL logs
sudo tail -f /var/log/postgresql/postgresql-16-main.log

Look for messages like: LOG: pg_ttl_index extension loaded

Database Setup

Now create the extension in your database:

Step 1: Connect to Your Database

# Connect via psql
psql -U postgres -d your_database_name

Step 2: Create the Extension

-- Create the extension
CREATE EXTENSION pg_ttl_index;

-- Verify extension was created
\dx pg_ttl_index

Expected output: Name | Version | Schema | Description --------------------+---------+------------+---------------------------------------- pg_ttl_index | 2.0.0 | public | Automatic TTL for PostgreSQL tables

Step 3: Start the Background Worker

:::warning Important The background worker does not start automatically. You must start it manually in each database. :::

-- Start the background worker
SELECT ttl_start_worker();

Expected output: ```

ttl_start_worker

t (1 row) ```

Step 4: Verify Worker is Running

-- Check worker status
SELECT * FROM ttl_worker_status();

You should see a worker process running for your database.

Verification

Run these commands to verify the installation is complete:

-- List all TTL functions
SELECT proname, prosrc 
FROM pg_proc 
WHERE proname LIKE 'ttl_%' 
ORDER BY proname;

-- Check if background worker is running
SELECT * FROM ttl_worker_status();

-- View current TTL configuration
SELECT name, setting, unit 
FROM pg_settings 
WHERE name LIKE 'pg_ttl_index%';

Troubleshooting Installation

Extension Files Not Found

Error: ERROR: could not open extension control file

Solution: ```bash

Verify extension files are installed

ls -la $(pg_config –sharedir)/extension/pg_ttl_index*

If missing, reinstall

sudo make install ```

Shared Library Not Found

Error: ERROR: could not load library

Solution: ```bash

Check if shared library exists

ls -la $(pg_config –pkglibdir)/pg_ttl_index.so

If missing, rebuild and install

make clean make sudo make install ```

Extension Not Loading at Startup

Error: Extension functions not available after creating extension

Solution: 1. Verify shared_preload_libraries is set correctly: sql SHOW shared_preload_libraries; 2. Restart PostgreSQL server 3. Check PostgreSQL logs for errors

Permission Denied

Error: ERROR: permission denied to create extension

Solution: You need superuser privileges: ```sql – Connect as superuser psql -U postgres -d your_database_name

– Or grant superuser temporarily ALTER USER your_user SUPERUSER; CREATE EXTENSION pg_ttl_index; ALTER USER your_user NOSUPERUSER; ```

Background Worker Not Starting

Error: ttl_worker_status() returns no rows

Solutions: 1. Verify extension is in shared_preload_libraries 2. Check pg_ttl_index.enabled is true: sql SHOW pg_ttl_index.enabled; 3. Manually start the worker: sql SELECT ttl_start_worker();

Platform-Specific Notes

Ubuntu/Debian

Install PostgreSQL development packages: bash sudo apt-get update sudo apt-get install postgresql-server-dev-16 build-essential

CentOS/RHEL

sudo yum install postgresql16-devel gcc make

macOS

# Install PostgreSQL via Homebrew
brew install postgresql@16

# Ensure development tools are available
xcode-select --install

Windows

Windows support is experimental. Consider using WSL2 for development.

Next Steps

Now that the extension is installed:

  1. Quick Start Guide - Create your first TTL index
  2. Configuration Guide - Tune performance settings
  3. API Reference - Learn all available functions

Getting Help

If you encounter issues: