pgsql编译安装13.3

postgreSQL 编译安装

操作系统：centos 7

数据库版本：pgsql13.3

新增postgres用户组

[root@localhost ~]#groupadd postgres

新增postgres用户并加入postgres组

[root@localhost ~]#useradd postgres -g postgres

新建数据库执行目录

[root@localhost ~]#mkdir -p /usr/local/pgsql
[root@localhost ~]#mkdir -p /data/postgres-log

新建数据库数据文件目录

[root@localhost ~]#mkdir -p /data/pgsql/data

修改目录拥有者

[root@localhost ~]#chown -R postgres /usr/local/pgsql/.
[root@localhost ~]#chown -R postgres /data/pgsql/
[root@localhost ~]#chown -R postgres /data/pgsql/data/
[root@localhost ~]#chown -R postgres /data/pgsql/data/.
[root@localhost ~]#chown -R postgres /data/postgres-log/.

设置变量，编译profile文件

[root@localhost ~]# vim /etc/profile
PATH=/usr/local/pgsql/bin:$PATH

刷新变量，使profile文件生效

[root@localhost ~]# source /etc/profile

安装编译源码所需要的工具和库

[root@localhost ~]# yum -y install wget gcc readline-devel zlib-devel make

下载、解压、编译、安装源码包

[root@localhost src]# ll
总用量 23868
-rw-r--r--. 1 root root 25536998 5月   9 2017 postgresql-13.3.tar.gz
[root@localhost src]# tar -zxvf postgresql-13.3.tar.gz
[root@localhost src]# cd postgresql-13.3
[root@192 postgresql-13.3]#./configure 
[root@192 postgresql-13.3]# make
[root@192 postgresql-13.3]# make install

变更登录用户

[root@localhost postgresql-13.3]# su postgres

执行初始化脚本

[postgres@localhost postgresql-13.3]$ /usr/local/pgsql/bin/initdb --encoding=utf8 -D /data/pgsql/data

退出变更登录

[postgres@localhost postgresql-13.3]$ exit
[root@localhost src]# cp /usr/src/postgresql-13.3/contrib/start-scripts/linux /etc/init.d/postgresql

增加执行权限

[root@localhost src]# chmod +x /etc/init.d/postgresql

编辑postgresql执行脚本，指定数据库目录(里面有个默认配置位置，记得修改)

[root@localhost src]# vim /etc/init.d/postgresql
PGhome="/data/pgsql/data"

编译配置文件，配置可访问数据库的网络地址

listen_addresses = '*'	
port = 5432				
max_connections = 4000			
superuser_reserved_connections = 5	

authentication_timeout = 30s		

shared_buffers = 8GB			
temp_buffers = 512MB			
max_prepared_transactions = 4000		
					
work_mem = 256MB			
maintenance_work_mem = 128MB		

dynamic_shared_memory_type = posix	

shared_preload_libraries = 'pg_stat_statements'		

pg_stat_statements.max = 1000
pg_stat_statements.track = top
pg_stat_statements.track_utility = true
pg_stat_statements.save = true

wal_level = replica 					

archive_mode = on
# 将wal备份至从库
archive_command = '' 
max_wal_senders = 1		
wal_keep_size = 64 	

max_replication_slots = 2000					

effective_cache_size = 16GB

log_destination = 'csvlog'		
logging_collector = on		
log_directory = '/data/postgres-log'							
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'						
log_rotation_age = 1d								
log_rotation_size = 20MB							
log_connections = on
log_disconnections = on
log_line_prefix = '%m %p %u %d %r '			
log_statement = 'mod'						
log_timezone = 'PRC'

安装插件

[root@localhost src]# cd /usr/src/postgresql-13.3/contrib/
[root@localhost contrib]# make
[root@localhost contrib]# make install

启动postgresql服务

[root@192 home]# service postgresql start
Starting PostgreSQL: ok
[root@192 home]# service postgresql status
pg_ctl: server is running (PID: 12253)
/usr/local/pgsql/bin/postgres "-D" "/data/pgsql/data"

以postgres用户登录数据库

[root@192 home]# psql -U postgres
psql (13.3)
Type "help" for help.

postgres=# 

编辑配置文件，设置密码md5验证，（后期需控制连接IP）

[postgres@192 home]$ vim /data/pgsql/data/pg_hba.conf
# TYPE  homeBASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0                md5

设置数据库开启自启动

[root@192 home]# chkconfig postgresql on