在PostgreSQL中，对模式中的所有表进行空间分析。

Question

在PostgreSQL中，对模式中的所有表进行空间分析。

22

我有一个非常大的Postgres数据库，其中包含一个特定的模式，每晚都被删除并重新创建。在该模式中的所有表创建完成后，我想要对它们进行Vacuum分析，但由于数据库太大，如果我执行完整的db VACUUM ANALYZE; ，需要大约半个小时。

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

- Grant Humphries

6个回答

15

下面的 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+\|.*/\1/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
}

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

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

或通过设置环境变量：

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

vacuum_analyze_schema

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

- Grant Humphries

7

简单点说，这么做岂不是更简单：

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

选项 -t 只打印行（无标题），而 -A 则避免格式化。

- PPH

6

一种基于@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

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

通过直接查询系统目录视图pg_tables来获取表名。这样做使提取表名更容易，因为我们只需要在输出中删除标题和页脚，使用head和tail即可。
添加了端口号，适用于需要端口号的安装环境。
更新了vacuum语句，将其转换为当前（PostgreSQL 9.0及更高版本）语法，使用括号。根据文档： "非括号语法已被弃用。"

- Robert

1

不错的解决方案，轻量级且消除了难以理解的sed命令。 - Grant Humphries

3

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

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"

你必须将三个大写字母术语替换为适用于你的值。对我来说完美无缺。

- khituras

-1

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
$$

- Andrey L

5

这段话的意思是：“这很相关，但我认为它被踩了因为它需要使用dblink并且没有解释。” - greatvovan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Fritz · Accepted Answer

你可以使用以下的 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 %.%', schemaName, tab.table_name;
    EXECUTE format('ANALYZE %I.%I', schemaName, tab.table_name);
  end loop;
end
$$;