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

二、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架构处理高并发问题。多线程架构在稳定性方面不如多进程，一个线程的问题可能会影响到同一进程中的其他线程。