CentOS7离线安装PostgreSQL11

www.postgresql.org/download/

www.postgresql.org/download/linux/redhat/

Direct RPM download

目录

1、准备工作

2、安装PostgreSQL

3、初始化及配置 PostgreSQL

4、配置开机启动

5、修改配置

6、修改 ipv4连接配置

7、重启 PostgreSQL

8、连接后可能需要开放5432端口给其他用户连接

9、修改postgres用户密码

10、修改配置文件更改数据目录

11、其他命令

---

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