Postgresql13源码安装部署_部署 postgressql13

Postgresql13源码安装部署

环境：OS:Centos7 DB:13.8

1.下载安装介质

下载地址:
源码下载地址: https://www.postgresql.org/ftp/source/
二进制下载地址(新版本的没有二进制下载,只能使用源码安装):https://www.enterprisedb.com/download-postgresql-binaries
我这里下载的介质是:
postgresql-13.8.tar.gz

2.安装依赖包

yum install readline
yum install gcc
yum -y install -y readline-devel
yum install zlib-devel

3.源码安装

tar -xvf postgresql-13.8.tar.gz
cd postgresql-13.8
mkdir -p /opt/pg13 ##这里是指定安装目录
/configure --prefix=/opt/pg13
make
make install

4.创建相应的用户

groupadd postgres
useradd -g postgres postgres

5.创建数据及日志目录，并做相应授权

su - postgres
cd /opt/pg13/bin
./initdb -D /opt/pg13/data/

看情况开启checksums
./initdb -D /opt/pg13/data/ --data-checksums

6.修改配置文件

cd /opt/pg13/data
vi postgresql.conf

listen_addresses='*'
port=5432
max_connections=1000

####内存相关#########
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=RAM*0.25GB             #RAM指内存,最大设置为64GB
effective_cache_size=RAM*0.5GB    #RAM指内存(free -g)

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

######慢查询部分#####################
logging_collector=on
log_directory = '/opt/pg13/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=onfull_page_writes=on
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

需要创建归档目录
mkdir -p /opt/pg13/archivelog

7.启动数据库

cd /opt/pg13/bin
./pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

8.登陆使用

cd /opt/pg13/bin
./psql -h localhost -U postgres -p 5432

psql (13.8)
Type “help” for help.

postgres=# \du
List of roles
Role name | Attributes | Member of
-----------±-----------------------------------------------------------±----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

9.设置环境变量

su - postgres
vi .bash_profile

# .bash_profile

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

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/pg13/bin

export PATH
1
2
3
4
5
6
7
8
9
10
11
12

10.修改postgres用户的访问密码并测试建库建表

PostgreSQL 数据库默认会创建一个postgres的数据库用户作为数据库的管理员，默认密码为空，我们需要修改为指定的密码，这里设定为postgres。

su - postgres
psql -h localhost -U postgres -p 5432

psql (13.8)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE
postgres=# select * from pg_shadow ;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | md53175bce1d3201d16594cebf9d7eb3f9d |          | 
(1 row)

#创建用户
postgres=# create user hxl with password 'postgres';
CREATE ROLE

#创建数据库
create database db_test encoding = 'utf8' owner hxl; -- 创建数据库指定字符集和属主
#将数据库得权限,全部赋给某个用户
grant all on database db_test to hxl;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

11.配置postgresql允许远程访问

只需要修改data目录下的pg_hba.conf和postgresql.conf这两个文件:
pg_hba.conf: 配置对数据库的访问权限;
postgresql.conf: 配置PostgreSQL数据库服务器的相应的参数

su - postgres
vi /opt/pg13/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

重新加载配置文件
su - postgres
pg_ctl -D /opt/pg13/data reload
server signaled
1
2
3
4
5
6
7
8
9
10
11

12.创建表并写入数据

登录提示输入密码

[postgres@host134 ~]$ psql -h 192.168.1.134 -U hxl -d db_test
Password for user hxl: 
psql (13.8)
Type "help" for help.


create table tb_test
(
id bigserial primary key not null,
name varchar(20),
createtime timestamp default current_timestamp,
modifytime timestamp default current_timestamp
);

insert into tb_test(name) values('name1');
insert into tb_test(name) values('name2');
insert into tb_test(name) values('name3');
insert into tb_test(name) values('name4');
insert into tb_test(name) values('name5');

db_test=> select * from tb_test;
 id | name  |         createtime         |         modifytime         
----+-------+----------------------------+----------------------------
  1 | name1 | 2022-10-18 11:32:33.649901 | 2022-10-18 11:32:33.649901
  2 | name2 | 2022-10-18 11:32:33.665863 | 2022-10-18 11:32:33.665863
  3 | name3 | 2022-10-18 11:32:33.691182 | 2022-10-18 11:32:33.691182
  4 | name4 | 2022-10-18 11:32:33.771843 | 2022-10-18 11:32:33.771843
  5 | name5 | 2022-10-18 11:32:34.496502 | 2022-10-18 11:32:34.496502
(5 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

13.开启归档模式(初始化参数已经配置的话可以忽略该步骤)

查看当前是在归档模式
[postgres@host134 ~]$ psql -h 192.168.1.134 -U postgres
Password for user postgres:
psql (13.8)
Type "help" for help.

postgres=# show archive_mode;
archive_mode
--------------
off
(1 row)

#可以看到当前没有在归档模式

#创建归档目录
su - postgres
mkdir -p /opt/pg13/archivelog

vi /opt/pg13/data/postgresql.conf

wal_level = replica
archive_mode = on
##archive_command = 'cp %p /opt/pg13/archivelog/%f' ##pa_wal目录的归档日志会拷贝到该目录
archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg13/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f' #按照日期目录归档,wal日志会自动拷贝到该目录


需要重新启动
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

查看归档情况
select pg_switch_wal();

归档切换
select pg_switch_wal();
select pg_switch_wal();

[postgres@host134 archivelog]$ ls
000000010000000000000006 000000010000000000000007 000000010000000000000008 000000010000000000000009 20221018
这个时候会看到自动创建了日志目录,日期目录有相应的归档日志
[postgres@host134 20221018]$ ls
00000001000000000000000A 00000001000000000000000B 00000001000000000000000C


删除归档
su - postgres
[postgres@host134 pg_wal]$ pg_controldata /opt/pg13/data ##可以查看当前使用的wal Latest checkpoint's REDO WAL file: 00000001000000000000000A
[postgres@host134 pg_wal]$ pg_archivecleanup -d /opt/pg13/archivelog 00000001000000000000000A ##删除00000001000000000000000A之前的归档

这里只能删除archivelog目录下的符合条件的归档,若该目录下还有子目录,比如安装日期创建文件夹的目录 ，是需要指定日期目录才能删除的，比如:

pg_archivecleanup -d /opt/pg13/archivelog/20221020 00000001000000000000000A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

14.设置开机自启动

请参考:
https://www.cnblogs.com/hxlasky/p/16802748.html

15.几个重要参数

show shared_buffers; ##建议值是设置成硬件内存的25%,不超过内存的50%
show work_mem; ##根据链接数生成配置
show wal_buffers; ##推荐值16MB
show effective_cache_size; ##推荐内存的1/2
show max_connections;
show maintenance_work_mem; ##推荐值4096MB
show fsync;
show wal_sync_method;
show synchronous_commit; ##默认为on
show default_statistics_target; ##默认100
show maintenance_work_mem;
show checkpoint_completion_target;
show effective_io_concurrency;
show min_wal_size;
show max_wal_size;
show max_worker_processes;
show max_parallel_workers;
show random_page_cost;
show log_min_duration_statement;

pg参数建议生成工具
https://pgtune.leopard.in.ua/#/
https://zhuanlan.zhihu.com/p/333201734

16.创建只读账号

使用超级账号登录创建只读用户

create user hxlread with password ‘postgres’;

使用数据库属主的账号登录并赋予权限
psql -h 192.168.1.134 -U hxl -d db_test
grant select on tb_test to hxlread;

使用只读账号登录
psql -h 192.168.1.134 -U hxlread -d db_test

###############安装常用的扩展######################################################

1.安装postgres_fdw扩展

进入到解压后的源码目录
[root@host134 postgres_fdw]#cd /soft/postgresql/postgresql-13.8/contrib/postgres_fdw
[root@host134 postgres_fdw]# make
[root@host134 postgres_fdw]# make install

安装好后会在extension有对应的文件,如:postgres_fdw.control
[root@host134 extension]# pwd
/opt/pg13/share/postgresql/extension
[root@host134 extension]# ls -al
total 20
drwx------ 2 postgres postgres 110 Oct 27 10:30 .
drwx------ 6 postgres postgres 4096 Oct 21 11:24 …
-rw-r–r-- 1 postgres postgres 658 Oct 21 11:24 plpgsql–1.0.sql
-rw-r–r-- 1 postgres postgres 193 Oct 21 11:24 plpgsql.control
-rw-r–r-- 1 root root 507 Oct 27 10:30 postgres_fdw–1.0.sql
-rw-r–r-- 1 root root 172 Oct 27 10:30 postgres_fdw.control
执行:
postgres=# create extension postgres_fdw;
CREATE EXTENSION

2.安装dblink扩展

[root@host134 dblink]# cd /soft/postgresql/postgresql-13.8/contrib/dblink
[root@host134 dblink]# make
[root@host134 dblink]# make install

3.修改权限目录

[root@host134 extension]# ls -al
total 40
drwx------ 2 postgres postgres 211 Oct 27 10:37 .
drwx------ 6 postgres postgres 4096 Oct 21 11:24 …
-rw-r–r-- 1 root root 419 Oct 27 10:37 dblink–1.0–1.1.sql
-rw-r–r-- 1 root root 2832 Oct 27 10:37 dblink–1.1–1.2.sql
-rw-r–r-- 1 root root 6645 Oct 27 10:37 dblink–1.2.sql
-rw-r–r-- 1 root root 170 Oct 27 10:37 dblink.control
-rw-r–r-- 1 postgres postgres 658 Oct 21 11:24 plpgsql–1.0.sql
-rw-r–r-- 1 postgres postgres 193 Oct 21 11:24 plpgsql.control
-rw-r–r-- 1 root root 507 Oct 27 10:30 postgres_fdw–1.0.sql
-rw-r–r-- 1 root root 172 Oct 27 10:30 postgres_fdw.control

可以看到刚才安装的扩展权限都是root的,需要修改成postgres
[root@host134 postgresql]# chown -R postgres:postgres ./extension/