digoal
2024-01-04
PostgreSQL , PolarDB , DuckDB , online DDL , 排他锁 , pg_migrate , pg_repack
https://github.com/phillbaker/pg_migrate
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.
Forked from the excellent pg_repack project (https://reorg.github.io/pg_repack).
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.
cd /tmp
git clone --depth 1 https://github.com/phillbaker/pg_migrate
cd pg_migrate/
USE_PGXS=1 make install
root@56000550f873:/tmp/pg_migrate# psql
psql (14.10 (Debian 14.10-1.pgdg110+1))
Type "help" for help.
postgres=# create extension pg_migrate ;
CREATE EXTENSION
postgres=# \q
root@56000550f873:/tmp/pg_migrate# pg_migrate --help
pg_migrate migrates a PostgreSQL table avoiding long locks.
Usage:
pg_migrate [OPTION]... [DBNAME]
Options:
-t, --table=TABLE table to target
-d, --database=DATABASE database in which the table lives
-s, --tablespace=TBLSPC move table to a new tablespace
-a, --alter=ALTER SQL of the alter statement
-N, --execute whether to run the migration
-j, --jobs=NUM Use this many parallel jobs for each table
-T, --wait-timeout=SECS timeout to cancel other backends on conflict
-D, --no-kill-backend don't kill other backends when timed out
-k, --no-superuser-check skip superuser checks in client
Connection options:
-d, --dbname=DBNAME database to connect
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
Generic options:
-e, --echo echo queries
-E, --elevel=LEVEL set output message level
--help show this help, then exit
--version output version information, then exit
Read the website for details: <https://github.com/phillbaker/pg_migrate>.
Report bugs to <https://github.com/phillbaker/pg_migrate/issues>.
