even recent PG's seem to still do this: https://dba.stackexchange.com/a/111092
so your only option other than downtime is to create a new table, migrate all the data towards it, then drop the old table and rename - then you'd need to restore additional data you might have lost while that occurred. the "batch" mode of Alembic helps with this though I'd definitely want to produce this as a fixed SQL script, test it on a staging database first, then run in production. then again, the ALTER will probably be extremely quick if you can in fact pause the load for a few minutes, since you are defaulting to NULL. On Tue, Jan 16, 2018 at 3:42 PM, George V. Reilly <george.v.rei...@gmail.com> wrote: > Twice recently, on two different PostgreSQL 9.5 databases hosted at Amazon > RDS, we've been unable to apply Alembic migrations. We have successfully run > dozens of Alembic migrations in the past against one of these databases but > those were quieter times for us. As far as I can tell, it's because our > databases are too busy now and the load never lets up. > > The Alembic process just hangs and other database queries start backing up. > The only recourse is to kill the Alembic process. In at least one case, > we've also had to track down the ALTER statement in pg_stat_activity and > terminate the associated pid. Once I had to reboot the database at RDS > before it recovered. > > The SQL generated for one of these migrations is: > ALTER TABLE redacted ADD COLUMN report_format VARCHAR(4) > which is as simple as it gets. > > In both cases, we're trying to add columns to tables that are hardly ever > modified—and not at all while the Alembic migrations were executing. > However, many of our most frequent SELECTs JOIN to these tables, so they're > constantly being read from. > > Aside, from making a planned outage, do we have any good options? > > Thanks! > /George Reilly > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
