pg_net 0.8.0 to 0.19.5 upgrade on debian

Dockerfile

FROM postgres:15-bookworm

# Install build dependencies
RUN apt-get update && apt-get install -y \
    build-essential \
    postgresql-server-dev-15 \
    libcurl4-openssl-dev \
    git \
    ca-certificates \
    wget \
    && rm -rf /var/lib/apt/lists/*

# Create a directory for pg_net source
RUN wget https://github.com/supabase/pg_net/releases/download/v0.8.0/pg_net-v0.8.0-pg15-arm64-linux-gnu.deb
RUN dpkg -i pg_net-v0.8.0-pg15-arm64-linux-gnu.deb


# Set up PostgreSQL configuration for pg_net
RUN echo "shared_preload_libraries = 'pg_net'" >> /usr/share/postgresql/postgresql.conf.sample


# Expose PostgreSQL port
EXPOSE 5432

run.sh

#!/bin/bash
# Run the pg_net upgrade test container

set -e

CONTAINER_NAME="pg_net_upgrade_test"
IMAGE_NAME="pg_net_upgrade_test:0.8.0-to-0.19.5"
POSTGRES_PASSWORD="postgres"
PORT="5433"

echo "Starting pg_net upgrade test container..."
echo ""

# Stop and remove existing container if it exists
if docker ps -a --format "{{.Names}}" | grep -q "^${CONTAINER_NAME}$"; then
    echo "Stopping and removing existing container..."
    docker stop "$CONTAINER_NAME" 2>/dev/null || true
    docker rm "$CONTAINER_NAME" 2>/dev/null || true
fi

# Run the container
echo "Starting new container..."
docker run -d \
    --name "$CONTAINER_NAME" \
    -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
    -e POSTGRES_DB=postgres \
    -p "$PORT:5432" \
    "$IMAGE_NAME"

echo ""
echo "✅ Container started successfully!"
echo ""
echo "Container name: $CONTAINER_NAME"
echo "PostgreSQL port: $PORT"
echo "Database: postgres"
echo "Username: postgres"
echo "Password: $POSTGRES_PASSWORD"
echo ""
echo "Waiting for PostgreSQL to be ready..."

# Wait for PostgreSQL to be ready
for i in {1..30}; do
    if docker exec "$CONTAINER_NAME" pg_isready -U postgres >/dev/null 2>&1; then
        echo "✅ PostgreSQL is ready!"
        break
    fi
    if [ $i -eq 30 ]; then
        echo "❌ Timeout waiting for PostgreSQL to start"
        exit 1
    fi
    sleep 1
done

echo "Check pg_net is installed and v0.8.0"
docker exec -it "$CONTAINER_NAME" psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_net; SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_net';"

upgrade.sh

#!/bin/bash
# Install pg_net 0.19.5 from source and perform upgrade

set -e

CONTAINER_NAME="pg_net_upgrade_test"

if ! docker ps --format "{{.Names}}" | grep -q "^${CONTAINER_NAME}$"; then
    echo "❌ Error: Container '$CONTAINER_NAME' is not running"
    echo "Please run ./upgrade-test/run-container.sh first"
    exit 1
fi

echo "Step 1: Drop existing pg_net extension..."
docker exec -i "$CONTAINER_NAME" psql -U postgres <<EOF
DROP EXTENSION IF EXISTS pg_net CASCADE;
SELECT 'Extension dropped successfully' as status;
EOF

echo ""
echo "Step 2: Uninstalling pg_net 0.8.0 package..."
docker exec "$CONTAINER_NAME" bash -c "dpkg -r pg-net || true"
# Could also run: apt-get remove -y pg-net or `make uninstall` from source

echo ""
echo "Step 3: Installing build dependencies..."
docker exec "$CONTAINER_NAME" bash -c "
    apt-get update && \
    apt-get install -y build-essential postgresql-server-dev-15 libcurl4-openssl-dev git ca-certificates
"

echo ""
echo "Step 4: Cloning and building pg_net 0.19.5 from source..."
docker exec "$CONTAINER_NAME" bash -c "
    cd /tmp && \
    rm -rf pg_net && \
    git clone --branch v0.19.5 https://github.com/supabase/pg_net.git && \
    cd pg_net && \
    make && \
    make install
"

echo ""
echo "Step 5: Restarting PostgreSQL..."
docker restart "$CONTAINER_NAME"

echo "Waiting for PostgreSQL to be ready..."
for i in {1..30}; do
    if docker exec "$CONTAINER_NAME" pg_isready -U postgres >/dev/null 2>&1; then
        echo "✅ PostgreSQL is ready!"
        break
    fi
    if [ $i -eq 30 ]; then
        echo "❌ Timeout waiting for PostgreSQL to start"
        exit 1
    fi
    sleep 1
done

echo ""
echo "Step 6: Creating pg_net extension at version 0.8.0..."
docker exec -i "$CONTAINER_NAME" psql -U postgres <<EOF
CREATE EXTENSION pg_net VERSION '0.19.5';

-- Verify version
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_net';

-- Test basic functionality
SELECT 'Testing http_get...' as test;
SELECT net.http_get('https://httpbin.org/get') as request_id;
EOF

Likely can be simplified and a cleanup file might be useful but steps are:

1. ./build.sh
2. ./run.sh - can psql from host machine and test
3. ./upgrade.sh