centos7安装postgresql14.9_postgres14.9 rpm包-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_41525527/article/details/134913599

postgresql安装

1、为什么选择postgresql数据库？

成本因素：PostgreSQL是一个开源数据库管理系统，使用PostgreSQL可以节省高昂的许可证费用。

可移植性：PostgreSQL采用标准的SQL语法和功能，因此迁移过程相对较为简单。应用程序可以保持相对独立，不会受到数据库平台的限制。

可扩展性：PostgreSQL具有强大的可扩展性和灵活性，可以处理大规模的数据和高并发访问。它支持分布式架构和并行查询，可以满足不断增长的数据需求。

社区支持：PostgreSQL拥有庞大的社区支持和活跃的开发者社区，提供了广泛的文档、教程和解决方案。用户可以通过社区获得及时的帮助和支持。

兼容性：PostgreSQL可以与其他数据库系统进行无缝集成，包括Oracle、MySQL等。因此，迁移到PostgreSQL可以与现有的数据库环境进行集成和协作。

安全性：PostgreSQL提供了强大的安全功能，包括访问控制、数据加密、审计和身份验证等。这可以保护敏感数据免受未经授权的访问和攻击。

2、安装postgresql

2.1、首先从postgresql的官网下载对应的postgresql版本postgresql下载地址https://www.postgresql.org/download/linux/redhat/

我这里选择的是centos环境下的postgresql14的版本

2.2、复制第四步里面的内容

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2.3、安装postgresql服务端

# 安装postgresql14
yum install -y postgresql14-server 
# 启动postgresql14
systemctl start postgresql14

2.4、postgresql的基础配置

#创建数据存放目录
mkdir -p /app/pgsql/14/data
#指定postgres用户的权限
chown -R postgres:postgres /app/pgsql/14/data
#登录postgres用户
su - postgres
#初始化数据库，并指定存放数据目录
/usr/pgsql-14/bin/initdb -D /app/pgsql/14/data
exit
#修改启动文件的启动目录
sudo vim /usr/lib/systemd/system/postgresql-14.service
#Enviroment=PGDATA=/var/lib/pgsql/14/data/ 
Enviroment=PGDATA=/app/pgsql/14/data
#重新加载配置文件
systemctl daemon-reload
systemctl start postgresql-14.service
#修改密码
sudo -u postgres psql
\passwd postgres

2.5、配置外部可以访问

#备份配置文件
cd /app/pgsql/14/data
cp postgresql.conf{,.ori}
cp pg_hba.conf{,.ori}

# 配置除本机外的IP访问
vi postgresql.conf
listen_addresses="*"
port=5342

vi pg_hba.conf
host	all		all		0.0.0.0/0	md5

# 重启postgresql
systemctl restart postgresql14

2.6、登录postgresql

# 访问本地搭建的postgresql数据库
sudo -u postgres psql -p 5432

# 访问远端的postgresql数据库 
psql -h 远端IP地址 -p 5432 -U postgres -d postgres -W

参数介绍：

-h 访问的IP地址

-p 指定postgresql的访问端口

-U 指定postgresql的访问用户

-d 指定访问数据库

-W 指定访问密码

3、postgresql的优化

3.1、附华为对postgresql数据库的优化地址

数据库参数调优 - HPC 22.0.0 运维指南 04 - 华为nullhttps://support.huawei.com/enterprise/zh/doc/EDOC1100283327/ea540e50

max_connections：最大连接数应该根据你的应用需求和服务器硬件来设置。通常，你可以尝试将它设置在 200 到 400 之间，然后监测系统性能并根据需要进行微调。不要将它设置得过高，以避免耗尽系统资源。
例如，你可以尝试设置为：
makefileCopy code
max_connections = 300
shared_buffers：共享缓冲区的大小应该根据服务器内存来设置。一般建议将其设置为总内存的 25% 到 30%。在你的情况下，可以尝试将其设置为 25GB 到 30GB。
makefileCopy code
shared_buffers = 30GB
work_mem：每个连接的工作内存大小可以设置得比较小，因为你有大量的内存可用。尝试将其设置为 128MB 到 256MB，然后根据需要进行微调。
makefileCopy code
work_mem = 256MB
maintenance_work_mem：维护操作的工作内存大小可以设置得相对较高，以加快维护操作的执行速度。可以考虑将其设置为 4GB 到 8GB。
makefileCopy code
maintenance_work_mem = 8GB
effective_cache_size：有效缓存大小应该设置为系统内存的大部分，因为你有大量的内存可用。可以尝试将其设置为 80GB 到 90GB。
makefileCopy code
effective_cache_size = 90GB
checkpoint_completion_target：检查点完成目标通常设置为 0.9，表示在检查点过程中，需要将数据写入磁盘的时间占总时间的 90%。这是一个相对较合理的默认值，通常不需要更改。
makefileCopy code
checkpoint_completion_target = 0.9

wal_level = minimal
max_wal_senders = 0
commit_delay = 100
commit_siblings = 10
checkpoint_segments = 256
checkpoint_timeout = 30min

4、安装postgresql的定时插件和自动分区插件

4.1、pg_cron的介绍

pg_cron是一个用于PostgreSQL数据库的扩展，它允许用户在数据库中创建和管理定期执行的任务。它基于cron表达式语法，可以指定任务在特定的时间间隔内执行。

pg_cron的主要特点包括：

简单易用：pg_cron提供了一套易于理解和使用的命令和函数，使用户可以轻松地创建和管理定期执行的任务。

高度可定制：用户可以使用cron表达式语法精确地指定任务的执行时间和频率。可以指定分钟、小时、日期、星期等各种时间条件。

可靠性：pg_cron通过使用PostgreSQL的可靠性和容错机制，确保任务的执行不会受到数据库故障或崩溃的影响。

多任务支持：pg_cron允许用户创建多个定期执行的任务，并可同时管理它们。用户可以指定任务的优先级和执行顺序。

日志记录：pg_cron提供了详细的日志记录功能，记录每个任务的执行情况和结果。用户可以方便地查看和分析任务的执行历史。

4.2、pg_partman介绍

pg_partman是一个用于PostgreSQL数据库的分区管理扩展，它提供了一套工具和函数，帮助用户轻松地创建和管理分区表。分区表是将大型数据表分割成更小和更易管理的部分的一种技术。

pg_partman的主要特点包括：

简单易用：pg_partman提供了一套简单易懂的命令和函数，使用户可以方便地创建、维护和查询分区表。它自动处理分区的创建、删除、数据迁移等任务。

灵活的分区策略：pg_partman支持多种分区策略，如按范围、按列表、按哈希等。用户可以根据自己的需求选择最适合的分区策略。

高性能：pg_partman通过在查询时只访问相关的分区，可以提高查询性能。它还支持并行查询，可以更快地处理大量数据。

数据保护：pg_partman提供了数据保护功能，可以将分区表的数据备份到其他表或服务器，以避免数据丢失。

定期维护：pg_partman可以自动执行定期维护任务，如删除过期的分区、优化分区表的性能等。

4.3、安装配置

# 创建存放插件的目录
mkdir -p /app/pgsql/plugins
cd /app/pgsql/plugins
# 下载pg_cron包
wget https://gitee.com/mirrors/pg_cron/repository/archive/v1.4.2.zip
# 下载pg_partman包
wget https://gitee.com/mirrors/Pg-Partman/repository/archive/v4.7.1.zip
# 解压
unzip v1.4.2.zip
unzip v4.7.1.zip
# 安装
cd pg_cron/
make install  PG_CONFIG=/usr/pgsql-14/bin/pg_config
cd Pg-Partman/
make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config
# 配置
#修改postgresql.conf配置
vi /var/lib/pgsql/14/data/postgresql.conf

shared_preload_libraries = 'pg_partman_bgw, pg_cron'    # (change requires restart)
cron.use_background_workers= on

# 重启postgresql
systemctl restart postgresql14


# Create extension
CREATE SCHEMA partman; #在需要自动分区的库运行
CREATE EXTENSION pg_partman WITH SCHEMA partman; #在需要自动分区的库运行
CREATE EXTENSION pg_cron;  #在postgres库中运行

4.4、pg_cron和pg_partman的基础使用

#配置自动分区

--每天6点30分(GMT) 运行vacuum test  
test=> SELECT cron.schedule('30 6 * * *', 'VACUUM test');
 schedule 

--每分钟调用存储过程test()
test=> SELECT cron.schedule('process-new-events', '* * * * *', 'CALL test()');

--PostgreSQL重启更新pg_cron扩展
test=> SELECT cron.schedule('upgrade-pgcron', '@reboot', 'ALTER EXTENSION pg_cron UPDATE');

--每天0点0分(GMT)删除1年前的历史数据
test=> SELECT cron.schedule('delete-old-events','0 0 * * *', $$DELETE FROM test WHERE createtime < now() - interval '1 year'$$);

--查看定时任务  
test=> SELECT * FROM cron.job;

--清楚历史数据
--每天0点0分(GMT)删除7天前的数据
test=> SELECT cron.schedule('clean audit log', '0 0 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() – interval '7 days'$$);

--每周0点0分(GMT)调用存储过程test()，通过jobname名称执行upsert
test=> SELECT cron.schedule('process-new-events', '0 0 * * 0', 'CALL test()');

--通过jobname名称删除
test=> SELECT cron.unschedule('process-new-events');