# 访问PostgreSQL官网或使用命令查看
curl -s https://www.postgresql.org/ftp/source/ | grep -E 'href="v[0-9]+\.[0-9]+/"' | tail -5

# 当前长期支持版本（LTS）
# PostgreSQL 16.x (最新稳定版)
# PostgreSQL 15.x (长期支持)
# PostgreSQL 14.x (长期支持到2026年)
# PostgreSQL 13.x (长期支持到2025年)

# Ubuntu/Debian
sudo apt update
sudo apt install -y \
    build-essential \
    libreadline-dev \
    zlib1g-dev \
    flex bison \
    libxml2-dev \
    libxslt-dev \
    libssl-dev \
    libssl-dev \
    libpam0g-dev \
    libldap-dev \
    libperl-dev \
    libicu-dev \
    tcl-dev \
    python3-dev \
    git \
    wget \
    curl

# CentOS/RHEL/Rocky/AlmaLinux
sudo yum groupinstall -y "Development Tools"
sudo yum install -y \
    readline-devel \
    zlib-devel \
    flex bison \
    libxml2-devel \
    libxslt-devel \
    openssl-devel \
    pam-devel \
    openldap-devel \
    perl-devel \
    perl-ExtUtils-Embed \
    tcl-devel \
    python3-devel \
    git \
    wget

vi /etc/sysctl.conf
#最大共享内存段大小
kernel.shmmax = 68719476736(默认) 
 #可以使用的共享内存的总量
kernel.shmall = 4294967296(默认)
#整个系统共享内存段的最大数目
kernel.shmmni = 4096 
#每个信号对象集的最大信号对象数
kernel.sem = 50100 64128000 50100 1280 
#文件句柄的最大数量。
fs.file-max = 7672460 
#应用程序可使用的IPv4端口范围
net.ipv4.ip_local_port_range = 9000 65000 
#套接字接收缓冲区大小的缺省值
net.core.rmem_default = 1048576 
#套接字发送缓冲区大小的缺省
net.core.wmem_default = 262144 值
#套接字发送缓冲区大小的最大值
net.core.wmem_max = 1048576 

# sysctl -p #配置生效

# 创建postgres系统用户和组
sudo groupadd -r postgres
sudo useradd -r -g postgres -s /bin/bash -d /usr/local/pgsql -m -k /dev/null postgres

# 创建安装目录和数据目录
sudo mkdir -p /usr/local/pgsql/{data,logs,backup}
sudo chown -R postgres:postgres /usr/local/pgsql
sudo chmod 750 /usr/local/pgsql

# 创建源码目录
sudo mkdir -p /opt/postgresql_src
sudo chown $(whoami):$(whoami) /opt/postgresql_src
cd /opt/postgresql_src

# 下载PostgreSQL 16.2（截至2024年3月的最新稳定版）
wget https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz

# 或者使用国内镜像（清华源）
# wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v16.2/postgresql-16.2.tar.gz

# 验证下载完整性（可选）
wget https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz.sha256
sha256sum -c postgresql-16.2.tar.gz.sha256

# 解压源码
tar -zxvf postgresql-16.2.tar.gz
cd postgresql-16.2

# 查看所有配置选项
./configure --help

# 创建编译目录（推荐）
mkdir build && cd build

# 基本配置（适用于大多数情况）
../configure \
    --prefix=/usr/local/pgsql \
    --with-pgport=5432 \
    --with-perl \
    --with-python \
    --with-tcl \
    --with-openssl \
    --with-pam \
    -with-uuid=ossp \
    --with-ldap \
    --with-libxml \
    --with-libxslt \
    --with-icu \
    --enable-thread-safety \
    --enable-debug \
    --enable-nls \
    --with-system-tzdata=/usr/share/zoneinfo
    

   

# 或者精简配置（最小化安装）
# ../configure --prefix=/usr/local/pgsql --with-openssl

# 查看CPU核心数，决定并行编译数
nproc

# 编译（使用4个并行任务，根据CPU核心数调整）
make -j4

# 可选：运行回归测试（需要较长时间）
# make check

# 安装到系统
sudo make install

# 安装contrib模块（扩展工具）
cd contrib
make -j4
sudo make install
cd ..

# 为postgres用户配置环境变量
sudo tee -a /usr/local/pgsql/.bash_profile << 'EOF'
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgsql/data
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export MANPATH=$PGHOME/share/man:$MANPATH
EOF

# 为当前用户配置环境变量
echo 'export PATH=/usr/local/pgsql/bin:$PATH' >> ~/.bashrc
source ~/.bashrc

# 验证安装
/usr/local/pgsql/bin/postgres --version

# 切换到postgres用户
sudo su - postgres

# 初始化数据库集群
initdb -D /usr/local/pgsql/data -E UTF8 --locale=C -U postgres

# 或者使用详细参数
initdb \
  -D /usr/local/pgsql/data \
  -E UTF8 \
  --locale=en_US.UTF-8 \
  --lc-collate=C \
  --lc-ctype=en_US.UTF-8 \
  --username=postgres \
  --pwprompt

# 设置密码（记下这个密码）
# 输入并确认postgres用户的密码

# 备份原始配置
cp /usr/local/pgsql/data/postgresql.conf /usr/local/pgsql/data/postgresql.conf.original

# 编辑主配置文件
nano /usr/local/pgsql/data/postgresql.conf

# 修改以下参数（根据服务器配置调整）
listen_addresses = '*'          # 允许远程连接
port = 5432                     # 监听端口
max_connections = 100           # 最大连接数
shared_buffers = 128MB          # 共享缓冲区大小（建议为内存的25%）
work_mem = 4MB                  # 每个查询的工作内存
maintenance_work_mem = 64MB     # 维护操作的内存
dynamic_shared_memory_type = posix  # 动态共享内存类型
wal_level = replica             # WAL级别
synchronous_commit = on         # 同步提交
wal_buffers = -1                # WAL缓冲区（-1表示自动）
checkpoint_timeout = 5min       # 检查点超时时间
max_wal_size = 1GB              # 最大WAL大小
min_wal_size = 80MB             # 最小WAL大小
archive_mode = off              # 归档模式
archive_command = '/bin/date'   # 归档命令（关闭时随意设置）
logging_collector = on          # 启用日志收集
log_directory = 'pg_log'        # 日志目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # 日志文件名格式
log_rotation_age = 1d           # 日志轮转时间
log_rotation_size = 10MB        # 日志轮转大小
log_truncate_on_rotation = on   # 轮转时截断
log_line_prefix = '%m [%p] %q%u@%d '  # 日志前缀
log_timezone = 'Asia/Shanghai'  # 日志时区
timezone = 'Asia/Shanghai'      # 时区
datestyle = 'iso, ymd'          # 日期格式
lc_messages = 'en_US.UTF-8'     # 消息语言
lc_monetary = 'en_US.UTF-8'     # 货币格式
lc_numeric = 'en_US.UTF-8'      # 数字格式
lc_time = 'en_US.UTF-8'         # 时间格式
default_text_search_config = 'pg_catalog.english'  # 全文搜索配置

# 编辑pg_hba.conf
nano /usr/local/pgsql/data/pg_hba.conf

# 退出postgres用户
exit

# 创建systemd服务文件
sudo tee /etc/systemd/system/postgresql.service << 'EOF'
[Unit]
Description=PostgreSQL Database Server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGDATA=/usr/local/pgsql/data
OOMScoreAdjust=-1000
ExecStart=/usr/local/pgsql/bin/pg_ctl -D ${PGDATA} start
ExecStop=/usr/local/pgsql/bin/pg_ctl -D ${PGDATA} stop
ExecReload=/usr/local/pgsql/bin/pg_ctl -D ${PGDATA} reload
TimeoutSec=300

[Install]
WantedBy=multi-user.target
EOF

# 或者使用simple类型（推荐）
sudo tee /etc/systemd/system/postgresql.service << 'EOF'
[Unit]
Description=PostgreSQL Database Server
After=network.target

[Service]
Type=simple
User=postgres
Group=postgres
Environment=PGDATA=/usr/local/pgsql/data
Environment=PGPORT=5432
OOMScoreAdjust=-1000
ExecStart=/usr/local/pgsql/bin/postgres -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=300

[Install]
WantedBy=multi-user.target
EOF

# 重新加载systemd配置
sudo systemctl daemon-reload

# 启动PostgreSQL服务
sudo systemctl start postgresql

# 设置开机自启
sudo systemctl enable postgresql

# 查看服务状态
sudo systemctl status postgresql

# 查看日志
sudo journalctl -u postgresql -f

# 连接到数据库
psql -h localhost -U postgres -d postgres

# 在psql中执行
SELECT version();
SELECT current_user;
SHOW data_directory;
SHOW config_file;
\q  # 退出

# 切换到postgres用户
sudo su - postgres

# 创建测试数据库
createdb test_db

# 创建应用用户
createuser -P -d -e app_user
# 输入密码：AppPass123

# 连接到数据库并授权
psql -d test_db <<EOF
-- 创建schema
CREATE SCHEMA IF NOT EXISTS app_schema;

-- 创建测试表
CREATE TABLE app_schema.users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO app_schema.users (username, email) VALUES
('admin', 'admin@example.com'),
('user1', 'user1@example.com');

-- 授权给应用用户
GRANT ALL PRIVILEGES ON DATABASE test_db TO app_user;
GRANT ALL PRIVILEGES ON SCHEMA app_schema TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app_schema TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app_schema TO app_user;

-- 查看表
SELECT * FROM app_schema.users;
EOF

# 安装pg_stat_statements（性能监控）
psql -U postgres -d test_db <<EOF
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS btree_gist;
SELECT * FROM pg_available_extensions ORDER BY name;
EOF

# 根据服务器内存调整配置
sudo nano /usr/local/pgsql/data/postgresql.conf

# 内存相关优化（8GB内存服务器示例）
shared_buffers = 2GB           # 内存的25%
work_mem = 16MB                # 每个查询的工作内存
maintenance_work_mem = 512MB   # 维护操作内存
effective_cache_size = 6GB     # 可用于缓存的磁盘空间估计值

# 性能优化
max_connections = 200          # 根据应用需求调整
checkpoint_completion_target = 0.9
random_page_cost = 1.1         # SSD设为1.1，HDD设为4.0
effective_io_concurrency = 200 # SSD可以设高，HDD设低

# WAL优化
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB

# 创建归档目录
sudo mkdir -p /usr/local/pgsql/archive
sudo chown postgres:postgres /usr/local/pgsql/archive

# 配置归档
sudo nano /usr/local/pgsql/data/postgresql.conf

# 启用归档
archive_mode = on
archive_command = 'test ! -f /usr/local/pgsql/archive/%f && cp %p /usr/local/pgsql/archive/%f'
archive_timeout = 3600  # 每小时强制归档

# 创建数据库健康检查脚本
sudo tee /usr/local/bin/check_postgres.sh << 'EOF'
#!/bin/bash
# PostgreSQL健康检查脚本

PGHOME=/usr/local/pgsql
PGDATA=/usr/local/pgsql/data
PGPORT=5432

echo "=== PostgreSQL健康检查 $(date) ==="

# 检查服务状态
systemctl is-active postgresql > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo "✓ 服务状态: 运行中"
else
    echo "✗ 服务状态: 停止"
    exit 1
fi

# 检查连接
$PGHOME/bin/pg_isready -p $PGPORT -h localhost -U postgres
if [ $? -eq 0 ]; then
    echo "✓ 数据库连接: 正常"
else
    echo "✗ 数据库连接: 失败"
fi

# 检查磁盘空间
df -h $PGDATA | tail -1

# 检查数据库大小
echo "数据库大小:"
$PGHOME/bin/psql -h localhost -U postgres -d postgres -c "
SELECT 
    datname as \"数据库\",
    pg_size_pretty(pg_database_size(datname)) as \"大小\"
FROM pg_database 
ORDER BY pg_database_size(datname) DESC;
"

echo "=== 检查完成 ==="
EOF

sudo chmod +x /usr/local/bin/check_postgres.sh
sudo chown postgres:postgres /usr/local/bin/check_postgres.sh

# 创建备份脚本
sudo tee /usr/local/bin/backup_postgres.sh << 'EOF'
#!/bin/bash
# PostgreSQL备份脚本

BACKUP_DIR="/usr/local/pgsql/backup"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE

echo "开始备份: $(date)"

# 备份所有数据库
/usr/local/pgsql/bin/pg_dumpall -h localhost -U postgres \
    | gzip > $BACKUP_DIR/$DATE/full_backup_$DATE.sql.gz

# 备份单个重要数据库（可选）
/usr/local/pgsql/bin/pg_dump -h localhost -U postgres test_db \
    -F c -f $BACKUP_DIR/$DATE/test_db_$DATE.dump

echo "备份完成: $(date)"
echo "备份文件:"
ls -lh $BACKUP_DIR/$DATE/

# 清理旧备份
find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

echo "已清理超过${RETENTION_DAYS}天的备份"
EOF

sudo chmod +x /usr/local/bin/backup_postgres.sh
sudo chown postgres:postgres /usr/local/bin/backup_postgres.sh

cd /opt/postgresql_src
wget https://ftp.postgresql.org/pub/source/v15.7/postgresql-15.7.tar.gz
tar -zxvf postgresql-15.7.tar.gz
cd postgresql-15.7
mkdir build && cd build

# 配置（与16.2类似）
../configure \
    --prefix=/usr/local/pgsql15 \
    --with-pgport=5433 \
    --with-openssl \
    --with-perl \
    --with-python

make -j$(nproc)
sudo make install

cd /opt/postgresql_src
wget https://ftp.postgresql.org/pub/source/v14.12/postgresql-14.12.tar.gz
tar -zxvf postgresql-14.12.tar.gz
cd postgresql-14.12
mkdir build && cd build

../configure --prefix=/usr/local/pgsql14 --with-openssl
make -j$(nproc)
sudo make install

# 1. 编译错误：缺少依赖
# 重新安装所有依赖后清理重试
make distclean

# 2. 启动失败：端口被占用
netstat -tlnp | grep 5432
sudo lsof -i :5432

# 3. 连接失败：认证问题
# 检查pg_hba.conf配置

# 4. 内存不足：调整编译并行度
make -j2  # 使用更少的并行任务

# 5. 查看详细错误日志
tail -f /usr/local/pgsql/data/log/postgresql-*.log

# - Where to Log -

log_destination = 'stderr'              # Valid values are combinations of
                                        # stderr, csvlog, jsonlog, syslog, and
                                        # eventlog, depending on platform.
                                        # csvlog and jsonlog require
                                        # logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr, jsonlog,
                                        # and csvlog into log files. Required
                                        # to be on for csvlogs and jsonlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'log'                  # directory where log files are written,
                                        # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
#log_truncate_on_rotation = off         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.