npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@clickup/pg-mig

v2.10.297

Published

PostgreSQL schema migration tool with micro-sharding and clustering support

Downloads

11

Readme

@clickup/pg-mig: PostgreSQL schema migration tool with micro-sharding and clustering support

See also Full API documentation.

CI run

The tool allows to create a PostgreSQL database schema (with tables, indexes, sequences, functions etc.) and apply it consistently across multiple PG hosts (even more, across multiple micro-shard schemas on multiple hosts). The behavior is transactional per each micro-shard per version ("all or nothing").

In other words, pg-mig helps to keep your database clusters' schemas identical (each micro-shard schema will have exactly the same DDL structure as any other schema on all other PG hosts).

Usage

pg-mig
  [--migdir=path/to/my-migrations/directory]
  [--hosts=master1,master2,...]
  [--port=5432]
  [--user=user-which-can-apply-ddl]
  [--pass=password]
  [--db=my-database-name]
  [--undo=20191107201239.my-migration-name.sh]
  [--make=my-migration-name@sh]
  [--parallelism=8]
  [--dry]
  [--list]
  [--ci]

All of the arguments are optional: the tool tries to use PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE environment variables which are standard for e.g. psql.

It also uses PGMIGDIR environment variable as a default value for --migdir option.

When running in default mode, pg-mig tool reads (in order) the migration versions *.up.sql files from the migration directory and applies them all of the hostnames passed (of course, checking whether it has already been applied before or not). See below for more details.

Migration Version File Format

The migration version file name has the following format, examples:

20191107201239.add-table-abc.sh0000.up.sql
20191107201239.add-table-abc.sh0000.dn.sql
20231317204837.some-other-name.sh.up.sql
20231317204837.some-other-name.sh.dn.sql
20231203493744.anything-works.public.up.sql
20231203493744.anything-works.public.dn.sql

Here,

  • the 1st part is a UTC timestamp when the migration version file was created,
  • the 2nd part is a descriptive name of the migration (can be arbitrary),
  • the 3rd part is the "PostgreSQL schema name prefix" (micro-shard name prefix)
  • the 4th part is either "up" ("up" migration) or "dn" ("down" migration). Up-migrations roll the database schema version forward, and down-migrations allow to undo the changes.

It is the responsibility of the user to create up- and down-migration SQL files. Basically, the user provides DDL SQL queries on how to roll the database schema forward and how to roll it backward.

You can use any psql-specific instructions in *.sql files: they are fed to psql tool directly. E.g. you can use environment variables, \echo, \ir for inclusion etc. See psql documentation for details.

Applying the Migrations

Each migration version will be applied (in order) to all PG schemas (aka micro-shards) on all hosts whose names start from the provided prefix (if multiple migration files match some schema, then only the file with the longest prefix will be used; in the above example, prefix "sh" effectively works as "sh* except sh0000" wildcard).

The main idea is that, if the migration file application succeeds, then it will be remembered on the corresponding PG host, in the corresponding schema (micro-shard) itself. So next time when you run the tool, it will understand that the migration version has already been applied, and won't try to apply it again.

When the tool runs, it prints a live-updating progress, which migration version file is in progress on which PG host in which schema (micro-shard). In the end, it prints the final versions map across all of the hosts and schemas.

Undoing the Migrations

If --undo argument is used, then the tool will try to run the down-migration for the the corresponding version everywhere. If it succeeds, then it will remember that fact on the corresponding PG host in the corresponding schema. Only the very latest migration version applied can be undone.

Undoing migrations in production is not recommended (since the code which uses the database may rely on its new structure), although you can use it of course. The main use case for undoing the migrations is while development: you may want to test your DDL statements multiple times, or you may pull from Git and get someone else's migration before yours, so you'll need to undo your migration and recreate its files.

Creating the New Migration Files

If --make argument is used, pg-mig creates a new pair of empty files in the migration directory. E.g. if you run:

pg-mig --migdir=my-dir --make=my-migration-name@sh

then it will create a pair of files which looks like my-dir/20231203493744.my-migration-name.sh.up.sql and my-dir/20231203493744.my-migration-name.sh.dn.sql which you can edit further.

New migration version files can only be appended in the end. If pg-mig detects that you try to apply migrations which conflict with the existing migration versions remembered in the database, it will print the error and refuse to continue. This is similar to "fast-forward" mode in Git.