PostgreSQL初体验及其与MySQL的对比

因为工作的原因接触到了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和访问控制文件pg_hba.conf。修改完后通过pg_ctl命令重启PG

#####配置文件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  

PostgreSQL通过WAL日志进行主从同步,不同于MySQL通过binlog进行逻辑复制。并且PostgreSQL 9.x之后引入了主从的流复制机制,所谓流复制,就是备服务器通过tcp流从主服务器中同步相应的数据,主服务器在WAL记录产生时即将它们以流式传送给备服务器,而不必等到WAL文件被填充。主从复制搭建的具体步骤可以参考如下:

#####主从同步配置
主库创建同步账号
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的数据库逻辑存储架构中,采用的是database-schema-table这样一个三层的架构,和SQLServer一样,SQLServer默认的模式是dboPostgresSQL中默认的模式是public。其实大多数应用中,database-table这样两层的架构足够了,三层架构感觉还是复杂了一些。每个database下面有两个默认的系统schemapg_cataloginformation_schemapg_catalog下面的表主要描述的是pg实例的配置信息,information_schema下面的表主要描述的当前database的数据字典信息。比如要查询当前database下面所有的表可以通过information_schema.tables表查询。在用户管理方面,PostgreSQL中角色的概念影响较深,用户即角色,创建角色的时候指定login属性即代表创建同名的用户。

二、PostgreSQL与MySQL对比

1. 开源协议

PostgreSQL采用的是宽松的BSD开源协议,基于开源PostgreSQL代码封装成的软件可以不公开源代码,它也不强制任何特定的版权声明,这使得它与许多其他开源和专有许可证兼容。基于这一点,很多国产数据库厂商采用了基于开源PG二次开发的数据库选型方案,华为的opengauss就是基于PG9版本,而vastbasemogdb又是基于opengauss,也可以认为是PostgreSQL系列的产品。

MySQL采用的是较为严格的GPLv2开源协议,该协议具有强传染性,这意味着任何基于GPLv2 许可的代码进行修改或扩展,并且要分发的派生作品,也必须在GPLv2开源协议下发布,长期来看,具有传染性的GPLv2开源协议更能把成果回馈社区,带动社区的发展。国内基于MySQL的几款数据库TDSQLGoldenDB在目前的国内的国产数据库份额中占有相当一部分比例,特别是在银行业。但是好像从来没有见过他们的开源版本,这个要较真起来很可能是违反开源协议的。

2. 表组织形式

PostgreSQL底层的表组织形式采用的是堆表(heap table),在堆表中数据的按数据插入的顺序进行排序,索引指向堆中行的指针(CTID),而不是实际的行数据。MySQL底层的表组织形式采用的是索引组织表(IOT),索引组织表中数据按主键或唯一索引进行排序,数据存储在主键索引的叶子节点中。对于基于主键索引查询的SQL语句,索引组织表不需要回表,性能更佳。

可能大家觉得堆表对于写入的性能会更高效,毕竟堆表中数据可以迅速地添加到表的末尾,不需要重新排序或调整数据,不需要像IOT那样频繁地对数据页进行合并或分裂来维护B+树结构,但其实生产环境中一个表可能会有多个索引,对于PostgreSQLB+树索引的维护同样会带来很多开销。所以那种表组织形式更好还需要看业务场景,通常来说索引组织表更适合于OLPT场景,堆表在OLAP场景中表现更好。

3. MVCC实现机制

MVCC实现机制和更新方式是一个问题,PostgreSQL采用的是异地更新(out-of-place update),它没有undo表空间,PostgreSQL将历史元组和最新元组都保存在Heap表中,这种方式的好处是无须做回滚操作,因此PostgreSQL的堆表需要存储多个行版本数据。但是,假设事务不停地更新数据,那么一条元组就会产生大量的历史版本。其他事务在访问时需要查看这些元组是否满足可见性要求,这会增加读操作的时延,降低数据扫描的效率。为了防止数据膨胀,PostgreSQL数据库采用Vacuum机制清理表中的无效元组,PostgreSQL默认会打开auto vacuum机制。

MySQLORACLE采用的都是原地更新(in-place update),如果事务更新了一条元组,它可以“原地”更新这条元组,历史元组会以Undo日志记录的形式保存到回滚段中,这样就实现了元组的原地更新(Inplace Update)。当有并发事务需要访问历史元组时,可以从回滚段中“回滚”出这条元组,如果事务异常终止,则可以利用Undo日志将数据恢复。当所有可能访问历史元组的事务全部结束后,Undo日志中的历史元组就可以被清理。由于Undo日志被集中存储到某一个回滚段,所以清理也较为便捷。

4. 多进程VS多线程

PostgreSQL采用的是多进程架构。优点主要在稳定性方面:在于每个连接都有自己的进程,一个进程崩溃不太会影响其他的进程,并且每个进程都有自己的内存空间,这可以减少内存泄漏或其他问题对整个系统的影响;缺点在于资源消耗更高:由于每个进程都有自己的内存空间,这可能导致更高的内存使用,并且进程间的上下文切换和进程间的通信开销更大。

MySQL采用的是多线程架构。优点在于资源消耗更低:线程共享相同的内存空间,这通常导致更低的内存使用和更快的上下文切换。并且多线程可以更好的适用多核CPU架构处理高并发问题。多线程架构在稳定性方面不如多进程,一个线程的问题可能会影响到同一进程中的其他线程。

热门相关:明朝败家子   庶子风流   绝代疯少   上古传人在都市   三国之袁氏枭雄