Skip to content

Latest commit

 

History

History
381 lines (304 loc) · 8.39 KB

01.源码安装PostgreSQL10.md

File metadata and controls

381 lines (304 loc) · 8.39 KB
Raw

PostgreSQL 10.2源码安装

下载PostgreSQL源码安装包

PostgreSQL源码下载地址
PostgreSQL文档地址

上传PostgreSQL源码包

yum install -y lrzsz
cd /home/
mkdir soft
cd soft
rz

查看系统版本

more /etc/redhat-release
uname -a

文件系统建议

磁盘SSD raid10 文件格式XFS/EXT4

需要安装的软件包

  • gcc-c++
  • readline-devel
  • zlib-devel
yum install -y gcc-c++ readline-devel zlib-devel git

关闭防火墙

systemctl stop firewalld

修改limits.conf文件

vi /etc/security/limits.conf

文件内容，在末尾添加

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 1024000
* hard nproc 1024000

修改20-nproc.conf

vi /etc/security/limits.d/20-nproc.conf

文件内容，修改原内容

* soft nproc 1024000
* hard nproc 1024000
root soft nproc unlimited

修改sysctl.conf

vi /etc/sysctl.conf

文件内容

# 关闭ipv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1

# 避免放大攻击
net.ipv4.icmp_echo_ignore_broadcasts = 1

# 开启恶意icmp错误消息保护
net.ipv4.icmp_ignore_bogus_error_responses = 1

# 关闭路由转发
net.ipv4.ip_forward = 0
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0

#开启反向路径过滤
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1

#处理无源路由的包
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0

#关闭sysrq功能
kernel.sysrq = 0

#core文件名中片甲pid作为扩展名
kernel.core_uses_pid = 1

#开启SYN洪水攻击保护
net.ipv4.tcp_syncookies = 1

#修改消息队列长度
kernel.msgmnb = 65536
kernel.msgmax = 65536

#设置最大内存共享段大小bytes
kernel.shmmax = 内存的一半
kernel.shmall = 上面的参数除以4096

#FS特性
fs.aio-max-nr = 1048576
fs.file-max = 76724600

#timewait的数量 默认180000
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.tcp_sack = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

#每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
net.core.netdev_max_backlog = 262144

#限制仅仅是为了繁殖简单的DDOS攻击
net.ipv4.tcp_max_orphans = 3276800

#未收到客户端确认信息的链接请求的最大值
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_timestamps = 0

#内核放弃建立连接之前发送SYNACK包的数量
net.ipv4.tcp_synack_retries = 1

#内核放弃建立连接之前发送SYN包的数量
net.ipv4.tcp_syn_retries = 1

#启用timewait快速回收
net.ipv4.tcp_tw_recycle = 1

#开启重用，允许将TIME-WAIT sockets重新用于新的TCP连接
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 30

#当keepalive起用的时候，TCP发送keepalive消息的频率，缺省是2小时
net.ipv4.tcp_keepalive_time = 30

#允许系统打开的端口范围
net.ipv4.ip_local_port_range = 1024 68500

#修改防火墙表大小，默认65536
# net.netfilter.nf_conntract_max = 655350
# net.netfilter.nf_conntract_tcp_timeout_established = 1200

#确保无人能修改路由表
net.ipv4.conf.all_accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0

查看变更状态

sysctl -p

创建postgres用户

useradd postgres
passwd postgres

mkdir /pgdata10
chown postgres.postgres /pgdata10
su - postgres
vi .bash_profile
## 文件末尾添加Start
export LD_LIBRARY_PATH=/opt/PostgreSQL/10.2/lib
PGHOME=/opt/PostgreSQL/10.2
export PGHOME
PGDATA=/pgdata10
export PGDATA
export MANPATH=/opt/PostgreSQL/10.2/share/man:$MANPATH
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/PostgreSQL/10.2/bin
## 文件末尾添加End
source .bash_profile
exit
## 在root用户也添加postgres的环境变量
vi .bash_profile
export LD_LIBRARY_PATH=/opt/PostgreSQL/10.2/lib
PGHOME=/opt/PostgreSQL/10.2
export PGHOME
PGDATA=/pgdata10
export PGDATA
export MANPATH=/opt/PostgreSQL/10.2/share/man:$MANPATH
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/PostgreSQL/10.2/bin
## 文件末尾添加End
source .bash_profile

安装postgres

mv postgresql-10.2.tar.gz /home/soft
cd /home/soft/
tar zxvf postgresql-10.2.tar.gz
cd postgresql-10.2
./configure --prefix=/opt/PostgreSQL/10.2
## 检查少包，没少的话不会报错。
make -j4
make install
cp contrib/start-scripts/linux /etc/init.d/postgresql
chmod +x /etc/init.d/postgresql
chkconfig --add postgresql
vi /etc/init.d/postgresql

## 修改文件内容 Start
prefix=/opt/PostgreSQL/10.2
PGDATA="/pgdata10"
start
su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &"
替换成
su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' &"

restart
su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &"
替换成
su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' &"
## 修改文件内容End
su - postgres
initdb -D /pgdata10/
exit
/etc/init.d/postgresql start
su - postgres
psql
\l
\q
exit

安装插件

  • pg_stat_statements
  • pgstattuple 检查
  • oid2name 查看目录跟表和数据库的对应关系
  • postgres_fdw 建立外部表
cd /home/soft/postgresql-10.2/contrib/pg_stat_statements
make
make install
cd /home/soft/postgresql-10.2/contrib/oid2name
make
make install
cd /home/soft/postgresql-10.2/contrib/pgstattuple
make
make install
cd /home/soft/postgresql-10.2/contrib/postgres_fdw
make
make install
cd /home/soft/postgresql-10.2/contrib/pg_buffercache
make
make install
cd /home/soft/postgresql-10.2/contrib/adminpack
make
make install
cd /home/soft
git clone https://github.com/postgrespro/pg_pathman
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/PostgreSQL/10.2/bin
cd pg_pathman/
make USE_PGXS=1
make USE_PGXS=1 install

vi /pgdata10/postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pgstattuple,pg_buffercache,postgres_fdw,pg_pathman'
/etc/init.d/postgresql restart

## 安装插件End
su - postgres
oid2name
plsql

## 每个库下需要执行一次
create extension pg_pathman;
create extension pg_stat_statements;
create extension pgstattuple;
create extension pg_buffercache;
create extension postgres_fdw;

服务配置调整

vi /pgdata10/postgresql.conf


## 在末尾加入如下配置：会自动覆盖同名其他配置

## 连接相关
listen_addresses = '*'
max_connections = 800

## 内存相关
shared_buffers = 100GB  ## 共享缓冲区
temp_buffers = 16GB  ## 临时缓冲区
work_mem = 64MB  ## 单个SQL可使用的内存
maintenance_work_mem = 16GB  ## 维护工作进程可使用内存，可加速索引建立、维护
autovacuum_work_mem = 8GB  ## 垃圾回收工作进程可使用内存

## 线程相关
max_worker_processes = 16
max_parallel_workers = 16

SHOW max_worker_processes_per_gather

## 日志相关
wal_level = logical
checkpoint_timeout = 60min
max_wal_size = 10GB
min_wal_size = 80MB
checkpoint_completion_target = 0.8
max_wal_senders = 10
wal_keep_segments = 5000
max_replication_slots = 10
hot_standby = on
hot_standby_feedback = on
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2
wal_buffers = 2048kB
wal_writer_delay = 10ms


## 日志记录
logging_collector = on  ## 开启日志记录
log_filename = 'postgresql-%a.log'  ## 记录7天
log_truncate_on_rotation = on  ## 文件名相同截断而不是附加
log_rotation_size = 0  ## 不循环使用日志文件
log_lock_waits = on  ## 记录锁等待
log_checkpoints = on  ## 记录checkpoints
# log_connections = on  ## 记录连接
# log_disconnections = on  ## 记录释放连接
log_temp_files = 0  
log_autovacuum_min_duration = 0
log_min_duration_statement = 1000  ## 大于1000毫秒记录

## 日志归档
archive_mode = on
archive_timeout = 3600
archive_command='test ! -f /pgdata10arch/%f && cp %p /pgdata10arch/%f'




## 垃圾回收相关
autovacuum = on
vacuum_cost_delay = 0
autovacuum_vacuum_cost_delay = 0
autovacuum_max_workers = 4
autovacuum_naptime = 6s
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0
effective_io_concurrency = 2