Postgresql运维

• 统计
• PostgreSQL统计表行数
• Postgresql硬盘空间查询
• 等保测评
• Postgresql设置密码复杂度策略
• 备份与恢复
• 使用copy语法备份与恢复

统计

PostgreSQL统计表行数

根据网上的资料修改脚本

#!/bin/bash
# Directory of the current script
CMD_DIRS=$(dirname $(readlink -f "$0"))
# Home
CMD_HOME=$(dirname ${CMD_DIRS})
# System home
SYS_HOME=$(cd ${CMD_HOME}/../../ && pwd)
if [[ -f "${CMD_HOME}/bin/setenv.sh" ]]; then
  . ${CMD_HOME}/bin/setenv.sh
fi
# Set defaults for configuration variables
PGSQL_HOME=${PGSQL_HOME:-"/usr/pgsql-${PGSQL_MAJOR_VERSION}"}
# Base directory
PGSQL_BASE=${CMD_HOME}
# Postgres user
PGSQL_USER=${PGSQL_USER:-"postgres"}
# Postgres role
PGSQL_ROLE=${PGSQL_ROLE:-"${PGSQL_USER}"}
# Postgres engine directory
PGSQL_ENGINE=${PGSQL_HOME}/bin
PGSQL_OPTS=${PGSQL_OPTS:-""}
# For SELinux we need to use 'runuser' not 'su'
if [[ -x "/sbin/runuser" ]]; then
    SU="/sbin/runuser -s /bin/bash"
else
    SU="/bin/su -s /bin/bash"
fi
# 使用操作系统账号免登录
if [[ $(whoami) != "${PGSQL_USER}" ]]; then
  SUDO="${SU} -l ${PGSQL_USER}"
  if [[ -x /bin/sudo && $(id -u) -ne 0 ]]; then
    SUDO="/bin/sudo ${SUDO}"
  fi
else
  SUDO="/bin/bash"
fi
# 统计数据库名称
PGSQL_DB_NAME=${1:-"snapshot"}
PGSQL_COUNT_OPTS="${PGSQL_OPTS} -d ${PGSQL_DB_NAME}"
if ! ${SUDO} -c "${PGSQL_ENGINE}/psql -qAtX ${PGSQL_COUNT_OPTS} -c \"SELECT VERSION()\" 2>&1 > /dev/null"; then
  echo "数据库查询失败"
  exit 1
fi
PGSQL_SQL_WHERE=""
# 获取服务器版本
PGSQL_CMD_OUT=$(${SUDO} -c "${PGSQL_ENGINE}/psql -qAtX ${PGSQL_COUNT_OPTS} -c \"SELECT VERSION()\"" | awk '{print $2}')
PGSQL_SERVER_VERSION=${PGSQL_CMD_OUT}
# postgresql-11分区表，排除主表防止行数重复
if [[ $(echo "${PGSQL_SERVER_VERSION} >= 11.0" | bc) -eq 1 ]]; then
  PGSQL_SQL_WHERE=" and not exists (SELECT * FROM (SELECT DISTINCT INHPARENT::REGCLASS AS TABLENAME FROM PG_INHERITS) AS T WHERE TABLENAME = (SELECT OID FROM PG_CLASS WHERE RELNAME = pg_tables.tablename))"
fi
# 统计每个表的行数，以及总数
PGSQL_COUNT_SQL=$(cat <<EOF
SELECT CASE
           WHEN t.row_total = 1 THEN
            'select * from(' || REPLACE(sql_content, ' union all ', ') a order by 2 desc,3 desc')
           WHEN t.row_total = t.row_seq THEN
            REPLACE(sql_content, ' union all ', ') a order by 2 desc,3 desc')
           WHEN t.row_seq = 1 THEN
            'select * from(' || sql_content
           ELSE
            sql_content
       END sql_content
  FROM (SELECT COUNT(*) over() row_total,
               row_number() over() row_seq,
               'SELECT ''' || quote_ident(tablename) ||
               ''' 表名, count(*) 表行数,pg_total_relation_size(''' ||
               quote_ident(tablename) || '''::regclass) 表总大小 from ' || quote_ident(tablename) ||
               ' union all ' sql_content
          FROM pg_tables
         WHERE schemaname = 'public'${PGSQL_SQL_WHERE}) t
 ORDER BY t.row_seq
EOF
)
## 生成SQL
PGSQL_CMD_OUT=$(${SUDO} -c "${PGSQL_ENGINE}/psql -qAtX ${PGSQL_COUNT_OPTS} -c \"${PGSQL_COUNT_SQL}\"")
# 生成的SQL可能超长，需要使用"heredoc"方式
${SUDO} <<EOF
${PGSQL_ENGINE}/psql ${PGSQL_COUNT_OPTS} <<SQL
SELECT SUM(表行数) AS 表行数,pg_size_pretty(SUM(表总大小)) AS 表总大小 FROM (${PGSQL_CMD_OUT}) AS T
SQL
EOF
${SUDO} <<EOF
${PGSQL_ENGINE}/psql ${PGSQL_COUNT_OPTS} <<SQL
SELECT 表名, 表行数,pg_size_pretty(表总大小) AS 表总大小 FROM (${PGSQL_CMD_OUT}) AS T ORDER BY T.表总大小 desc, T.表行数 desc
SQL
EOF

参考资料

• 墨天轮 - 一键查询PostgreSQL模式中所有表行数和表大小

Postgresql硬盘空间查询

一、查看所有表，索引占用空间

-- 表、索引占用空间
SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

二、查看数据库占用空间

-- 查询指定数据库占用空间
select pg_size_pretty (pg_database_size('test_database'));

-- 查询所有数据库占用空间
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;

三、查看当前数据库所有表占用空间

-- 查询当前数据库所有表占用空间
select
    table_full_name,
    pg_size_pretty(size)
from
(
        SELECT
            table_schema || '.' || table_name AS table_full_name,
            pg_total_relation_size(
                '"' || table_schema || '"."' || table_name || '"'
            ) AS size
        FROM
            information_schema.tables
        ORDER BY
            size DESC
    ) as T

 

等保测评

Postgresql设置密码复杂度策略

安装和配置 passwordcheck 扩展

以下是如何使用 passwordcheck 扩展来设置 PostgreSQL 用户密码复杂度策略的步骤：

1. 安装 passwordcheck 扩展

首先，确保已安装 PostgreSQL 和 contrib 模块：

sudo yum install postgresql13-server postgresql13-contrib

根据你的 PostgreSQL 版本，可能需要调整 postgresql13 为你所使用的版本。

2. 启用 passwordcheck 扩展

将 passwordcheck 扩展添加到 PostgreSQL 配置文件 postgresql.conf 中：

sudo vi /var/lib/pgsql/13/data/postgresql.conf

添加以下行：

shared_preload_libraries = 'passwordcheck'

3. 配置密码复杂度策略

在 postgresql.conf 文件中添加具体的密码复杂度策略配置：

passwordcheck.min_length = 8
passwordcheck.max_length = 20
passwordcheck.min_lowercase = 1
passwordcheck.min_uppercase = 1
passwordcheck.min_digits = 1
passwordcheck.min_special = 1

保存并关闭文件。

4. 重启 PostgreSQL 服务

重启 PostgreSQL 以使配置生效：

sudo systemctl restart postgresql-13

5. 验证配置

尝试更改 PostgreSQL 用户密码，确保密码复杂度策略生效：

ALTER USER your_username WITH PASSWORD 'SimplePass'; -- 应该会失败
ALTER USER your_username WITH PASSWORD 'ComplexPass1!'; -- 应该会成功

注意事项

• 密码策略配置项：passwordcheck 扩展中的配置项可能不如一些外部插件详细和可定制，但是它可以覆盖基本的密码复杂度要求。
• 数据库用户：以上配置适用于 PostgreSQL 自身的数据库用户，而不是应用系统中的用户。

备份与恢复

使用copy语法备份与恢复

导出数据包含换行符的表

COPY my_table TO '/path/to/output_file.csv' WITH (FORMAT CSV, HEADER, QUOTE '"', ESCAPE '\');

导入包含换行符的数据

COPY my_table FROM '/path/to/output_file.csv' WITH (FORMAT CSV, HEADER, QUOTE '"', ESCAPE '\');

注意事项

• CSV 格式：确保你导出的 CSV 文件符合标准 CSV 格式，其中换行符、引号等都被正确转义。
• QUOTE 和 ESCAPE 参数：使用这些参数可以帮助正确处理包含换行符、引号、逗号等特殊字符的数据。