系统环境配置
关闭SELinux
[root@postgis postgresql]# setenforce 0
[root@postgis postgresql]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
[root@postgis postgresql]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
文件最大打开数限制
# linux资源限制配置文件是/etc/security/limits.conf；限制用户进程的数量对于linux系统的稳定性非常重要。
# limits.conf文件限制着用户可以使用的最大文件数，最大线程，最大内存等资源使用量
vim /etc/security/limit.conf
* soft nofile 655350 #任何用户可以打开的最大的文件描述符数量，默认1024，这里的数值会限制tcp连接
* hard nofile 655350
* soft nproc 655350 #任何用户可以打开的最大进程数
* hard nproc 650000
开放系统防火墙
# 添加防火墙规则
[root@postgis lib]# firewall-cmd --permanent --add-port=5432/tcp
success
[root@postgis lib]# firewall-cmd --reload
# 验证
[root@postgis lib]# iptables -nL
Chain IN_public_allow (1 references)
target prot opt source destination
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22 ctstate NEW
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:5432 ctstate NEW
预防OOM
Linux 2.6及以后版本中，可修改内核行为，使其不会“过量使用内存”。虽然不能阻止OOM发生，但可显著降低其发生的可能性。通过sysctl 选择严格的过量使用模式来实现：
sysctl -w vm.overcommit_memory=2
或者写入配置文件：
echo "vm.overcommit_memory=2" >> /etc/sysctl.conf
YUM安装
# 安装官方yum仓库源
[root@postgis ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
已加载插件：fastestmirror
pgdg-redhat-repo-latest.noarch.rpm | 6.8 kB 00:00:00
正在检查 /var/tmp/yum-root-XGSKOt/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-14.noarch
/var/tmp/yum-root-XGSKOt/pgdg-redhat-repo-latest.noarch.rpm 将被安装
正在解决依赖关系
--> 正在检查事务
---> 软件包 pgdg-redhat-repo.noarch.0.42.0-14 将被 安装
--> 解决依赖关系完成
依赖关系解决
=============================================================================================================================================
Package 架构 版本 源 大小
=============================================================================================================================================
正在安装:
pgdg-redhat-repo noarch 42.0-14 /pgdg-redhat-repo-latest.noarch 11 k
事务概要
=============================================================================================================================================
安装 1 软件包
总计：11 k
安装大小：11 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : pgdg-redhat-repo-42.0-14.noarch 1/1
验证中 : pgdg-redhat-repo-42.0-14.noarch 1/1
已安装:
pgdg-redhat-repo.noarch 0:42.0-14
完毕！
# yum方式安装postgreSQL 13 版本
[root@postgis ~]# yum install -y postgresql13-server
已加载插件：fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.bit.edu.cn
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
pgdg-common | 2.9 kB 00:00:00
pgdg10 | 3.6 kB 00:00:00
pgdg11 | 3.6 kB 00:00:00
pgdg12 | 3.6 kB 00:00:00
pgdg13 | 3.6 kB 00:00:00
pgdg95 | 3.6 kB 00:00:00
pgdg96 | 3.6 kB 00:00:00
(1/13): pgdg11/7/x86_64/group_gz | 245 B 00:00:01
(2/13): pgdg12/7/x86_64/group_gz | 245 B 00:00:00
(3/13): pgdg10/7/x86_64/group_gz | 245 B 00:00:01
(4/13): pgdg13/7/x86_64/group_gz | 246 B 00:00:00
(5/13): pgdg13/7/x86_64/primary_db | 47 kB 00:00:02
(6/13): pgdg95/7/x86_64/group_gz | 249 B 00:00:00
(7/13): pgdg11/7/x86_64/primary_db | 275 kB 00:00:05
(8/13): pgdg96/7/x86_64/group_gz | 249 B 00:00:00
(9/13): pgdg10/7/x86_64/primary_db | 267 kB 00:00:09
(10/13): pgdg-common/7/x86_64/primary_db | 135 kB 00:00:09
(11/13): pgdg12/7/x86_64/primary_db | 142 kB 00:00:10
(12/13): pgdg96/7/x86_64/primary_db | 257 kB 00:00:12
(13/13): pgdg95/7/x86_64/primary_db | 226 kB 00:00:19
正在解决依赖关系
--> 正在检查事务
---> 软件包 postgresql13-server.x86_64.0.13.0-1PGDG.rhel7 将被 安装
--> 正在处理依赖关系 postgresql13-libs(x86-64) = 13.0-1PGDG.rhel7，它被软件包 postgresql13-server-13.0-1PGDG.rhel7.x86_64 需要
--> 正在处理依赖关系 postgresql13(x86-64) = 13.0-1PGDG.rhel7，它被软件包 postgresql13-server-13.0-1PGDG.rhel7.x86_64 需要
--> 正在处理依赖关系 libpq.so.5()(64bit)，它被软件包 postgresql13-server-13.0-1PGDG.rhel7.x86_64 需要
--> 正在处理依赖关系 libicuuc.so.50()(64bit)，它被软件包 postgresql13-server-13.0-1PGDG.rhel7.x86_64 需要
--> 正在处理依赖关系 libicui18n.so.50()(64bit)，它被软件包 postgresql13-server-13.0-1PGDG.rhel7.x86_64 需要
--> 正在检查事务
---> 软件包 libicu.x86_64.0.50.2-4.el7_7 将被 安装
---> 软件包 postgresql13.x86_64.0.13.0-1PGDG.rhel7 将被 安装
---> 软件包 postgresql13-libs.x86_64.0.13.0-1PGDG.rhel7 将被 安装
--> 解决依赖关系完成
依赖关系解决
=====================================================================================================================================================================================================================
Package 架构 版本 源 大小
=====================================================================================================================================================================================================================
正在安装:
postgresql13-server x86_64 13.0-1PGDG.rhel7 pgdg13 5.4 M
为依赖而安装:
libicu x86_64 50.2-4.el7_7 updates 6.9 M
postgresql13 x86_64 13.0-1PGDG.rhel7 pgdg13 1.4 M
postgresql13-libs x86_64 13.0-1PGDG.rhel7 pgdg13 379 k
事务概要
=====================================================================================================================================================================================================================
安装 1 软件包 (+3 依赖软件包)
总下载量：14 M
安装大小：54 M
Downloading packages:
(1/4): libicu-50.2-4.el7_7.x86_64.rpm | 6.9 MB 00:00:00
warning: /var/cache/yum/x86_64/7/pgdg13/packages/postgresql13-libs-13.0-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY ] 65 kB/s | 7.6 MB 00:01:40 ETA
postgresql13-libs-13.0-1PGDG.rhel7.x86_64.rpm 的公钥尚未安装
(2/4): postgresql13-libs-13.0-1PGDG.rhel7.x86_64.rpm | 379 kB 00:00:23
(3/4): postgresql13-13.0-1PGDG.rhel7.x86_64.rpm | 1.4 MB 00:01:02
(4/4): postgresql13-server-13.0-1PGDG.rhel7.x86_64.rpm | 5.4 MB 00:04:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
总计 54 kB/s | 14 MB 00:04:23
从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥
导入 GPG key 0x442DF0F8:
用户ID : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
软件包 : pgdg-redhat-repo-42.0-14.noarch (installed)
来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : libicu-50.2-4.el7_7.x86_64 1/4
正在安装 : postgresql13-libs-13.0-1PGDG.rhel7.x86_64 2/4
正在安装 : postgresql13-13.0-1PGDG.rhel7.x86_64 3/4
正在安装 : postgresql13-server-13.0-1PGDG.rhel7.x86_64 4/4
验证中 : postgresql13-13.0-1PGDG.rhel7.x86_64 1/4
验证中 : postgresql13-server-13.0-1PGDG.rhel7.x86_64 2/4
验证中 : postgresql13-libs-13.0-1PGDG.rhel7.x86_64 3/4
验证中 : libicu-50.2-4.el7_7.x86_64 4/4
已安装:
postgresql13-server.x86_64 0:13.0-1PGDG.rhel7
作为依赖被安装:
libicu.x86_64 0:50.2-4.el7_7 postgresql13.x86_64 0:13.0-1PGDG.rhel7 postgresql13-libs.x86_64 0:13.0-1PGDG.rhel7
完毕！
安装postgresql并自定义数据目录【推荐】
初始化自定义数据目录
单实时建议直接修改原文件
[root@postgis lib]# cp /lib/systemd/system/postgresql-13.service /etc/systemd/system/postgresql-13-product.service
# 初始化数据目录
[root@postgis lib]# /usr/pgsql-13/bin/postgresql-13-setup initdb postgresql-13-product
Initializing database ... OK
[root@postgis lib]# ll /data
总用量 0
drwxr-xr-x. 3 root root 16 10月 17 16:31 pgsql-product
修改配置文件
vim /data/pgsql-product/13/data/postgresql.conf
# 以下为为修改项：
listen_addresses = '0.0.0.0' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 1000 # (change requires restart)
superuser_reserved_connections = 5 # (change requires restart)
shared_buffers = 8192MB # min 128kB
work_mem = 16MB # min 64kB
maintenance_work_mem = 512MB # min 1MB
vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables)
max_worker_processes = 128 # (change requires restart)
max_parallel_maintenance_workers = 8 # taken from max_parallel_workers
max_parallel_workers_per_gather = 16 # taken from max_parallel_workers
fsync = off # flush data to disk for crash safety
commit_delay = 1000 # range 0-100000, in microseconds
commit_siblings = 100 # range 1-1000
checkpoint_completion_target = 0.8 # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 16GB
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
vim /data/pgsql-product/13/data/pg_hba.conf
# 允许10.100.0.0（公司内部网络）访问postgresql
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 10.100.0.0/16 scram-sha-256 # 增加一条配置
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
启动
[root@postgis data]# systemctl start postgresql-13-product.service
[root@postgis data]# systemctl status postgresql-13-product.service
● postgresql-13-secondary.service - PostgreSQL 13 product server
Loaded: loaded (/etc/systemd/system/postgresql-13-product.service; disabled; vendor preset: disabled)
Active: active (running) since 六 2020-10-17 17:10:25 CST; 7s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 19301 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 19308 (postmaster)
CGroup: /system.slice/postgresql-13-product.service
├─19308 /usr/pgsql-13/bin/postmaster -D /data/pgsql-product/13/data/
├─19310 postgres: logger
├─19312 postgres: checkpointer
├─19313 postgres: background writer
├─19314 postgres: walwriter
├─19315 postgres: autovacuum launcher
├─19316 postgres: stats collector
└─19317 postgres: logical replication launcher
10月 17 17:10:25 postgis systemd[1]: Starting PostgreSQL 13 database server...
10月 17 17:10:25 postgis postmaster[19308]: 2020-10-17 17:10:25.787 CST [19308] LOG: redirecting log output to logging collector process
10月 17 17:10:25 postgis postmaster[19308]: 2020-10-17 17:10:25.787 CST [19308] HINT: Future log output will appear in directory "log".
10月 17 17:10:25 postgis systemd[1]: Started PostgreSQL 13 database server.
设置开机自启 postgresql-13-product.service
[root@postgis data]# systemctl enable postgresql-13-product.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-13-product.service to /etc/systemd/system/postgresql-13-product.service.
验证
# 查看数据库版本信息
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 行记录)
# 查看当前连接信息
postgres-# c
您现在已经连接到数据库 "postgres",用户 "postgres".
postgres-# conninfo
以用户 "postgres" 的身份，通过套接字"/var/run/postgresql"在端口"5432"连接到数据库 "postgres"
# 显示客户端当前编码
postgres-# encoding
UTF8
# 查看当前参数配置
show all # 查看所有参数配置
# 查询shared_buffers当前参数
postgres=# show shared_buffers;
shared_buffers
----------------
8GB
(1 行记录)
# 查询config_file
postgres=# show config_file;
config_file
-------------------------------------
/data/pgsql/13/data/postgresql.conf
(1 行记录)
# 查询当前数据目录
postgres=# show data_directory;
data_directory
---------------------
/data/pgsql/13/data
(1 行记录)
卸载
[root@postgis /]# yum remove postgresql13-server
已加载插件：fastestmirror
正在解决依赖关系
--> 正在检查事务
---> 软件包 postgresql13-server.x86_64.0.13.0-1PGDG.rhel7 将被 删除
--> 解决依赖关系完成
依赖关系解决
=====================================================================================================================================================================================================================
Package 架构 版本 源 大小
=====================================================================================================================================================================================================================
正在删除:
postgresql13-server x86_64 13.0-1PGDG.rhel7 @pgdg13 22 M
事务概要
=====================================================================================================================================================================================================================
移除 1 软件包
安装大小：22 M
是否继续？[y/N]：y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在删除 : postgresql13-server-13.0-1PGDG.rhel7.x86_64 1/1
验证中 : postgresql13-server-13.0-1PGDG.rhel7.x86_64 1/1
删除:
postgresql13-server.x86_64 0:13.0-1PGDG.rhel7
完毕！
374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
