postgresql 14安装部署(rpm安装)

环境:
Os:Centos 7
pg:14

 

1.下载介质
https://yum.postgresql.org/14/redhat/rhel-7-x86_64/repoview/postgresqldbserver14.group.html
我这里下载的如下介质:

[root@localhost pg14]# ls -al
total 8172
drwxr-xr-x  2 root    root        222 May  6 10:49 .
drwxr-xr-x 41 jenkins jenkins    8192 May  6 10:48 ..
-rw-r--r--  1 root    root    1598044 May  6 10:31 postgresql14-14.17-1PGDG.rhel7.x86_64.rpm
-rw-r--r--  1 root    root     714848 May  6 10:31 postgresql14-contrib-14.17-1PGDG.rhel7.x86_64.rpm
-rw-r--r--  1 root    root     281260 May  6 10:45 postgresql14-libs-14.17-1PGDG.rhel7.x86_64.rpm
-rw-r--r--  1 root    root    5751388 May  6 10:46 postgresql14-server-14.17-1PGDG.rhel7.x86_64.rpm

 

2.安装系统依赖包
yum install libxslt
yum install perl
这些包在操作系统的介质里可以找得到,修改yum指向本地光盘挂载目录即可安装

 

3.创建用户
[root@localhost opt]# groupadd postgres
[root@localhost opt]# useradd -g postgres postgres

 

4.修改系统配置
关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service

修改vi /etc/selinux/config
SELINUX=disabled

vi /etc/security/limits.conf ,最后面增加两行
* hard nofile 65535
* soft nofile 65535

#sysctl -p
然后退出重新登录
[mysql@localhost logfile]# ulimit -n
65535

 

5.安装
cd /soft/pg14/
rpm -ivh libicu-50.2-4.el7_7.x86_64.rpm
rpm -ivh postgresql14-libs-14.17-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-14.17-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-server-14.17-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-contrib-14.17-1PGDG.rhel7.x86_64.rpm

 

默认postgreql 安装在/usr/pgsql-14
数据存储目录:/var/lib/pgsql/版本号/data,
在实际生产中/var可能存在硬盘空间不足的问题,我们一般将数据存储目录放在挂载的硬盘如/data下

libicu下载地址为:
http://mirror.centos.org/centos/7/os/x86_64/Packages/libicu-50.2-4.el7_7.x86_64.rpm

 

6.创建数据存储目录
[root@localhost bin]#mkdir -p /opt/pg14/data
[root@localhost bin]#mkdir -p /opt/pg14/log
[root@localhost bin]#mkdir -p /opt/pg14/archivelog
[root@localhost bin]#chown -R postgres:postgres /opt/pg14
[root@localhost bin]#chmod 0700 /opt/pg14/data

 

7.初始化数据库
su - postgres
/usr/pgsql-14/bin/initdb -D /opt/pg14/data

 

8.修改启动参数
su - root
[root@localhost postgresql-13.service.d]# ls -al /usr/lib/systemd/system/postgresql-14.service
-rw-r--r--. 1 root root 1764 Aug 10 06:06 /usr/lib/systemd/system/postgresql-14.service

 

vi /usr/lib/systemd/system/postgresql-14.service
修改为Environment=PGDATA=/opt/pg14/data/

 

9.修改配置参数postgresql.conf

su - postgres
vi /opt/pg14/data/postgresql.conf
修改的内容如下:

[root@dsc1 data]# more postgresql.conf 
listen_addresses='*'
port=5432
max_connections=1000
unix_socket_directories = '/tmp'

####内存相关#########
work_mem=10MB
maintenance_work_mem=2GB
max_locks_per_transaction=1024
max_wal_size=64GB
checkpoint_timeout=30min
checkpoint_completion_target=0.9
shared_buffers=2GB          ##RAM*0.25GB
effective_cache_size=4GB    ##RAM*0.5GB    #RAM指内存(free -g)

wal_level=replica
archive_mode=on
archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg14/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f'


######慢查询部分#####################
logging_collector=on
log_directory = '/opt/pg14/log'                  ##指定具体目录,否则会默认在pgdata目录下创建log目录
log_destination='stderr'
log_min_duration_statement=1000                  ##超过1秒的慢查询都会记录
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = off                   ##是否覆盖
log_rotation_age = 1d                            ##每天生成
log_rotation_size = 10MB                         ##每个日志大小
#log_statement = all 和 log_min_duration_statement = 5000   
#根据需要两者设置其一即可
#如果两个都进行了配置默认所有SQL全部打印，log_min_duration_statement设置失效
##log_statement = all                              #需设置跟踪所有语句，否则只能跟踪出错信息
log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'
wal_log_hints=on
full_page_writes=on

##时区
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'

wal_keep_size = 1024MB ##为standby从库保留的wal日志大小

 

10.修改配置参数pg_hba.conf
su - postgres
vi /opt/pg14/data/pg_hba.conf

# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5

 

11.设置postgres用户环境变量

su - postgres
[postgres@localhost ~]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PGHOME=/usr/pgsql-14
PGDATA=/opt/pg14/data
PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export PGHOST=/tmp ##因为参数unix_socket_directories = '/tmp' 设置在tmp目录

 

14.启动数据库
[root@localhost pgsql-14]#systemctl daemon-reload
[root@localhost pgsql-14]#systemctl start postgresql-14
[root@localhost pgsql-14]#systemctl enable postgresql-14
[root@localhost pgsql-14]#systemctl status postgresql-14

 

15.登录并修改postgres账号的密码
su - postgres
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE

 

16.创建用户和创建数据库
创建用户
create user hxl with password 'postgres';

创建数据库
create database db_test owner hxl; -- 创建数据库指定所属者

将数据库得权限,全部赋给某个用户
grant all on database db_test to hxl;

17.使用新账号登录
[postgres@localhost ~]$ psql -h localhost -U hxl -p5432 -d db_test
psql (14.17)