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 2023/08/24 17:45:15 UTC
Backing up metadata db with pg_dump
Hi airflow-users,
I'm looking to institute some periodic cleanup of the Airflow metadata
database and am heeding the recommendation in the docs to back it up
first. We use Postgres as our back end, and so using the pg_dump utility
seems appropriate. However, pg_dump hits an error:
$ pg_dump airflow_db > airflow_db0.sql
pg_dump: error: query failed: ERROR: permission denied for table dag_code
pg_dump: detail: Query was: LOCK TABLE public.dag_code IN ACCESS SHARE MODE
Per StackOverflow[1], it sounds like this is probably due to another
lock being issued on the table (ACCESS EXCLUSIVE) and that there's no
way around this, except to try to run `pg_dump` when this lock isn't in
place. However, I imagine that this lock is issued every time the
scheduler parsers the DAG code -- i.e. very frequently.
Have others on the list run into this issue? Any suggested workarounds?
One idea may be to only back up tables *other* than dag_code. However, I
think I'd like to avoid only grabbing some tables and not others (and
I'm not sure this would solve the issue, anyhow).
Many thanks!
[1]:
https://stackoverflow.com/questions/63485415/schema-pg-dump-failed-due-to-a-lock-on-a-table
--
Ben Hancock (he/him)
Senior Product Data Engineer
ALM Media
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
For additional commands, e-mail: users-help@airflow.apache.org
Re: Backing up metadata db with pg_dump
Posted by Ben Hancock <bh...@alm.com.INVALID>.
>> In your sample script, you have:
>>
>>> pg_dump —username={insert admin username here} --no-password --format=custom \
>>>
>>> --section=pre-data --section=data --section=post-data \
>>>
>>> --verbose —file={location here}.backup ${db_name} 2>${tempout}
>>
>> Can you explain the need to pass the various `--section` flags?
>
> Those flags are just default. Not need to add them. Sometimes I write
> out the default flags to be more specific, but that’s just a quirky
> thing I *sometimes* do.
Got it - thanks for explaining!
Ben
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
For additional commands, e-mail: users-help@airflow.apache.org
Re: Backing up metadata db with pg_dump
Posted by Anthony Joyce <an...@omicronmedia.com>.
Happy Friday!
Those flags are just default. Not need to add them. Sometimes I write out the default flags to be more specific, but that’s just a quirky thing I *sometimes* do.
Anthony
> On Aug 25, 2023, at 12:29 PM, Ben Hancock <bh...@alm.com.invalid> wrote:
>
> [You don't often get email from bhancock@alm.com.invalid. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]
>
> Anthony Joyce wrote:
>
>> When we back up we use the admin or superuser username of your
>> Postgres database. Perhaps that would help the permission issue that
>> you are running into? It is worth a shot
>
> Thanks Anthony -- you're right, that did work. I thought my user had
> sufficient permissions (USAGE) on the schema, but apparently not. FWIW,
> I was able to execute this as the Airflow user as well.
>
> In your sample script, you have:
>
>> pg_dump —username={insert admin username here} --no-password --format=custom \
>>
>> --section=pre-data --section=data --section=post-data \
>>
>> --verbose —file={location here}.backup ${db_name} 2>${tempout}
>
> Can you explain the need to pass the various `--section` flags? I'm
> probably missing something, but from my read of the man page it seems
> like passing all of these sections together would be the same as the
> default behavior without the flags.
>
> Many thanks again!
>
> Ben
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
> For additional commands, e-mail: users-help@airflow.apache.org
>
Re: Backing up metadata db with pg_dump
Posted by Ben Hancock <bh...@alm.com.INVALID>.
Anthony Joyce wrote:
> When we back up we use the admin or superuser username of your
> Postgres database. Perhaps that would help the permission issue that
> you are running into? It is worth a shot
Thanks Anthony -- you're right, that did work. I thought my user had
sufficient permissions (USAGE) on the schema, but apparently not. FWIW,
I was able to execute this as the Airflow user as well.
In your sample script, you have:
> pg_dump —username={insert admin username here} --no-password --format=custom \
>
> --section=pre-data --section=data --section=post-data \
>
> --verbose —file={location here}.backup ${db_name} 2>${tempout}
Can you explain the need to pass the various `--section` flags? I'm
probably missing something, but from my read of the man page it seems
like passing all of these sections together would be the same as the
default behavior without the flags.
Many thanks again!
Ben
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
For additional commands, e-mail: users-help@airflow.apache.org
Re: Backing up metadata db with pg_dump
Posted by Anthony Joyce <an...@omicronmedia.com>.
Hi Ben-
We back up our Airflow metadata db.
For example, we run this code to create a cron that backs up the airflow db every night. When we back up we use the admin or superuser username of your Postgres database. Perhaps that would help the permission issue that you are running into? It is worth a shot. Below is a sample my shell script that specifies username.
# Make daily backup of specified PostgreSQL database
stat_msg "INFO: Starting backup of ${db_name} database"
pg_dump —username={insert admin username here} --no-password --format=custom \
--section=pre-data --section=data --section=post-data \
--verbose —file={location here}.backup ${db_name} 2>${tempout}
cat ${tempout} >> ${log}
Anthony
On Aug 24, 2023, at 1:45 PM, Ben Hancock <bh...@alm.com.INVALID> wrote:
[You don't often get email from bhancock@alm.com.invalid. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]
Hi airflow-users,
I'm looking to institute some periodic cleanup of the Airflow metadata
database and am heeding the recommendation in the docs to back it up
first. We use Postgres as our back end, and so using the pg_dump utility
seems appropriate. However, pg_dump hits an error:
$ pg_dump airflow_db > airflow_db0.sql
pg_dump: error: query failed: ERROR: permission denied for table dag_code
pg_dump: detail: Query was: LOCK TABLE public.dag_code IN ACCESS SHARE MODE
Per StackOverflow[1], it sounds like this is probably due to another
lock being issued on the table (ACCESS EXCLUSIVE) and that there's no
way around this, except to try to run `pg_dump` when this lock isn't in
place. However, I imagine that this lock is issued every time the
scheduler parsers the DAG code -- i.e. very frequently.
Have others on the list run into this issue? Any suggested workarounds?
One idea may be to only back up tables *other* than dag_code. However, I
think I'd like to avoid only grabbing some tables and not others (and
I'm not sure this would solve the issue, anyhow).
Many thanks!
[1]:
https://stackoverflow.com/questions/63485415/schema-pg-dump-failed-due-to-a-lock-on-a-table
--
Ben Hancock (he/him)
Senior Product Data Engineer
ALM Media
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@airflow.apache.org
For additional commands, e-mail: users-help@airflow.apache.org