Extension Versioning and Upgrades

In this lesson, you will learn how to transition your custom PostgreSQL extensions from one version to another by writing incremental upgrade scripts that preserve user data.

The Problem: Evolving Schemas

When you create a custom extension (like a new data type or a set of utility functions), your schema is rarely static. As your requirements grow, you might need to add a new column to a table managed by the extension, or add an entirely new function. Because PostgreSQL extensions are strictly versioned, you cannot simply overwrite the original creation script; you must provide a path for the database to migrate from version 1.0 to 1.1.

Upgrade Script Mechanics

PostgreSQL manages upgrades using "extension update scripts." These are plain SQL files named following a specific convention: extension_name--old_version--new_version.sql.

If your extension is named my_tool and you are moving from 1.0 to 1.1, your file would be named my_tool--1.0--1.1.sql. When a user runs ALTER EXTENSION my_tool UPDATE TO '1.1';, PostgreSQL looks for this specific file in your extension's control directory and executes the commands inside it.

Example: Adding a Function

Suppose my_tool version 1.0 has a function calc_total(). In 1.1, you want to add a helper function calc_tax(). Your my_tool--1.0--1.1.sql script would look like this:

-- This script handles the transition from 1.0 to 1.1
CREATE OR REPLACE FUNCTION calc_tax(amount numeric) 
RETURNS numeric AS $$
    SELECT amount * 0.05;
$$ LANGUAGE sql;

Best Practices

1. Idempotency: Always ensure your scripts can handle scenarios where a user might have already manually performed a step. Using CREATE OR REPLACE or checking for the existence of an object before creating it prevents the upgrade script from crashing.
2. Transaction Safety: PostgreSQL executes the entire upgrade script inside a single transaction. If a command fails, the database rolls back to version 1.0, keeping your system in a consistent state rather than a half-upgraded mess.
3. Keep it Small: Each script should only handle the delta between two specific versions. If you have many versions, PostgreSQL can chain these files together (e.g., 1.0 to 1.1, then 1.1 to 1.2).

Key Takeaway: Extension upgrades are managed by creating specific, incremental SQL delta files that PostgreSQL chains together to transition your schema safely within a single, atomic transaction.