postgresql 下载安装
一、postgresql 下载
pg官网：postgres.org
一般推荐用源码安装，下载 .tar.gz 包
二、安装
本文以12.6版本安装为例：2.1、安装前要求和环境配置
# 1、要求GNU make版本3.80或以上（GNU make有时以名字gmake安装），要测试make版本可以使用以下命令（如果是安装其他版本的pg具体要求make的版本详情见官网对应版本的安装文档）：
[root@xl001 ~]# make --version
GNU Make 3.82
# 2、安装依赖包
yum install -y gcc gcc-c++ epel-release llvm5.0 llvm5.0-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel
正常情况下，OS已经帮我们把一些包已经装好了，只需要安装以下三个包就好
yum -y install readline-devel zlib-devel gcc
# 3、关闭防火墙
systemctl disable firewalld
systemctl stop firewalld
# 4、创建postgres用户
useradd postgres
passwd postgres # 设置密码
# 5、系统参数调优
# 5.1、修改系统内核参数
vi /etc/sysctl.conf
# 主要是修改三个参数
kernel.shmall=expr `free |grep Mem|awk '{print $2 *1024}'` / `getconf PAGE_SIZE`
kernel.shmmax=free |grep Mem|awk '{print $2 *1024}'
vm.nr_hugepages
参数解释：
kernel.shmall 表示内核可以为共享内存分配的最大页数（pages）,这里的“页”是指内核的页面大小，默认通常是 4KB。
kernel.shmmax 设置了单个共享内存段的最大最大字节数
vm.nr_hugepages 参数用于指定系统中可以分配的大页数量
# 其他参数
fs.file-max = 6815744
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 819200
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=3000
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288
sysctl -p #即时生效
# 修改最大进程数和文件句柄数
vim /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535
postgres soft nofile 65535
postgres hard nofile 65535
postgres soft nproc 65535
postgres hard nproc 65535
# 6、创建目录
mkdir -p /opt/pgsql-12.6
mkdir -p /opt/pgdata/data
mkdir -p /opt/pgdata/wal
mkdir -p /opt/pgdata/archive
mkdir -p /opt/pgdata/logs
chown -R postgres:postgres /opt/pgsql-12.6/ /opt/pgdata
# 7、配置postgres环境变量
su - postgres
vim .bash_profile
export PGHOME=/opt/pgsql-12.6
export PGDATA=/opt/pgdata/data
export PGHOST=/opt/pgdata/data # 设置这个即可直接psql命令连接数据库，不用加参数，这会使用socket连接
export PGUSER=postgres
export PGPORT=5432
export PATH=$HOME/bin:$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
source .bash_profile
# 8、修改主机名
hostnamectl set-hostname pg1
# 9、配置时间同步服务器
ntp或者chrony,这个自行研究，很简单
2.2、安装
# 1、上传安装包并解压
tar -zxvf postgresql-12.6.tar.gz
cd postgresql-12.6
./configure --prefix=/opt/pgsql-12.6 --with-pgport=5432 # 设置安装目录和端口
# 解释
./configure 这一步可以设置一些参数，具体可./configure -h 查看
# 2、安装
gmake world #这个命令是全部功能安装，也可以精细化部署，具体可以参考官网
gmake install-world
chown -R postgres:postgres /opt/pgsql-12.6
# 3、初始化数据库
su - postgres
/opt/pgsql-12.6/bin/initdb -D /opt/pgdata/data
# 4、编辑配置文件pg_hba.conf，此配置控制的是连接数据库相关，文件中配置的ip才可以连接数据库，没配置的都不能连接
vim /opt/pgdata/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust # 此行ADDRESS没有值，代表是socket连接
# IPv4 local connections:
host all all 127.0.0.1/32 trust #若这一行没有，则表示在本地不能用tcp/ip方式连接
host all all 0.0.0.0/0 trust
###如果出现两个相同的配置，则下面的不起作用，比如
host all all 192.168.225.0/24 reject
host all all 192.168.225.201/24 MD5 #例如这两个配置，比如有个连接从客户端192.168.225.201过来，读到上面的配置，
#因为192.168.225.201满足192.168.225.0/24，所以连接被拒绝，即使在读到下面一条可以连接，也不会生效了，最终就是连接被拒绝
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
# 5、修改配置文件postgresql.conf
vim /opt/pgdata/dataa/postgresql.con
listen_addresses = 'localhost' #把localhost改为*，默认是只能本地连接
# 6、启动数据库
pg_ctl -D /opt/pgdata/data start
# pg修改配置文件后可以动态加载，不用重启数据库
pg_ctl -D /opt/pgdata/data reload
# 7、关闭数据库
pg_ctl -D /opt/pgdata stop -m smart|fast|immediate #smart,会等到所有客户端连接都断开始之后才关闭，fast会kill掉所有连接回滚所有还未完成的事务然后关闭（默认方式），immediate相当于直接kill -9进程，不建议
pg_ctl -D /opt/pgdata/data stop -mf
# 8、设置密码
[postgres@xl001 data]$ psql -Upostgres -h127.0.0.1 -dpostgres -p5432 -W
psql (12.6)
Type "help" for help.
postgres=# \password
Enter new password:
Enter it again:
postgres=#
2.3、pg几个进程介绍
查看进程ps -ef | grep postgres| 序号 | 进程名 | 作用 |
|---|---|---|
| 1 | logger | 记录数据库日志进程 |
| 2 | checkpointer | 执行checkpoint的进程 |
| 3 | background writer | 刷盘进程 |
| 4 | walwriter | 刷wal日志进程 |
| 5 | autovacuum launcher | 后台回收进程 |
| 6 | archiver | 归档进程 |
| 7 | stats collector | 统计进程 |
| 8 | walsender | wal投递进程 |
| 9 | logical replication launcher | 逻辑库复制进程 |
| 10 | walreceiver | 备库wal接收进程 |
| 11 | startup | 备库wal应用进程 |
注意：第九个 logical replication launcher进程主库上会有，备库上如果开启了逻辑复制才会有该进程。
第十、十一两个进程备库才有
2.4、附一份常用的postgresql.conf配置文件
点击查看代码
listen_addresses = '*'
port = 6010
cluster_name ='rmse'
max_connections = 500
unix_socket_directories ='/opt/pgdata/data' #socket文件（.s.PGSQL.5432） 目录，默认在/tmp下，
若指定在别的目录下，用socket 登录时需要指定socket的目录，例如 pgsql -h /opt/pgdata/rmse/data -dpostgres,
若不想指定-h，则需要配置环境变量export PGHOST=/opt/pgdata/rmse/data
unix_socket_group = 'postgres'
unix_socket_permissions = 0700
shared_buffers = 4GB
dynamic_shared_memory_type = posix
temp_buffers = 2GB #用于存放数据库会话访问临时表数据，默认值8MB
work_mem = 32MB #内部排序聚合操作和hash表在使用临时磁盘文件时使用的内存缓冲区，需要关注的是：每个排序操作都会消耗一个work_mem内存，并不是一个SQL消耗一个，因此，当您的系统中有大量的排序时，此值可适当调小，防止内存用尽
maintenance_work_mem = 4GB #维护操作（如VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY等）使用的内存缓冲区
wal_level = logical
checkpoint_timeout = 5min #多久执行一次checkpoint 刷脏，默认300秒
max_wal_size = 10GB #指定每两个checkpoint检查点之间产生的WAL最大量，当堆积的WAL文件总大小超过max_wal_size的话会立马触发一个checkpoint
min_wal_size = 1GB #只要所有WAL文件总大小保持低于此设置，旧的WAL文件将始终在checkpoint时被回收以备将来使用，而不是被删除。一个wal 文件大小默认是16M
archive_mode = always #off：关闭归档，on：开启归档，但不允许在recovery模式下进行归档，always：开启归档，且允许在recovery模式下进行归档
archive_command = 'cp %p /opt/pgdata/rmse/archive/%f'
max_wal_senders = 10
wal_keep_segments = 512 #主备条件下，为备库同步保留的日志文件段。那么大约就有512*16=8GB的数
wal_buffers = -1 #设置成-1就是随着shared_buffers的改变而改变，大约是1/32*shared_buffers，修改此参数需要重启
wal_log_hints = on
effective_io_concurrency = 200
max_worker_processes = 4
max_parallel_workers = 4
max_replication_slots = 8
hot_standby = on
synchronous_commit = on # off, local, remote_write, remote_apply, or on
解释：
on (默认值)：主从的wal都写到磁盘上时（如果有备库），主库提交。类似半同步，不会丢数据。
off：事务可以在 WAL 记录被写入内存缓冲区后立即返回成功，无需等待其被写入磁盘。这种方式可以提高性能，但若在 WAL 记录被物理写入磁盘之前 发生系统崩溃，则可能会丢失最近的事务。
local：和on相同，但是只能控制本地实例，备库不受控制，即无论有没有备库，都只本地实例 wal写入磁盘即可commit事务
remote_apply:从库已应用了wal，主库才可以提交。这种模式主从是同步的
remote_write：备库接收到wal并将wal日志写到文件系统cache上时，主库提交。此时从库的接收到wal但是还没有落盘，如果操作系统crash，会丢失数据。
注意：如果是没有备库，则该参数只有on和off两种区别，其他几个值都和on相同
logging_collector = on
log_destination = csvlog
log_directory = '/opt/pgdata/logs'
log_filename = 'postgresql-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = on #当日志文件已存在时，该配置如果为off，新生成的日志将在文件尾部追加，如果为on，则会覆盖原来的日志。
log_rotation_age = 1d #单个日志文件的生存期，默认1天，在日志文件大小没有达到log_rotation_size时，一天只生成一个日志文件
log_min_messages = warning
log_min_duration_statement = 30s #慢sql，-1 表示不开启，0表示记录所有sql
log_checkpoints = on #检查点和重启点被记录在服务器日志中
log_connections = off #用户session登陆时是否写入日志，默认off
log_duration = off #记录sql执行时间
log_lock_waits = on #锁相关信息记录到日志
log_statement = 'none' # none, ddl, mod, all 控制记录哪些SQL语句。none不记录，ddl记录所有数据定义命令，比如CREATE,ALTER,和DROP 语句。mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等,all记录所有执行的语句
log_timezone = 'PRC' # 日志时区，例如UTC,Asia/Shanghai,最好和服务器设置相同的时区
track_activities = on # 用于控制是否记录关于会话活动的统计信息，设置此参数后，PostgreSQL 会在 pg_stat_activity 视图中记录有关当前活动会话的信息。默认on
track_counts = on # 用于控制是否记录表和索引的行数统计信息。这些统计信息可以帮助优化器做出更准确的查询计划，从而提高查询性能。默认on
track_io_timing = on # 用于控制是否记录磁盘 I/O 操作的详细时序信息，默认off
track_functions = all # 用于控制是否记录函数调用的统计信息，默认none,有以下几个值
none：不记录任何函数调用的统计信息。
all：记录所有函数调用的统计信息。
pl：记录 PL/pgSQL 函数调用的统计信息。
sql：记录 SQL 函数调用的统计信息。
ddl：记录数据定义语言（DDL）函数调用的统计信息
datestyle = 'iso, mdy' # 用于控制日期和时间数据的显示格式，默认值通常是 "ISO, MDY"，表示使用ISO 格式，并按照年-月-日的顺序显示日期。
timezone = 'PRC' # 设置数据库的时区,例如UTC,PRC（People's Republic of China，即中国标准时间）
lc_messages = 'en_US.utf8' # 用于设置数据库管理系统（DBMS）发出的消息的语言，默认值是en_US.utf8
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
random_page_cost = 1.1 # random_page_cost 的值是一个浮点数，表示随机磁盘访问的成本。这个值越大，表示优化器认为随机磁盘访问越昂贵，从而可能倾向于选择那些能够减少随机磁盘访问次数的查询计划。默认值4.0
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_vacuum_cost_delay = 0
autovacuum_max_workers = 4
autovacuum_naptime = 15
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements,pgstattuple,pg_buffercache,postgres_fdw'
至此，postgresql 安装完毕
