mirror of
https://github.com/hanzoai/skills.git
synced 2026-11-19 00:47:36 +00:00
1460 lines
65 KiB
HTML
1460 lines
65 KiB
HTML
<!doctype html>
|
|
<html lang="en"><head>
|
|
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
|
|
<title>reference</title>
|
|
<meta name="color-scheme" content="dark">
|
|
<style>
|
|
/* Committed to one visual world: a dark instrument panel. No light variant —
|
|
the dot field and the hairline rules only read on black, and a naive
|
|
inversion would wash both out. Every colour is painted explicitly so the page
|
|
never borrows a host background. */
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Pixel Square';src:url('/fonts/ZenPixel-Square.woff2') format('woff2');font-style:normal;font-display:swap}
|
|
|
|
:root{
|
|
--ink:#f4f4f2; --soft:#9c9c98; --faint:#6a6a66; --ground:#000; --panel:#0a0a0a;
|
|
--line:#232323; --line-hi:#3a3a3a; --accent:#f4f4f2;
|
|
--font-zen-sans:'Zen',-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
|
|
--font-zen-mono:'Zen Mono',ui-monospace,"SF Mono",SFMono-Regular,Menlo,Consolas,monospace;
|
|
--font-zen-pixel-square:'Zen Pixel Square',monospace;
|
|
--mono:var(--font-zen-mono);
|
|
--sans:var(--font-zen-sans);
|
|
/* Controls are pills and surfaces are soft, as on hanzo.ai. */
|
|
--r:999px; --r-soft:14px;
|
|
}
|
|
*{box-sizing:border-box}
|
|
html{-webkit-text-size-adjust:100%}
|
|
body{margin:0;background:var(--ground);color:var(--ink);font-family:var(--sans);
|
|
font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased}
|
|
.wrap{max-width:60rem;margin:0 auto;padding:0 1.75rem}
|
|
|
|
/* The dot field. A halftone made of one repeating radial-gradient, faded out
|
|
along the diagonal by a mask so it reads as a wave rather than wallpaper. */
|
|
/* No overflow clip here: the dot field is inset to the box already, and a clip
|
|
would cut off the copy-prompt menu. */
|
|
.hero{position:relative;border-bottom:1px solid var(--line)}
|
|
.hero::before{content:"";position:absolute;inset:0;
|
|
background-image:radial-gradient(var(--line-hi) 1px, transparent 1px);
|
|
background-size:7px 7px;
|
|
-webkit-mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
pointer-events:none}
|
|
.hero .wrap{position:relative;padding-top:5.5rem;padding-bottom:4rem}
|
|
|
|
nav{border-bottom:1px solid var(--line)}
|
|
nav .in{max-width:60rem;margin:0 auto;padding:1.1rem 1.75rem;display:flex;
|
|
justify-content:space-between;align-items:center;gap:1rem}
|
|
.brand{font-family:var(--mono);font-size:.85rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--ink);text-decoration:none;
|
|
display:inline-flex;align-items:center;gap:.6rem}
|
|
.brand i{font-style:normal;color:var(--faint)}
|
|
.mark{width:1.15em;height:1.15em;display:block;flex:0 0 auto}
|
|
nav a.up{font-family:var(--mono);font-size:.72rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--soft);text-decoration:none}
|
|
nav a.up:hover{color:var(--ink)}
|
|
|
|
h1{font-family:var(--mono);font-size:clamp(2rem,5.4vw,3.4rem);line-height:1.12;
|
|
letter-spacing:-.02em;margin:0 0 1.1rem;font-weight:500;text-wrap:balance}
|
|
h1 em{font-style:normal;color:var(--faint)}
|
|
.lede{color:var(--soft);font-size:1.05rem;max-width:52ch;margin:0 0 2.25rem}
|
|
|
|
.btns{display:flex;flex-wrap:wrap;gap:.7rem;margin:0 0 .5rem;align-items:center}
|
|
.btn{display:inline-flex;align-items:center;gap:.5rem;font-family:var(--mono);
|
|
font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;
|
|
padding:.8rem 1.4rem;border:1px solid var(--line-hi);border-radius:var(--r);
|
|
color:var(--ink);text-decoration:none;background:transparent;cursor:pointer;
|
|
line-height:1.2;font-weight:400}
|
|
.btn:hover{background:#141414;text-decoration:none;border-color:var(--soft)}
|
|
.btn.solid{background:var(--ink);color:#000;border-color:var(--ink)}
|
|
.btn.solid:hover{background:#d8d8d4}
|
|
.btn .chev{width:.7em;height:.7em;flex:0 0 auto;transition:transform .15s ease}
|
|
.btn[aria-expanded="true"] .chev{transform:rotate(180deg)}
|
|
|
|
/* Copy-prompt selector. Each row copies the prompt for its own target. */
|
|
.copy{position:relative;display:inline-flex}
|
|
.menu{position:absolute;top:calc(100% + .5rem);left:0;z-index:20;min-width:21rem;
|
|
background:var(--panel);border:1px solid var(--line-hi);border-radius:var(--r-soft);
|
|
padding:.4rem;display:flex;flex-direction:column;
|
|
box-shadow:0 12px 32px rgba(0,0,0,.75)}
|
|
.menu[hidden]{display:none}
|
|
.mi{display:flex;align-items:center;gap:.7rem;width:100%;text-align:left;
|
|
padding:.62rem .8rem;border:0;border-radius:calc(var(--r-soft) - .35rem);
|
|
background:transparent;color:var(--ink);font-family:var(--mono);font-size:.76rem;
|
|
letter-spacing:.04em;text-decoration:none;cursor:pointer;line-height:1.3;
|
|
white-space:nowrap}
|
|
.mi:hover,.mi:focus-visible{background:#181818;outline:none}
|
|
.mi .mark{width:1.05rem;height:1.05rem;color:var(--soft)}
|
|
.mi:hover .mark{color:var(--ink)}
|
|
.mi u{margin-left:auto;color:var(--faint);text-decoration:none;font-size:.68rem;
|
|
letter-spacing:.1em;text-transform:uppercase}
|
|
|
|
/* Works-with bar: one scrolling row, name and mark per agent. */
|
|
.strip{border-bottom:1px solid var(--line);background:var(--panel)}
|
|
.strip .in{max-width:60rem;margin:0 auto;padding:.9rem 1.75rem;display:flex;
|
|
align-items:center;gap:1.25rem;font-family:var(--mono);font-size:.72rem;
|
|
letter-spacing:.1em;text-transform:uppercase;color:var(--faint)}
|
|
.strip b{color:var(--soft);font-weight:400;flex:0 0 auto;
|
|
border-right:1px solid var(--line);padding-right:1.25rem;align-self:stretch;
|
|
display:flex;align-items:center}
|
|
.bar{display:flex;align-items:center;gap:.5rem;flex:1 1 auto;min-width:0;
|
|
overflow-x:auto;scrollbar-width:none;scroll-snap-type:x proximity;
|
|
-webkit-overflow-scrolling:touch;
|
|
-webkit-mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent);
|
|
mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent)}
|
|
.bar::-webkit-scrollbar{display:none}
|
|
.agent{display:inline-flex;align-items:center;gap:.45rem;flex:0 0 auto;
|
|
padding:.4rem .85rem;border:1px solid var(--line);border-radius:var(--r);
|
|
color:var(--soft);white-space:nowrap;scroll-snap-align:start;
|
|
text-decoration:none;background:var(--ground)}
|
|
.agent:hover{color:var(--ink);border-color:var(--line-hi);text-decoration:none}
|
|
.agent .mark{width:.95rem;height:.95rem}
|
|
|
|
h2{font-family:var(--mono);font-size:1.1rem;letter-spacing:-.01em;margin:2.75rem 0 .75rem;font-weight:500}
|
|
h3{font-size:1rem;margin:2rem 0 .5rem;font-weight:600}
|
|
h4,h5,h6{font-size:.94rem;margin:1.5rem 0 .5rem;font-weight:600}
|
|
p{margin:0 0 1rem;max-width:70ch}
|
|
ul,ol{margin:0 0 1rem;padding-left:1.35rem;max-width:70ch}
|
|
li{margin:.22rem 0}
|
|
a{color:var(--ink);text-decoration:underline;text-underline-offset:3px;
|
|
text-decoration-color:var(--line-hi)}
|
|
a:hover{text-decoration-color:var(--ink)}
|
|
a:focus-visible{outline:1px solid var(--ink);outline-offset:3px}
|
|
code{font-family:var(--mono);font-size:.85em;background:var(--panel);
|
|
border:1px solid var(--line);border-radius:5px;padding:.1em .36em}
|
|
pre{background:var(--panel);border:1px solid var(--line);padding:1rem 1.1rem;
|
|
border-radius:var(--r-soft);overflow-x:auto;margin:0 0 1.25rem}
|
|
pre code{background:none;border:0;padding:0;font-size:.82rem;line-height:1.7}
|
|
blockquote{margin:0 0 1rem;padding-left:1rem;border-left:1px solid var(--line-hi);color:var(--soft)}
|
|
hr{border:0;border-top:1px solid var(--line);margin:2.5rem 0}
|
|
table{border-collapse:collapse;width:100%;margin:0 0 1.25rem;font-size:.92rem;
|
|
display:block;overflow-x:auto}
|
|
th,td{border:1px solid var(--line);padding:.5rem .7rem;text-align:left}
|
|
|
|
.curl{display:block;background:var(--panel);border:1px solid var(--line);
|
|
border-radius:var(--r-soft);padding:.95rem 1.1rem;font-family:var(--mono);
|
|
font-size:.8rem;color:var(--soft);overflow-x:auto;margin:0 0 2.5rem;
|
|
white-space:pre;line-height:1.9}
|
|
.curl b{color:var(--ink);font-weight:400}
|
|
.curl i{font-style:normal;color:var(--faint)}
|
|
|
|
/* index */
|
|
#q{width:100%;padding:.85rem 1.2rem;font-family:var(--mono);font-size:.85rem;color:var(--ink);
|
|
background:var(--panel);border:1px solid var(--line);border-radius:var(--r);margin:0 0 .6rem}
|
|
#q::placeholder{color:var(--faint);text-transform:uppercase;letter-spacing:.1em;font-size:.72rem}
|
|
#q:focus{outline:none;border-color:var(--soft)}
|
|
#count{color:var(--faint);font-family:var(--mono);font-size:.72rem;letter-spacing:.1em;
|
|
text-transform:uppercase;margin:0 0 2rem}
|
|
.cat{margin:2.5rem 0 .5rem;font-family:var(--mono);font-size:.7rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);border-bottom:1px solid var(--line);
|
|
padding-bottom:.45rem}
|
|
.row{display:block;padding:.6rem .9rem;margin:0 -.9rem;border-bottom:1px solid var(--line);
|
|
border-radius:var(--r-soft);text-decoration:none}
|
|
.row:hover{background:#0a0a0a}
|
|
.nm{font-family:var(--mono);font-size:.84rem;color:var(--ink);display:block}
|
|
.ds{color:var(--soft);font-size:.9rem;display:block;margin-top:.12rem}
|
|
footer{margin-top:4rem;padding:2rem 0 4rem;border-top:1px solid var(--line);
|
|
color:var(--faint);font-size:.85rem}
|
|
footer a{color:var(--soft)}
|
|
.fmap{display:grid;gap:1.75rem;grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));
|
|
margin:0 0 2rem;font-size:.85rem}
|
|
.fmap b{display:block;font-family:var(--mono);font-size:.68rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);font-weight:400;margin:0 0 .6rem;
|
|
border-bottom:1px solid var(--line);padding-bottom:.4rem}
|
|
.fmap a{display:inline-block;color:var(--ink);text-decoration:none;margin:.3rem .35rem 0 0}
|
|
.fmap a:hover{text-decoration:underline}
|
|
.fmap div{color:var(--faint);line-height:1.5}
|
|
.fend{border-top:1px solid var(--line);padding-top:1.25rem;font-size:.82rem}
|
|
|
|
@media (max-width:640px){
|
|
.hero .wrap{padding-top:3.25rem;padding-bottom:2.75rem}
|
|
.btns{gap:.5rem}
|
|
.btn{padding:.75rem 1.15rem;font-size:.68rem}
|
|
.copy,.copy .btn{width:100%;justify-content:center}
|
|
.menu{left:0;right:0;min-width:0}
|
|
.strip .in{flex-direction:column;align-items:flex-start;gap:.6rem;padding:.9rem 1.75rem}
|
|
.strip b{border-right:0;padding-right:0}
|
|
.bar{width:100%}
|
|
}
|
|
</style>
|
|
<link rel="icon" href="/favicon.svg">
|
|
<link rel="canonical" href="https://hanzoskills.com/reference">
|
|
<link rel="alternate" type="text/markdown" href="/reference.md">
|
|
<script type="module" src="/webmcp.js"></script>
|
|
</head>
|
|
<!-- Reading this with a machine? Every page here is also plain markdown.
|
|
This one: https://hanzoskills.com/reference.md
|
|
Index: https://hanzoskills.com/llms.txt
|
|
Corpus: https://hanzoskills.com/all.md
|
|
Append .md to any path for the markdown twin. -->
|
|
<body>
|
|
<nav><div class="in">
|
|
<a class="brand" href="/"><svg class="mark" viewBox="0 0 67 67" fill="currentColor" aria-hidden="true"><path d="M22.21 67V44.6369H0V67H22.21Z"/><path d="M66.7038 22.3184H22.2534L0.0878906 44.6367H44.4634L66.7038 22.3184Z"/><path d="M22.21 0H0V22.3184H22.21V0Z"/><path d="M66.7198 0H44.5098V22.3184H66.7198V0Z"/><path d="M66.7198 67V44.6369H44.5098V67H66.7198Z"/></svg> HANZO<i>SKILLS</i></a>
|
|
<a class="up" href="/">All skills</a>
|
|
</div></nav>
|
|
|
|
<div class="wrap">
|
|
<h1>reference</h1><p class="lede">Scope: Comprehensive guide to database migrations, schema versioning, zero-downtime deployments, and migration tooling Lines: ~1800 Last Updated: 2025-10-27</p><h1>PostgreSQL Migrations Reference</h1>
|
|
<p><strong>Scope</strong>: Comprehensive guide to database migrations, schema versioning, zero-downtime deployments, and migration tooling <strong>Lines</strong>: ~1800 <strong>Last Updated</strong>: 2025-10-27</p>
|
|
<h2>Table of Contents</h2>
|
|
<ol><li><a href="#migration-fundamentals">Migration Fundamentals</a></li><li><a href="#schema-versioning-strategies">Schema Versioning Strategies</a></li><li><a href="#migration-tools-deep-dive">Migration Tools Deep Dive</a></li><li><a href="#writing-safe-migrations">Writing Safe Migrations</a></li><li><a href="#zero-downtime-patterns">Zero-Downtime Patterns</a></li><li><a href="#postgresql-lock-behavior">PostgreSQL Lock Behavior</a></li><li><a href="#data-migrations">Data Migrations</a></li><li><a href="#testing-migrations">Testing Migrations</a></li><li><a href="#rollback-strategies">Rollback Strategies</a></li><li><a href="#common-pitfalls">Common Pitfalls</a></li><li><a href="#advanced-topics">Advanced Topics</a></li></ol>
|
|
<hr>
|
|
<h2>Migration Fundamentals</h2>
|
|
<h3>What Are Migrations?</h3>
|
|
<p>Migrations are <strong>versioned, ordered scripts</strong> that evolve database schema over time.</p>
|
|
<p><strong>Core Properties</strong>:</p>
|
|
<ul><li><strong>Versioned</strong>: Each migration has a unique identifier (timestamp, sequence number, hash)</li><li><strong>Ordered</strong>: Applied sequentially to maintain consistency</li><li><strong>Tracked</strong>: Database maintains record of applied migrations</li><li><strong>Reversible</strong>: Can roll back changes (ideally)</li><li><strong>Idempotent</strong>: Safe to run multiple times (ideally)</li><li><strong>Atomic</strong>: Changes succeed or fail as a unit (when possible)</li></ul>
|
|
<h3>Why Migrations Matter</h3>
|
|
<p><strong>Without migrations</strong>:</p>
|
|
<pre><code class="lang-sql">-- Developer A: Creates table manually
|
|
CREATE TABLE users (id INT, email VARCHAR(255));
|
|
|
|
-- Developer B: Different schema!
|
|
CREATE TABLE users (id SERIAL, email TEXT, created_at TIMESTAMP);
|
|
|
|
-- Production: Who knows what's deployed?</code></pre>
|
|
<p><strong>With migrations</strong>:</p>
|
|
<pre><code class="lang-sql">-- V1: Everyone runs same migration
|
|
-- Version controlled, tested, reproducible
|
|
-- Production schema matches development</code></pre>
|
|
<h3>Migration Lifecycle</h3>
|
|
<pre><code>Development → Testing → Staging → Production
|
|
↓ ↓ ↓ ↓
|
|
V1, V2 V1, V2 V1, V2 V1, V2</code></pre>
|
|
<p><strong>Key principle</strong>: Same migrations run in all environments.</p>
|
|
<h3>Migration Tracking</h3>
|
|
<p>Most tools create a metadata table:</p>
|
|
<pre><code class="lang-sql">-- Example: Flyway's schema_version table
|
|
CREATE TABLE flyway_schema_history (
|
|
installed_rank INT NOT NULL,
|
|
version VARCHAR(50),
|
|
description VARCHAR(200) NOT NULL,
|
|
type VARCHAR(20) NOT NULL,
|
|
script VARCHAR(1000) NOT NULL,
|
|
checksum INT,
|
|
installed_by VARCHAR(100) NOT NULL,
|
|
installed_on TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
execution_time INT NOT NULL,
|
|
success BOOLEAN NOT NULL
|
|
);</code></pre>
|
|
<p><strong>Query applied migrations</strong>:</p>
|
|
<pre><code class="lang-sql">SELECT version, description, installed_on, success
|
|
FROM flyway_schema_history
|
|
ORDER BY installed_rank;</code></pre>
|
|
<hr>
|
|
<h2>Schema Versioning Strategies</h2>
|
|
<h3>Strategy 1: Forward-Only Migrations</h3>
|
|
<p><strong>Approach</strong>: Only write "up" migrations, never roll back.</p>
|
|
<pre><code>V1 → V2 → V3 → V4
|
|
↓ ↓ ↓ ↓</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Simplest approach</li><li>No data loss from rollbacks</li><li>Production always moves forward</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Can't easily undo mistakes</li><li>Failed migrations require fix-forward</li></ul>
|
|
<p><strong>Best for</strong>: Production systems where rollback is rare.</p>
|
|
<p><strong>Example</strong>:</p>
|
|
<pre><code class="lang-sql">-- V1__initial_schema.sql
|
|
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255));
|
|
|
|
-- V2__add_username.sql
|
|
ALTER TABLE users ADD COLUMN username VARCHAR(100);
|
|
|
|
-- V3__add_index.sql
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
|
|
|
|
-- V4 (fix for V3 if it failed)
|
|
-- V4__retry_index.sql
|
|
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
|
|
<h3>Strategy 2: Bidirectional Migrations (Up/Down)</h3>
|
|
<p><strong>Approach</strong>: Every migration has "up" and "down" version.</p>
|
|
<pre><code>V1 ⇄ V2 ⇄ V3 ⇄ V4</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Can roll back bad deployments</li><li>Easier to develop locally (apply/revert)</li><li>Better for testing</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Down migrations can lose data</li><li>More code to maintain</li><li>Rollback may not be safe in production</li></ul>
|
|
<p><strong>Best for</strong>: Development, staging, new projects.</p>
|
|
<p><strong>Example</strong>:</p>
|
|
<pre><code class="lang-sql">-- 001_add_users.up.sql
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) UNIQUE NOT NULL
|
|
);
|
|
|
|
-- 001_add_users.down.sql
|
|
-- Example of rollback migration - destructive operation for reverting schema
|
|
DROP TABLE users;
|
|
|
|
-- 002_add_username.up.sql
|
|
ALTER TABLE users ADD COLUMN username VARCHAR(100);
|
|
|
|
-- 002_add_username.down.sql
|
|
ALTER TABLE users DROP COLUMN username;</code></pre>
|
|
<h3>Strategy 3: Immutable Migrations</h3>
|
|
<p><strong>Approach</strong>: Once applied, migrations NEVER change. Fixes go in new migrations.</p>
|
|
<p><strong>Rule</strong>: Migration files are immutable after merge to main.</p>
|
|
<pre><code>V1 (bug!) → V2 (fix) → V3</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>History preserved</li><li>No checksum conflicts</li><li>Clear audit trail</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Can't fix typos in old migrations</li><li>Must create new migration for fixes</li></ul>
|
|
<p><strong>Best practice</strong>: Use checksums to detect unauthorized changes.</p>
|
|
<p><strong>Example</strong>:</p>
|
|
<pre><code class="lang-sql">-- V1__create_users.sql (has bug: wrong column type)
|
|
CREATE TABLE users (id INT, email VARCHAR(50)); -- Bug: email too short!
|
|
|
|
-- V2__fix_email_length.sql (fix in new migration)
|
|
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);</code></pre>
|
|
<h3>Strategy 4: State-Based Migrations</h3>
|
|
<p><strong>Approach</strong>: Define desired state, tool generates migrations.</p>
|
|
<p><strong>Tools</strong>: Atlas, Prisma Migrate, TypeORM with synchronize.</p>
|
|
<pre><code>Schema Definition (code) → Tool → Generated Migrations</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Less manual SQL</li><li>Auto-detects schema drift</li><li>Type-safe (if using ORM)</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Less control over migration details</li><li>Can generate suboptimal SQL</li><li>Tool lock-in</li></ul>
|
|
<p><strong>Example (Atlas)</strong>:</p>
|
|
<pre><code class="lang-hcl">// schema.hcl
|
|
table "users" {
|
|
column "id" { type = serial }
|
|
column "email" { type = varchar(255) }
|
|
primary_key { columns = [column.id] }
|
|
}</code></pre>
|
|
<pre><code class="lang-bash">atlas migrate diff --env local
|
|
# Generates: 20231027_initial.sql</code></pre>
|
|
<hr>
|
|
<h2>Migration Tools Deep Dive</h2>
|
|
<h3>Flyway</h3>
|
|
<p><strong>Language</strong>: Java/SQL <strong>Philosophy</strong>: SQL-first, version-based <strong>Best for</strong>: Enterprise Java applications</p>
|
|
<p><strong>Features</strong>:</p>
|
|
<ul><li>Simple SQL-based migrations</li><li>Commercial support available</li><li>Callbacks for custom logic</li><li>Out-of-order migrations (advanced)</li></ul>
|
|
<p><strong>Configuration</strong>:</p>
|
|
<pre><code class="lang-properties"># flyway.conf
|
|
flyway.url=jdbc:postgresql://localhost:5432/mydb
|
|
flyway.user=postgres
|
|
flyway.password=secret
|
|
flyway.locations=filesystem:./migrations
|
|
flyway.baselineVersion=1
|
|
flyway.baselineOnMigrate=true</code></pre>
|
|
<p><strong>Naming Convention</strong>:</p>
|
|
<pre><code>V{version}__{description}.sql
|
|
V1__initial_schema.sql
|
|
V2__add_users_table.sql
|
|
V2.1__add_users_email_index.sql # Dot notation for sub-versions</code></pre>
|
|
<p><strong>Commands</strong>:</p>
|
|
<pre><code class="lang-bash">flyway migrate # Apply pending migrations
|
|
flyway info # Show migration status
|
|
flyway validate # Validate applied migrations
|
|
flyway baseline # Baseline existing database
|
|
flyway repair # Fix metadata (use with caution)
|
|
flyway clean # Drop all objects (DANGEROUS!)</code></pre>
|
|
<p><strong>Callbacks</strong>:</p>
|
|
<pre><code class="lang-sql">-- beforeMigrate.sql: Runs before any migration
|
|
SET statement_timeout = '30s';
|
|
|
|
-- afterMigrate.sql: Runs after all migrations
|
|
ANALYZE;</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Battle-tested</li><li>Great documentation</li><li>Commercial support</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Java dependency (JVM required)</li><li>Pro features require license</li><li>No native down migrations</li></ul>
|
|
<hr>
|
|
<h3>golang-migrate</h3>
|
|
<p><strong>Language</strong>: Go/SQL <strong>Philosophy</strong>: Simple, CLI-focused, up/down pattern <strong>Best for</strong>: Go microservices</p>
|
|
<p><strong>Features</strong>:</p>
|
|
<ul><li>Native Go binary (no JVM)</li><li>Up/down migrations</li><li>Multiple database support</li><li>Programmatic API</li></ul>
|
|
<p><strong>Installation</strong>:</p>
|
|
<pre><code class="lang-bash">go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest</code></pre>
|
|
<p><strong>Naming Convention</strong>:</p>
|
|
<pre><code>{version}_{description}.up.sql
|
|
{version}_{description}.down.sql
|
|
|
|
000001_initial_schema.up.sql
|
|
000001_initial_schema.down.sql
|
|
000002_add_users_table.up.sql
|
|
000002_add_users_table.down.sql</code></pre>
|
|
<p><strong>Commands</strong>:</p>
|
|
<pre><code class="lang-bash"># Create migration
|
|
migrate create -ext sql -dir migrations -seq add_users_table
|
|
|
|
# Apply all up migrations
|
|
migrate -database "postgres://localhost:5432/db?sslmode=disable" -path migrations up
|
|
|
|
# Apply one up migration
|
|
migrate -database "$DB_URL" -path migrations up 1
|
|
|
|
# Rollback one migration
|
|
migrate -database "$DB_URL" -path migrations down 1
|
|
|
|
# Force version (recovery from dirty state)
|
|
migrate -database "$DB_URL" -path migrations force 5
|
|
|
|
# Show current version
|
|
migrate -database "$DB_URL" -path migrations version</code></pre>
|
|
<p><strong>Programmatic Usage</strong>:</p>
|
|
<pre><code class="lang-go">import (
|
|
"github.com/golang-migrate/migrate/v4"
|
|
_ "github.com/golang-migrate/migrate/v4/database/postgres"
|
|
_ "github.com/golang-migrate/migrate/v4/source/file"
|
|
)
|
|
|
|
m, err := migrate.New(
|
|
"file://migrations",
|
|
"postgres://localhost:5432/db?sslmode=disable")
|
|
|
|
m.Up() // Apply all migrations
|
|
m.Steps(2) // Apply 2 migrations
|
|
m.Down() // Rollback all (DANGEROUS!)</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Lightweight, single binary</li><li>Native Go integration</li><li>Simple up/down pattern</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Manual version numbering</li><li>No automatic rollback on failure</li><li>Limited advanced features</li></ul>
|
|
<hr>
|
|
<h3>Alembic</h3>
|
|
<p><strong>Language</strong>: Python/SQL <strong>Philosophy</strong>: SQLAlchemy integration, auto-generate <strong>Best for</strong>: Python applications (Flask, FastAPI, Django alternatives)</p>
|
|
<p><strong>Features</strong>:</p>
|
|
<ul><li>Auto-generate from SQLAlchemy models</li><li>Branching/merging migrations</li><li>Offline SQL generation</li><li>Revision history</li></ul>
|
|
<p><strong>Installation</strong>:</p>
|
|
<pre><code class="lang-bash">uv add alembic psycopg2-binary
|
|
alembic init alembic</code></pre>
|
|
<p><strong>Configuration</strong>:</p>
|
|
<pre><code class="lang-python"># alembic.ini
|
|
[alembic]
|
|
script_location = alembic
|
|
sqlalchemy.url = postgresql://localhost:5432/mydb
|
|
|
|
# alembic/env.py
|
|
from myapp.models import Base
|
|
target_metadata = Base.metadata</code></pre>
|
|
<p><strong>Creating Migrations</strong>:</p>
|
|
<pre><code class="lang-bash"># Manual migration
|
|
alembic revision -m "add users table"
|
|
|
|
# Auto-generate from models
|
|
alembic revision --autogenerate -m "add users table"</code></pre>
|
|
<p><strong>Migration File</strong>:</p>
|
|
<pre><code class="lang-python"># alembic/versions/abc123_add_users_table.py
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = 'abc123'
|
|
down_revision = 'def456' # Previous migration
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
def upgrade():
|
|
op.create_table(
|
|
'users',
|
|
sa.Column('id', sa.Integer(), primary_key=True),
|
|
sa.Column('email', sa.String(255), unique=True, nullable=False),
|
|
sa.Column('created_at', sa.DateTime(), server_default=sa.func.now())
|
|
)
|
|
op.create_index('idx_users_email', 'users', ['email'])
|
|
|
|
def downgrade():
|
|
op.drop_index('idx_users_email', 'users')
|
|
op.drop_table('users')</code></pre>
|
|
<p><strong>Commands</strong>:</p>
|
|
<pre><code class="lang-bash">alembic upgrade head # Apply all migrations
|
|
alembic upgrade +1 # Apply one migration
|
|
alembic downgrade -1 # Rollback one migration
|
|
alembic current # Show current version
|
|
alembic history # Show migration history
|
|
alembic stamp head # Mark as current without running
|
|
alembic revision --sql ... # Generate SQL without applying</code></pre>
|
|
<p><strong>Branching</strong>:</p>
|
|
<pre><code class="lang-bash"># Create branch
|
|
alembic revision -m "feature A" --head=base --branch-label=feature_a
|
|
alembic revision -m "feature B" --head=base --branch-label=feature_b
|
|
|
|
# Merge branches
|
|
alembic merge -m "merge features" feature_a feature_b</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Tight SQLAlchemy integration</li><li>Auto-generate migrations</li><li>Advanced branching/merging</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Python-only</li><li>Auto-generate not always accurate</li><li>Complex for simple projects</li></ul>
|
|
<hr>
|
|
<h3>Liquibase</h3>
|
|
<p><strong>Language</strong>: Java/XML/YAML/SQL <strong>Philosophy</strong>: Database-agnostic, change sets <strong>Best for</strong>: Enterprise multi-database environments</p>
|
|
<p><strong>Features</strong>:</p>
|
|
<ul><li>Database-agnostic (Postgres, MySQL, Oracle, etc.)</li><li>XML/YAML/SQL/JSON formats</li><li>Preconditions and contexts</li><li>Rollback tags</li></ul>
|
|
<p><strong>Example (YAML)</strong>:</p>
|
|
<pre><code class="lang-yaml"># changelog.yml
|
|
databaseChangeLog:
|
|
- changeSet:
|
|
id: 1
|
|
author: developer
|
|
changes:
|
|
- createTable:
|
|
tableName: users
|
|
columns:
|
|
- column:
|
|
name: id
|
|
type: SERIAL
|
|
constraints:
|
|
primaryKey: true
|
|
- column:
|
|
name: email
|
|
type: VARCHAR(255)
|
|
constraints:
|
|
unique: true
|
|
nullable: false
|
|
rollback:
|
|
- dropTable:
|
|
tableName: users</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Database-agnostic</li><li>Rich feature set</li><li>Enterprise support</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Complex XML/YAML syntax</li><li>Java dependency</li><li>Steeper learning curve</li></ul>
|
|
<hr>
|
|
<h3>dbmate</h3>
|
|
<p><strong>Language</strong>: Any/SQL <strong>Philosophy</strong>: Simple, language-agnostic, minimal <strong>Best for</strong>: Polyglot projects, simple setups</p>
|
|
<p><strong>Features</strong>:</p>
|
|
<ul><li>Single binary</li><li>Simple up/down SQL</li><li>No dependencies</li><li>URL-based configuration</li></ul>
|
|
<p><strong>Installation</strong>:</p>
|
|
<pre><code class="lang-bash"># macOS
|
|
brew install dbmate
|
|
|
|
# Or download binary
|
|
curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64
|
|
chmod +x /usr/local/bin/dbmate</code></pre>
|
|
<p><strong>Usage</strong>:</p>
|
|
<pre><code class="lang-bash"># Set database URL
|
|
export DATABASE_URL="postgres://localhost:5432/mydb?sslmode=disable"
|
|
|
|
# Create migration
|
|
dbmate new add_users_table
|
|
|
|
# Apply migrations
|
|
dbmate up
|
|
|
|
# Rollback
|
|
dbmate down
|
|
|
|
# Status
|
|
dbmate status</code></pre>
|
|
<p><strong>Migration File</strong>:</p>
|
|
<pre><code class="lang-sql">-- migrations/20231027120000_add_users_table.sql
|
|
-- migrate:up
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) UNIQUE NOT NULL
|
|
);
|
|
|
|
-- migrate:down
|
|
-- Example of rollback migration - destructive operation for reverting schema
|
|
DROP TABLE users;</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Dead simple</li><li>Language-agnostic</li><li>Lightweight</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Limited features</li><li>No auto-generate</li><li>No branching</li></ul>
|
|
<hr>
|
|
<h3>Atlas</h3>
|
|
<p><strong>Language</strong>: Go/HCL <strong>Philosophy</strong>: Modern, declarative, schema-as-code <strong>Best for</strong>: Modern Go applications, GitOps workflows</p>
|
|
<p><strong>Features</strong>:</p>
|
|
<ul><li>Declarative schema definition</li><li>Automatic migration generation</li><li>Schema diffing</li><li>Policy-as-code (Pro)</li><li>Cloud integration (Pro)</li></ul>
|
|
<p><strong>Schema Definition</strong>:</p>
|
|
<pre><code class="lang-hcl">// schema.hcl
|
|
table "users" {
|
|
schema = schema.public
|
|
column "id" {
|
|
type = serial
|
|
}
|
|
column "email" {
|
|
type = varchar(255)
|
|
}
|
|
primary_key {
|
|
columns = [column.id]
|
|
}
|
|
index "idx_email" {
|
|
unique = true
|
|
columns = [column.email]
|
|
}
|
|
}</code></pre>
|
|
<p><strong>Commands</strong>:</p>
|
|
<pre><code class="lang-bash"># Inspect current schema
|
|
atlas schema inspect -u "postgres://localhost:5432/db" > schema.hcl
|
|
|
|
# Generate migration
|
|
atlas migrate diff add_users --env local
|
|
|
|
# Apply migrations
|
|
atlas migrate apply --env local
|
|
|
|
# Validate
|
|
atlas migrate validate --env local</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Modern, declarative approach</li><li>Type-safe schema definitions</li><li>Great CI/CD integration</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Newer tool (less mature)</li><li>Pro features require license</li><li>Go-centric</li></ul>
|
|
<hr>
|
|
<h2>Writing Safe Migrations</h2>
|
|
<h3>Safety Principles</h3>
|
|
<ol><li><strong>Idempotency</strong>: Safe to run multiple times</li><li><strong>Atomicity</strong>: All-or-nothing changes</li><li><strong>Reversibility</strong>: Can undo changes</li><li><strong>Non-blocking</strong>: Don't lock tables</li><li><strong>Backward compatible</strong>: Old code still works</li><li><strong>Testable</strong>: Can verify locally</li></ol>
|
|
<h3>Idempotent Migrations</h3>
|
|
<p><strong>Problem</strong>: Migration fails halfway, retried, errors on existing objects.</p>
|
|
<p><strong>Solution</strong>: Use <code>IF NOT EXISTS</code> / <code>IF EXISTS</code>.</p>
|
|
<pre><code class="lang-sql">-- ✅ GOOD: Idempotent
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255)
|
|
);
|
|
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
|
|
ALTER TABLE users ADD CONSTRAINT IF NOT EXISTS users_email_unique UNIQUE (email);
|
|
|
|
-- ✅ GOOD: Idempotent drop (safe cleanup operation)
|
|
DROP TABLE IF EXISTS old_temp_table;
|
|
|
|
DROP INDEX IF EXISTS idx_old_index;</code></pre>
|
|
<p><strong>PostgreSQL version check</strong>:</p>
|
|
<pre><code class="lang-sql">-- IF NOT EXISTS added in:
|
|
-- CREATE TABLE IF NOT EXISTS: 9.1+
|
|
-- ALTER TABLE ADD COLUMN IF NOT EXISTS: 9.6+
|
|
-- CREATE INDEX IF NOT EXISTS: 9.5+</code></pre>
|
|
<h3>Transaction Wrapping</h3>
|
|
<p><strong>Use transactions when possible</strong>:</p>
|
|
<pre><code class="lang-sql">BEGIN;
|
|
|
|
CREATE TABLE orders (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INT NOT NULL REFERENCES users(id),
|
|
total DECIMAL(10,2) NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_orders_user_id ON orders(user_id);
|
|
|
|
COMMIT;</code></pre>
|
|
<p><strong>When NOT to use transactions</strong>:</p>
|
|
<pre><code class="lang-sql">-- ❌ Cannot run in transaction block
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
|
|
DROP INDEX CONCURRENTLY idx_old_index;
|
|
VACUUM;
|
|
REINDEX CONCURRENTLY;</code></pre>
|
|
<p><strong>Solution</strong>: Split into separate migration files.</p>
|
|
<pre><code class="lang-sql">-- V1__add_orders_table.sql (transactional)
|
|
BEGIN;
|
|
CREATE TABLE orders (...);
|
|
COMMIT;
|
|
|
|
-- V2__add_orders_index.sql (non-transactional)
|
|
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);</code></pre>
|
|
<h3>Checking Constraints Without Locking</h3>
|
|
<p><strong>Problem</strong>: <code>ALTER TABLE ADD CONSTRAINT</code> locks table for validation.</p>
|
|
<p><strong>Solution</strong>: Add constraint as <code>NOT VALID</code>, then validate separately.</p>
|
|
<pre><code class="lang-sql">-- Step 1: Add constraint without validation (fast, allows writes)
|
|
ALTER TABLE users
|
|
ADD CONSTRAINT check_age_positive
|
|
CHECK (age > 0) NOT VALID;
|
|
|
|
-- Step 2: Validate constraint (slow, but doesn't block writes)
|
|
ALTER TABLE users VALIDATE CONSTRAINT check_age_positive;</code></pre>
|
|
<p><strong>Locking behavior</strong>:</p>
|
|
<ul><li><code>ADD CONSTRAINT ... NOT VALID</code>: ShareUpdateExclusiveLock (allows SELECT, INSERT, UPDATE, DELETE)</li><li><code>VALIDATE CONSTRAINT</code>: ShareUpdateExclusiveLock (allows SELECT, INSERT, UPDATE, DELETE)</li></ul>
|
|
<h3>Creating Indexes Without Locking</h3>
|
|
<p><strong>Problem</strong>: <code>CREATE INDEX</code> takes AccessExclusiveLock, blocks all queries.</p>
|
|
<p><strong>Solution</strong>: <code>CREATE INDEX CONCURRENTLY</code></p>
|
|
<pre><code class="lang-sql">-- ❌ BAD: Locks table
|
|
CREATE INDEX idx_users_email ON users(email);
|
|
|
|
-- ✅ GOOD: No lock
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
|
|
<p><strong>Trade-offs</strong>:</p>
|
|
<ul><li>Takes 2-3x longer than regular CREATE INDEX</li><li>Cannot run in transaction</li><li>Can fail and leave invalid index</li></ul>
|
|
<p><strong>Check for invalid indexes</strong>:</p>
|
|
<pre><code class="lang-sql">SELECT
|
|
schemaname,
|
|
tablename,
|
|
indexname,
|
|
indexdef
|
|
FROM pg_indexes
|
|
WHERE indexname NOT IN (
|
|
SELECT indexrelid::regclass::text
|
|
FROM pg_index
|
|
WHERE indisvalid
|
|
);
|
|
|
|
-- Or simpler:
|
|
SELECT indexrelid::regclass AS index_name
|
|
FROM pg_index
|
|
WHERE NOT indisvalid;</code></pre>
|
|
<p><strong>Clean up invalid index</strong>:</p>
|
|
<pre><code class="lang-sql">DROP INDEX CONCURRENTLY idx_users_email;
|
|
-- Then retry creation
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
|
|
<h3>Adding NOT NULL Columns</h3>
|
|
<p><strong>Problem</strong>: Adding NOT NULL column to non-empty table fails.</p>
|
|
<p><strong>Solution</strong>: Multi-step approach.</p>
|
|
<pre><code class="lang-sql">-- ❌ FAILS on existing rows
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;
|
|
-- ERROR: column "phone" contains null values
|
|
|
|
-- ✅ GOOD: Multi-step
|
|
-- Step 1: Add nullable column
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
|
|
|
|
-- Step 2: Backfill (in batches, see Data Migrations section)
|
|
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;
|
|
|
|
-- Step 3: Add NOT NULL constraint (fast, already validated)
|
|
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;</code></pre>
|
|
<p><strong>Alternative with default</strong>:</p>
|
|
<pre><code class="lang-sql">-- ✅ GOOD: Use default value
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT 'unknown';
|
|
|
|
-- Optionally remove default after
|
|
ALTER TABLE users ALTER COLUMN phone DROP DEFAULT;</code></pre>
|
|
<p><strong>Note</strong>: Adding column with DEFAULT rewrites entire table in PG < 11. In PG 11+, it's fast (default stored in metadata).</p>
|
|
<hr>
|
|
<h2>Zero-Downtime Patterns</h2>
|
|
<h3>Pattern 1: Adding Nullable Column</h3>
|
|
<p><strong>Scenario</strong>: Add new optional column.</p>
|
|
<p><strong>Strategy</strong>: Single-step, backward compatible.</p>
|
|
<pre><code class="lang-sql">-- Migration: Add nullable column
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20);</code></pre>
|
|
<p><strong>Deployment</strong>:</p>
|
|
<ol><li>Apply migration</li><li>Deploy new code (can read/write phone)</li></ol>
|
|
<p><strong>Compatibility</strong>:</p>
|
|
<ul><li>Old code: Ignores new column</li><li>New code: Reads/writes new column</li><li>Zero downtime</li></ul>
|
|
<hr>
|
|
<h3>Pattern 2: Adding NOT NULL Column</h3>
|
|
<p><strong>Scenario</strong>: Add required column.</p>
|
|
<p><strong>Strategy</strong>: Multi-phase deployment.</p>
|
|
<p><strong>Phase 1 Migration</strong>:</p>
|
|
<pre><code class="lang-sql">-- Add nullable column with default
|
|
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';</code></pre>
|
|
<p><strong>Phase 1 Deployment</strong>:</p>
|
|
<ul><li>Deploy code that writes to status</li><li>Old code ignores column, new code writes it</li></ul>
|
|
<p><strong>Phase 2 Migration</strong> (after all instances updated):</p>
|
|
<pre><code class="lang-sql">-- Backfill any NULLs (shouldn't be any if Phase 1 worked)
|
|
UPDATE users SET status = 'active' WHERE status IS NULL;
|
|
|
|
-- Add NOT NULL constraint
|
|
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
|
|
|
|
-- Optionally drop default
|
|
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;</code></pre>
|
|
<p><strong>Timeline</strong>:</p>
|
|
<ul><li>Day 1: Phase 1 migration + deployment</li><li>Day 7: Verify all rows have status set</li><li>Day 7: Phase 2 migration</li></ul>
|
|
<hr>
|
|
<h3>Pattern 3: Removing Column (Expand-Contract)</h3>
|
|
<p><strong>Scenario</strong>: Remove unused column.</p>
|
|
<p><strong>Strategy</strong>: Three-phase deployment.</p>
|
|
<p><strong>Phase 1</strong>: Stop writing (code change only, no migration)</p>
|
|
<ul><li>Deploy code that stops writing to old column</li><li>Old code may still read column</li></ul>
|
|
<p><strong>Phase 2</strong>: Stop reading (code change only, no migration)</p>
|
|
<ul><li>Deploy code that doesn't reference column at all</li><li>Wait for all instances to update</li></ul>
|
|
<p><strong>Phase 3</strong>: Remove column (migration)</p>
|
|
<pre><code class="lang-sql">ALTER TABLE users DROP COLUMN old_field;</code></pre>
|
|
<p><strong>Timeline</strong>:</p>
|
|
<ul><li>Week 1: Phase 1 - stop writing</li><li>Week 2: Phase 2 - stop reading</li><li>Week 3: Verify no references in logs</li><li>Week 3: Phase 3 - drop column</li></ul>
|
|
<hr>
|
|
<h3>Pattern 4: Renaming Column (Dual-Write)</h3>
|
|
<p><strong>Scenario</strong>: Rename column (email → email_address).</p>
|
|
<p><strong>Strategy</strong>: Four-phase deployment with dual-write.</p>
|
|
<p><strong>Phase 1 Migration</strong>:</p>
|
|
<pre><code class="lang-sql">-- Add new column
|
|
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
|
|
|
|
-- Create trigger for dual-write
|
|
CREATE OR REPLACE FUNCTION sync_email_columns()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.email_address := NEW.email;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER sync_email_to_email_address
|
|
BEFORE INSERT OR UPDATE ON users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION sync_email_columns();</code></pre>
|
|
<p><strong>Phase 1 Deployment</strong>:</p>
|
|
<ul><li>Application writes to email (trigger copies to email_address)</li></ul>
|
|
<p><strong>Phase 2 Migration</strong>:</p>
|
|
<pre><code class="lang-sql">-- Backfill existing rows
|
|
UPDATE users SET email_address = email WHERE email_address IS NULL;</code></pre>
|
|
<p><strong>Phase 3 Deployment</strong>:</p>
|
|
<ul><li>Application reads from email_address, writes to both</li></ul>
|
|
<p><strong>Phase 4 Migration</strong>:</p>
|
|
<pre><code class="lang-sql">-- Drop trigger
|
|
DROP TRIGGER sync_email_to_email_address ON users;
|
|
DROP FUNCTION sync_email_columns();
|
|
|
|
-- Drop old column
|
|
ALTER TABLE users DROP COLUMN email;
|
|
|
|
-- Optionally rename (fast metadata change)
|
|
-- Or keep email_address as final name</code></pre>
|
|
<p><strong>Timeline</strong>:</p>
|
|
<ul><li>Week 1: Phase 1 - add column, trigger</li><li>Week 2: Phase 2 - backfill</li><li>Week 3: Phase 3 - app reads new column</li><li>Week 4: Verify, Phase 4 - drop old column</li></ul>
|
|
<hr>
|
|
<h3>Pattern 5: Changing Column Type</h3>
|
|
<p><strong>Scenario</strong>: Change column type (age INT → age BIGINT).</p>
|
|
<p><strong>Strategy</strong>: Add new column, dual-write, swap.</p>
|
|
<p><strong>Phase 1 Migration</strong>:</p>
|
|
<pre><code class="lang-sql">-- Add new column with new type
|
|
ALTER TABLE users ADD COLUMN age_new BIGINT;</code></pre>
|
|
<p><strong>Phase 1 Deployment</strong>:</p>
|
|
<ul><li>Application writes to both age and age_new</li></ul>
|
|
<p><strong>Phase 2 Migration</strong>:</p>
|
|
<pre><code class="lang-sql">-- Backfill
|
|
UPDATE users SET age_new = age WHERE age_new IS NULL;</code></pre>
|
|
<p><strong>Phase 3 Deployment</strong>:</p>
|
|
<ul><li>Application reads from age_new</li></ul>
|
|
<p><strong>Phase 4 Migration</strong>:</p>
|
|
<pre><code class="lang-sql">-- Drop old, rename new
|
|
ALTER TABLE users DROP COLUMN age;
|
|
ALTER TABLE users RENAME COLUMN age_new TO age;</code></pre>
|
|
<p><strong>Shortcut for compatible types</strong>:</p>
|
|
<pre><code class="lang-sql">-- Some type changes don't require table rewrite
|
|
ALTER TABLE users ALTER COLUMN email TYPE TEXT; -- VARCHAR → TEXT (fast)
|
|
ALTER TABLE users ALTER COLUMN age TYPE BIGINT USING age::BIGINT; -- May rewrite table</code></pre>
|
|
<p><strong>Fast type changes (PG 12+)</strong>:</p>
|
|
<ul><li>VARCHAR(N) → VARCHAR(M) where M > N</li><li>VARCHAR → TEXT</li><li>NUMERIC(a,b) → NUMERIC(c,d) where c >= a and d = b</li></ul>
|
|
<hr>
|
|
<h3>Pattern 6: Adding Index (CONCURRENTLY)</h3>
|
|
<p><strong>Scenario</strong>: Add index without blocking writes.</p>
|
|
<p><strong>Strategy</strong>: CREATE INDEX CONCURRENTLY.</p>
|
|
<pre><code class="lang-sql">-- ❌ BAD: Locks table
|
|
CREATE INDEX idx_users_email ON users(email);
|
|
|
|
-- ✅ GOOD: No lock
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
|
|
<p><strong>Migration file</strong>:</p>
|
|
<pre><code class="lang-sql">-- V5__add_users_email_index.sql
|
|
-- NOTE: Cannot run in transaction
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);</code></pre>
|
|
<p><strong>Handling failures</strong>:</p>
|
|
<pre><code class="lang-sql">-- Check for invalid indexes
|
|
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
|
|
|
|
-- Drop and retry
|
|
DROP INDEX CONCURRENTLY idx_users_email;
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
|
|
<hr>
|
|
<h3>Pattern 7: Adding Foreign Key</h3>
|
|
<p><strong>Scenario</strong>: Add foreign key constraint.</p>
|
|
<p><strong>Strategy</strong>: Add NOT VALID, then validate.</p>
|
|
<pre><code class="lang-sql">-- Step 1: Add constraint without validation
|
|
ALTER TABLE orders
|
|
ADD CONSTRAINT fk_orders_user_id
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
NOT VALID;
|
|
|
|
-- Step 2: Validate (allows concurrent reads/writes)
|
|
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;</code></pre>
|
|
<p><strong>Locking</strong>:</p>
|
|
<ul><li>Step 1: ShareRowExclusiveLock (allows SELECT, INSERT, UPDATE, DELETE)</li><li>Step 2: ShareUpdateExclusiveLock (allows SELECT, INSERT, UPDATE, DELETE)</li></ul>
|
|
<hr>
|
|
<h2>PostgreSQL Lock Behavior</h2>
|
|
<h3>Lock Modes Reference</h3>
|
|
<p>| Lock Mode | SELECT | INSERT | UPDATE | DELETE | DDL | |-----------|--------|--------|--------|--------|-----| | AccessShareLock | ✓ | ✓ | ✓ | ✓ | ✗ | | RowShareLock | ✓ | ✓ | ✓ | ✓ | ✗ | | RowExclusiveLock | ✓ | ✓ | ✓ | ✓ | ✗ | | ShareUpdateExclusiveLock | ✓ | ✓ | ✓ | ✓ | ✗ | | ShareLock | ✓ | ✗ | ✗ | ✗ | ✗ | | ShareRowExclusiveLock | ✓ | ✗ | ✗ | ✗ | ✗ | | ExclusiveLock | ✓ | ✗ | ✗ | ✗ | ✗ | | AccessExclusiveLock | ✗ | ✗ | ✗ | ✗ | ✗ |</p>
|
|
<h3>Common DDL Operations and Locks</h3>
|
|
<p>| Operation | Lock Mode | Blocks Reads? | Blocks Writes? | |-----------|-----------|---------------|----------------| | CREATE TABLE | AccessExclusiveLock | ✗ (new table) | ✗ (new table) | | DROP TABLE | AccessExclusiveLock | ✓ | ✓ | <!-- Example of operation impact --> | ALTER TABLE ADD COLUMN | AccessExclusiveLock | ✓ | ✓ | | ALTER TABLE ADD COLUMN (with DEFAULT, PG 11+) | AccessExclusiveLock (brief) | ✗ (metadata only) | ✗ (metadata only) | | CREATE INDEX | ShareLock | ✗ | ✓ | | CREATE INDEX CONCURRENTLY | ShareUpdateExclusiveLock | ✗ | ✗ | | DROP INDEX | AccessExclusiveLock | ✓ | ✓ | | DROP INDEX CONCURRENTLY | ShareUpdateExclusiveLock | ✗ | ✗ | | ADD CONSTRAINT (validated) | AccessExclusiveLock | ✓ | ✓ | | ADD CONSTRAINT NOT VALID | ShareUpdateExclusiveLock | ✗ | ✗ | | VALIDATE CONSTRAINT | ShareUpdateExclusiveLock | ✗ | ✗ |</p>
|
|
<h3>Monitoring Locks</h3>
|
|
<p><strong>View current locks</strong>:</p>
|
|
<pre><code class="lang-sql">SELECT
|
|
locktype,
|
|
relation::regclass,
|
|
mode,
|
|
granted,
|
|
pid,
|
|
usename,
|
|
query
|
|
FROM pg_locks
|
|
JOIN pg_stat_activity USING (pid)
|
|
WHERE NOT granted
|
|
ORDER BY relation;</code></pre>
|
|
<p><strong>View blocking queries</strong>:</p>
|
|
<pre><code class="lang-sql">SELECT
|
|
blocked_locks.pid AS blocked_pid,
|
|
blocked_activity.usename AS blocked_user,
|
|
blocking_locks.pid AS blocking_pid,
|
|
blocking_activity.usename AS blocking_user,
|
|
blocked_activity.query AS blocked_query,
|
|
blocking_activity.query AS blocking_query
|
|
FROM pg_catalog.pg_locks blocked_locks
|
|
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
|
|
JOIN pg_catalog.pg_locks blocking_locks
|
|
ON blocking_locks.locktype = blocked_locks.locktype
|
|
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
|
|
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
|
|
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
|
|
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
|
|
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
|
|
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
|
|
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
|
|
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
|
|
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
|
|
AND blocking_locks.pid != blocked_locks.pid
|
|
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
|
|
WHERE NOT blocked_locks.granted;</code></pre>
|
|
<p><strong>Set statement timeout for migrations</strong>:</p>
|
|
<pre><code class="lang-sql">-- Prevent migration from locking too long
|
|
SET statement_timeout = '30s';
|
|
|
|
-- Then run migration
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20);</code></pre>
|
|
<hr>
|
|
<h2>Data Migrations</h2>
|
|
<h3>Batching Large Updates</h3>
|
|
<p><strong>Problem</strong>: <code>UPDATE users SET status = 'active'</code> locks rows and can time out.</p>
|
|
<p><strong>Solution</strong>: Batch updates with pg_sleep.</p>
|
|
<pre><code class="lang-sql">-- ❌ BAD: Locks entire table
|
|
UPDATE users SET status = 'active' WHERE status IS NULL;
|
|
|
|
-- ✅ GOOD: Batch updates
|
|
DO $$
|
|
DECLARE
|
|
batch_size INT := 1000;
|
|
rows_updated INT;
|
|
total_updated INT := 0;
|
|
BEGIN
|
|
LOOP
|
|
UPDATE users
|
|
SET status = 'active'
|
|
WHERE id IN (
|
|
SELECT id
|
|
FROM users
|
|
WHERE status IS NULL
|
|
LIMIT batch_size
|
|
);
|
|
|
|
GET DIAGNOSTICS rows_updated = ROW_COUNT;
|
|
total_updated := total_updated + rows_updated;
|
|
|
|
RAISE NOTICE 'Updated % rows (total: %)', rows_updated, total_updated;
|
|
|
|
EXIT WHEN rows_updated = 0;
|
|
|
|
COMMIT; -- Release locks
|
|
PERFORM pg_sleep(0.1); -- Avoid overwhelming database
|
|
END LOOP;
|
|
END $$;</code></pre>
|
|
<p><strong>Alternative with external script</strong>:</p>
|
|
<pre><code class="lang-python"># batch_update.py
|
|
import psycopg2
|
|
import time
|
|
|
|
conn = psycopg2.connect("dbname=mydb")
|
|
cursor = conn.cursor()
|
|
|
|
batch_size = 1000
|
|
total_updated = 0
|
|
|
|
while True:
|
|
cursor.execute("""
|
|
UPDATE users SET status = 'active'
|
|
WHERE id IN (
|
|
SELECT id FROM users WHERE status IS NULL LIMIT %s
|
|
)
|
|
""", (batch_size,))
|
|
|
|
rows = cursor.rowcount
|
|
total_updated += rows
|
|
conn.commit()
|
|
|
|
print(f"Updated {rows} rows (total: {total_updated})")
|
|
|
|
if rows == 0:
|
|
break
|
|
|
|
time.sleep(0.1)
|
|
|
|
cursor.close()
|
|
conn.close()</code></pre>
|
|
<h3>Backfilling Data</h3>
|
|
<p><strong>Scenario</strong>: New column needs data from existing columns.</p>
|
|
<pre><code class="lang-sql">-- Add column
|
|
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
|
|
|
|
-- Backfill in batches
|
|
DO $$
|
|
DECLARE
|
|
batch_size INT := 1000;
|
|
rows_updated INT;
|
|
BEGIN
|
|
LOOP
|
|
UPDATE users
|
|
SET full_name = first_name || ' ' || last_name
|
|
WHERE id IN (
|
|
SELECT id
|
|
FROM users
|
|
WHERE full_name IS NULL
|
|
LIMIT batch_size
|
|
);
|
|
|
|
GET DIAGNOSTICS rows_updated = ROW_COUNT;
|
|
EXIT WHEN rows_updated = 0;
|
|
|
|
COMMIT;
|
|
PERFORM pg_sleep(0.1);
|
|
END LOOP;
|
|
END $$;</code></pre>
|
|
<h3>Complex Data Transformations</h3>
|
|
<p><strong>Scenario</strong>: Normalize data (e.g., extract JSON to columns).</p>
|
|
<pre><code class="lang-sql">-- Existing: user_data JSONB column
|
|
-- New: email, phone columns
|
|
|
|
ALTER TABLE users ADD COLUMN email VARCHAR(255);
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
|
|
|
|
-- Backfill
|
|
UPDATE users
|
|
SET
|
|
email = user_data->>'email',
|
|
phone = user_data->>'phone'
|
|
WHERE email IS NULL;</code></pre>
|
|
<p><strong>With batching</strong>:</p>
|
|
<pre><code class="lang-python">import psycopg2
|
|
import time
|
|
|
|
conn = psycopg2.connect("dbname=mydb")
|
|
cursor = conn.cursor()
|
|
|
|
batch_size = 1000
|
|
offset = 0
|
|
|
|
while True:
|
|
cursor.execute("""
|
|
WITH batch AS (
|
|
SELECT id, user_data
|
|
FROM users
|
|
WHERE email IS NULL
|
|
LIMIT %s OFFSET %s
|
|
)
|
|
UPDATE users
|
|
SET
|
|
email = batch.user_data->>'email',
|
|
phone = batch.user_data->>'phone'
|
|
FROM batch
|
|
WHERE users.id = batch.id
|
|
""", (batch_size, offset))
|
|
|
|
rows = cursor.rowcount
|
|
conn.commit()
|
|
|
|
if rows == 0:
|
|
break
|
|
|
|
offset += batch_size
|
|
time.sleep(0.1)
|
|
|
|
cursor.close()
|
|
conn.close()</code></pre>
|
|
<hr>
|
|
<h2>Testing Migrations</h2>
|
|
<h3>Local Testing</h3>
|
|
<p><strong>1. Test on production-like data</strong>:</p>
|
|
<pre><code class="lang-bash"># Dump production schema + sample data
|
|
pg_dump -Fc --schema-only production > schema.dump
|
|
pg_dump -Fc --data-only --table=users --limit=10000 production > data.dump
|
|
|
|
# Restore to local
|
|
createdb local_test
|
|
pg_restore -d local_test schema.dump
|
|
pg_restore -d local_test data.dump
|
|
|
|
# Apply migration
|
|
migrate -database "postgres://localhost:5432/local_test" up
|
|
|
|
# Verify schema
|
|
psql local_test -c "\d users"</code></pre>
|
|
<p><strong>2. Test rollback</strong>:</p>
|
|
<pre><code class="lang-bash"># Apply migration
|
|
migrate up
|
|
|
|
# Test rollback
|
|
migrate down
|
|
|
|
# Re-apply
|
|
migrate up
|
|
|
|
# Verify data integrity
|
|
psql local_test -c "SELECT COUNT(*) FROM users"</code></pre>
|
|
<p><strong>3. Test application against new schema</strong>:</p>
|
|
<pre><code class="lang-bash"># Point application to migrated database
|
|
export DATABASE_URL="postgres://localhost:5432/local_test"
|
|
|
|
# Run tests
|
|
pytest tests/
|
|
npm test
|
|
go test ./...</code></pre>
|
|
<h3>Staging Testing</h3>
|
|
<p><strong>1. Mirror production</strong>:</p>
|
|
<pre><code class="lang-bash"># Refresh staging from production
|
|
pg_dump -Fc production | pg_restore -d staging
|
|
|
|
# Apply migration
|
|
migrate -database "$STAGING_DB" up</code></pre>
|
|
<p><strong>2. Run smoke tests</strong>:</p>
|
|
<pre><code class="lang-bash"># API smoke tests
|
|
curl https://staging.example.com/api/users
|
|
curl https://staging.example.com/api/health
|
|
|
|
# Load test (optional)
|
|
ab -n 1000 -c 10 https://staging.example.com/api/users</code></pre>
|
|
<p><strong>3. Monitor</strong>:</p>
|
|
<pre><code class="lang-sql">-- Check for slow queries
|
|
SELECT query, mean_exec_time, calls
|
|
FROM pg_stat_statements
|
|
ORDER BY mean_exec_time DESC
|
|
LIMIT 10;
|
|
|
|
-- Check for locks
|
|
SELECT * FROM pg_locks WHERE NOT granted;</code></pre>
|
|
<h3>CI/CD Testing</h3>
|
|
<p><strong>GitHub Actions example</strong>:</p>
|
|
<pre><code class="lang-yaml">name: Test Migrations
|
|
|
|
on: [push, pull_request]
|
|
|
|
jobs:
|
|
test-migrations:
|
|
runs-on: ubuntu-latest
|
|
|
|
services:
|
|
postgres:
|
|
image: postgres:15
|
|
env:
|
|
POSTGRES_DB: testdb
|
|
POSTGRES_PASSWORD: postgres
|
|
options: >-
|
|
--health-cmd pg_isready
|
|
--health-interval 10s
|
|
--health-timeout 5s
|
|
--health-retries 5
|
|
ports:
|
|
- 5432:5432
|
|
|
|
steps:
|
|
- uses: actions/checkout@v3
|
|
|
|
- name: Apply migrations
|
|
run: |
|
|
migrate -database "postgres://postgres:postgres@localhost:5432/testdb?sslmode=disable" up
|
|
|
|
- name: Verify schema
|
|
run: |
|
|
psql postgres://postgres:postgres@localhost:5432/testdb -c "\d users"
|
|
|
|
- name: Test rollback
|
|
run: |
|
|
migrate -database "postgres://postgres:postgres@localhost:5432/testdb?sslmode=disable" down
|
|
migrate -database "postgres://postgres:postgres@localhost:5432/testdb?sslmode=disable" up
|
|
|
|
- name: Run application tests
|
|
run: |
|
|
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/testdb"
|
|
pytest tests/</code></pre>
|
|
<hr>
|
|
<h2>Rollback Strategies</h2>
|
|
<h3>Strategy 1: Down Migrations</h3>
|
|
<p><strong>Approach</strong>: Write explicit rollback in down migration.</p>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Fast rollback</li><li>Built into migration tool</li><li>Easy to test</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Data loss if columns dropped</li><li>Not all changes reversible</li></ul>
|
|
<p><strong>Example</strong>:</p>
|
|
<pre><code class="lang-sql">-- 001_add_users.up.sql
|
|
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255));
|
|
|
|
-- 001_add_users.down.sql
|
|
-- Example of dangerous rollback - loses data! Use data preservation strategies instead.
|
|
DROP TABLE users; -- ⚠️ Loses data!</code></pre>
|
|
<p><strong>Safe rollback with data preservation</strong>:</p>
|
|
<pre><code class="lang-sql">-- 002_add_phone.up.sql
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
|
|
|
|
-- 002_add_phone.down.sql
|
|
-- Instead of DROP, could archive to another table
|
|
CREATE TABLE IF NOT EXISTS users_phone_archive AS
|
|
SELECT id, phone FROM users WHERE phone IS NOT NULL;
|
|
|
|
ALTER TABLE users DROP COLUMN phone;</code></pre>
|
|
<h3>Strategy 2: Database Backups</h3>
|
|
<p><strong>Approach</strong>: Backup before migration, restore if needed.</p>
|
|
<pre><code class="lang-bash"># Before migration
|
|
pg_dump -Fc production > backup_$(date +%Y%m%d_%H%M%S).dump
|
|
|
|
# Apply migration
|
|
migrate up
|
|
|
|
# If rollback needed
|
|
pg_restore -d production backup_20231027_120000.dump</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Complete rollback</li><li>Restores data</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Slow for large databases</li><li>Downtime during restore</li><li>Loses data written after migration</li></ul>
|
|
<h3>Strategy 3: Point-in-Time Recovery (PITR)</h3>
|
|
<p><strong>Approach</strong>: Use WAL archiving to recover to specific point in time.</p>
|
|
<p><strong>Setup</strong>:</p>
|
|
<pre><code class="lang-sql">-- postgresql.conf
|
|
wal_level = replica
|
|
archive_mode = on
|
|
archive_command = 'cp %p /mnt/archive/%f'</code></pre>
|
|
<p><strong>Recovery</strong>:</p>
|
|
<pre><code class="lang-bash"># Stop database
|
|
pg_ctl stop
|
|
|
|
# ⚠️ WARNING: This permanently deletes all database data
|
|
# Always verify backups before running
|
|
# Restore base backup
|
|
rm -rf $PGDATA/*
|
|
tar -xzf base_backup.tar.gz -C $PGDATA
|
|
|
|
# Create recovery.conf (PG < 12) or recovery.signal (PG 12+)
|
|
cat > $PGDATA/recovery.signal <<EOF
|
|
restore_command = 'cp /mnt/archive/%f %p'
|
|
recovery_target_time = '2023-10-27 12:00:00'
|
|
EOF
|
|
|
|
# Start database
|
|
pg_ctl start</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Precise rollback</li><li>No data loss (up to target time)</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Requires WAL archiving setup</li><li>Complex recovery process</li><li>Downtime</li></ul>
|
|
<h3>Strategy 4: Blue-Green Deployment</h3>
|
|
<p><strong>Approach</strong>: Run two databases, switch traffic.</p>
|
|
<pre><code>Blue (current) ←─── Traffic
|
|
Green (with migrations)</code></pre>
|
|
<p><strong>Process</strong>:</p>
|
|
<ol><li>Clone Blue → Green</li><li>Apply migrations to Green</li><li>Switch traffic Blue → Green</li><li>If issues, switch back to Blue</li></ol>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>Instant rollback</li><li>Zero downtime</li><li>Test migrations in production environment</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Requires load balancer/proxy</li><li>Data sync complexity</li><li>Expensive (two databases)</li></ul>
|
|
<h3>Strategy 5: Forward-Fix</h3>
|
|
<p><strong>Approach</strong>: Don't roll back, fix forward with new migration.</p>
|
|
<p><strong>Example</strong>:</p>
|
|
<pre><code class="lang-sql">-- V5__add_age_column.sql (has bug: wrong type)
|
|
ALTER TABLE users ADD COLUMN age INT; -- Should be SMALLINT!
|
|
|
|
-- Don't rollback, instead:
|
|
-- V6__fix_age_column_type.sql
|
|
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;</code></pre>
|
|
<p><strong>Pros</strong>:</p>
|
|
<ul><li>No data loss</li><li>Simple</li><li>Maintains migration history</li></ul>
|
|
<p><strong>Cons</strong>:</p>
|
|
<ul><li>Requires quick fix</li><li>Can't undo schema changes immediately</li></ul>
|
|
<p><strong>Best for</strong>: Production systems with continuous deployment.</p>
|
|
<hr>
|
|
<h2>Common Pitfalls</h2>
|
|
<h3>Pitfall 1: Adding NOT NULL Without Default</h3>
|
|
<p><strong>Problem</strong>:</p>
|
|
<pre><code class="lang-sql">-- ❌ Fails on existing rows
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;
|
|
-- ERROR: column "phone" contains null values</code></pre>
|
|
<p><strong>Fix</strong>:</p>
|
|
<pre><code class="lang-sql">-- ✅ Add with default, or add nullable then backfill
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT 'unknown';
|
|
|
|
-- Or multi-step:
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
|
|
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;
|
|
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;</code></pre>
|
|
<hr>
|
|
<h3>Pitfall 2: Renaming Columns Directly</h3>
|
|
<p><strong>Problem</strong>:</p>
|
|
<pre><code class="lang-sql">-- ❌ Breaks old code immediately
|
|
ALTER TABLE users RENAME COLUMN email TO email_address;</code></pre>
|
|
<p><strong>Fix</strong>: Use expand-contract pattern (see Zero-Downtime Patterns).</p>
|
|
<hr>
|
|
<h3>Pitfall 3: Creating Indexes Without CONCURRENTLY</h3>
|
|
<p><strong>Problem</strong>:</p>
|
|
<pre><code class="lang-sql">-- ❌ Locks table, blocks writes
|
|
CREATE INDEX idx_users_email ON users(email);</code></pre>
|
|
<p><strong>Fix</strong>:</p>
|
|
<pre><code class="lang-sql">-- ✅ No lock
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
|
|
<hr>
|
|
<h3>Pitfall 4: Large UPDATEs in One Transaction</h3>
|
|
<p><strong>Problem</strong>:</p>
|
|
<pre><code class="lang-sql">-- ❌ Locks millions of rows, can timeout
|
|
UPDATE users SET status = 'active'; -- 10M rows</code></pre>
|
|
<p><strong>Fix</strong>: Batch with LIMIT and pg_sleep (see Data Migrations).</p>
|
|
<hr>
|
|
<h3>Pitfall 5: Changing Column Type Without Care</h3>
|
|
<p><strong>Problem</strong>:</p>
|
|
<pre><code class="lang-sql">-- ❌ Rewrites entire table, locks
|
|
ALTER TABLE users ALTER COLUMN email TYPE TEXT;</code></pre>
|
|
<p><strong>Fix</strong>: Check if rewrite needed, or use add/drop pattern.</p>
|
|
<pre><code class="lang-sql">-- Fast (no rewrite) in PostgreSQL
|
|
ALTER TABLE users ALTER COLUMN email TYPE TEXT;
|
|
-- VARCHAR → TEXT is safe, no rewrite
|
|
|
|
-- Slow (rewrites table)
|
|
ALTER TABLE users ALTER COLUMN age TYPE BIGINT USING age::BIGINT;</code></pre>
|
|
<hr>
|
|
<h3>Pitfall 6: Not Testing Rollback</h3>
|
|
<p><strong>Problem</strong>: Migration applied, can't be reversed, data lost.</p>
|
|
<p><strong>Fix</strong>: Always test down migration locally.</p>
|
|
<pre><code class="lang-bash">migrate up
|
|
migrate down # Test rollback
|
|
migrate up # Re-apply</code></pre>
|
|
<hr>
|
|
<h3>Pitfall 7: Mixing Schema and Data Changes</h3>
|
|
<p><strong>Problem</strong>: Hard to debug, slow migrations.</p>
|
|
<pre><code class="lang-sql">-- ❌ Mixing concerns
|
|
ALTER TABLE users ADD COLUMN status VARCHAR(20);
|
|
UPDATE users SET status = 'active' WHERE status IS NULL;
|
|
ALTER TABLE users ALTER COLUMN status SET NOT NULL;</code></pre>
|
|
<p><strong>Fix</strong>: Separate migrations.</p>
|
|
<pre><code class="lang-sql">-- V1__add_status_column.sql (schema)
|
|
ALTER TABLE users ADD COLUMN status VARCHAR(20);
|
|
|
|
-- V2__backfill_status.sql (data)
|
|
UPDATE users SET status = 'active' WHERE status IS NULL;
|
|
|
|
-- V3__make_status_not_null.sql (schema)
|
|
ALTER TABLE users ALTER COLUMN status SET NOT NULL;</code></pre>
|
|
<hr>
|
|
<h2>Advanced Topics</h2>
|
|
<h3>Handling Schema Drift</h3>
|
|
<p><strong>Problem</strong>: Production schema differs from migration history.</p>
|
|
<p><strong>Detection</strong>:</p>
|
|
<pre><code class="lang-bash"># Dump production schema
|
|
pg_dump --schema-only production > production_schema.sql
|
|
|
|
# Apply migrations to clean database
|
|
migrate -database "postgres://localhost/test" up
|
|
pg_dump --schema-only test > migrated_schema.sql
|
|
|
|
# Compare
|
|
diff production_schema.sql migrated_schema.sql</code></pre>
|
|
<p><strong>Fix</strong>:</p>
|
|
<pre><code class="lang-bash"># Option 1: Baseline (for tools supporting it)
|
|
flyway baseline -baselineVersion=10
|
|
|
|
# Option 2: Force version
|
|
migrate force 10
|
|
|
|
# Option 3: Manually add/remove from metadata table
|
|
INSERT INTO schema_migrations (version) VALUES ('20231027120000');</code></pre>
|
|
<hr>
|
|
<h3>Multi-Tenancy Migrations</h3>
|
|
<p><strong>Strategy 1: Shared schema</strong>:</p>
|
|
<pre><code class="lang-sql">-- All tenants share same schema
|
|
-- Tenant ID in each table
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
tenant_id INT NOT NULL,
|
|
email VARCHAR(255)
|
|
);
|
|
|
|
-- Migration applies to all tenants
|
|
ALTER TABLE users ADD COLUMN phone VARCHAR(20);</code></pre>
|
|
<p><strong>Strategy 2: Schema-per-tenant</strong>:</p>
|
|
<pre><code class="lang-sql">-- Each tenant has own schema
|
|
CREATE SCHEMA tenant_123;
|
|
CREATE TABLE tenant_123.users (...);
|
|
|
|
CREATE SCHEMA tenant_456;
|
|
CREATE TABLE tenant_456.users (...);
|
|
|
|
-- Migration must run for each schema
|
|
DO $$
|
|
DECLARE
|
|
schema_name TEXT;
|
|
BEGIN
|
|
FOR schema_name IN
|
|
SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant_%'
|
|
LOOP
|
|
EXECUTE format('ALTER TABLE %I.users ADD COLUMN phone VARCHAR(20)', schema_name);
|
|
END LOOP;
|
|
END $$;</code></pre>
|
|
<hr>
|
|
<h3>Handling Enum Changes</h3>
|
|
<p><strong>Problem</strong>: Can't modify enum values in place.</p>
|
|
<pre><code class="lang-sql">-- Existing enum
|
|
CREATE TYPE status_enum AS ENUM ('pending', 'active');
|
|
|
|
-- ❌ Can't add value to enum in one step in transaction
|
|
ALTER TYPE status_enum ADD VALUE 'inactive'; -- Works, but not in transaction</code></pre>
|
|
<p><strong>Safe approach</strong>:</p>
|
|
<pre><code class="lang-sql">-- Step 1: Create new enum
|
|
CREATE TYPE status_enum_new AS ENUM ('pending', 'active', 'inactive');
|
|
|
|
-- Step 2: Add new column with new enum
|
|
ALTER TABLE users ADD COLUMN status_new status_enum_new;
|
|
|
|
-- Step 3: Migrate data
|
|
UPDATE users SET status_new = status::TEXT::status_enum_new;
|
|
|
|
-- Step 4: Drop old column, rename new
|
|
ALTER TABLE users DROP COLUMN status;
|
|
ALTER TABLE users RENAME COLUMN status_new TO status;
|
|
|
|
-- Step 5: Drop old enum
|
|
DROP TYPE status_enum;
|
|
|
|
-- Step 6: Rename new enum (optional)
|
|
ALTER TYPE status_enum_new RENAME TO status_enum;</code></pre>
|
|
<hr>
|
|
<h3>Partitioned Tables</h3>
|
|
<p><strong>Adding partitions</strong>:</p>
|
|
<pre><code class="lang-sql">-- Existing partitioned table
|
|
CREATE TABLE events (
|
|
id BIGSERIAL,
|
|
event_date DATE,
|
|
data JSONB
|
|
) PARTITION BY RANGE (event_date);
|
|
|
|
-- Add new partition (safe, no lock on parent)
|
|
CREATE TABLE events_2023_11 PARTITION OF events
|
|
FOR VALUES FROM ('2023-11-01') TO ('2023-12-01');</code></pre>
|
|
<p><strong>Detaching partitions</strong>:</p>
|
|
<pre><code class="lang-sql">-- Detach old partition (for archiving)
|
|
ALTER TABLE events DETACH PARTITION events_2023_01;
|
|
|
|
-- Archive
|
|
\copy events_2023_01 TO '/archive/events_2023_01.csv' CSV
|
|
|
|
-- Drop partition after archiving (safe cleanup operation)
|
|
DROP TABLE events_2023_01;</code></pre>
|
|
<hr>
|
|
<h3>Managing Long-Running Migrations</h3>
|
|
<p><strong>Problem</strong>: Migration takes hours, blocks deployments.</p>
|
|
<p><strong>Solution</strong>: Run migrations outside deployment.</p>
|
|
<p><strong>Workflow</strong>:</p>
|
|
<ol><li>Run migration manually during low-traffic window</li><li>Mark as applied in migration metadata</li><li>Deploy application (skips already-applied migration)</li></ol>
|
|
<p><strong>Example</strong>:</p>
|
|
<pre><code class="lang-bash"># Manually apply slow migration
|
|
psql production < migrations/V5__add_large_index.sql
|
|
|
|
# Mark as applied
|
|
INSERT INTO schema_migrations (version, description, success)
|
|
VALUES ('V5', 'add_large_index', TRUE);
|
|
|
|
# Deploy application
|
|
# Migration tool sees V5 already applied, skips it</code></pre>
|
|
<hr>
|
|
<p><strong>End of Reference</strong></p>
|
|
<p>This reference covers PostgreSQL migrations comprehensively. For practical scripts and examples, see the <code>scripts/</code> and <code>examples/</code> directories.</p>
|
|
<footer>
|
|
<div class="fmap">
|
|
<div><b>Build</b>
|
|
<a href="https://hanzo.app">hanzo.app</a> app builder
|
|
<a href="https://hanzo.codes">hanzo.codes</a> code with an agent
|
|
<a href="https://hanzoskills.com">hanzoskills.com</a> what an agent reads</div>
|
|
<div><b>Run</b>
|
|
<a href="https://console.hanzo.ai">console.hanzo.ai</a> cloud console
|
|
<a href="https://api.hanzo.ai">api.hanzo.ai</a> the API</div>
|
|
<div><b>Account</b>
|
|
<a href="https://hanzo.id">hanzo.id</a> sign in
|
|
<a href="https://pay.hanzo.ai">pay.hanzo.ai</a> top up
|
|
<a href="https://billing.hanzo.ai">billing.hanzo.ai</a> invoices and usage</div>
|
|
<div><b>Company</b>
|
|
<a href="https://hanzo.ai">hanzo.ai</a> canonical
|
|
<a href="https://hanzo.ventures">hanzo.ventures</a> ventures</div>
|
|
</div>
|
|
<div class="fend"><a href="/reference.md">This page as plain text</a> · <a href="/llms.txt">Index for agents</a></div>
|
|
</footer>
|
|
</div></body></html> |