Rocky10 源码安装 Postgresql 18.1

## 安装依赖
dnf -y update
dnf groupinstall -y "Development Tools"
dnf install -y flex bison perl perl-core perl-libs python3 python3-devel readline-devel zlib-devel openssl-devel libicu-devel libxml2-devel libxslt-devel libuuid-devel openldap-devel

## 设置用户
useradd -r -m -d /var/lib/pgsql -s /bin/bash postgres
-r 不用于普通登录,常用于运行后台服务
-m 创建home目录
-d 指定Home目录路径
-s 指定用户登录shell （-s /sbin/nologin）

## 编译安装
cd /usr/local/src
wget https://ftp.postgresql.org/pub/source/v18.1/postgresql-18.1.tar.gz
tar xf postgresql-18.1.tar.gz && cd postgresql-18.1
./configure --prefix=/opt/pgsql --with-openssl --with-icu --enable-nls
make -j$(nproc)
make install

cat >> /etc/profile.d/pgsql.sh << 'EOF'
export PGHOME=/opt/pgsql
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
EOF

source /etc/profile.d/pgsql.sh

## 初始化数据库
mkdir -p /data/pgsql/18/data
chown -R postgres:postgres /data/pgsql

su - postgres
/opt/pgsql/bin/initdb -D /data/pgsql/18/data --encoding=UTF8 --locale=en_US.UTF-8

## 设置postgres密码
su - postgres
psql
postgres=# ALTER USER postgres WITH encrypted PASSWORD 'Mypostgresql898';
postgres=# \l
postgres=# \q
exit

## 验证 PostgreSQL
su - postgres
/opt/pgsql/bin/pg_ctl -D /data/pgsql/18/data -l /data/pgsql/18/logfile start
psql -p 5432 -c "select version();"
/opt/pgsql/bin/pg_ctl -D /data/pgsql/18/data -l /data/pgsql/18/logfile stop

## 基础安全 & 参数（最小）
vim /data/pgsql/18/data/postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 200
----------------------------------
shared_buffers = 2GB
work_mem = 16MB
maintenance_work_mem = 512MB
wal_compression = on
----------------------------------

vim /data/pgsql/18/data/pg_hba.conf
#host all all 0.0.0.0/0 scram-sha-256
host all all 0.0.0.0/0 md5

## 启动服务
vim /usr/lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL 18.1 Database Server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres

Environment=PGDATA=/data/pgsql/18/data

ExecStart=/opt/pgsql/bin/pg_ctl start -D ${PGDATA} -s -w -t 300
ExecStop=/opt/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/opt/pgsql/bin/pg_ctl reload -D ${PGDATA} -s

TimeoutSec=300
Restart=on-failure

[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl enable postgresql
systemctl start postgresql

## 最终自检
pg_config --configure
pg_config --version

## 设置防火墙规则
firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --reload && iptables -L --line-numbers|grep ACCEPT

## 测试连接
/opt/pgsql/bin/psql -h 43.246.209.38 -p 5432 -U postgres

## 备份、还原

单库全量备份
pg_dump -U postgres -Fc -Z 9 test_db >/data/bak/test_db_$(date +%F).dump

单库全量还原
dropdb test_db
createdb test_db
pg_restore -U postgres -j 4 -d test_db test_db_full.dump

 

备份脚本
vim backup_pg_db

#!/bin/bash
set -e

DB_NAME="test_db"
BACKUP_DIR="/data/bak"
RETENTION=7

mkdir -p "$BACKUP_DIR"
FILE="${BACKUP_DIR}/${DB_NAME}_$(date +%F).dump"
pg_dump -Fc -Z 9 -f "$FILE" "$DB_NAME"
find "$BACKUP_DIR" -type f -mtime +$RETENTION -delete

 

## 常用命令

创建数据库：
create database [数据库名];

删除数据库：
drop database [数据库名];

*重命名一个表：
alter table [表名A] rename to [表名B];

*删除一个表：
drop table [表名];

*在已有的表里添加字段：
alter table [表名] add column [字段名] [类型];

删除表中的字段：
alter table [表名] drop column [字段名];

修改数据库列属性
alter table 表名 alter 列名 type 类型名(350)

重命名一个字段：
alter table [表名] rename column [字段名A] to [字段名B];

*给一个字段设置缺省值：
alter table [表名] alter column [字段名] set default [新的默认值];

*去除缺省值：
alter table [表名] alter column [字段名] drop default;

在表中插入数据：
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);

修改表中的某行某列的数据：
update [表名] set [目标字段名]=[目标值] where [该行特征];

删除表中某行数据：
delete from [表名] where [该行特征];
delete from [表名];--删空整个表

创建表：
create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);

 

## 授权指定用户访问指定数据库
CREATE ROLE testuser LOGIN PASSWORD 'oege@@1234';
CREATE DATABASE test_db OWNER testuser;
------------------------------------------------------------------
创建用户（角色）
CREATE ROLE testuser LOGIN PASSWORD 'oege@@1234';

创建数据库并指定 owner
CREATE DATABASE test_db OWNER testuser;

授权 test_db 数据库级权限
GRANT CONNECT, TEMP ON DATABASE test_db TO testuser;

授权 schema 权限（非常关键）
GRANT USAGE ON SCHEMA public TO testuser;
GRANT CREATE ON SCHEMA public TO testuser;

授权当前已有表 / 序列 / 函数
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO testuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO testuser;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO testuser;

授权未来新建对象（非常重要）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO testuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON SEQUENCES TO testuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON FUNCTIONS TO testuser;
------------------------------------------------------------------