# 1. 安装依赖
sudo apt update
sudo apt install -y wget gnupg2 lsb-release

# 2. 添加 PostgreSQL 官方 APT 仓库
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt \
$(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# 3. 导入仓库签名密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  sudo apt-key add -

# 4. 更新包索引并安装
sudo apt update
sudo apt install -y postgresql-17

# 5. 验证安装
sudo systemctl status postgresql
psql --version

sudo apt install -y postgresql-17-contrib postgresql-17-postgis-3

# 1. 安装官方仓库 RPM
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 2. 禁用内置 PostgreSQL 模块（RHEL 8+/CentOS 8+）
sudo dnf -qy module disable postgresql

# 3. 安装 PostgreSQL 17
sudo yum install -y postgresql17-server postgresql17

# 4. 初始化数据库
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

# 5. 启动并设置开机自启
sudo systemctl start postgresql-17
sudo systemctl enable postgresql-17

# 6. 验证
sudo systemctl status postgresql-17
/usr/pgsql-17/bin/psql --version

# 安装
brew install postgresql@17

# 启动服务
brew services start postgresql@17

# 验证
psql --version

# 首次连接（macOS 默认使用当前系统用户）
psql postgres

# 1. 安装编译依赖
sudo apt install -y build-essential libreadline-dev zlib1g-dev \
  libssl-dev libxml2-dev libxslt-dev libsystemd-dev \
  libicu-dev pkg-config

# 2. 下载源码
wget https://ftp.postgresql.org/pub/source/v17.0/postgresql-17.0.tar.gz
tar xzf postgresql-17.0.tar.gz
cd postgresql-17.0

# 3. 配置编译选项
./configure --prefix=/usr/local/pgsql \
  --with-openssl \
  --with-systemd \
  --with-icu \
  --with-libxml

# 4. 编译并安装
make -j$(nproc)
sudo make install

# 5. 安装 contrib 扩展
cd contrib
make -j$(nproc)
sudo make install

# 6. 创建用户和数据目录
sudo useradd -m -s /bin/bash postgres
sudo mkdir -p /var/lib/postgresql/data
sudo chown postgres:postgres /var/lib/postgresql/data

# 7. 初始化数据库
sudo -u postgres /usr/local/pgsql/bin/initdb \
  -D /var/lib/postgresql/data \
  --encoding=UTF8 \
  --locale=en_US.UTF-8

# 8. 启动
sudo -u postgres /usr/local/pgsql/bin/pg_ctl \
  -D /var/lib/postgresql/data \
  -l logfile start

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=yourpassword \
  -p 5432:5432 \
  postgres:17

docker run -d \
  --name postgres \
  --restart=always \
  -e POSTGRES_USER=myapp \
  -e POSTGRES_PASSWORD=strongpassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -v ./init.sql:/docker-entrypoint-initdb.d/init.sql \
  --shm-size=256m \
  postgres:17 \
    -c shared_buffers=512MB \
    -c effective_cache_size=1536MB \
    -c max_connections=200 \
    -c work_mem=16MB \
    -c maintenance_work_mem=128MB \
    -c wal_buffers=16MB \
    -c random_page_cost=1.1

version: "3.8"

services:
  postgres:
    image: postgres:17
    container_name: pg-main
    restart: always
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: securepassword
      POSTGRES_DB: appdb
      PGDATA: /var/lib/postgresql/data/pgdata
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./config/pg_hba.conf:/etc/postgresql/pg_hba.conf
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    shm_size: 256m
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U appuser -d appdb"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  pgdata:
    driver: local

-- 查看当前配置
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;

-- 或者查看所有配置
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'effective_cache_size', 'work_mem',
    'maintenance_work_mem', 'max_connections', 'wal_buffers',
    'random_page_cost', 'log_min_duration_statement'
);

# 内存相关
shared_buffers = 1GB              # 总内存的 25%
effective_cache_size = 3GB        # 总内存的 75%
work_mem = 8MB                    # 每个排序/哈希操作的内存
maintenance_work_mem = 256MB      # VACUUM/CREATE INDEX 的内存
wal_buffers = 64MB                # WAL 缓冲区

# 连接相关
max_connections = 200             # 最大连接数
superuser_reserved_connections = 3 # 为超级用户保留的连接

# WAL 相关
wal_level = replica               # 支持复制和 PITR
max_wal_size = 2GB
min_wal_size = 512MB

# 日志
log_min_duration_statement = 1000 # 记录超过 1 秒的慢查询
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

# 随机读代价（SSD 环境）
random_page_cost = 1.1            # SSD 使用 1.1，HDD 使用 4.0

# 方式 1：在 psql 中重载（大部分参数支持）
SELECT pg_reload_conf();

# 方式 2：系统命令重启
sudo systemctl restart postgresql

# 方式 3：使用 ALTER SYSTEM（推荐，不需要编辑文件）
ALTER SYSTEM SET shared_buffers = '1GB';
ALTER SYSTEM SET work_mem = '8MB';
-- 修改后需要重启才能生效的参数
SELECT pg_reload_conf();

# 服务管理（systemd）
sudo systemctl start postgresql     # 启动
sudo systemctl stop postgresql      # 停止
sudo systemctl restart postgresql   # 重启
sudo systemctl reload postgresql    # 重载配置
sudo systemctl status postgresql    # 查看状态

# 使用 pg_ctl 管理（适合非 systemd 环境）
pg_ctl -D /var/lib/postgresql/data start
pg_ctl -D /var/lib/postgresql/data stop
pg_ctl -D /var/lib/postgresql/data reload

# 查看数据目录
sudo -u postgres psql -c "SHOW data_directory;"
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"