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

## 验证 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

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

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

listen_addresses = '*'
port = 5432
work_mem = 16MB
maintenance_work_mem = 512MB
wal_compression = on

shared_buffers = 2GB　　# 修改
max_connections = 200　 # 修改

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.X 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 143.46.20.31 -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="mytest_db"
BACKUP_DIR="/data/bak/PG_${DB_NAME}"
RETENTION=7
NOW_TIME="`date +%Y%m%d_%H%M%S`"

mkdir -p "$BACKUP_DIR"
FILE="${BACKUP_DIR}/${DB_NAME}.dump_${NOW_TIME}"

pg_dump -p 11832 -U postgres -Fc -Z 9 "$DB_NAME" > "$FILE"
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,...)>;);

 

1.序列
自增序列：
create SEQUENCE test_id_seq start 1;
test_id_seq :序列名，自己随意取

使用自增序列，设计表的时候使用
nextval('test_id_seq '::regclass)

序列值初始化：
alter sequence test_id_seq restart with 1

2.case when的使用
SELECT (CASE WHEN type='t' THEN 1 ELSE 0 END) AS manual,(CASE WHEN other='f' THEN 1 ELSE 0 END) AS automatic FROM testWHERE shift=(SELECT shift FROM test ORDER BY ID DESC LIMIT 1)

表示当type 字段的数据库值是‘t’时，查出结果为1，否则为0
表示当other字段的数据库值是‘f’时，查出结果为1，否则为0

3.offset的使用
在某些情况下，可能需要从一个特定的偏移开始提取记录：
例：从第三位开始提取 3 个记录
SELECT * FROM test LIMIT 3 OFFSET 2

##  在 PostgreSQL 中实现“一个账号只能操作特定数据库”的权限隔离

核心思路是：先创建账号并赋予登录权限，然后通过剥夺公共权限来切断其对其他数据库的默认访问，最后只授予目标数据库的权限。

详细的设置步骤

第一步：登录 PostgreSQL
以超级管理员（通常是 postgres）身份登录数据库，后续所有操作皆在此环境中执行。
sudo -u postgres psql

第二步：创建新数据库
为你的应用或项目创建一个独立的数据库。
CREATE DATABASE myapp_db;

第三步：创建专用登录账号
创建一个新角色（用户），并设置密码。关键在于不要赋予它创建数据库或创建新角色的权限。

-- 创建用户，允许登录，设置密码，且没有任何全局创建权限
CREATE USER myapp_user WITH PASSWORD 'strong_password' NOCREATEDB NOCREATEROLE;

验证方式说明：
如果你希望账号在登录时强制使用密码验证，需要确保 pg_hba.conf 配置文件中,
对应连接的认证方式为 md5 或 scram-sha-256，而不是 trust。

第四步：撤销全局默认权限（关键步骤）
这是实现权限隔离的核心。PostgreSQL 默认允许所有用户连接 postgres、template1 等数据库（除非显式撤销）。
为了确保 myapp_user 无法访问其他数据库，我们需从数据库级别撤销其连接权限，并从公共模式撤销其创建权限。

-- 1. 撤销用户对默认数据库 postgres 的连接权限
REVOKE CONNECT ON DATABASE postgres FROM myapp_user;

-- 2. 撤销用户对数据库集群中公共模式 (public schema) 的创建权限（这会影响非目标数据库的公共区域，加强隔离）
REVOKE CREATE ON SCHEMA public FROM myapp_user;

-- 3. (可选但推荐) 如果担心用户窥探其他数据库的系统表，可以撤销其全局查询权限
-- REVOKE ALL ON DATABASE template1 FROM myapp_user;

第五步：授予目标数据库的完全操作权限
现在，专门为新用户配置其所需的唯一数据库。

-- 1. 授予连接目标数据库的权限
GRANT CONNECT ON DATABASE myapp_db TO myapp_user;

-- 2. 切换到目标数据库（因为权限操作通常需要在对应的数据库内执行）
\c myapp_db

-- 3. 授予用户在公共模式（public schema）下的所有权限（创建表、增删改查等）
GRANT ALL ON SCHEMA public TO myapp_user;

-- 4. 授予用户对该数据库中未来创建的所有表的全部权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myapp_user;

-- 5. 授予用户对该数据库中未来创建的所有序列的全部权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO myapp_user;

权限层级说明：
PostgreSQL 的权限控制涉及数据库、模式、表等多个层级。仅仅授予数据库的 CONNECT 权限还不够，
用户还需要模式（如 public）的 USAGE 和 CREATE 权限才能真正操作数据。

ALTER DEFAULT PRIVILEGES 的作用：这条命令非常重要。
它确保了未来在此数据库中创建的新表或视图，也会自动被授权给 myapp_user，无需人工干预。

第六步：验证配置
为了确保设置符合预期，建议进行登录测试。

# 尝试用新用户连接目标数据库（应该成功）
psql -U myapp_user -d myapp_db -h localhost

# 尝试连接默认的 postgres 数据库（应该失败）
psql -U myapp_user -d postgres -h localhost

补充说明
配置环节 核心命令/操作 目的与说明
数据库创建 CREATE DATABASE myapp_db; 创建应用专属的数据存储空间。
用户创建 CREATE USER myapp_user ... NOCREATEDB; 创建无全局权限的普通用户。NOCREATEDB 确保其无法创建新数据库。
权限隔离 REVOKE CONNECT ON DATABASE postgres FROM ...; 这是隔离的关键，强行阻止用户访问系统数据库或其他业务库。
功能授权 GRANT ALL ON SCHEMA public TO ...; 授予数据操作的核心权限（增删改查）。如果对权限要求严格，可将 ALL 替换为 SELECT, INSERT, UPDATE, DELETE 等。

关于超级用户：建议始终避免让 myapp_user 成为超级用户，遵循最小权限原则。

现有数据的处理：
如果你的数据库在授权前已经存在表，需要额外执行 GRANT ALL ON ALL TABLES IN SCHEMA public TO myapp_user; 来接管现有数据。

 

从数据库源头隐藏 (高级)
这是更彻底的方案，但操作也更复杂，可能有副作用。核心是阻止用户对 pg_database 表的查询。

步骤1：撤销公共查询权限
这是关键一步，能从源头阻止包括你的业务用户在内的所有普通用户查询 pg_database。
REVOKE SELECT ON pg_catalog.pg_database FROM PUBLIC;

步骤2：恢复超级用户的查询权限
上述操作会影响包括 postgres 超级用户在内的所有人。为了让管理员能正常工作，需要单独授权。
GRANT SELECT ON pg_catalog.pg_database TO postgres;

========================================

postgresql主从（主备）架构

192.168.182.4 node1 主库（读写）
192.168.182.5 node2 备库（只读）

配置主从
主库执行，以下操作无特殊情况在postgres用户下执行
修改postgresql.conf，修改如下配置项：
# 在文件中修改(此配置仅用于远程访问, 流复制后续还有额外配置):
listen_addresses = '*'
port = 15432
max_connections = 1500 # 最大连接数，据说从机需要大于或等于该值
wal_level = replica
max_wal_senders = 2 #最多有2个流复制连接
wal_keep_segments = 16
wal_sender_timeout = 60s #流复制超时时间

修改pg_hba.conf，添加图中红框中2行配置
host all all 0.0.0.0/0 md5
host replication all 0.0.0.0/0 mdt

赋予权限（root执行）
chown -R postgres:postgres /var/run/postgresql/

启动PG
pg_ctl start

创建流复制用户
su - postgres
psql -h localhost -p 15432
create role replica login replication encrypted password 'abc123';
SELECT rolname from pg_roles;

从库执行

赋权
chown -R postgres:postgres /var/run/postgresql/

从主库复制数据文件到本地
# 先清空原有数据文件（如非空）
rm -rf /home/postgres/pgdata/*
# 执行复制
pg_basebackup -h 192.168.182.4 -p 15432 -U replica -Fp -Xs -Pv -R -D /home/postgres/pgdata

# 输入主库中创建的replica用户密码后，开始同步

 

更改/home/postgres/pgdata目录权限为700
chmod -R 700 /home/postgres/pgdata/

启动PG
pg_ctl start

 

验证主从

 

主库操作
select * from pg_stat_replication;
# client_addr 192.168.10.5
# sync_state async

select pg_is_in_recovery();
pg_is_in_recovery f

创建数据库、表然后插入1条数据

备库执行
select pg_is_in_recovery();
t

查看数据是否正常同步
\list

测试插入数据

====================================================
postgresql开启慢查询

1.全局设置
修改配置配置文件 postgres.conf ，一般位置pgsql的data目录下，单位是毫秒。
如下设置的是10,000毫秒，相当于10秒钟，即：当运行时间超过10秒钟后会以日志的格式记录下来：
log_min_duration_statement=10000

然后加载配置：
postgres=# select pg_reload_conf();

查看配置：
postgres=# show log_min_duration_statement;

log_min_duration_statement
----------------------------
10s

2.个性化设置
即：也可以针对某个用户或者某数据库进行设置：
postgres=# alter database test set log_min_duration_statement=5000;