Track your PostgreSQL database structure in a version control system.
SchemaKeeper is a read-only tool that saves each tracked schema object as a separate file, so schema changes become small, reviewable diffs:
schemakeeper dump /path/to/dump # dump structure to files
schemakeeper verify /path/to/dump # verify files against database/path/to/dump
├── extensions/
│ ├── pgcrypto.txt
│ └── btree_gist.txt
└── structure/
├── public/
│ ├── tables/
│ │ ├── users.txt
│ ├── views/
│ │ └── active_users.txt
│ ├── materialized_views/
│ │ └── monthly_stats.txt
│ ├── functions/
│ │ └── validate_email(text).sql
│ ├── procedures/
│ │ └── refresh_cache(int4).sql
│ ├── triggers/
│ │ └── orders.audit_trigger.sql
│ ├── types/
│ │ └── order_status.txt
│ └── sequences/
│ └── orders_id_seq.txt
└── billing/
├── tables/
│ └── invoices.txt
└── functions/
└── calc_tax(numeric).sql
- Untracked changes: A teammate runs
ALTER TABLEdirectly in production. Migrations won't catch it. SchemaKeeper will. - Environment drift: Staging has an extra index, dev is missing a trigger. You only find out when something breaks. SchemaKeeper surfaces every difference.
- Schema review in PRs: Migrations show what you intended. SchemaKeeper shows what actually happened — every column, constraint, and function definition. All reviewable in a normal
git diff.
SchemaKeeper complements your migration tool, it doesn't replace it.
pg_dump -s is for recreating schemas. SchemaKeeper is for tracking and reviewing schema changes in Git.
- One file per object.
pg_dump -sputs the entire schema into a single file. SchemaKeeper gives each object its own file, so changed objects show up as changed files ingit status. - Built-in drift detection.
schemakeeper verifycompares a live database against the committed dump and prints unified diffs.
- PHP >= 7.4
ext-pdo+ext-pdo_pgsql- PostgreSQL 10+
composer require schema-keeper/schema-keeperwget https://github.com/dmytro-demchyna/schema-keeper/releases/latest/download/schemakeeper.phar
chmod +x schemakeeper.phar
./schemakeeper.phar --versionNote: Examples below use
schemakeeperas the command name. Replace withvendor/bin/schemakeeperor./schemakeeper.phardepending on your installation method.
1. Dump your database
schemakeeper dump /path/to/dump -h localhost -p 5432 -d mydb -U postgresIf the database requires a password, see Password handling.
2. Commit the result
git add /path/to/dump
git commit -m "Add database structure dump"3. Add verification to CI
Add verify to your CI pipeline (against the test database, after applying migrations). This ensures every migration is accompanied by an up-to-date dump:
- name: Verify database structure
run: schemakeeper verify /path/to/dump -h localhost -p 5432 -d mydb -U postgres
env:
PGPASSWORD: ${{ secrets.DB_PASSWORD }}Prefer PHPUnit over CLI? See PHPUnit integration to run verification as a test.
4. Monitor production for drift
Run verify against your production database on a schedule to catch untracked DDL — the ALTER TABLE someone ran directly without a migration:
schemakeeper verify /path/to/dump -h prod-host -p 5432 -d mydb -U postgresverify exits with code 1 on mismatch. Set it up as a cron job or a post-deployment step on any machine with database access.
When the database doesn't match the committed dump, schemakeeper verify prints unified diffs for every difference:
--- functions/public.func_sql_simple(integer, integer)
+++ functions/public.func_sql_simple(integer, integer)
@@ @@
CREATE OR REPLACE FUNCTION public.func_sql_simple(a integer, b integer)
RETURNS integer
LANGUAGE sql
+ IMMUTABLE
AS $function$
- SELECT a + b;
+ SELECT a * b;
$function$
--- /dev/null
+++ triggers/public.test_table.notify_on_update
@@ @@
+CREATE TRIGGER notify_on_update AFTER UPDATE ON test_table FOR EACH ROW EXECUTE FUNCTION trig_test()
--- types/public.test_enum_type
+++ types/public.test_enum_type
@@ @@
-{enum1,enum2}
+{enum1,enum2,enum3}The diff shows:
func_sql_simplegainedIMMUTABLEand its body changed froma + btoa * btest_tablegot a new triggernotify_on_updatetest_enum_typegot a new enum valueenum3
See File format reference for tracked object types, file naming, and example output.
SchemaKeeper can also run as a PHPUnit test that fails on schema drift. See PHPUnit integration for setup instructions.
See CLI reference for the full list of options, filter flags, exit codes, and password handling.
Different objects live in separate files, so changes to different objects auto-merge without conflicts.
When two branches modify the same object:
- Merge the branch as usual
- Accept either side of each conflict (
--oursor--theirs) - Apply all migrations from both branches to your local database
- Run
schemakeeper dump - Commit the result
The choice in step 2 doesn't matter — step 4 overwrites the files with the correct state.
A failing verify means the database doesn't match the committed dump.
| Cause | Fix |
|---|---|
| Forgot to dump after migration | Run dump and commit the updated files |
| Untracked DDL ran directly on database | Create a migration (or revert the change), then re-dump |
| Stale dump after merge | Re-apply migrations and re-dump (see above) |
| Environment-specific object | Exclude it with --skip-schema or --skip-section |
Not tracked:
- RLS policies
- Roles/permissions (GRANT/REVOKE)
- Rules
- Foreign data wrappers
- Publications/subscriptions
- Event triggers
- Operators and operator classes
- Aggregate and window functions
- Multirange types
- Comments (
COMMENT ON)
Procedures require PostgreSQL 11+. On older versions, the procedures section is empty.
Cross-version formatting: Dumps are deterministic within a PostgreSQL major version. Some formatting may change after a PostgreSQL major upgrade.
Contributions are welcome. Please see CONTRIBUTING.md for guidelines.
MIT — see LICENSE for details.