A database migration is a planned change to database structure or related objects applied to an existing database. PostgreSQL upgrades are usually manageable, but moving from another relational database system requires careful preparation because mistakes can cause performance problems, outages, or data corruption. section 10.0
Treat compatibility as something to verify, not assume. A MySQL or MariaDB database may contain years of data encoded according to historical defaults, even after the platform’s current defaults have changed. section 10.3
Encoding failure during a PostgreSQL import
A dump created from a MySQL database using latin1 can fail when imported into a UTF-8 PostgreSQL database: `psql service=staging -f prod_dump.sql` may report `ERROR: invalid byte sequence for encoding "UTF8": 0xe9`. Converting the dump with `iconv -f latin1 -t utf8 prod_dump_latin1.sql -o prod_dump_utf8.sql` addresses the character encoding, but a subsequent `0x00` error may require removing null bytes before retrying. Validate database encoding and locale settings rather than trusting application-level SQL compatibility. section 10.3
Reduce migration risk by:
- Review differences between database types and versions, including upgrade paths and release notes. section 10.0
- Test the complete upgrade or migration before production, including representative data and failure handling. section 10.0
- Scan schema-change code with Squawk before execution; it can warn about locks that block reads or writes and suggest safer alternatives. section 2.10.1
> squawk example.sql
example.sql:2:1: warning: prefer-text-field
2 | --
3 | -- Create model Bar
4 | --
5 | CREATE TABLE "core_bar" (
6 | "id" serial NOT NULL PRIMARY KEY,
7 | "alpha" varchar(100) NOT NULL
8 | );
note: Changing the size of a varchar field requires an ACCESS EXCLUSIVE lock.
help: Use a text field with a check constraint.
example.sql:9:2: warning: require-concurrent-index-creation
9 |
10 | CREATE INDEX "field_name_idx" ON "table_name" ("field_name");
note: Creating an index blocks writes.
note: Create the index CONCURRENTLY.
example.sql:11:2: warning: disallowed-unique-constraint
11 |
12 | ALTER TABLE table_name ADD CONSTRAINT field_name_constraint
UNIQUE (field_name);
note: Adding a UNIQUE constraint requires an ACCESS EXCLUSIVE lock
which blocks reads.
help: Create an index CONCURRENTLY and create the constraint using
the index.
Inspect concrete Squawk warnings showing how migration DDL can acquire restrictive locks and how concurrent index creation can reduce blocking. section 2.10.1
