postgresql 真空分析模式 postgres 中的所有表

Vacuum analyze all tables in a schema postgres

提问by Grant Humphries

I have a very large postgres database that has one particular schema in it which is dropped in and recreated nightly. After all of the tables in that schema are created I want to vacuum analyze them, however the database is so large that if a do a full db VACUUM ANALYZE;it takes about a half hour.

我有一个非常大的 postgres 数据库，其中有一个特定的模式，每晚都会放入并重新创建。创建该模式中的所有表后，我想对它们进行真空分析，但是数据库太大了，如果做一个完整的数据库VACUUM ANALYZE;需要大约半小时。

How can I go about vacuum analyzing each of the tables in this schema only without writing a separate SQL command for each table?

如何仅在不为每个表编写单独的 SQL 命令的情况下对该模式中的每个表进行真空分析？

回答by Grant Humphries

The bash function below utilizes the CLI tool psqlto vacuum analyze tables in a single schema which can be identified by either passing the name of the schema as the first parameter to the function or setting the environment variable PG_SCHEMA:

下面的 bash 函数利用 CLI 工具psql对单个模式中的表进行真空分析，可以通过将模式名称作为第一个参数传递给函数或设置环境变量来识别该模式PG_SCHEMA：

vacuum_analyze_schema() {
    # vacuum analyze only the tables in the specified schema

    # postgres info can be supplied by either passing it as parameters to this
    # function, setting environment variables or a combination of the two
    local pg_schema="${1:-${PG_SCHEMA}}"
    local pg_db="${2:-${PG_DB}}"
    local pg_user="${3:-${PG_USER}}"
    local pg_host="${4:-${PG_HOST}}"

    echo "Vacuuming schema \`${pg_schema}\`:"

    # extract schema table names from psql output and put them in a bash array
    local psql_tbls="\dt ${pg_schema}.*"
    local sed_str="s/${pg_schema}\s+\|\s+(\w+)\s+\|.*//p"
    local table_names=$( echo "${psql_tbls}" | psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}"  | sed -nr "${sed_str}" )
    local tables_array=( $( echo "${table_names}" | tr '\n' ' ' ) )

    # loop through the table names creating and executing a vacuum
    # command for each one
    for t in "${tables_array[@]}"; do
        echo "doing table \`${t}\`..."
        psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}" \
            -c "VACUUM (ANALYZE) ${pg_schema}.${t};"
    done
}

This function can be added to your .bashrcto provide the ability to invoke it from the command line at any time. Like the schema, Postgres connection and database values can be set by either supplying them as function parameters:

此函数可以添加到您的，.bashrc以提供随时从命令行调用它的能力。与模式一样，可以通过将它们作为函数参数提供来设置 Postgres 连接和数据库值：

# params must be in this order
vacuum_analyze_schema '<your-pg-schema>' '<your-pg-db>' '<your-pg-user>' '<your-pg-host>'

or by setting environment variables:

或通过设置环境变量：

PG_SCHEMA='<your-pg-schema>'
PG_USER='<your-pg-user>'
PG_HOST='<your-pg-host>'
PG_DB='<your-pg-db>'

vacuum_analyze_schema

or by a combination of both. Values passed as params will take precedence over corresponding environment vars.

或两者结合。作为参数传递的值将优先于相应的环境变量。

回答by Fritz

You can use the following pl/pgsql script (if you only want to analyze, vacuum cannot be executed from a function or multi-command string):

可以使用下面的pl/pgsql脚本（如果只是想分析，不能从函数或多命令字符串执行vacuum）：

DO $$
DECLARE
  tab RECORD;
  schemaName VARCHAR := 'your_schema';
BEGIN
  for tab in (select t.relname::varchar AS table_name
                FROM pg_class t
                JOIN pg_namespace n ON n.oid = t.relnamespace
                WHERE t.relkind = 'r' and n.nspname::varchar = schemaName
                order by 1)
  LOOP
    RAISE NOTICE 'ANALYZE %1.%2', schemaName, tab.table_name;
    EXECUTE 'ANALYZE '||schemaName||'.'||tab.table_name;
  end loop;
end
$$;

回答by PPH

Wouldn't it be simpler to:

这样做不是更简单：

psql -t -A -U postgres -c "select format('analyse verbose %I.%I;', n.nspname::varchar, t.relname::varchar) FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace WHERE t.relkind = 'r' and n.nspname::varchar = 'your_schema' order by 1" | psql -U postgres

Options -t only prints rows (no headers) and -A avoid formatting

选项 -t 仅打印行（无标题）和 -A 避免格式化

回答by khituras

While searching for the answer of this exact question, I found the solution approach given by PPH the preferable. Unfortunately, the given command line does not quite work out of the box (tested with psql 10.8 on a Postgres server v9.6.13). Here is the command line I successfully used to VACUUM ANALYZEall tables of a specific schema in a specific PostgreSQL database:

在寻找这个确切问题的答案时，我发现 PPH 给出的解决方案更可取。不幸的是，给定的命令行并不是开箱即用的（在 Postgres 服务器 v9.6.13 上用 psql 10.8 测试）。这是我成功用于VACUUM ANALYZE特定 PostgreSQL 数据库中特定模式的所有表的命令行：

psql -t -A -d "YOUR_DATABASE" -c "select format('vacuum analyse verbose %I.%I;', n.nspname::varchar, t.relname::varchar) FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace WHERE t.relkind = 'r' and n.nspname::varchar = 'YOUR_SCHEMA' order by 1" | psql -U postgres -d "YOUR_DATABASE"

psql -t -A -d "YOUR_DATABASE" -c "select format('vacuum analyse verbose %I.%I;', n.nspname::varchar, t.relname::varchar) FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace WHERE t.relkind = 'r' and n.nspname::varchar = 'YOUR_SCHEMA' order by 1" | psql -U postgres -d "YOUR_DATABASE"

You have to replace the three upper-case terms with the values applying to your case. Worked flawlessly for me.

您必须用适用于您的案例的值替换三个大写的术语。对我来说完美无缺。

回答by Robert

A solution based on those of @Grant Humphries and @Fritz but somewhat shorter and simpler:

基于@Grant Humphries 和@Fritz 的解决方案，但更短更简单：

PGUSER=your_postgres_username
PGHOST=your_postgres_host
PGPORT=your_postgres_port
PGDB=your_postgres_db_name
PGSCHEMA=your_postgres_schema  

for table in $(psql -h ${PGHOST} -p ${PGPORT} -d ${PGDB} -U ${PGUSER} \
  -c "select tablename from pg_tables where schemaname = '${PGSCHEMA}';" | \
  tail -n +3 | head -n -2); do
    psql -h ${PGHOST} -p ${PGPORT} -d ${PGDB} -U ${PGUSER} \
         -c "VACUUM (ANALYZE) ${PGSCHEMA}.${table};";
done

Differences to the mentioned solutions:

与上述解决方案的不同之处：

• Get table names through a direct query on the system catalog view pg_tables. This makes it easier to extract the table names as we only need to remove the header and footer in the output, using head and tail.
• Added port number, for those installations where it is required.
• Updated the vacuum statement to the current (PostgreSQL 9.0 and later) syntax with parentheses. According to the documentation: "the unparenthesized syntax is deprecated."

• 通过对系统目录视图 pg_tables 的直接查询来获取表名。这使得提取表名更容易，因为我们只需要使用 head 和 tail 删除输出中的页眉和页脚。
• 添加了端口号，用于需要的那些安装。
• 将vacuum 语句更新为带括号的当前（PostgreSQL 9.0 及更高版本）语法。根据文档：“不推荐使用无括号的语法。”

回答by Andrey L

do
$$
declare
  r record;
  schemaname varchar := 'contact';
begin
    perform dblink_connect('vacuum_connection', 'dbname=' || current_database());
    for r in (  select t.oid::regclass::text tname
                from pg_class t
                where t.relkind = 'r' and t.relnamespace = schemaname::regnamespace)
    loop
        raise notice '%1', r.tname;
        perform dblink_exec('vacuum_connection', 'vacuum analyze ' || r.tname);
    end loop;
    perform dblink_disconnect('vacuum_connection');
end
$$