- pg_migrate 0.1.1
- Migrates a PostgreSQL table avoiding long locks
pg_migrate -- Perform schema changes in PostgreSQL with minimal locks
- Download: https://github.com/phillbaker/pg_migrate/releases
- Development: https://github.com/phillbaker/pg_migrate
- Bug Reports: https://github.com/phillbaker/pg_migrate/issues
pg_migrate is a PostgreSQL extension and CLI which lets you make schema
changes to tables and indexes. Unlike
ALTER TABLE it works online, without
holding a long lived exclusive lock on the processed tables during the
migration. It builds a copy of the target table and swaps them.
Please check the documentation (in the
doc directory or online) for
installation and usage instructions.
Forked from the excellent pg_repack project (https://reorg.github.io/pg_repack).
Supported Postgres Versions
Postgres >= 9.6
apt-get to install the package matching the Postgres version (
postgresql-<version>-pg_migrate) being run from this repo's APT repository.
To add to your
/etc/apt/sources.list.d, add the signing GPG key, and update the package DB, run:
curl -L https://github.com/phillbaker/pg_migrate/releases/download/apt-release-amd64/apt-add-repo | sh
Then to install, for example, for Postgres 10:
apt-get install -y postgresql-10-pg_migrate
Load the pg_migrate Postgres extension in the database you want to work on:
psql -c "DROP EXTENSION IF EXISTS pg_migrate cascade; CREATE EXTENSION pg_migrate" -d postgres
Change the type of a column
pg_migrate --table=my_table --alter='ALTER COLUMN id TYPE bigint' # Add --execute to run
Add a column with a default (non-nullable)
pg_migrate --table=my_table --alter='ADD COLUMN foo integer NOT NULL DEFAULT 42' # Add --execute to run
- Unique constraints are converted into unique indexes, they are equivalent in Postgres. However, this may be an unexpected change.
- Index names on the target table and foreign key constraints are changed during the migration.
- If the generated names are > 63 characters, this will likely break
- If the target table is used in views, those objects will continue to reference the original table - this is not supported currently.
- If the target table is used in stored procedures, those functions are stored as text so are not linked through object IDs and will reference the migrated table.
- DDL to drop columns or add columns without a default is not currently supported
- Hosted PG databases (RDS, Cloud SQL) are not supported because they do not allow installing custom extensions.