|
|
因为工作的原因接触到了pgsql数据库，对PostgreSQL的体系和运维操作也有了一定的了解。PostgreSQL在官网上标称为世界上最先进的开源数据库，而MySQL在官网上标称的是世界上最流行的开源数据库，可见PostgresSQL还是比较高调的。
一、PostgreSQL初体验
首先是数据库的安装，PostgreSQL官网上不像MySQL那样提供了二进制包的下载，PostgreSQL主要提供了RPM包下载和源码下载，通常使用源码编译安装，安装步骤相对比较简单：- ######postgres单实例安装
- 1、官网下载源码包：https://www.postgresql.org/ftp/source/v14.8/
- 2、解压
- tar -xvf postgresql-14.0.tar.gz
- 3、新建postgres用户
- groupadd postgres
- useradd -g postgres postgres
- 4、安装依赖包
- yum install *zlib*
- yum install *libreadline*
- 5、编译安装
- ./configure
- make && make install
- 6、修改安装目录所属用户组
- chown -R postgres:postgres /usr/local/pgsql
- 7、新建postgresql的数据目录
- mkdir /pgdata
- chown postgres:postgres /pgdata
- 8、配置环境变量
- su - postgres
- vi ~/.bash_profile
- export PATH=$PATH:/usr/local/pgsql/bin
- 9、初始化数据库
- initdb -D /pgdata
- 10、启动数据库
- pg_ctl -D /pgdata start
- 11、验证是否可登录
- psql
- #####配置文件postgresql.conf
- #connection control
- listen_addresses = '*' #不限制连接ip
- max_connections = 1000
- superuser_reserved_connections = 10 #为超级用户保留的连接数
- #memory management
- shared_buffers = 512MB #推荐操作系统物理内存的1/4
- work_mem = 8MB #单个查询操作(例如排序或哈希表)可使用的最大内存
- maintenance_work_mem = 512MB #维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的最大的内存
- max_files_per_process = 24800
- effective_cache_size = 1GB #推荐操作系统物理内存的1/2
- #log optimization
- log_destination = 'csvlog'
- logging_collector = on
- log_directory = '/pgdata/logs' # 日志存放路径，提前规划在系统上创建好
- log_truncate_on_rotation = on
- #####访问控制文件pg_hba.conf加上下面这行
- host all all 0.0.0.0/0 md5
- #####主从同步配置
- 主库创建同步账号
- CREATE ROLE replica login replication encrypted password 'Temp##2022';
- 主库修改pg_hba.conf增加从库访问控制
- host replication replica 10.2.111.192/32 md5
- 主库重启
- pg_ctl -D /pgdata restart
- 停止从库
- pg_ctl stop -D /pgdata
- 清空从库数据文件
- rm -rf /pgdata/*
- 从库拉取主库数据文件
- pg_basebackup -h 10.2.111.192 -D /pgdata -p 5432 -U replica -Fp -Xs -Pv -R --checkpoint=fast
- 从库postgresql.conf文件添加主库信息
- primary_conninfo = 'host=10.2.111.193 port=5432 user=replica password=Temp##2022'
- 启动从库
- pg_ctl start -D /pgdata
- 主库验证主从同步正常
- select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication;
- 备库提升为主库
- pg_ctl promote -D /pgdata
- pg_controldata -D /pgdata | grep cluster #检查数据库状态，为in production，说明备库已提升为主库
二、PostgreSQL与MySQL对比
1. 开源协议
PostgreSQL采用的是宽松的BSD开源协议，基于开源PostgreSQL代码封装成的软件可以不公开源代码，它也不强制任何特定的版权声明，这使得它与许多其他开源和专有许可证兼容。基于这一点，很多国产数据库厂商采用了基于开源PG二次开发的数据库选型方案，华为的opengauss就是基于PG9版本，而vastbase、mogdb又是基于opengauss，也可以认为是PostgreSQL系列的产品。
MySQL采用的是较为严格的GPLv2开源协议，该协议具有强传染性，这意味着任何基于GPLv2 许可的代码进行修改或扩展，并且要分发的派生作品，也必须在GPLv2开源协议下发布，长期来看，具有传染性的GPLv2开源协议更能把成果回馈社区，带动社区的发展。国内基于MySQL的几款数据库TDSQL、GoldenDB在目前的国内的国产数据库份额中占有相当一部分比例，特别是在银行业。但是好像从来没有见过他们的开源版本，这个要较真起来很可能是违反开源协议的。
2. 表组织形式
PostgreSQL底层的表组织形式采用的是堆表（heap table），在堆表中数据的按数据插入的顺序进行排序，索引指向堆中行的指针（CTID），而不是实际的行数据。MySQL底层的表组织形式采用的是索引组织表（IOT），索引组织表中数据按主键或唯一索引进行排序，数据存储在主键索引的叶子节点中。对于基于主键索引查询的SQL语句，索引组织表不需要回表，性能更佳。
可能大家觉得堆表对于写入的性能会更高效，毕竟堆表中数据可以迅速地添加到表的末尾，不需要重新排序或调整数据，不需要像IOT那样频繁地对数据页进行合并或分裂来维护B+树结构，但其实生产环境中一个表可能会有多个索引，对于PostgreSQL的B+树索引的维护同样会带来很多开销。所以那种表组织形式更好还需要看业务场景，通常来说索引组织表更适合于OLPT场景，堆表在OLAP场景中表现更好。
3. MVCC实现机制
MVCC实现机制和更新方式是一个问题，PostgreSQL采用的是异地更新（out-of-place update），它没有undo表空间，PostgreSQL将历史元组和最新元组都保存在Heap表中，这种方式的好处是无须做回滚操作，因此PostgreSQL的堆表需要存储多个行版本数据。但是，假设事务不停地更新数据，那么一条元组就会产生大量的历史版本。其他事务在访问时需要查看这些元组是否满足可见性要求，这会增加读操作的时延，降低数据扫描的效率。为了防止数据膨胀，PostgreSQL数据库采用Vacuum机制清理表中的无效元组，PostgreSQL默认会打开auto vacuum机制。
MySQL、ORACLE采用的都是原地更新（in-place update），如果事务更新了一条元组，它可以“原地”更新这条元组，历史元组会以Undo日志记录的形式保存到回滚段中，这样就实现了元组的原地更新（Inplace Update）。当有并发事务需要访问历史元组时，可以从回滚段中“回滚”出这条元组，如果事务异常终止，则可以利用Undo日志将数据恢复。当所有可能访问历史元组的事务全部结束后，Undo日志中的历史元组就可以被清理。由于Undo日志被集中存储到某一个回滚段，所以清理也较为便捷。
4. 多进程VS多线程
PostgreSQL采用的是多进程架构。优点主要在稳定性方面：在于每个连接都有自己的进程，一个进程崩溃不太会影响其他的进程，并且每个进程都有自己的内存空间，这可以减少内存泄漏或其他问题对整个系统的影响；缺点在于资源消耗更高：由于每个进程都有自己的内存空间，这可能导致更高的内存使用，并且进程间的上下文切换和进程间的通信开销更大。
MySQL采用的是多线程架构。优点在于资源消耗更低：线程共享相同的内存空间，这通常导致更低的内存使用和更快的上下文切换。并且多线程可以更好的适用多核CPU架构处理高并发问题。多线程架构在稳定性方面不如多进程，一个线程的问题可能会影响到同一进程中的其他线程。
来源:https://www.cnblogs.com/coygfly/p/18043591
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！ |
|
