Squashing a history of migrations.

[jenstroeger]

Looks like discussion #927 is somewhat related.

We have a lengthy history of Alembic migrations on a single Postgres db, and we want to squash them into one single migration step (mainly for performance reasons when testing, or standing up a deployment). So here’s where I am at the moment, with one detail still open (see below):

• Create an empty db in the psql console:

  drop database the_db; create database the_db;
  

• Run all migrations to the head

  alembic upgrade head
  

• Create a dump of everything in the db:

  pg_dump --host localhost --port 5432 --user postgres --inserts the_db > pgdump.sql
  

• Delete all existing Alembic migrations, and create a new one:

  rm alembic/versions/*
  alembic revision -m "Initial db setup"
  

• Paste the dump file into that new migration:

  def upgrade():
      dump = """
      -- Here goes the pgdump.sql
      """
      op.execute(sa.text(dump))
  
  def downgrade():
      pass  # Base is the pg dump.

And that’s where things become a little tricky. First, as mentioned in comment psycopg/psycopg#958 (comment) the psycopg driver doesn’t really support the files written by pg_dump (though it doesn’t error out in our case).

Second, the dump contains the alembic_version table as well, but that’s actually managed by Alembic as per documentation here:

The process which occurred here included that Alembic first checked if the database had a table called alembic_version, and if not, created it. [...] It then invokes the upgrade() method in each file to get to the target revision.

When I remove that table from the dump file, then running that new squashed migration against an empty db raises:

psycopg.errors.UndefinedTable: relation "alembic_version" does not exist
LINE 1: INSERT INTO alembic_version (version_num) VALUES ('48de627e2...

But the docs said the Alembic creates that table before running the migrations, no? So, I used the original dump file (the one with the alembic_version table) and then I get:

sqlalchemy.exc.ProgrammingError: (psycopg.errors.DuplicateTable) relation "alembic_version" already exists

So now that table exists? Not quite sure what’s going on.

But considering the aforementioned psycopg comment that dump files aren’t supported, and considering the previous conversation #927 I’m curious about the recommended approach to squash a history of migrations? Might Alembic not quite be suited here, and should we just restore the dump manually and start Alembic from that?

[zzzeek]

hi -

if you are making a dump file like that where that's to become the initial migration for a clean DB, then yes you have to attend to the alembic_version table in that dump file. that "INSERT INTO alembic_version (version_num) VALUES ('48de627e2.." is in your dump file. you have to remove those INSERT statements also.

if you run your alembic with a SQL dump that has nothing about alembic_version in it, and alembic_version does not exist in the target DB, alembic will see the database as unmanaged and create the table.

[cjw296]

@zzzeek - is there a documented pattern for squashing a big set of existing migrations that have already been applied down into one?

[zzzeek]

i sort of thought we had a paragraph on this but maybe it got edited ou

put your codebase such that your MetaData represents what you want the "squashed" version to look like. then connect to a blank dev database and do an autogenerate. that's now your base revision.

it's a bit manual but that's all it really is

[cjw296]

In case it helps others, I did:

pg_dump --schema-only --no-owner ...  > alembic/versions/initial.sql

After some manual massaging of initial.sql, I then put this in place of the latest revision that was applied to the database:

from pathlib import Path

# revision identifiers, used by Alembic.
revision = 'aae0bbd75652'
down_revision = None

from alembic import op


def upgrade():
    for sql in (Path(__file__).parent / 'initial.sql').read_text().split(";\n"):
        if not sql.strip():
            continue
        op.execute(sql)

It looks like this has worked when run against the production db:

uv run alembic current
aae0bbd75652 (head)

uv run alembic check
No new upgrade operations detected.

[will-specht]

When I remove that table from the dump file, then running that new squashed migration against an empty db raises:

psycopg.errors.UndefinedTable: relation "alembic_version" does not exist
LINE 1: INSERT INTO alembic_version (version_num) VALUES ('48de627e2...

But the docs said the Alembic creates that table before running the migrations, no? So, I used the original dump file (the one with the alembic_version table) and then I get:

sqlalchemy.exc.ProgrammingError: (psycopg.errors.DuplicateTable) relation "alembic_version" already exists

So now that table exists? Not quite sure what’s going on.

For anyone running into this issue after trying to follow the steps above I think I found the issue. For me, pg_dump was erasing my default search_path SELECT pg_catalog.set_config('search_path', '', false);. This meant after running the initial script, alembic_version couldn't be found because it was looking in the blank schema not public schema which should be the default. When left in, the pg_dump create table explicitly names the schema, public.alembic_version, so there is a conflict with the table created by alembic.

Hopes this helps anyone that was spinning in circles like I was.