www.postgresql.org/download/
www.postgresql.org/download/linux/redhat/
Direct RPM download
目录
1、准备工作
客户端 : postgresql11 - PostgreSQL client programs and libraries
附件模块 : postgresql11-contrib - Contributed source and binaries distributed with PostgreSQL
库文件 : postgresql11-libs - The shared libraries required for any PostgreSQL clients
服务端 : postgresql11-server - The programs needed to create and run a PostgreSQL server
postgresql11-11.8-1PGDG.rhel7
postgresql11-libs-11.8-1PGDG.rhel7
postgresql11-server-11.8-1PGDG.rhel7
libicu-50.2-3.el7.x86_64.rpm (可能需要2019-Aug-23)
https://mirrors.huaweicloud.com/centos/7/os/x86_64/Packages/
2、安装PostgreSQL
(rpm -ivh或rpm -ivh --prefix=/opt/postgresql)
postgresql11-lib(libicu)-->postgresql11-->postgresql11-server
软件包 postgresql11-server 不能重定位
3、初始化及配置 PostgreSQL
(-D /opt/postgresql/data)
/usr/pgsql-11/bin/postgresql-11-setup initdb
初始化过程中可能会出现 ‘ Data directory is not empty ’ 的问题,这是删掉 /var/lib/pgsql/ 目录后重试就可以初始化了。
4、配置开机启动
#systemctl enable postgresql-11
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.
5、修改配置
/var/lib/pgsql/11/data/
postgresql.conf
第59行取消注释,更改为:listen_addresses = '*'
# 第395行,添加:log_line_prefix = '%t %u %d '
max_connection = 10000 # 最大连接数到10000
shared_buffers = 80MB #(推荐物理内存的1/4)
fsync = on
work_mem = 4MB
maintenance_work_mem = 64MB
commit_delay = 0
commit_siblings = 5
effective_cache_size = 4GB
#log日志配置
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = '/postgres/pglog' # directory where log files are written,
log_filename = 'postgresql-%u.log' # log file name pattern,
log_file_mode = 0644 #0640 or 0644
log_truncate_on_rotation = on
6、修改 ipv4连接配置
/var/lib/pgsql/11/data/pg_hba.conf
host all all 0.0.0.0/0 md5
7、重启 PostgreSQL
(start 、stop 、restart )
#systemctl restart postgresql-11
8、连接后可能需要开放5432端口给其他用户连接
# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload
9、修改postgres用户密码
sudo -u postgres psql
psql -c "alter user postgres with encrypted password 'jakee.cn';'"
=#alter user postgres with encrypted password 'jakee.cn';
=#\q
备注:\q退出数据库;\l列出所有用户;\du列出库下所有表
10、修改配置文件更改数据目录
vim /usr/lib/systemd/system/postgresql-11.service
# Location of database directory
Environment=PGDATA=/opt/postgresql/data
11、其他命令
show data_directory;
show max_connections;
CREATE USER dbuser WITH PASSWORD '*****';
CREATE DATABASE exampledb OWNER dbuser;
GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser;
ps -ef | grep postgres
chown postgres:postgres -R /opt/postgresql/data
chmod 750 /opt/postgresql/data
ipcs -lm
cat /proc/meminfo | grep -i huge //检查Linux机器上的大页面设置和利用率
1.建立/postgres文件夹
# mkdir postgres
# chmod 777 postgres
源码安装
1、建立postgresql用户、内核资源的配置,主要涉及/etc/security/limits.conf、/etc/ 文件,类似oracle也需要配置这两个文件
/etc/sysctl.conf(sysctl -p)
kernel.shmall = 3145728
kernel.shmmax = 12884901888
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
# 获取SHMMAX值
sysctl kernel.shmmax
kernel.shmmax = 4278190079
# 获取SHMALL值
sysctl kernel.shmall
kernel.shmall = 4278190079
# 设置SHMMAX值
sysctl -w kernel.shmmax=16777216
kernel.shmmax = 16777216
查看最大连接数:
show max_connections;
select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal
from
(select count(*) used from pg_stat_activity) t1,
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
(select setting::int max_conn from pg_settings where name=$$max_connections$$) t3