You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@airflow.apache.org by Ben Hancock <bh...@alm.com.INVALID> on 2024/08/15 23:53:24 UTC
Restoring the metadata database in Postgres
Hi fellow Airflow users,
I recently did some routine maintenance of the metadata database for one
of our Airflow instances, using the `db clean` command to expunge old
records. We use Postgres as a back-end and I made sure to create a
backup beforehand using `pg_dump`. Afterward, I also exported the
archived tables to CSV using the `db export-archived` command.
Everything went smoothly. My question is: What are the considerations or
gotchas in the event that a `db clean` operation *does not* go smoothly?
The most straightforward path would seem to be to halt the scheduler and
webserver, drop the metadata database, then restore from the backup
using `pg_restore`. I imagine that would work fine. But I wonder whether
any other users have encountered sharp edges following this process?
Short of doing a full restore, it also seems feasible (albeit more
laborious) to bring the expunged data back from the archived tables ---
or archived CSV files --- using something like:
INSERT INTO {target} SELECT * FROM {archived_table};
... I imagine that could be scripted to more easily match the archive
table name to the correct target, if it came down to that.
Hopefully this falls under the category of things that I never actually
_need_ to know, but any advice or wisdom would be appreciated.
Thanks,
Ben
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
For additional commands, e-mail: users-help@airflow.apache.org
Re: Restoring the metadata database in Postgres
Posted by Ben Hancock <bh...@alm.com.INVALID>.
Daniel Standish writes:
> I have however heard reports of it taking a super long time and
> perhaps failing to complete, when the db table is quite large (and
> perhaps pushing the capabilities of the db instance), likely due to
> statement timeout. When the process fails, probably it will be in
> the delete step, after the archive table has been created. In this
> case the negative consequence is you now have an archive table (of
> the records that were to be deleted) and you still have all the
> records in the main table. So you can drop the archive, increase
> statement timeout and try again. Better though to batch it by
> starting with an older "delete before timestamp" and incrementally
> make that more recent. This will result in smaller batches.
Thanks Daniel -- this is helpful insight and performing cleanups in
small batches sounds like good advice.
> As for gotchas, the one that comes to mind is if say you did this
> prior to an upgrade, then did not realize that you put in the wrong
> date until after the upgrade, and the upgrade had migrations in a
> table that you cleaned.
Aha, well noted. It sounds like the best approach here is to perform any
upgrades (and associated migrations) /prior/ to doing a cleanup ... and
for any archives we keep around, to note the Airflow version from which
they were exported.
I appreciate your help!
Ben
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
For additional commands, e-mail: users-help@airflow.apache.org
Re: Restoring the metadata database in Postgres
Posted by Daniel Standish <da...@astronomer.io.INVALID>.
Should add, good on you for backing up prior to running the command. And
yeah if you did put in the wrong date or something, you would probably want
to insert from the archive table (and that's why it's there). As for
gotchas, the one that comes to mind is if say you did this prior to an
upgrade, then did not realize that you put in the wrong date until after
the upgrade, and the upgrade had migrations in a table that you cleaned.
Then you might have to work a little harder to craft your insert
statement. But it probably would not be too bad. The migrations can be
found in the airflow github repo, and you can also print out the sql for
migrations (between any two versions or revisions) in the airflow db
upgrade command. See the `--show-sql-only` option.
On Thu, Aug 15, 2024 at 6:22 PM Daniel Standish <
daniel.standish@astronomer.io> wrote:
> Hi Ben
>
> I think the main risk you worry about with this kind of thing is deleting
> records you did not intend to. IIRC the command is reasonably well tested
> and has been used pretty widely and I am not aware of any "it ate my
> database" reports.
>
> I have however heard reports of it taking a super long time and perhaps
> failing to complete, when the db table is quite large (and perhaps pushing
> the capabilities of the db instance), likely due to statement timeout.
> When the process fails, probably it will be in the delete step, after the
> archive table has been created. In this case the negative consequence is
> you now have an archive table (of the records that were to be deleted) and
> you still have all the records in the main table. So you can drop the
> archive, increase statement timeout and try again. Better though to batch
> it by starting with an older "delete before timestamp" and incrementally
> make that more recent. This will result in smaller batches. There is an
> idea floating around to refactor the command to do batching always, but
> that's a project without a champion at this time -- if you're interested,
> have at it!
>
> On Thu, Aug 15, 2024 at 4:54 PM Ben Hancock <bh...@alm.com.invalid>
> wrote:
>
>> Hi fellow Airflow users,
>>
>> I recently did some routine maintenance of the metadata database for one
>> of our Airflow instances, using the `db clean` command to expunge old
>> records. We use Postgres as a back-end and I made sure to create a
>> backup beforehand using `pg_dump`. Afterward, I also exported the
>> archived tables to CSV using the `db export-archived` command.
>>
>> Everything went smoothly. My question is: What are the considerations or
>> gotchas in the event that a `db clean` operation *does not* go smoothly?
>>
>> The most straightforward path would seem to be to halt the scheduler and
>> webserver, drop the metadata database, then restore from the backup
>> using `pg_restore`. I imagine that would work fine. But I wonder whether
>> any other users have encountered sharp edges following this process?
>>
>> Short of doing a full restore, it also seems feasible (albeit more
>> laborious) to bring the expunged data back from the archived tables ---
>> or archived CSV files --- using something like:
>>
>> INSERT INTO {target} SELECT * FROM {archived_table};
>>
>> ... I imagine that could be scripted to more easily match the archive
>> table name to the correct target, if it came down to that.
>>
>> Hopefully this falls under the category of things that I never actually
>> _need_ to know, but any advice or wisdom would be appreciated.
>>
>> Thanks,
>>
>> Ben
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
>> For additional commands, e-mail: users-help@airflow.apache.org
>>
>>
Re: Restoring the metadata database in Postgres
Posted by Daniel Standish <da...@astronomer.io.INVALID>.
Hi Ben
I think the main risk you worry about with this kind of thing is deleting
records you did not intend to. IIRC the command is reasonably well tested
and has been used pretty widely and I am not aware of any "it ate my
database" reports.
I have however heard reports of it taking a super long time and perhaps
failing to complete, when the db table is quite large (and perhaps pushing
the capabilities of the db instance), likely due to statement timeout.
When the process fails, probably it will be in the delete step, after the
archive table has been created. In this case the negative consequence is
you now have an archive table (of the records that were to be deleted) and
you still have all the records in the main table. So you can drop the
archive, increase statement timeout and try again. Better though to batch
it by starting with an older "delete before timestamp" and incrementally
make that more recent. This will result in smaller batches. There is an
idea floating around to refactor the command to do batching always, but
that's a project without a champion at this time -- if you're interested,
have at it!
On Thu, Aug 15, 2024 at 4:54 PM Ben Hancock <bh...@alm.com.invalid>
wrote:
> Hi fellow Airflow users,
>
> I recently did some routine maintenance of the metadata database for one
> of our Airflow instances, using the `db clean` command to expunge old
> records. We use Postgres as a back-end and I made sure to create a
> backup beforehand using `pg_dump`. Afterward, I also exported the
> archived tables to CSV using the `db export-archived` command.
>
> Everything went smoothly. My question is: What are the considerations or
> gotchas in the event that a `db clean` operation *does not* go smoothly?
>
> The most straightforward path would seem to be to halt the scheduler and
> webserver, drop the metadata database, then restore from the backup
> using `pg_restore`. I imagine that would work fine. But I wonder whether
> any other users have encountered sharp edges following this process?
>
> Short of doing a full restore, it also seems feasible (albeit more
> laborious) to bring the expunged data back from the archived tables ---
> or archived CSV files --- using something like:
>
> INSERT INTO {target} SELECT * FROM {archived_table};
>
> ... I imagine that could be scripted to more easily match the archive
> table name to the correct target, if it came down to that.
>
> Hopefully this falls under the category of things that I never actually
> _need_ to know, but any advice or wisdom would be appreciated.
>
> Thanks,
>
> Ben
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
> For additional commands, e-mail: users-help@airflow.apache.org
>
>