apt update
apt install -y ca-certificates curl gnupg lsb-release postgresql-common

install -d /usr/share/postgresql-common/pgdg

curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://mirrors.aliyun.com/postgresql/repos/apt/ACCC4CF8.asc

cat > /etc/apt/sources.list.d/pgdg.sources <<EOF
Types: deb
URIs: https://mirrors.aliyun.com/postgresql/repos/apt
Suites: $(lsb_release -cs)-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF

apt update
apt install -y postgresql-16 postgresql-client-16 postgresql-contrib

systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2026-05-15 10:20:51 CST; 5min ago
   Main PID: 43730 (code=exited, status=0/SUCCESS)
        CPU: 930us

May 15 10:20:51 instance-wc5p3ngj systemd[1]: Starting PostgreSQL RDBMS...
May 15 10:20:51 instance-wc5p3ngj systemd[1]: Finished PostgreSQL RDBMS.


pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

su - postgres 

psql -U postgres 

psql (16.14 (Ubuntu 16.14-1.pgdg22.04+1))
Type "help" for help.

postgres-# \l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
(3 rows)

postgres-# \du 
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres-# \q

cp -a /etc/postgresql/16/main/postgresql.conf /etc/postgresql/16/main/postgresql.conf.bak.$(date +%F_%H%M%S)

cp -a /etc/postgresql/16/main/pg_hba.conf /etc/postgresql/16/main/pg_hba.conf.bak.$(date +%F_%H%M%S)

PG_CONF="/etc/postgresql/16/main/postgresql.conf"

sed -ri "s|^[#[:space:]]*listen_addresses[[:space:]]*=.*|listen_addresses = '*'|" "$PG_CONF"
sed -ri "s|^[#[:space:]]*port[[:space:]]*=.*|port = 5432|" "$PG_CONF"
sed -ri "s|^[#[:space:]]*password_encryption[[:space:]]*=.*|password_encryption = 'scram-sha-256'|" "$PG_CONF"
sed -ri "s|^[#[:space:]]*timezone[[:space:]]*=.*|timezone = 'Asia/Shanghai'|" "$PG_CONF"
sed -ri "s|^[#[:space:]]*log_timezone[[:space:]]*=.*|log_timezone = 'Asia/Shanghai'|" "$PG_CONF"

grep -q "^log_min_duration_statement" "$PG_CONF" \
  && sed -ri "s|^[#[:space:]]*log_min_duration_statement[[:space:]]*=.*|log_min_duration_statement = 1000|" "$PG_CONF" \
  || echo "log_min_duration_statement = 1000" >> "$PG_CONF"

grep -q "^log_lock_waits" "$PG_CONF" \
  && sed -ri "s|^[#[:space:]]*log_lock_waits[[:space:]]*=.*|log_lock_waits = on|" "$PG_CONF" \
  || echo "log_lock_waits = on" >> "$PG_CONF"

grep -q "^deadlock_timeout" "$PG_CONF" \
  && sed -ri "s|^[#[:space:]]*deadlock_timeout[[:space:]]*=.*|deadlock_timeout = '1s'|" "$PG_CONF" \
  || echo "deadlock_timeout = '1s'" >> "$PG_CONF"

listen_addresses = '*'
port = 5432
password_encryption = 'scram-sha-256'
timezone = 'Asia/Shanghai'
log_timezone = 'Asia/Shanghai'
log_min_duration_statement = 1000
log_lock_waits = on
deadlock_timeout = '1s'

root@instance-wc5p3ngj:/data# grep -E 'listen_addresses|port|password_encryption|timezone|log_timezone|log_min_duration_statement|log_lock_waits|deadlock_timeout' /etc/postgresql/16/main/postgresql.conf 

listen_addresses = '*'
port = 5432
password_encryption = 'scram-sha-256'
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'
log_min_duration_statement = 1000
log_lock_waits = on
deadlock_timeout = '1s'

# 重启PostgreSQL 16服务
sudo systemctl restart postgresql@16-main

# 验证服务是否正常运行
sudo systemctl status postgresql@16-main

root@instance-wc5p3ngj:/data# sudo -u postgres psql
psql (16.14 (Ubuntu 16.14-1.pgdg22.04+1))
Type "help" for help.

postgres=# SHOW listen_addresses; 
 listen_addresses 
------------------
 *
(1 row)

postgres=# SHOW port; 
 port 
------
 5432
(1 row)

postgres=# SHOW password_encryption;
 password_encryption 
---------------------
 scram-sha-256
(1 row)

postgres=# SHOW timezone;
   TimeZone    
---------------
 Asia/Shanghai
(1 row)

postgres=# SHOW log_min_duration_statement;
 log_min_duration_statement 
----------------------------
 1s
(1 row)

postgres=# SHOW log_lock_waits;
 log_lock_waits 
----------------
 on
(1 row)

postgres=# SHOW deadlock_timeout; 
 deadlock_timeout 
------------------
 1s
(1 row)

postgres=# 

su - postgres
psql

ALTER USER postgres WITH PASSWORD '你的强密码';
\q

psql -U postgres -h localhost -d postgres 
Password for user postgres: 
psql (16.14 (Ubuntu 16.14-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=# 

#!/usr/bin/env bash
# PostgreSQL apt 源安装脚本
# Ubuntu/Debian
# 默认所有数据、日志、备份和凭据均放在 /data/postgresql 下
# 使用方式：sudo bash postgresql_pgdg_install.sh install
# 卸载方式：sudo bash postgresql_pgdg_install.sh uninstall
# 查看状态：sudo bash postgresql_pgdg_install.sh status
# 重置密码：sudo bash postgresql_pgdg_install.sh reset-password

set -Eeuo pipefail
umask 027
export DEBIAN_FRONTEND=noninteractive

# PostgreSQL 主版本；PGDG APT 包按主版本安装，例如 postgresql-16
POSTGRES_VERSION="${POSTGRES_VERSION:-16}"
# 官方仓库默认会安装16.13

# PostgreSQL 集群名；Ubuntu/Debian 多版本多实例体系中常用 main
CLUSTER_NAME="${CLUSTER_NAME:-main}"

# PostgreSQL 运行用户和组；PGDG/Ubuntu 包默认使用 postgres
POSTGRES_USER="${POSTGRES_USER:-postgres}"
POSTGRES_GROUP="${POSTGRES_GROUP:-postgres}"

# 所有持久化目录默认都在 /data/postgresql 下
PG_BASE_DIR="${PG_BASE_DIR:-/data/postgresql}"
DATA_DIR="${DATA_DIR:-}"
LOG_DIR="${LOG_DIR:-}"
BACKUP_DIR="${BACKUP_DIR:-}"
AUTH_FILE="${AUTH_FILE:-}"

# PostgreSQL 软件真实安装目录由官方 APT 包固定为 /usr/lib/postgresql/<主版本>
# 这里提供软链接，便于统一运维路径
INSTALL_PREFIX="${INSTALL_PREFIX:-}"
INSTALL_LINK="${INSTALL_LINK:-/usr/local/pgsql}"

# 配置目录；pg_createcluster 默认生成到 /etc/postgresql/<版本>/<集群名>
CONFIG_BASE_DIR="${CONFIG_BASE_DIR:-/etc/postgresql}"
CONFIG_DIR="${CONFIG_DIR:-}"
CONF_FILE="${CONF_FILE:-}"
HBA_FILE="${HBA_FILE:-}"
IDENT_FILE="${IDENT_FILE:-}"

# 运行时目录仍使用 /run/postgresql，这是 PostgreSQL-common/systemd 约定目录，不属于持久化数据
RUN_DIR="${RUN_DIR:-/run/postgresql}"

# PGDG 仓库。默认使用阿里云国内镜像；如需官方源，设为 https://apt.postgresql.org/pub/repos/apt
PGDG_REPO_URL="${PGDG_REPO_URL:-https://mirrors.aliyun.com/postgresql/repos/apt}"
PGDG_KEY="${PGDG_KEY:-/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc}"
PGDG_SOURCE="${PGDG_SOURCE:-/etc/apt/sources.list.d/pgdg.sources}"
DISABLE_AUTO_MAIN_CLUSTER="${DISABLE_AUTO_MAIN_CLUSTER:-true}"

# 监听配置
POSTGRES_PORT="${POSTGRES_PORT:-5432}"
LISTEN_ADDRESSES="${LISTEN_ADDRESSES:-0.0.0.0}"
ALLOW_REMOTE_CIDR="${ALLOW_REMOTE_CIDR:-0.0.0.0/0}"
PASSWORD_ENCRYPTION="${PASSWORD_ENCRYPTION:-scram-sha-256}"
POSTGRES_PASSWORD="${POSTGRES_PASSWORD:-passwd}"

# 是否保留关闭防火墙逻辑；如由安全组/iptables/nftables 管控，可设为 false
CLOSE_FIREWALL="${CLOSE_FIREWALL:-true}"

# 基础生产配置
MAX_CONNECTIONS="${MAX_CONNECTIONS:-1000}"
SHARED_BUFFERS="${SHARED_BUFFERS:-auto}"
SHARED_BUFFERS_PERCENT="${SHARED_BUFFERS_PERCENT:-25}"
EFFECTIVE_CACHE_SIZE="${EFFECTIVE_CACHE_SIZE:-auto}"
EFFECTIVE_CACHE_SIZE_PERCENT="${EFFECTIVE_CACHE_SIZE_PERCENT:-75}"
MAINTENANCE_WORK_MEM="${MAINTENANCE_WORK_MEM:-auto}"
WORK_MEM="${WORK_MEM:-auto}"
HUGE_PAGES="${HUGE_PAGES:-try}"
TIMEZONE="${TIMEZONE:-Asia/Shanghai}"
DEFAULT_TEXT_SEARCH_CONFIG="${DEFAULT_TEXT_SEARCH_CONFIG:-pg_catalog.simple}"

# WAL/checkpoint/复制相关配置
WAL_LEVEL="${WAL_LEVEL:-replica}"
WAL_COMPRESSION="${WAL_COMPRESSION:-on}"
MAX_WAL_SIZE="${MAX_WAL_SIZE:-8GB}"
MIN_WAL_SIZE="${MIN_WAL_SIZE:-1GB}"
CHECKPOINT_COMPLETION_TARGET="${CHECKPOINT_COMPLETION_TARGET:-0.9}"
MAX_WAL_SENDERS="${MAX_WAL_SENDERS:-10}"
MAX_REPLICATION_SLOTS="${MAX_REPLICATION_SLOTS:-10}"

# IO/查询优化配置；默认偏 SSD/NVMe。机械盘建议调高 RANDOM_PAGE_COST 并降低 EFFECTIVE_IO_CONCURRENCY
RANDOM_PAGE_COST="${RANDOM_PAGE_COST:-1.1}"
EFFECTIVE_IO_CONCURRENCY="${EFFECTIVE_IO_CONCURRENCY:-200}"
DEFAULT_STATISTICS_TARGET="${DEFAULT_STATISTICS_TARGET:-100}"
JIT="${JIT:-off}"

# 日志配置
LOG_MIN_DURATION_STATEMENT="${LOG_MIN_DURATION_STATEMENT:-1000}"
LOG_LINE_PREFIX="${LOG_LINE_PREFIX:-%m [%p] %u@%d %r }"
LOG_FILENAME="${LOG_FILENAME:-}"
LOG_ROTATION_AGE="${LOG_ROTATION_AGE:-1d}"
LOG_ROTATION_SIZE="${LOG_ROTATION_SIZE:-100MB}"
LOG_TRUNCATE_ON_ROTATION="${LOG_TRUNCATE_ON_ROTATION:-on}"
LOG_LOCK_WAITS="${LOG_LOCK_WAITS:-on}"
DEADLOCK_TIMEOUT="${DEADLOCK_TIMEOUT:-1s}"

# autovacuum 配置
AUTOVACUUM="${AUTOVACUUM:-on}"
AUTOVACUUM_MAX_WORKERS="${AUTOVACUUM_MAX_WORKERS:-5}"
AUTOVACUUM_NAPTIME="${AUTOVACUUM_NAPTIME:-30s}"
AUTOVACUUM_VACUUM_COST_LIMIT="${AUTOVACUUM_VACUUM_COST_LIMIT:-2000}"
AUTOVACUUM_VACUUM_SCALE_FACTOR="${AUTOVACUUM_VACUUM_SCALE_FACTOR:-0.05}"
AUTOVACUUM_ANALYZE_SCALE_FACTOR="${AUTOVACUUM_ANALYZE_SCALE_FACTOR:-0.02}"

# systemd、limits、sysctl
SERVICE_NAME="${SERVICE_NAME:-}"
LIMIT_NOFILE="${LIMIT_NOFILE:-65535}"
MANAGE_LIMITS="${MANAGE_LIMITS:-true}"
MANAGE_SYSCTL="${MANAGE_SYSCTL:-true}"
SWAPPINESS="${SWAPPINESS:-1}"
SOMAXCONN="${SOMAXCONN:-1024}"
FS_FILE_MAX="${FS_FILE_MAX:-1000000}"
VM_OVERCOMMIT_MEMORY="${VM_OVERCOMMIT_MEMORY:-0}"

# 是否把 PostgreSQL 常用命令软链接到系统 PATH 中
LINK_BINARIES="${LINK_BINARIES:-true}"
BIN_LINK_DIR="${BIN_LINK_DIR:-/usr/local/bin}"
WRITE_PROFILE="${WRITE_PROFILE:-true}"

# 是否创建业务库和业务用户
CREATE_APP_USER="${CREATE_APP_USER:-false}"
APP_DB_NAME="${APP_DB_NAME:-appdb}"
APP_DB_USER="${APP_DB_USER:-appuser}"
APP_DB_PASSWORD="${APP_DB_PASSWORD:-}"
APP_HOST_CIDR="${APP_HOST_CIDR:-0.0.0.0/0}"
APP_PRIVILEGES="${APP_PRIVILEGES:-ALL PRIVILEGES}"

# 卸载行为
PURGE_PACKAGES="${PURGE_PACKAGES:-true}"
REMOVE_POSTGRES_USER="${REMOVE_POSTGRES_USER:-false}"
REMOVE_LOG_FILES="${REMOVE_LOG_FILES:-true}"

OS_ID=""
OS_VERSION_CODENAME=""
PG_MAJOR=""
NEW_CLUSTER_CREATED="false"

log() {
  echo "[$(date '+%F %T')] [INFO] $*"
}

warn() {
  echo "[$(date '+%F %T')] [WARN] $*" >&2
}

err() {
  echo "[$(date '+%F %T')] [ERROR] $*" >&2
}

die() {
  err "$*"
  exit 1
}

usage() {
  cat <<EOF_USAGE
用法：
  sudo bash $0 install
  sudo bash $0 uninstall
  sudo bash $0 status
  sudo bash $0 reset-password

不传参数时默认执行 install。

默认目录：
  数据目录：/data/postgresql/<主版本>/<集群名>
  日志目录：/data/postgresql/log
  备份目录：/data/postgresql/backup
  凭据文件：/data/postgresql/backup/.postgresql.auth

常用环境变量示例：
  POSTGRES_VERSION=17 POSTGRES_PASSWORD='StrongPassword' sudo -E bash $0 install
  POSTGRES_VERSION=17 DATA_DIR=/data/postgresql/17/main LOG_DIR=/data/postgresql/log sudo -E bash $0 install
  ALLOW_REMOTE_CIDR='10.0.0.0/8' CLOSE_FIREWALL=false sudo -E bash $0 install
  SHARED_BUFFERS=8GB EFFECTIVE_CACHE_SIZE=24GB MAX_CONNECTIONS=500 sudo -E bash $0 install
  CREATE_APP_USER=true APP_DB_NAME=appdb APP_DB_USER=appuser APP_DB_PASSWORD='AppStrongPassword' sudo -E bash $0 install
  PGDG_REPO_URL='https://apt.postgresql.org/pub/repos/apt' sudo -E bash $0 install
EOF_USAGE
}

is_true() {
  [[ "${1,,}" == "true" || "${1,,}" == "yes" || "${1}" == "1" ]]
}

command_exists() {
  command -v "$1" >/dev/null 2>&1
}

require_root() {
  [[ "${EUID}" -eq 0 ]] || die "请使用 root 用户或 sudo 执行。"
}

confirm_yes() {
  local prompt="$1"
  local answer=""
  read -r -p "${prompt} 输入 yes 继续：" answer || return 1
  [[ "${answer}" == "yes" ]]
}

sql_escape() {
  printf "%s" "$1" | sed "s/'/''/g"
}

conf_quote() {
  local escaped
  escaped="$(printf "%s" "$1" | sed "s/'/''/g")"
  printf "'%s'" "${escaped}"
}

generate_password() {
  LC_ALL=C tr -dc 'A-Za-z0-9_@#%+=-' </dev/urandom | head -c 24
  echo
}

backup_file() {
  local file="$1"
  [[ -f "${file}" ]] || return 0
  install -d -m 0750 -o root -g root "${BACKUP_DIR}"
  cp -a "${file}" "${BACKUP_DIR}/$(basename "${file}").$(date '+%Y%m%d%H%M%S').bak"
}

detect_os() {
  [[ -r /etc/os-release ]] || die "无法识别系统：/etc/os-release 不存在。"
  . /etc/os-release
  OS_ID="${ID,,}"
  OS_VERSION_CODENAME="${VERSION_CODENAME:-}"

  case "${OS_ID}" in
    ubuntu|debian)
      ;;
    *)
      die "当前脚本仅支持 Ubuntu/Debian，当前系统：${OS_ID}。"
      ;;
  esac

  [[ -n "${OS_VERSION_CODENAME}" ]] || die "无法读取 VERSION_CODENAME。"
  log "系统识别完成：${PRETTY_NAME:-${OS_ID}}，codename=${OS_VERSION_CODENAME}。"
}

normalize_version_and_paths() {
  if [[ "${POSTGRES_VERSION}" =~ ^[0-9]+\.[0-9]+$ ]]; then
    warn "PGDG APT 包按主版本安装，已将 POSTGRES_VERSION=${POSTGRES_VERSION} 规范化为 ${POSTGRES_VERSION%%.*}。"
    POSTGRES_VERSION="${POSTGRES_VERSION%%.*}"
  fi

  [[ "${POSTGRES_VERSION}" =~ ^[0-9]+$ ]] || die "POSTGRES_VERSION 必须是主版本号，例如 16、17、18。"
  PG_MAJOR="${POSTGRES_VERSION}"

  INSTALL_PREFIX="${INSTALL_PREFIX:-/usr/lib/postgresql/${PG_MAJOR}}"
  CONFIG_DIR="${CONFIG_DIR:-${CONFIG_BASE_DIR}/${PG_MAJOR}/${CLUSTER_NAME}}"
  CONF_FILE="${CONF_FILE:-${CONFIG_DIR}/postgresql.conf}"
  HBA_FILE="${HBA_FILE:-${CONFIG_DIR}/pg_hba.conf}"
  IDENT_FILE="${IDENT_FILE:-${CONFIG_DIR}/pg_ident.conf}"

  DATA_DIR="${DATA_DIR:-${PG_BASE_DIR}/${PG_MAJOR}/${CLUSTER_NAME}}"
  LOG_DIR="${LOG_DIR:-${PG_BASE_DIR}/log}"
  BACKUP_DIR="${BACKUP_DIR:-${PG_BASE_DIR}/backup}"
  AUTH_FILE="${AUTH_FILE:-${BACKUP_DIR}/.postgresql.auth}"

  LOG_FILENAME="${LOG_FILENAME:-postgresql-${PG_MAJOR}-${CLUSTER_NAME}-%Y-%m-%d.log}"
  SERVICE_NAME="${SERVICE_NAME:-postgresql@${PG_MAJOR}-${CLUSTER_NAME}.service}"
  PGDG_REPO_URL="${PGDG_REPO_URL%/}"
}

validate_inputs() {
  [[ "${CLUSTER_NAME}" =~ ^[A-Za-z0-9_]+$ ]] || die "CLUSTER_NAME 只能包含字母、数字、下划线。"
  [[ "${POSTGRES_PORT}" =~ ^[0-9]+$ ]] || die "POSTGRES_PORT 必须是数字。"
  (( POSTGRES_PORT >= 1 && POSTGRES_PORT <= 65535 )) || die "POSTGRES_PORT 必须在 1 到 65535 之间。"
  [[ "${MAX_CONNECTIONS}" =~ ^[0-9]+$ ]] || die "MAX_CONNECTIONS 必须是数字。"
  [[ "${SHARED_BUFFERS_PERCENT}" =~ ^[0-9]+$ ]] || die "SHARED_BUFFERS_PERCENT 必须是数字。"
  [[ "${EFFECTIVE_CACHE_SIZE_PERCENT}" =~ ^[0-9]+$ ]] || die "EFFECTIVE_CACHE_SIZE_PERCENT 必须是数字。"

  case "${PASSWORD_ENCRYPTION}" in
    scram-sha-256|md5) ;;
    *) die "PASSWORD_ENCRYPTION 只能是 scram-sha-256 或 md5。" ;;
  esac

  case "${HUGE_PAGES}" in
    on|off|try) ;;
    *) die "HUGE_PAGES 只能是 on、off 或 try。" ;;
  esac

  if is_true "${CREATE_APP_USER}"; then
    [[ "${APP_DB_NAME}" =~ ^[A-Za-z0-9_]+$ ]] || die "APP_DB_NAME 只能包含字母、数字、下划线。"
    [[ "${APP_DB_USER}" =~ ^[A-Za-z0-9_]+$ ]] || die "APP_DB_USER 只能包含字母、数字、下划线。"
  fi
}

install_base_dependencies() {
  log "安装基础依赖。"
  apt-get update -y
  apt-get install -y ca-certificates curl gnupg lsb-release procps sysstat acl postgresql-common
}

disable_auto_cluster_creation() {
  is_true "${DISABLE_AUTO_MAIN_CLUSTER}" || return 0

  local file="/etc/postgresql-common/createcluster.conf"
  [[ -f "${file}" ]] || return 0

  backup_file "${file}"

  if grep -Eq '^[#[:space:]]*create_main_cluster[[:space:]]*=' "${file}"; then
    sed -ri 's/^[#[:space:]]*create_main_cluster[[:space:]]*=.*/create_main_cluster = false/' "${file}"
  else
    echo 'create_main_cluster = false' >> "${file}"
  fi

  log "已设置 ${file}: create_main_cluster = false，避免安装包自动创建默认数据目录。"
}

setup_pgdg_repo() {
  log "配置 PostgreSQL PGDG APT 仓库：${PGDG_REPO_URL}"

  install -d -m 0755 "$(dirname "${PGDG_KEY}")"

  if curl -fsSL -o "${PGDG_KEY}" "${PGDG_REPO_URL}/ACCC4CF8.asc"; then
    log "已从 ${PGDG_REPO_URL} 下载 PostgreSQL 仓库签名密钥。"
  else
    warn "从 ${PGDG_REPO_URL}/ACCC4CF8.asc 下载密钥失败，尝试官方源。"
    curl -fsSL -o "${PGDG_KEY}" https://www.postgresql.org/media/keys/ACCC4CF8.asc
  fi
  chmod 0644 "${PGDG_KEY}"

  cat > "${PGDG_SOURCE}" <<EOF_PGDG
Types: deb
URIs: ${PGDG_REPO_URL}
Suites: ${OS_VERSION_CODENAME}-pgdg
Architectures: $(dpkg --print-architecture)
Components: main ${PG_MAJOR}
Signed-By: ${PGDG_KEY}
EOF_PGDG

  rm -f /var/lib/apt/lists/*apt.postgresql.org* 2>/dev/null || true
  rm -f /var/lib/apt/lists/*mirrors.aliyun.com*postgresql* 2>/dev/null || true
  rm -f /var/lib/apt/lists/*mirrors.cloud.aliyuncs.com*postgresql* 2>/dev/null || true

  apt-get update -y

  if ! apt-cache show "postgresql-${PG_MAJOR}" >/dev/null 2>&1; then
    echo
    warn "当前可见的 PostgreSQL 主版本包如下："
    apt-cache search '^postgresql-[0-9]+$' || true
    echo
    die "未找到 postgresql-${PG_MAJOR} 包。请检查 PGDG_REPO_URL=${PGDG_REPO_URL} 是否同步完整，或更换 POSTGRES_VERSION。"
  fi

  log "已找到 postgresql-${PG_MAJOR}：$(apt-cache policy "postgresql-${PG_MAJOR}" | awk '/Candidate:/ {print $2; exit}')"
}

install_postgresql_packages() {
  local packages=()

  packages+=("postgresql-${PG_MAJOR}")
  packages+=("postgresql-client-${PG_MAJOR}")

  if apt-cache show "postgresql-contrib-${PG_MAJOR}" >/dev/null 2>&1; then
    packages+=("postgresql-contrib-${PG_MAJOR}")
  elif apt-cache show "postgresql-contrib" >/dev/null 2>&1; then
    packages+=("postgresql-contrib")
  else
    warn "未找到 postgresql-contrib 包，跳过 contrib 扩展包安装。"
  fi

  log "安装 PostgreSQL ${PG_MAJOR} 官方二进制包：${packages[*]}。"
  apt-get install -y "${packages[@]}"

  [[ -x "/usr/lib/postgresql/${PG_MAJOR}/bin/postgres" ]] || die "PostgreSQL 二进制文件不存在：/usr/lib/postgresql/${PG_MAJOR}/bin/postgres。"

  ln -sfn "/usr/lib/postgresql/${PG_MAJOR}" "${INSTALL_LINK}"

  if [[ -d "${INSTALL_LINK}/lib" ]]; then
    echo "${INSTALL_LINK}/lib" > "/etc/ld.so.conf.d/postgresql-${PG_MAJOR}.conf"
    ldconfig || warn "ldconfig 执行失败，请手动检查。"
  fi
}

cluster_exists() {
  pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" '
    $1 == ver && $2 == name { found=1 }
    END { exit found ? 0 : 1 }
  '
}

cluster_status() {
  pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" '
    $1 == ver && $2 == name { print $4; exit }
  '
}

cluster_data_dir() {
  pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" '
    $1 == ver && $2 == name { print $6; exit }
  '
}

cluster_port() {
  pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" '
    $1 == ver && $2 == name { print $3; exit }
  '
}

check_port_conflict() {
  local conflict
  conflict="$(pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" -v port="${POSTGRES_PORT}" '
    $3 == port && !($1 == ver && $2 == name) { print $1 "/" $2 " 使用端口 " $3 }
  ' || true)"

  [[ -z "${conflict}" ]] || die "端口 ${POSTGRES_PORT} 已被其他 PostgreSQL 集群占用：${conflict}。"
}

prepare_dirs() {
  log "创建 PostgreSQL 目录。"
  install -d -m 0750 -o "${POSTGRES_USER}" -g "${POSTGRES_GROUP}" "${DATA_DIR}"
  install -d -m 0750 -o "${POSTGRES_USER}" -g "${POSTGRES_GROUP}" "${LOG_DIR}"
  install -d -m 0755 -o "${POSTGRES_USER}" -g "${POSTGRES_GROUP}" "${RUN_DIR}" || true
  install -d -m 0750 -o root -g root "${BACKUP_DIR}"
}

ensure_data_dir_safe() {
  if [[ -f "${DATA_DIR}/PG_VERSION" ]]; then
    return 0
  fi

  if find "${DATA_DIR}" -mindepth 1 -maxdepth 1 ! -name lost+found | grep -q .; then
    die "数据目录 ${DATA_DIR} 非空但未检测到 PostgreSQL 初始化标记 PG_VERSION。为避免误删数据，请清空目录或更换 DATA_DIR。"
  fi
}

create_or_validate_cluster() {
  check_port_conflict
  prepare_dirs
  ensure_data_dir_safe

  if cluster_exists; then
    local existing_dir real_existing real_expected existing_port
    existing_dir="$(cluster_data_dir)"
    existing_port="$(cluster_port)"
    real_existing="$(readlink -f "${existing_dir}")"
    real_expected="$(readlink -f "${DATA_DIR}")"

    [[ "${real_existing}" == "${real_expected}" ]] || die "已存在集群 ${PG_MAJOR}/${CLUSTER_NAME}，但数据目录为 ${existing_dir}，不是当前配置的 ${DATA_DIR}。"
    [[ "${existing_port}" == "${POSTGRES_PORT}" ]] || warn "已存在集群端口为 ${existing_port}，当前配置 POSTGRES_PORT=${POSTGRES_PORT}；稍后会写入配置并重启。"
    log "PostgreSQL 集群已存在：${PG_MAJOR}/${CLUSTER_NAME}。"
    NEW_CLUSTER_CREATED="false"
  else
    log "创建 PostgreSQL 集群：${PG_MAJOR}/${CLUSTER_NAME}，数据目录：${DATA_DIR}。"
    pg_createcluster "${PG_MAJOR}" "${CLUSTER_NAME}" \
      --datadir="${DATA_DIR}" \
      --port="${POSTGRES_PORT}" \
      --start
    NEW_CLUSTER_CREATED="true"
  fi

  [[ -f "${CONF_FILE}" ]] || die "配置文件不存在：${CONF_FILE}。"
  [[ -f "${HBA_FILE}" ]] || die "认证文件不存在：${HBA_FILE}。"
}

calculate_percent_mb() {
  local percent="$1"
  local min_mb="$2"
  local max_mb="$3"
  local mem_kb mem_mb result

  mem_kb="$(awk '/MemTotal/ {print $2}' /proc/meminfo)"
  mem_mb=$(( mem_kb / 1024 ))
  result=$(( mem_mb * percent / 100 ))

  (( result >= min_mb )) || result="${min_mb}"
  if [[ "${max_mb}" -gt 0 && "${result}" -gt "${max_mb}" ]]; then
    result="${max_mb}"
  fi

  echo "${result}MB"
}

calculate_work_mem() {
  if [[ "${WORK_MEM}" != "auto" ]]; then
    echo "${WORK_MEM}"
    return 0
  fi

  local mem_kb mem_mb result
  mem_kb="$(awk '/MemTotal/ {print $2}' /proc/meminfo)"
  mem_mb=$(( mem_kb / 1024 ))
  result=$(( mem_mb / MAX_CONNECTIONS / 4 ))
  (( result >= 4 )) || result=4
  (( result <= 64 )) || result=64
  echo "${result}MB"
}

postgresql_conf_set() {
  local key="$1"
  local value="$2"
  local file="${CONF_FILE}"

  if grep -Eq "^[#[:space:]]*${key}[[:space:]]*=" "${file}"; then
    sed -ri "s|^[#[:space:]]*${key}[[:space:]]*=.*|${key} = ${value}|" "${file}"
  else
    echo "${key} = ${value}" >> "${file}"
  fi
}

write_postgresql_conf() {
  backup_file "${CONF_FILE}"

  local effective_shared_buffers effective_cache effective_maintenance_mem effective_work_mem

  if [[ "${SHARED_BUFFERS}" == "auto" ]]; then
    effective_shared_buffers="$(calculate_percent_mb "${SHARED_BUFFERS_PERCENT}" 128 0)"
  else
    effective_shared_buffers="${SHARED_BUFFERS}"
  fi

  if [[ "${EFFECTIVE_CACHE_SIZE}" == "auto" ]]; then
    effective_cache="$(calculate_percent_mb "${EFFECTIVE_CACHE_SIZE_PERCENT}" 256 0)"
  else
    effective_cache="${EFFECTIVE_CACHE_SIZE}"
  fi

  if [[ "${MAINTENANCE_WORK_MEM}" == "auto" ]]; then
    effective_maintenance_mem="$(calculate_percent_mb 5 64 2048)"
  else
    effective_maintenance_mem="${MAINTENANCE_WORK_MEM}"
  fi

  effective_work_mem="$(calculate_work_mem)"

  log "写入 PostgreSQL 生产配置：${CONF_FILE}。"

  postgresql_conf_set "data_directory" "$(conf_quote "${DATA_DIR}")"
  postgresql_conf_set "hba_file" "$(conf_quote "${HBA_FILE}")"
  postgresql_conf_set "ident_file" "$(conf_quote "${IDENT_FILE}")"
  postgresql_conf_set "external_pid_file" "$(conf_quote "${RUN_DIR}/${PG_MAJOR}-${CLUSTER_NAME}.pid")"
  postgresql_conf_set "listen_addresses" "$(conf_quote "${LISTEN_ADDRESSES}")"
  postgresql_conf_set "port" "${POSTGRES_PORT}"
  postgresql_conf_set "unix_socket_directories" "$(conf_quote "${RUN_DIR}")"
  postgresql_conf_set "password_encryption" "$(conf_quote "${PASSWORD_ENCRYPTION}")"
  postgresql_conf_set "max_connections" "${MAX_CONNECTIONS}"
  postgresql_conf_set "shared_buffers" "${effective_shared_buffers}"
  postgresql_conf_set "effective_cache_size" "${effective_cache}"
  postgresql_conf_set "maintenance_work_mem" "${effective_maintenance_mem}"
  postgresql_conf_set "work_mem" "${effective_work_mem}"
  postgresql_conf_set "huge_pages" "${HUGE_PAGES}"
  postgresql_conf_set "dynamic_shared_memory_type" "posix"
  postgresql_conf_set "timezone" "$(conf_quote "${TIMEZONE}")"
  postgresql_conf_set "log_timezone" "$(conf_quote "${TIMEZONE}")"
  postgresql_conf_set "default_text_search_config" "$(conf_quote "${DEFAULT_TEXT_SEARCH_CONFIG}")"

  postgresql_conf_set "wal_level" "${WAL_LEVEL}"
  postgresql_conf_set "wal_compression" "${WAL_COMPRESSION}"
  postgresql_conf_set "max_wal_size" "${MAX_WAL_SIZE}"
  postgresql_conf_set "min_wal_size" "${MIN_WAL_SIZE}"
  postgresql_conf_set "checkpoint_completion_target" "${CHECKPOINT_COMPLETION_TARGET}"
  postgresql_conf_set "max_wal_senders" "${MAX_WAL_SENDERS}"
  postgresql_conf_set "max_replication_slots" "${MAX_REPLICATION_SLOTS}"

  postgresql_conf_set "random_page_cost" "${RANDOM_PAGE_COST}"
  postgresql_conf_set "effective_io_concurrency" "${EFFECTIVE_IO_CONCURRENCY}"
  postgresql_conf_set "default_statistics_target" "${DEFAULT_STATISTICS_TARGET}"
  postgresql_conf_set "jit" "${JIT}"

  postgresql_conf_set "logging_collector" "on"
  postgresql_conf_set "log_directory" "$(conf_quote "${LOG_DIR}")"
  postgresql_conf_set "log_filename" "$(conf_quote "${LOG_FILENAME}")"
  postgresql_conf_set "log_rotation_age" "${LOG_ROTATION_AGE}"
  postgresql_conf_set "log_rotation_size" "${LOG_ROTATION_SIZE}"
  postgresql_conf_set "log_truncate_on_rotation" "${LOG_TRUNCATE_ON_ROTATION}"
  postgresql_conf_set "log_min_duration_statement" "${LOG_MIN_DURATION_STATEMENT}"
  postgresql_conf_set "log_line_prefix" "$(conf_quote "${LOG_LINE_PREFIX}")"
  postgresql_conf_set "log_lock_waits" "${LOG_LOCK_WAITS}"
  postgresql_conf_set "deadlock_timeout" "${DEADLOCK_TIMEOUT}"
  postgresql_conf_set "log_checkpoints" "on"
  postgresql_conf_set "log_autovacuum_min_duration" "1000"

  postgresql_conf_set "autovacuum" "${AUTOVACUUM}"
  postgresql_conf_set "autovacuum_max_workers" "${AUTOVACUUM_MAX_WORKERS}"
  postgresql_conf_set "autovacuum_naptime" "${AUTOVACUUM_NAPTIME}"
  postgresql_conf_set "autovacuum_vacuum_cost_limit" "${AUTOVACUUM_VACUUM_COST_LIMIT}"
  postgresql_conf_set "autovacuum_vacuum_scale_factor" "${AUTOVACUUM_VACUUM_SCALE_FACTOR}"
  postgresql_conf_set "autovacuum_analyze_scale_factor" "${AUTOVACUUM_ANALYZE_SCALE_FACTOR}"

  chown "${POSTGRES_USER}:${POSTGRES_GROUP}" "${CONF_FILE}"
  chmod 0640 "${CONF_FILE}"
}

write_pg_hba() {
  backup_file "${HBA_FILE}"

  log "写入 PostgreSQL 认证配置：${HBA_FILE}。"

  cat > "${HBA_FILE}" <<EOF_HBA
# 由 postgresql_pgdg_install.sh 生成
# local postgres 使用 peer，便于本机 root/runuser 运维
# 业务访问默认使用 ${PASSWORD_ENCRYPTION}

local   all             postgres                                peer
local   all             all                                     ${PASSWORD_ENCRYPTION}
host    all             all             127.0.0.1/32            ${PASSWORD_ENCRYPTION}
host    all             all             ::1/128                 ${PASSWORD_ENCRYPTION}
host    all             all             ${ALLOW_REMOTE_CIDR}    ${PASSWORD_ENCRYPTION}
EOF_HBA

  chown "${POSTGRES_USER}:${POSTGRES_GROUP}" "${HBA_FILE}"
  chmod 0640 "${HBA_FILE}"
}

configure_limits() {
  is_true "${MANAGE_LIMITS}" || return 0
  log "配置 PostgreSQL 文件句柄限制。"

  cat > "/etc/security/limits.d/99-${POSTGRES_USER}.conf" <<EOF_LIMITS
${POSTGRES_USER} soft nofile ${LIMIT_NOFILE}
${POSTGRES_USER} hard nofile ${LIMIT_NOFILE}
${POSTGRES_USER} soft nproc ${LIMIT_NOFILE}
${POSTGRES_USER} hard nproc ${LIMIT_NOFILE}
EOF_LIMITS
}

configure_sysctl() {
  is_true "${MANAGE_SYSCTL}" || return 0
  log "写入 PostgreSQL sysctl 参数。"

  cat > /etc/sysctl.d/99-postgresql.conf <<EOF_SYSCTL
vm.swappiness = ${SWAPPINESS}
net.core.somaxconn = ${SOMAXCONN}
fs.file-max = ${FS_FILE_MAX}
vm.overcommit_memory = ${VM_OVERCOMMIT_MEMORY}
EOF_SYSCTL

  sysctl -p /etc/sysctl.d/99-postgresql.conf >/dev/null || warn "加载 sysctl 参数失败，请手动检查 /etc/sysctl.d/99-postgresql.conf。"
}

write_systemd_override() {
  local override_dir="/etc/systemd/system/${SERVICE_NAME}.d"
  install -d -m 0755 "${override_dir}"

  log "写入 systemd override：${override_dir}/override.conf。"

  cat > "${override_dir}/override.conf" <<EOF_SYSTEMD
[Service]
LimitNOFILE=${LIMIT_NOFILE}
OOMScoreAdjust=-500
EOF_SYSTEMD

  systemctl daemon-reload
}

link_binaries() {
  is_true "${LINK_BINARIES}" || return 0
  log "创建 PostgreSQL 常用命令软链接到 ${BIN_LINK_DIR}。"

  install -d -m 0755 "${BIN_LINK_DIR}"
  local bin
  for bin in psql pg_dump pg_restore pg_basebackup pg_isready pg_ctl initdb postgres createdb createuser dropdb dropuser vacuumdb reindexdb clusterdb; do
    if [[ -x "${INSTALL_LINK}/bin/${bin}" ]]; then
      ln -sfn "${INSTALL_LINK}/bin/${bin}" "${BIN_LINK_DIR}/${bin}"
    fi
  done
}

write_profile() {
  is_true "${WRITE_PROFILE}" || return 0
  log "写入 PostgreSQL PATH 配置。"

  cat > /etc/profile.d/postgresql.sh <<EOF_PROFILE
# 由 postgresql_pgdg_install.sh 生成
export PGHOME=${INSTALL_LINK}
export PGDATA=${DATA_DIR}
export PGPORT=${POSTGRES_PORT}
export PATH=${INSTALL_LINK}/bin:\$PATH
EOF_PROFILE

  chmod 0644 /etc/profile.d/postgresql.sh
}

close_firewall() {
  is_true "${CLOSE_FIREWALL}" || return 0

  if command_exists ufw && ufw status 2>/dev/null | grep -q "active"; then
    log "关闭 ufw 防火墙。"
    ufw disable >/dev/null || warn "ufw disable 执行失败。"
  fi

  if command_exists firewall-cmd && systemctl is-active --quiet firewalld; then
    log "关闭 firewalld 防火墙。"
    systemctl stop firewalld || true
    systemctl disable firewalld >/dev/null 2>&1 || true
  fi
}

start_or_restart_cluster() {
  log "启动/重启 PostgreSQL 集群：${PG_MAJOR}/${CLUSTER_NAME}。"

  install -d -m 2775 -o "${POSTGRES_USER}" -g "${POSTGRES_GROUP}" "${RUN_DIR}"

  if [[ "$(cluster_status || true)" == "online" ]]; then
    pg_ctlcluster "${PG_MAJOR}" "${CLUSTER_NAME}" restart
  else
    pg_ctlcluster "${PG_MAJOR}" "${CLUSTER_NAME}" start
  fi

  systemctl enable postgresql >/dev/null 2>&1 || true

  local i
  for i in $(seq 1 60); do
    if pg_isready -h 127.0.0.1 -p "${POSTGRES_PORT}" -U postgres >/dev/null 2>&1; then
      log "PostgreSQL 健康检查通过。"
      return 0
    fi
    sleep 1
  done

  echo
  warn "PostgreSQL 健康检查失败，输出最近日志："
  pg_lsclusters || true
  journalctl -u "postgresql@${PG_MAJOR}-${CLUSTER_NAME}.service" --no-pager -n 120 >&2 || true
  find "${LOG_DIR}" -maxdepth 1 -type f -name "postgresql-${PG_MAJOR}-${CLUSTER_NAME}-*.log" -print -exec tail -n 120 {} \; 2>/dev/null || true
  die "PostgreSQL 健康检查失败。"
}

set_postgres_password() {
  if [[ -z "${POSTGRES_PASSWORD}" ]]; then
    POSTGRES_PASSWORD="$(generate_password)"
    log "已自动生成 postgres 密码。"
  fi

  local escaped
  escaped="$(sql_escape "${POSTGRES_PASSWORD}")"

  log "设置 postgres 用户密码。"
  runuser -u "${POSTGRES_USER}" -- "${INSTALL_LINK}/bin/psql" -X -v ON_ERROR_STOP=1 --dbname=postgres <<EOF_SQL
ALTER USER postgres WITH PASSWORD '${escaped}';
EOF_SQL

  install -d -m 0750 -o root -g root "$(dirname "${AUTH_FILE}")"
  cat > "${AUTH_FILE}" <<EOF_AUTH
PGHOST=127.0.0.1
PGPORT=${POSTGRES_PORT}
PGUSER=postgres
PGPASSWORD=${POSTGRES_PASSWORD}
PGDATABASE=postgres
PGDATA=${DATA_DIR}
PGSERVICE=${SERVICE_NAME}
EOF_AUTH
  chmod 0600 "${AUTH_FILE}"
}

run_postgres_sql() {
  PGPASSWORD="${POSTGRES_PASSWORD}" "${INSTALL_LINK}/bin/psql" \
    -h 127.0.0.1 \
    -p "${POSTGRES_PORT}" \
    -U postgres \
    -d postgres \
    -X \
    -v ON_ERROR_STOP=1
}

create_app_database() {
  is_true "${CREATE_APP_USER}" || return 0

  if [[ -z "${APP_DB_PASSWORD}" ]]; then
    APP_DB_PASSWORD="$(generate_password)"
    log "已自动生成业务用户密码。"
  fi

  local escaped_app_password
  escaped_app_password="$(sql_escape "${APP_DB_PASSWORD}")"

  log "创建业务库和业务用户：${APP_DB_NAME} / ${APP_DB_USER}。"

  run_postgres_sql <<EOF_SQL
DO \$\$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '${APP_DB_USER}') THEN
    CREATE ROLE ${APP_DB_USER} LOGIN PASSWORD '${escaped_app_password}';
  ELSE
    ALTER ROLE ${APP_DB_USER} WITH LOGIN PASSWORD '${escaped_app_password}';
  END IF;
END
\$\$;

SELECT 'CREATE DATABASE ${APP_DB_NAME} OWNER ${APP_DB_USER}'
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = '${APP_DB_NAME}')\gexec

GRANT ${APP_PRIVILEGES} ON DATABASE ${APP_DB_NAME} TO ${APP_DB_USER};
EOF_SQL

  if ! grep -q '^APP_DB_NAME=' "${AUTH_FILE}" 2>/dev/null; then
    cat >> "${AUTH_FILE}" <<EOF_APP_AUTH
APP_DB_NAME=${APP_DB_NAME}
APP_DB_USER=${APP_DB_USER}
APP_DB_PASSWORD=${APP_DB_PASSWORD}
APP_HOST_CIDR=${APP_HOST_CIDR}
EOF_APP_AUTH
    chmod 0600 "${AUTH_FILE}"
  fi

  if ! grep -Eq "^host[[:space:]]+${APP_DB_NAME}[[:space:]]+${APP_DB_USER}[[:space:]]+${APP_HOST_CIDR}[[:space:]]+" "${HBA_FILE}"; then
    cat >> "${HBA_FILE}" <<EOF_APP_HBA
host    ${APP_DB_NAME}    ${APP_DB_USER}    ${APP_HOST_CIDR}    ${PASSWORD_ENCRYPTION}
EOF_APP_HBA
    pg_ctlcluster "${PG_MAJOR}" "${CLUSTER_NAME}" reload
  fi
}

print_result() {
  local effective_shared effective_cache effective_work_mem
  effective_shared="$(awk -F= '/^[[:space:]]*shared_buffers[[:space:]]*=/{gsub(/[[:space:]]/,"",$2); print $2}' "${CONF_FILE}" | tail -n1)"
  effective_cache="$(awk -F= '/^[[:space:]]*effective_cache_size[[:space:]]*=/{gsub(/[[:space:]]/,"",$2); print $2}' "${CONF_FILE}" | tail -n1)"
  effective_work_mem="$(awk -F= '/^[[:space:]]*work_mem[[:space:]]*=/{gsub(/[[:space:]]/,"",$2); print $2}' "${CONF_FILE}" | tail -n1)"

  cat <<EOF_RESULT

PostgreSQL 安装完成。

版本：${PG_MAJOR}
服务：${SERVICE_NAME}
软件目录：/usr/lib/postgresql/${PG_MAJOR}
软链接：${INSTALL_LINK}
命令目录：${INSTALL_LINK}/bin
配置目录：${CONFIG_DIR}
配置文件：${CONF_FILE}
认证文件：${HBA_FILE}
数据目录：${DATA_DIR}
日志目录：${LOG_DIR}
备份目录：${BACKUP_DIR}
运行目录：${RUN_DIR}
端口：${POSTGRES_PORT}
监听：${LISTEN_ADDRESSES}
允许访问：${ALLOW_REMOTE_CIDR}
shared_buffers：${effective_shared}
effective_cache_size：${effective_cache}
work_mem：${effective_work_mem}
凭据文件：${AUTH_FILE}

常用命令：
  systemctl status ${SERVICE_NAME}
  systemctl restart ${SERVICE_NAME}
  journalctl -u ${SERVICE_NAME} -f
  psql -h 127.0.0.1 -p ${POSTGRES_PORT} -U postgres -d postgres
  pg_lsclusters
EOF_RESULT

  if is_true "${CREATE_APP_USER}"; then
    cat <<EOF_APP_RESULT
业务库：
  数据库：${APP_DB_NAME}
  用户：${APP_DB_USER}
  密码：已写入 ${AUTH_FILE}
EOF_APP_RESULT
  fi
}

install_postgresql() {
  require_root
  normalize_version_and_paths
  validate_inputs
  detect_os
  install_base_dependencies
  disable_auto_cluster_creation
  setup_pgdg_repo
  install_postgresql_packages
  configure_limits
  configure_sysctl
  link_binaries
  write_profile
  close_firewall
  create_or_validate_cluster
  write_postgresql_conf
  write_pg_hba
  write_systemd_override
  start_or_restart_cluster
  set_postgres_password
  start_or_restart_cluster
  create_app_database
  print_result
}

reset_password() {
  require_root
  normalize_version_and_paths
  validate_inputs

  command_exists pg_lsclusters || die "未找到 pg_lsclusters，请先安装 PostgreSQL。"
  cluster_exists || die "未找到 PostgreSQL 集群 ${PG_MAJOR}/${CLUSTER_NAME}。"

  if [[ "$(cluster_status || true)" != "online" ]]; then
    pg_ctlcluster "${PG_MAJOR}" "${CLUSTER_NAME}" start
  fi

  set_postgres_password
  log "postgres 密码重置完成，凭据已写入 ${AUTH_FILE}。"
}

status_postgresql() {
  require_root
  normalize_version_and_paths
  detect_os

  echo
  pg_lsclusters || true
  echo

  if command_exists systemctl; then
    systemctl --no-pager --full status "${SERVICE_NAME}" || true
  fi
}

uninstall_postgresql() {
  require_root
  normalize_version_and_paths
  detect_os

  echo
  warn "即将卸载 PostgreSQL ${PG_MAJOR}/${CLUSTER_NAME}。"
  warn "服务：${SERVICE_NAME}"
  warn "配置目录：${CONFIG_DIR}"
  warn "数据目录：${DATA_DIR}"
  warn "日志目录：${LOG_DIR}"
  warn "备份目录：${BACKUP_DIR}"
  echo

  if ! confirm_yes "第一次确认：是否真的卸载 PostgreSQL？"; then
    log "用户取消卸载。"
    exit 0
  fi

  if command_exists pg_lsclusters && cluster_exists; then
    log "删除 PostgreSQL 集群：${PG_MAJOR}/${CLUSTER_NAME}。"
    pg_dropcluster --stop "${PG_MAJOR}" "${CLUSTER_NAME}"
  else
    warn "未检测到集群 ${PG_MAJOR}/${CLUSTER_NAME}，跳过 pg_dropcluster。"
    systemctl stop "${SERVICE_NAME}" 2>/dev/null || true
    systemctl disable "${SERVICE_NAME}" 2>/dev/null || true
  fi

  log "清理 systemd override。"
  rm -rf "/etc/systemd/system/${SERVICE_NAME}.d"
  systemctl daemon-reload || true
  systemctl reset-failed || true

  echo
  warn "第二次确认将删除 PostgreSQL 相关文件。"
  warn "将删除：${INSTALL_LINK}、${CONFIG_DIR}、${DATA_DIR}、${AUTH_FILE}、profile、limits、sysctl、ldconfig、命令软链接。"
  if is_true "${REMOVE_LOG_FILES}"; then
    warn "还将删除匹配 ${LOG_DIR}/postgresql-${PG_MAJOR}-${CLUSTER_NAME}-*.log 的日志文件。"
  fi
  echo

  if ! confirm_yes "第二次确认：是否删除配置、数据和脚本生成的文件？"; then
    log "用户选择保留文件。卸载已完成：集群已停止并移除，文件未删除。"
    exit 0
  fi

  log "清理 PostgreSQL 命令软链接。"
  local bin
  for bin in psql pg_dump pg_restore pg_basebackup pg_isready pg_ctl initdb postgres createdb createuser dropdb dropuser vacuumdb reindexdb clusterdb; do
    if [[ -L "${BIN_LINK_DIR}/${bin}" ]] && readlink "${BIN_LINK_DIR}/${bin}" | grep -q "${INSTALL_LINK}/bin/"; then
      rm -f "${BIN_LINK_DIR}/${bin}"
    fi
  done

  log "清理 PostgreSQL 文件。"
  rm -f "${INSTALL_LINK}"
  rm -rf "${CONFIG_DIR}"
  rm -rf "${DATA_DIR}"
  rm -f "${AUTH_FILE}"
  rm -f /etc/profile.d/postgresql.sh
  rm -f "/etc/security/limits.d/99-${POSTGRES_USER}.conf"
  rm -f /etc/sysctl.d/99-postgresql.conf
  rm -f "/etc/ld.so.conf.d/postgresql-${PG_MAJOR}.conf"
  ldconfig || true

  if is_true "${REMOVE_LOG_FILES}"; then
    find "${LOG_DIR}" -maxdepth 1 -type f -name "postgresql-${PG_MAJOR}-${CLUSTER_NAME}-*.log" -delete 2>/dev/null || true
  fi

  if is_true "${PURGE_PACKAGES}"; then
    log "卸载 PostgreSQL ${PG_MAJOR} 软件包。"
    apt-get purge -y "postgresql-${PG_MAJOR}" "postgresql-client-${PG_MAJOR}" || true
    apt-get autoremove -y || true
  fi

  if is_true "${REMOVE_POSTGRES_USER}"; then
    warn "删除 postgres 用户可能影响其他 PostgreSQL 版本或集群。"
    userdel "${POSTGRES_USER}" 2>/dev/null || true
    groupdel "${POSTGRES_GROUP}" 2>/dev/null || true
  fi

  log "PostgreSQL 卸载完成。"
}

main() {
  local action="${1:-install}"

  case "${action}" in
    install)
      install_postgresql
      ;;
    uninstall|remove|purge)
      uninstall_postgresql
      ;;
    status)
      status_postgresql
      ;;
    reset-password|resetpwd)
      reset_password
      ;;
    -h|--help|help)
      usage
      ;;
    *)
      usage
      die "不支持的参数：${action}。"
      ;;
  esac
}

main "$@"

POSTGRES_VERSION="${POSTGRES_VERSION:-16}"
POSTGRES_PORT="${POSTGRES_PORT:-5432}"
LISTEN_ADDRESSES="${LISTEN_ADDRESSES:-0.0.0.0}"
ALLOW_REMOTE_CIDR="${ALLOW_REMOTE_CIDR:-0.0.0.0/0}"
POSTGRES_PASSWORD="${POSTGRES_PASSWORD:-passwd}"

sudo bash postgresql_pgdg_install.sh install

sudo bash postgresql_pgdg_install.sh

sudo bash postgresql_pgdg_install.sh status

POSTGRES_PASSWORD='NewStrongPassword' sudo -E bash postgresql_pgdg_install.sh reset-password

sudo bash postgresql_pgdg_install.sh uninstall

 ./psql_install.sh install 
[2026-05-15 10:56:25] [INFO] 系统识别完成：Ubuntu 22.04.5 LTS，codename=jammy。
[2026-05-15 10:56:25] [INFO] 安装基础依赖。
Hit:1 http://mirrors.tencentyun.com/ubuntu jammy InRelease
Hit:2 http://mirrors.tencentyun.com/ubuntu jammy-updates InRelease
Hit:3 http://mirrors.tencentyun.com/ubuntu jammy-security InRelease

...... 自动安装过程省略......

[2026-05-15 10:57:07] [INFO] 写入 PostgreSQL 生产配置：/etc/postgresql/16/main/postgresql.conf。
[2026-05-15 10:57:07] [INFO] 写入 PostgreSQL 认证配置：/etc/postgresql/16/main/pg_hba.conf。
[2026-05-15 10:57:07] [INFO] 写入 systemd override：/etc/systemd/system/postgresql@16-main.service.d/override.conf。
[2026-05-15 10:57:07] [INFO] 启动/重启 PostgreSQL 集群：16/main。
[2026-05-15 10:57:11] [INFO] PostgreSQL 健康检查通过。
[2026-05-15 10:57:11] [INFO] 设置 postgres 用户密码。
ALTER ROLE
[2026-05-15 10:57:11] [INFO] 启动/重启 PostgreSQL 集群：16/main。
[2026-05-15 10:57:14] [INFO] PostgreSQL 健康检查通过。

PostgreSQL 安装完成。

版本：16
服务：postgresql@16-main.service
软件目录：/usr/lib/postgresql/16
软链接：/usr/local/pgsql
命令目录：/usr/local/pgsql/bin
配置目录：/etc/postgresql/16/main
配置文件：/etc/postgresql/16/main/postgresql.conf
认证文件：/etc/postgresql/16/main/pg_hba.conf
数据目录：/data/postgresql/16/main
日志目录：/data/postgresql/log
备份目录：/data/postgresql/backup
运行目录：/run/postgresql
端口：5432
监听：0.0.0.0
允许访问：0.0.0.0/0
shared_buffers：1904MB
effective_cache_size：5714MB
work_mem：4MB
凭据文件：/data/postgresql/backup/.postgresql.auth

常用命令：
  systemctl status postgresql@16-main.service
  systemctl restart postgresql@16-main.service
  journalctl -u postgresql@16-main.service -f
  psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
  pg_lsclusters

mkdir -p /data/postgresql/docker/data
mkdir -p /data/postgresql/docker/backup

chmod 700 /data/postgresql/docker/data

docker run -d \
  --name postgres16 \
  --restart always \
  -p 5432:5432 \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD='你的强密码' \
  -e POSTGRES_DB=postgres \
  -e TZ=Asia/Shanghai \
  -v /data/postgresql/docker/data:/var/lib/postgresql/data \
  postgres:16

docker ps | grep postgres16

docker logs -f postgres16

docker exec -it postgres16 bash

docker exec -it postgres16 psql -U postgres -d postgres

mkdir -p /data/postgresql/docker-compose
cd /data/postgresql/docker-compose

version: "3.8"

services:
  postgres:
    image: postgres:16
    container_name: postgres16
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: "你的强密码"
      POSTGRES_DB: postgres
      TZ: Asia/Shanghai
    volumes:
      - /data/postgresql/docker/data:/var/lib/postgresql/data
      - /data/postgresql/docker/backup:/backup
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

docker compose up -d

docker-compose up -d

docker compose ps

docker compose logs -f postgres

docker compose down

docker compose restart postgres

psql -h 127.0.0.1 -p 5432 -U postgres -d postgres

docker exec -it postgres16 psql -U postgres -d postgres

\l
\du
SELECT version();
\q

docker exec -it postgres16 psql -U postgres -d postgres

CREATE USER appuser WITH PASSWORD '业务用户强密码';

CREATE DATABASE appdb OWNER appuser;

GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

\c appdb

GRANT USAGE, CREATE ON SCHEMA public TO appuser;

ALTER SCHEMA public OWNER TO appuser;

\q

psql -h PostgreSQL服务器IP -p 5432 -U appuser -d appdb

# 查看容器
docker ps | grep postgres16

# 查看日志
docker logs -f postgres16

# 重启容器
docker restart postgres16

# 停止容器
docker stop postgres16

# 启动容器
docker start postgres16

# 进入容器
docker exec -it postgres16 bash

# 进入数据库
docker exec -it postgres16 psql -U postgres -d postgres

docker exec -t postgres16 pg_dump -U postgres -d appdb > /data/postgresql/docker/backup/appdb.sql

cat /data/postgresql/docker/backup/appdb.sql | docker exec -i postgres16 psql -U postgres -d appdb

docker exec -t postgres16 pg_dump -U postgres -d appdb -F c > /data/postgresql/docker/backup/appdb.dump

cat /data/postgresql/docker/backup/appdb.dump | docker exec -i postgres16 pg_restore -U postgres -d appdb

ALTER USER postgres WITH PASSWORD '新密码';

# 查看 PostgreSQL 总服务状态
systemctl status postgresql

# 查看 PostgreSQL 16/main 实例状态
systemctl status postgresql@16-main.service

# 启动 PostgreSQL 16/main
systemctl start postgresql@16-main.service

# 停止 PostgreSQL 16/main
systemctl stop postgresql@16-main.service

# 重启 PostgreSQL 16/main
systemctl restart postgresql@16-main.service

# 设置开机自启
systemctl enable postgresql

# 查看 PostgreSQL 服务日志
journalctl -u postgresql@16-main.service -f

# 查看当前 PostgreSQL 集群
pg_lsclusters

# 启动指定集群
pg_ctlcluster 16 main start

# 停止指定集群
pg_ctlcluster 16 main stop

# 重启指定集群
pg_ctlcluster 16 main restart

# 重载配置，不中断数据库连接
pg_ctlcluster 16 main reload

# 切换到 postgres 系统用户
su - postgres

# 登录 PostgreSQL
psql

# 指定主机、端口、用户、数据库登录
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres

# 退出 psql
\q

-- 查看数据库列表
\l

-- 查看用户 / 角色
\du

-- 查看当前连接信息
\conninfo

-- 查看当前数据库下的表
\dt

-- 查看表结构
\d 表名

-- 查看所有 schema
\dn

-- 切换数据库
\c 数据库名

-- 查看 psql 帮助
\?

-- 查看 SQL 帮助
\h

-- 退出
\q

-- 创建数据库
CREATE DATABASE testdb;

-- 删除数据库
DROP DATABASE testdb;

-- 创建用户
CREATE USER testuser WITH PASSWORD 'StrongPassword';

-- 修改用户密码
ALTER USER testuser WITH PASSWORD 'NewStrongPassword';

-- 给用户授权数据库
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;

-- 删除用户
DROP USER testuser;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, age, email)
VALUES ('zhangsan', 25, 'zhangsan@example.com');

INSERT INTO users (username, age, email)
VALUES 
('lisi', 28, 'lisi@example.com'),
('wangwu', 30, 'wangwu@example.com');

-- 查询所有数据
SELECT * FROM users;
 id | username | age |        email         |         created_at         
----+----------+-----+----------------------+----------------------------
  1 | zhangsan |  25 | zhangsan@example.com | 2026-05-15 11:10:37.106878
  2 | lisi     |  28 | lisi@example.com     | 2026-05-15 11:10:37.108108
  3 | wangwu   |  30 | wangwu@example.com   | 2026-05-15 11:10:37.108108
(3 rows)

-- 查询指定字段
SELECT id, username, email FROM users;
 id | username |        email         
----+----------+----------------------
  1 | zhangsan | zhangsan@example.com
  2 | lisi     | lisi@example.com
  3 | wangwu   | wangwu@example.com
(3 rows)

-- 条件查询
SELECT * FROM users WHERE age > 25;
 id | username | age |       email        |         created_at         
----+----------+-----+--------------------+----------------------------
  2 | lisi     |  28 | lisi@example.com   | 2026-05-15 11:10:37.108108
  3 | wangwu   |  30 | wangwu@example.com | 2026-05-15 11:10:37.108108
(2 rows)

-- 模糊查询
SELECT * FROM users WHERE username LIKE '%zhang%';
 id | username | age |        email         |         created_at         
----+----------+-----+----------------------+----------------------------
  1 | zhangsan |  25 | zhangsan@example.com | 2026-05-15 11:10:37.106878
(1 row)

-- 排序查询
SELECT * FROM users ORDER BY id DESC;
 id | username | age |        email         |         created_at         
----+----------+-----+----------------------+----------------------------
  3 | wangwu   |  30 | wangwu@example.com   | 2026-05-15 11:10:37.108108
  2 | lisi     |  28 | lisi@example.com     | 2026-05-15 11:10:37.108108
  1 | zhangsan |  25 | zhangsan@example.com | 2026-05-15 11:10:37.106878
(3 rows)

-- 限制返回条数
SELECT * FROM users LIMIT 10;

-- 分页查询
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 修改单个字段
UPDATE users
SET age = 26
WHERE username = 'zhangsan';

-- 验证 25->26
 SELECT * FROM users WHERE username LIKE '%zhang%'; 
 id | username | age |        email         |         created_at         
----+----------+-----+----------------------+----------------------------
  1 | zhangsan |  26 | zhangsan@example.com | 2026-05-15 11:10:37.106878
(1 row)


-- 修改多个字段
UPDATE users
SET age = 29,
    email = 'new_lisi@example.com'
WHERE username = 'lisi';

-- 验证 多条数据改变
SELECT * FROM users WHERE username LIKE '%lisi%';                      
 id | username | age |        email         |         created_at         
----+----------+-----+----------------------+----------------------------
  2 | lisi     |  29 | new_lisi@example.com | 2026-05-15 11:10:37.108108
(1 row)

-- 删除指定用户
DELETE FROM users
WHERE username = 'wangwu';

-- 验证
SELECT * FROM users;                               
 id | username | age |        email         |         created_at         
----+----------+-----+----------------------+----------------------------
  1 | zhangsan |  26 | zhangsan@example.com | 2026-05-15 11:10:37.106878
  2 | lisi     |  29 | new_lisi@example.com | 2026-05-15 11:10:37.108108
(2 rows)

-- 删除年龄小于 18 的用户
DELETE FROM users
WHERE age < 18;

-- 增加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 验证
\d users 
                                        Table "public.users"
   Column   |            Type             | Collation | Nullable |              Default              
------------+-----------------------------+-----------+----------+-----------------------------------
 id         | integer                     |           | not null | nextval('users_id_seq'::regclass)
 username   | character varying(50)       |           | not null | 
 age        | integer                     |           |          | 
 email      | character varying(100)      |           |          | 
 created_at | timestamp without time zone |           |          | CURRENT_TIMESTAMP
 phone      | character varying(20)       |           |          | 

-- 修改字段类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(50);

-- 删除字段
ALTER TABLE users DROP COLUMN phone;

-- 重命名字段
ALTER TABLE users RENAME COLUMN username TO name;

-- 重命名表
ALTER TABLE users RENAME TO user_info;

-- 创建普通索引
CREATE INDEX idx_users_username ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 删除索引
DROP INDEX idx_users_username;

WHERE 条件字段
JOIN 关联字段
ORDER BY 排序字段
高频查询字段

-- 开启事务
BEGIN;

-- 执行修改
UPDATE users SET age = 31 WHERE username = 'zhangsan';

-- 提交事务
COMMIT;

BEGIN;

DELETE FROM users WHERE username = 'lisi';

ROLLBACK;

SELECT version();

PostgreSQL 16.14 (Ubuntu 16.14-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(1 row)

SELECT current_database();

SELECT current_user;

SELECT inet_client_addr();

SELECT inet_server_addr();

SELECT
    pid,
    usename,
    datname,
    client_addr,
    state,
    backend_start,
    query_start,
    query
FROM pg_stat_activity
ORDER BY query_start DESC;

SELECT
    pid,
    usename,
    datname,
    client_addr,
    now() - query_start AS running_time,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY running_time DESC;

SELECT
    pid,
    usename,
    datname,
    client_addr,
    state,
    now() - state_change AS idle_time
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_time DESC;

SELECT
    datname,
    COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY datname
ORDER BY connection_count DESC;

SHOW max_connections;

SELECT
    pid,
    usename,
    datname,
    wait_event_type,
    wait_event,
    state,
    query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
    ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
   AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
   AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
   AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
   AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
   AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
   AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
   AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
   AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
   AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
   AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking
    ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

SELECT
    datname,
    blks_hit,
    blks_read,
    ROUND(
        blks_hit * 100.0 / NULLIF(blks_hit + blks_read, 0),
        2
    ) AS cache_hit_ratio
FROM pg_stat_database
WHERE blks_hit + blks_read > 0
ORDER BY cache_hit_ratio ASC;

SELECT
    datname,
    xact_commit,
    xact_rollback,
    ROUND(
        xact_rollback * 100.0 / NULLIF(xact_commit + xact_rollback, 0),
        2
    ) AS rollback_ratio
FROM pg_stat_database
ORDER BY rollback_ratio DESC;

SHOW log_min_duration_statement;

1000

SHOW listen_addresses;
SHOW port;
SHOW max_connections;
SHOW shared_buffers;
SHOW work_mem;
SHOW timezone;
SHOW log_timezone;

SELECT pg_cancel_backend(pid);

SELECT pg_cancel_backend(12345);

SELECT pg_terminate_backend(12345);

PostgreSQL
    ↓
postgres_exporter
    ↓
 Categraf
    ↓
victoriametrics
    ↓
Nightingale

su - postgres
psql

CREATE USER postgres_exporter WITH PASSWORD '<监控用户密码>';

GRANT pg_monitor TO postgres_exporter;

GRANT CONNECT ON DATABASE postgres TO postgres_exporter;

\q

psql -h 127.0.0.1 -p 5432 -U postgres_exporter -d postgres

SELECT current_user;
SELECT current_database();
\q

postgres_exporter

cat > /etc/default/postgres_exporter <<'EOF'
DATA_SOURCE_URI=127.0.0.1:5432/postgres?sslmode=disable
DATA_SOURCE_USER=postgres_exporter
DATA_SOURCE_PASS=<监控用户密码>
EOF

chmod 600 /etc/default/postgres_exporter

/data/postgres_exporter