pg-osc

pg-online-schema-change (pg-osc) is a tool for making schema changes (any ALTER statements) in Postgres tables with minimal locks, thus helping achieve zero downtime schema changes against production workloads.

pg-osc uses the concept of shadow table to perform schema changes. At a high level, it creates a shadow table that looks structurally the same as the primary table, performs the schema change on the shadow table, copies contents from the primary table to the shadow table and swaps the table names in the end while preserving all changes to the primary table using triggers (via audit table).

pg-osc is inspired by the design and workings of tools like pg_repack and pt-online-schema-change (MySQL). Read more below on how does it work, prominent features, the caveats and examples

⚠️ Proceed with caution when using this on production like workloads. Best to try on similar setup or staging like environment first. Read on below for some examples and caveats.

run

# Ensure PGPASSWORD environment variable contains the password.
docker run --network host -it --rm shayonj/pg-osc:latest \
    pg-online-schema-change perform \
    --alter-statement 'ALTER TABLE books ADD COLUMN "purchased" BOOLEAN DEFAULT FALSE; ALTER TABLE books RENAME COLUMN email TO new_email;' \
    --dbname "postgres" \
    --host "localhost" \
    --username "jamesbond" \
    --pull-batch-count 1000
    --delta-count 20
    --wait-time-for-lock 5 \
    --kill-backends \
    --drop

More examples: https://github.com/shayonj/pg-osc#examples⁠