简介
PostgreSQL,简称PG或PGSQL。C和C++开发。开源的关系型数据库。
中文社区: www.postgres.cn
1.1 PostgreSQL 逻辑结构
- 第一层是实例(Instance)。
- 第二层是数据库(Database)；一个实例下有多个数据库；每个数据库之间是完全独立的。
- 第三层是Schema；数据库下面有多个Schema；其中“public” 是数据库创建时产生的。
- 每个Schema下面可以创建表，视图，索引，函数，序列，物化视图，外部表等等。
源码安装配置
基础环境优化
systemctl stop firewalld.service
systemctl disable firewalld.service
#查看selinux
getenforce
#关闭selinux
setenforce 0
#永久关闭selinux
sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
#清除iptables 规则
iptables -F
systemctl stop NetworkManager
systemctl disable NetworkManager
#备份
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
#配置源
curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
#清除缓存
yum clean all
yum install -y net-tools lrzsz vim libaio-devel tree libaio-devel lsof sysstat bash-completion bash-completion-extras ntpdate
cat >>/var/spool/cron/root<<EOF
# ntp 时间同步
00 00 * * * /usr/sbin/ntpdate -u ntp.aliyun.com
EOF
/usr/sbin/ntpdate -u ntp1.aliyun.com
hwclock --systohc
创建用户
groupadd postgres
useradd postgres -g postgres
echo 'dyh666' | sudo passwd --stdin postgres
安装依赖
yum groupinstall -y "Development Tools" "Legacy UNIX Compatibility"
yum install -y bison flex readline* zlib-devel gcc* make systemd-devel
创建目录并授权
mkdir -p /usr/local/pg12 # 安装目录
mkdir -p /pgdata/12/data # 数据目录
mkdir -p /archive # 归档目录
chown -R postgres:postgres /archive /usr/local/pg12 /pgdata
chmod -R 700 /pgdata/12/data
系统参数优化
cat >/etc/sysctl.conf<<'EOF'
kernel.shmmax = 68719476736
kernel.shmall=4294967296
kernel.sem = 50100 64128000 50100 12800
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
EOF
cat >/etc/security/limits.conf<<'EOF'
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock 50000000
* hard memlock 50000000
EOF
sysctl -p
# 建议关闭numa，设置IO策略为deadline（机械）活着noop（SSD）
安装pg
su - postgres
# 下载包
wget https://ftp.postgresql.org/pub/source/v12.6/postgresql-12.6.tar.gz --no-check-certificate
tar xf postgresql-12.6.tar.gz
cd postgresql-12.6/
# 配置pg ./configure --help // 查看配置项
./configure --prefix=/usr/local/pg12 --with-pgport=5432 --with-systemd # 出现问题检查依赖
# 编译 使用 gmake word 会安装一些拓展模块
gmake world
# 安装
make install-world
设置环境变量
cat >>/home/postgres/.bash_profile<<'EOF'
export PGDATA=/pgdata/12/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pg12
export LD_LIBRARY_PATH=$PGHOME:$LD_LIBRARY_PATH
export PGUSER=postgres
export PATH=/usr/local/pg12/bin:$PATH
EOF
source /home/postgres/.bash_profile
初始化数据库
su - postgres
# 简易初始化
#initdb -D /pgdata/12/data -W
# 生产初始化
/usr/local/pg12/bin/initdb -A md5 -D /pgdata/12/data -E utf8 --locale=C -W # 配置管理员密码
修改配置文件
# 修改防火墙规则
cat >/pgdata/12/data/pg_hba.conf<<'EOF'
# TYPE DATABASE USER ADDRESS METHOD 可设置/reject/md5/trust
# 配置自己对应的user/host
host all all 0.0.0.0/0 md5
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# 允许具有复制权限的用户从本地主机进行复制连接
local replication all md5
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
EOF
# 修改postgresql.conf配置文件
cat >/pgdata/12/data/postgresql.conf<<'EOF'
# 监听地址
listen_addresses = '*'
# 最大连接数
max_connections = 2000
# 缓冲区大小
shared_buffers = 1GB
# 动态共享内存类型
dynamic_shared_memory_type = posix
# 日志详细程度
wal_level = replica
# 检查点之后第一次页面更改（即使是对提示位进行非关键性的修改）写 FPI 到 WAL 中
wal_log_hints = on
# 重做日志最大物理大小
max_wal_size = 1GB
# 重做日志最小物理大小
min_wal_size = 80MB
# 开启归档
archive_mode = on
# 检查归档
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
# 最大从库数 多设置点没事
max_wal_senders = 10
# 重做日志保存数量
wal_keep_segments = 128
# 流复制主机发送数据的超时时间
wal_sender_timeout = 60s
# 开启说明如果作为从库可以读
hot_standby = on
# 流复制的最大延迟时间
max_standby_streaming_delay = 30s
# 向主库报告从库当前状态的最大时间
wal_receiver_status_interval = 10s
# 如果有错误的数据复制，是否向主库反馈
hot_standby_feedback = on
# 日志时区（prc 中华人民共和国）
log_timezone = 'PRC'
# 时间格式
datestyle = 'iso, mdy'
# 数据库时区
timezone = 'PRC'
# 系统错误消息的区域设置
lc_messages = 'C'
# 货币格式的区域设置
lc_monetary = 'C'
# 数字格式设置的区域设置
lc_numeric = 'C'
# 时间格式的区域设置
lc_time = 'C'
# 默认文本搜索配置
default_text_search_config = 'pg_catalog.english'
# 日志输出
log_destination = 'stderr'
# 开启日志采集
logging_collector = on
# 日志路径
log_directory = '/pgdata/12/log'
# 日志格式
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
# 日志权限
log_file_mode = 0600
EOF
启动和关闭
手动方式：
#pg_ctl -D /pgdata/12/data -l logfile start # 手动常用
-D 指定数据目录
-l 启动日志追加文件
#pg_ctl -D /pgdata/12/data stop -ms # smart 所有客户端连接断开后关闭 // 慢
#pg_ctl -D /pgdata/12/data stop -mf # fast 自动kill进程将未提交事务进行回滚 // 生产常用默认
#pg_ctl -D /pgdata/12/data stop -mi # immediate 自动kill进程，不回滚事务，启动后做回滚，有可能造成数据不一致
#pg_ctl restart -mf
# 脚本方式:
#/opt/postgresql-12.6/contrib/start-scripts/linux
#netstat -lntp
systemd管理
cat >/usr/lib/systemd/system/pgsql.service<<'EOF'
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
# 要侦听的服务器的端口号
Environment=PGPORT=5432
# 数据库目录的位置
Environment=PGDATA=/pgdata/12/data
# 禁用OOM kill
OOMScoreAdjust=-1000
ExecStart=/usr/local/pg12/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/usr/local/pg12/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pg12/bin/pg_ctl reload -D ${PGDATA} -s
# 为服务器提供合理的启动/关闭时间
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF
# 刷新systemd
systemctl daemon-reload
# 启动
systemctl start pgsql
# 自启动
systemctl enable pgsql
# 状态信息
systemctl status pgsql
# 查看端口
netstat -lntp
