pg数据库部署

一、环境准备

1、服务器环境配置

关闭防火墙

systemctl disable firewalld && systemctl stop firewalld

yum源配置

cat /etc/yum.repos.d/kylin_v10-iso.repo

[ks10-iso]
name=kylin10-iso
baseurl=ftp://172.30.11.19/linuxfile/Kylin-Server-10-SP1-Release-Build20-20210518m-arm64/
enabled=1
gpgcheck=0

因当前鲲鹏云?期和局?的专线还未打通，?法使?局?的yum源，故上传ISO?件做本地 yum源

mkdir -p /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak

vi /etc/yum.repos.d/kylin_v10-iso.repo
[kylin10]
name=kylin10
baseurl=file:///yum
enabled=1
gpgcheck=0

mkdir /yum
mount -o loop /root/Kylin-Server-10-SP1-Release-Build20-20210518marm64.iso /yum
yum clean all
yum makecache

?安装依赖包

yum -y install readline-devel
yum -y install python3-numpy
yum -y install openssl-devel
yum -y install gcc*
yum -y install libxml*

[回?录](# ?录)

2、postgres创建用户、配置环境变量

创建postgres??

groupadd postgres -g 701
useradd -u 701 -g postgres postgres
passwd postgres

配置postgres??环境变量

vi ~/.bash_profile

# Source /root/.bashrc if user has one
[ -f ~/.bashrc ] && . ~/.bashrc
export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/local/lib64/pkgconfig:/usr/local/lib/pkgconfig:/usr/lib64/pkgconfig:/usr/share/pkgconfig:/postgresql/postgresql-15.2/proj/lib/pkgconfig:/postgresql/postgresql-15.2/gdal/lib/pkgconfig:/postgresql/postgresql-15.2/json-c/lib64/pkgconfig
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/postgresql/postgresql-15.2/lib:/postgresql/postgresql-15.2/json-c/lib64:/postgresql/postgresql-15.2/proj/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/postgresql/postgresql-15.2/geos/lib:/postgresql/postgresql-15.2/gdal/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/postgresql/postgresql-15.2/postgis/bin
export PATH=/postgresql/postgresql-15.2/bin:$PATH
export PGDATA=/postgresql/pgdata

[回?录](# ?录)

3、磁盘划分

扩展基础盘到94.5GB

fdisk /dev/vda<<EOF &> /dev/null
p
n
4


p
w
EOF

刷新硬盘
?

partx -s /dev/vda
echo "Disk Partition /dev/vda4 Create OK!"
pvcreate /dev/vda4
rootlvname=`df -h|grep "\-root"|awk '{print $1}'`
vgname=`vgs|grep klas|awk '{print $1}'`
vgextend ${vgname} /dev/vda4
lvextend -L 94.5G ${rootlvname}
xfs_growfs ${rootlvname}
df -h

数据盘空间并?系统盘（都是超?IO）

pvcreate /dev/vdb
pvs
vgextend klas_host-10-134-21-9 /dev/vdb
lvextend -L +199.99G /dev/mapper/klas_host--10--134--21--9-root
xfs_growfs /dev/mapper/klas_host--10--134--21--9-root
df -h

环境准备环节结束。 [回?录](# ?录)

二、源码安装postgreSQL15.2软件

1、上传安装包

使?postgres??安装软件和数据库，安装包对应版本

gdal-3.5.0.tar.gz
geos-3.9.3.tar.bz2
postgis-3.4.0.tar.gz
postgresql-15.2.tar.gz
proj-6.2.0.tar.gz

2、源码安装postgreSQL15.2软件

解压源码包

tar -zxf postgresql-15.2.tar.gz
cd postgresql-15.2/

配置编译参数并编译

./configure --
prefix=/postgresql/postgresql-15.2 --with-perl --with-python --withblocksize=32 --with-wal-blocksize=32 --with-openssl
make
make install

备注：
--prefix=/postgresql/postgresql-15.2 指定postgreSQL软件安装?录
--with-perl
--with-python
--with-blocksize=32
--with-wal-blocksize=32
--with-openssl 启?ssl加密连接?持，只有在编译的时候加?ssl?持，后?才能开启ssl访
问验证
?此，postgreSQL数据库被安装到/postgresql/postgresql-15.2?录

配置??参数?件

vim /home/postgres/.bashrc

# Source default setting
[ -f /etc/bashrc ] && . /etc/bashrc

# User environment PATH
PATH="$HOME/.local/bin:$HOME/bin:$PATH"
export PATH
export PATH=/postgresql/postgresql-15.2/bin:$PATH
export LD_LIBRARY_PATH=/postgresql/postgresql-15.2/lib:$LD_LIBRARY_PATH
export PGDATA=/postgresql/pgdata
export PGHOST=/tmp
export libdir=/postgresql/postgresql-15.2/lib

?[回?录](# ?录)

3、创建数据库

创建数据库目录

mkdir /postgresql/pgdata
chown postgres:postgres /postgresql/pgdata/

使?postgres??初始化

/postgresql/postgresql-15.2/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=C -k

# initdb -k(开启数据块校验功能)

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are enabled.

fixing permissions on existing directory /postgresql/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
 
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the
option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:
 
/postgresql/postgresql-15.2/bin/pg_ctl -D /postgresql/pgdata -l logfile start

?停?postgreSQL数据库

pg_ctl stop -D /postgresql/pgdata [-m shutdown-mode]
-m:
smart：相当于oracle中的shutdown不带参数，需要客?端终?连接后才能关闭数据库
fast：相当于oracle中的shutdown immediate
immediate：相当于oracle中的shutdown abort
 
/postgresql/postgresql-15.2/bin/pg_ctl stop -D /postgresql/pgdata/ -m fast
waiting for server to shut down.... done
server stopped

[回?录](# ?录)

4、开启ssl验证登录

修改pg_hba.conf 添加（鲲鹏云?期本地互访、鲲鹏云?期访问、政务云访问还有局机房?产访问）

hostssl    all    all   10.76.0.0/16 md5
hostssl    all    all   10.223.0.0/16 md5
hostssl    all    all   10.224.0.0/16 md5
hostssl    all    all   61.144.226.0/24 md5
hostssl    all    all   172.30.11.0/24 md5
hostssl    all    all   172.30.10.0/24 md5

修改postgresql.conf 添加

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

利?openssl?成server.key?件，过程汇总要求输?密码：

openssl genrsa -des3 -out server.key 2048

删除设置的密码：

openssl rsa -in server.key -out server.key

创建基于server.key的服务器证书，所有设置直接回?跳过：

openssl req -new -key server.key -days 3650 -out server.crt -x509

修改server.key的权限为600

chmod og-rwx server.key

5、修改pg_hba.conf允许主备节点互访

允许复制进程连接

host     replication   all    10.76.18.101/32   md5
host     replication   all    10.76.18.102/32   md5
host     replication   all    10.76.18.103/32   md5

[回?录](# ?录)

6、修改postgresql.conf配置

新增监听ip跟端?

listen_addresses = '*'
port = 5432
max_connections = 1000     # 最?连接数默认值是100，修改为1000

修改内存参数

因postgresql15依然使?操作系统缓存，因此shared_buffers数据库缓存??官?建议初始化 ??为总内存??的25%，这?总??为32GB，因此设置为8GB。

参考：https://www.postgresql.org/docs/15/runtime-config-resource.html

shared_buffers = 8192MB

配置慢查询?志

log_destination = 'jsonlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# 记录执?慢的SQL
log_min_duration_statement = 2000
# 查询时?超过2秒的语句
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%m'

[回?录](# ?录)