Hello there, I'm a long-time postgres user but vacuumdb newbie :). $ time vacuumdb --full --echo -U postgres --jobs=8 -d mydatabase_test -p 5434 --password As shown below I ran into the (100%?) predictable/expected deadlock with sys catalog tables documented in the vacuumdb doc page (https://www.postgresql.org/docs/12/app-vacuumdb.html). Question: I specified "-d mydatabase_test" - why is it also doing a full vac on the system catalog (postgres db) which is practically guaranteed to cause a deadlock? I don't need or want it to do a full vac on the postgres db as I can do that myself later if I need to, and it takes no time compared to full vac of my own 7 TB database that I am "extremely eager" to parallelize. Is my only option list all schemas.tables and feed to vacuumdb as "-t schema1.table1 -t schema1.table2 ........" ? That's manageable but unfortunate as it creates more work (having to list all tables) and adds additional work for automation scripts as well. I've tried searching forums/goog for past issues related to this. I did find an old discussion about possible enhancements to "vacuumdb --jobs" to help the user avoid these predictable deadlocks, where the conclusion was to let the user figure out a way to deal with it such as via "-t table -t table -t table .......". Maybe "--jobs" should only apply to the user's own databases, and when vacuumdb is working on system catalog tables it should apply a strictly serial/sequential approach to handle those tables? Thanks! ------------------------------------------ output from vacuumdb's --echo ------------------------------------------ ... VACUUM (FULL) mycompanytesting.sometable1; VACUUM (FULL) index.mydatabasestd_missing; VACUUM (FULL) mycompanytesting.sometable2; VACUUM (FULL) mycompanytesting.sometable3; VACUUM (FULL) pg_catalog.pg_attribute; VACUUM (FULL) mycompanydata.sometable4; VACUUM (FULL) pg_catalog.pg_statistic; VACUUM (FULL) mycompanytesting.sometable5; VACUUM (FULL) pg_catalog.pg_proc; VACUUM (FULL) pg_catalog.pg_depend; VACUUM (FULL) pg_catalog.pg_class; VACUUM (FULL) pg_catalog.pg_type; vacuumdb: error: vacuuming of database "mydatabase_test" failed: ERROR: deadlock detected DETAIL: Process 28183 waits for AccessShareLock on relation 1259 of database 35239378; blocked by process 28182. Process 28182 waits for AccessShareLock on relation 1247 of database 35239378; blocked by process 28183. HINT: See server log for query details. ------------------------------ /var/log/postgresql-9.6 ------------------------------ ... LOG: received SIGHUP, reloading configuration files ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "mydatabase_test.someschema.sometable" ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "mydatabase_test.someschema.sometable" ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "mydatabase_test.pg_toast.pg_toast_35240266" LOG: could not receive data from client: Connection timed out LOG: received SIGHUP, reloading configuration files LOG: received SIGHUP, reloading configuration files ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "mydatabase_test.someschema.sometable" LOG: received SIGHUP, reloading configuration files LOG: received SIGHUP, reloading configuration files ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "mydatabase_test.pg_toast.pg_toast_35240330" ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "mydatabase_test.someschema.sometable" ERROR: deadlock detected DETAIL: Process 28183 waits for AccessShareLock on relation 1259 of database 35239378; blocked by process 28182. Process 28182 waits for AccessShareLock on relation 1247 of database 35239378; blocked by process 28183. Process 28183: VACUUM (FULL) pg_catalog.pg_type; Process 28182: VACUUM (FULL) pg_catalog.pg_class; HINT: See server log for query details. STATEMENT: VACUUM (FULL) pg_catalog.pg_type; ERROR: canceling statement due to user request STATEMENT: VACUUM (FULL) pg_catalog.pg_class; ERROR: canceling statement due to user request STATEMENT: VACUUM (FULL) pg_catalog.pg_depend; ERROR: canceling statement due to user request STATEMENT: VACUUM (FULL) someschema.sometable LOG: could not send data to client: Broken pipe FATAL: connection to client lost LOG: could not send data to client: Broken pipe FATAL: connection to client lost ERROR: canceling statement due to user request STATEMENT: VACUUM (FULL) mycompanydata.wildcard_replacement_bkp; LOG: could not send data to client: Broken pipe STATEMENT: VACUUM (FULL) mycompanydata.wildcard_replacement_bkp; FATAL: connection to client lost ERROR: canceling statement due to user request STATEMENT: VACUUM (FULL) pg_catalog.pg_proc; LOG: could not send data to client: Broken pipe STATEMENT: VACUUM (FULL) pg_catalog.pg_proc; FATAL: connection to client lost ERROR: canceling statement due to user request STATEMENT: VACUUM (FULL) mycompanydata.cpc_tag_score2; LOG: could not send data to client: Broken pipe STATEMENT: VACUUM (FULL) mycompanydata.cpc_tag_score2; LOG: could not send data to client: Broken pipe FATAL: connection to client lost FATAL: connection to client lost LOG: received SIGHUP, reloading configuration files LOG: received SIGHUP, reloading configuration files |
