Windows下绿色安装PostgreSQL笔记
介绍
PostgreSQL, Postgres, 以下简称为PG，是一款关系型数据库，本地安装支持两种方式，一键安装和绿色解压安装两种方式
下载、解压
- 安装版：
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
- 绿色解压版：
本次使用绿色解压版、windows环境，版本选择次新版本16，最新的小版本号16.4.1
https://get.enterprisedb.com/postgresql/postgresql-16.4-1-windows-x64-binaries.zip
解压到本地目录，如：E:\developer\postgresql-16.4-1\pgsql\
配置
可选，配置环境变量是方便简写，如不配置，后续命令里的该目录需要替换成绝对路径
- 用户环境变量配置：PG_HOME，值为：
E:\developer\postgresql-16.4-1\pgsql - 配置用户PATH，追加：
%PG_HOME%\bin - 打开新CMD窗口检查配置是否已配好：
echo %PG_HOME%,echo %PATH% - 检查版本号：
postgres --version，输出与下载解压的版本号一致，则表示安装成功。如果不一致，则表示此机上可能存在其他版本的PG.
初始化
- 定位到PG目录：
cd %PG_HOME% - 初始化：
initdb.exe -D "%PG_HOME%\data" -E UTF-8 -U postgres -W- 命令窗口提示输入密码并牢记，例如：123456abc
- 等待初始化，大约10秒钟左右后，可见到执行结果。
完成示例执行结果：
D:\Users\aha>initdb.exe -D "%PG_HOME%\data" -E UTF-8 -U postgres -W
属于此数据库系统的文件宿主为用户 "aha".
此用户也必须为服务器进程的宿主.
数据库簇将使用本地化语言 "Chinese (Simplified)_China.936"进行初始化.
initdb: 无法为本地化语言环境"Chinese (Simplified)_China.936"找到合适的文本搜索配置
缺省的文本搜索配置将会被设置到"simple"
禁止为数据页生成校验和.
输入新的超级用户口令:
再输入一遍:
创建目录 E:/developer/postgresql-16.4-1/pgsql/data ... 成功
正在创建子目录 ... 成功
选择动态共享内存实现 ......windows
选择默认最大联接数 (max_connections) ... 100
选择默认共享缓冲区大小 (shared_buffers) ... 128MB
选择默认时区 ... Asia/Shanghai
创建配置文件 ... 成功
正在运行自举脚本 ...成功
正在执行自举后初始化 ...成功
同步数据到磁盘...成功
initdb: 警告: 为本地连接启用"trust"身份验证
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
成功。您现在可以用下面的命令开启数据库服务器：
pg_ctl -D ^"E^:^\developer^\postgresql^-16^.4^-1^\pgsql^\data^" -l 日志文件 start
启动数据库服务
通过命令启动
pg_ctl -D "%PG_HOME%\data" -l log_file start
输出：
等待服务器进程启动 .... 完成
服务器进程已经启动
表示启动成功，注：默认端口为5432
检查端口状态：
执行命令：netstat -ano|findstr 5432
如出现：
TCP 127.0.0.1:5432 0.0.0.0:0 LISTENING 1892
则表示端口状态正常
停服务
有几种停止PG数据库服务器的命令
- 安全关闭模式：最安全的关闭方式，它向postgres主服务进程发送一个SIGTERM信号。服务器将不允许新的客户端连接，同时等待已有会话正常完成工作。当所有会话都主动终止连接之后，关闭服务。
pg_ctl -D "%PG_HOME%\data" stop -m smart
- 快速关闭模式：服务进程发送SIGTERM信号，回滚进行中的事务并且强制断开所有客户端的连接，然后关闭数据库
pg_ctl -D "%PG_HOME%\data" stop -m fast
- 立即关闭模式：向所有的子进程发送SIGQUIT信号，如果5秒内子进程没有终止，继续发送立即终止的SIGKILL信号。当所有子进程退出后，主服务进程立即终止，不会执行常规的数据库关闭流程。
pg_ctl -D "%PG_HOME%\data" stop -m immediate
关闭成功的控制台输出示例：
等待服务器进程关闭 .... 完成
服务器进程已经关闭
开启远程客户端访问权限
分两步
监听任意端口
找到：%PG_HOME%\data\postgresql.conf，打开，找到配置项：listen_addresses，默认是只监听localhost，放开注释，并改为listen_addresses = '*'，表示此主机的所有IP(如有)都将开启，保存。
添加客户端白名单
pg默认情况下未开启远程客户端连接权限，如需要开启，找到%PG_HOME%\data\pg_hba.conf，在末尾追加
host all all 0.0.0.0/0 trust
保存。
重启pg服务生效。
保存启停脚本
start.bat
call pg_ctl -D "%PG_HOME%\data" -l log_file start
pause
stop.bat
call pg_ctl -D "%PG_HOME%\data" stop -m fast
pause
应用程序JDBC连接PostgreSQL
参数大全：
Maven:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.2</version>
</dependency>
注：spring boot工程里已经对pg的驱动版本号做了自动版本适配，可以不用写版本号。
spring.datasource:
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://localhost:5432/postgres?currentSchema=your_database_schema&reWriteBatchedInserts=true
username: postgres
password: your_password
PostgreSQL调优
- 查询所有PG系统参数：
select * from pg_settings; work_mem: 客户端查询时的缓存大小，默认值：4MB，修改：%PG_HOME\data\postgresql.conf，找到work_mem，该值需要遵循：最大连接数 * work_mem < 操作系统 / 2，保存并重启。检查是否生效：select * from pg_settings where ;maintenance_work_mem：此值是pg服务器用于维护的缓存大小，默认值：64MB，最大建议值：操作系统总内存 / 10logical_decoding_work_mem：用于逻辑解码的参数，它指定了在将解码的更改写入本地磁盘之前，逻辑解码可以占用的最大内存量。effective_cache_size：effective_cache_size是优化器假设查询可以使用的最大内存(包括PostgreSQL和系统缓存)，单位：8kB，默认值：524288wal_buffers：为 WAL 设置共享内存中磁盘页缓冲区的个数，单位：8kB，默认值：512shared_buffers：设置服务器使用的共享内存缓冲区的数量. 单位：8kB，默认值：16384
主要配置参数查询：
select
*
from
pg_settings
where
name in (
'checkpoint_completion_target',
'default_statistics_target',
'effective_cache_size',
'effective_io_concurrency',
'huge_pages',
'maintenance_work_mem',
'max_connections',
'max_parallel_maintenance_workers',
'max_parallel_workers',
'max_parallel_workers_per_gather',
'max_wal_size',
'max_worker_processes',
'min_wal_size',
'random_page_cost',
'shared_buffers',
'wal_buffers',
'work_mem'
)
order by
name;
固态硬盘调优
effective_io_concurrency、random_page_cost默认值是普通机械硬件的配置值，如果服务器是固态硬件，可优化为：
random_page_cost=1.1
effective_io_concurrency=200
在线快速生成调优建议参数值
网址：
https://pgtune.leopard.in.ua/
输入系统、核心数等可快速生成调优参数
PG整体数据、配置迁移
直接把postgresql-16.4-1剪切到其他位置，重新配置环境变量PG_HOME的值，即完成了数据库的迁移。
慢查询、死进程
SELECT pid,relname,* FROM pg_locks l JOIN pg_class c ON l.relation = c.oid WHERE c.relname = 'repayment_plan';
SELECT pg_terminate_backend(53924); -- 上面查到的pid，放入此函数。
