pg_migrate

This Release
pg_migrate 0.1.1
Date
Status
Unstable
Other Releases
Abstract
Migrates a PostgreSQL table avoiding long locks
Description
Reorganize tables in PostgreSQL databases with minimal locks
Released By
phillbaker
License
BSD
Resources
Special Files
Tags

Extensions

pg_migrate 0.1.1
Migrates a PostgreSQL table avoiding long locks

Documentation

release
What to do to release pg_repack
CHANGELOG
Changelog
pg_repack
pg_repack -- Reorganize tables in PostgreSQL databases with minimal locks

README

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

About

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

Installation

Linux

Use 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

Examples

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

Known Limitations

  • 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.