【Postrsql】postgresql的介绍、安装和使用

介绍

1.基本信息

PostgreSQL是一个功能强大的开源关系型数据库系统。经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。目前PostgreSQL可以运行在所有主流操作系统上，包括Linux、Unix和Windows。
PostgreSQL是完全的事务安全性数据库，支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。PostgreSQL数据库提供了丰富的接口，可以很方便地扩展它的功能，如可以在GiST框架下实现自己的索引类型，支持使用C语言写自定义函数、触发器，也支持使用流行的编程语言写自定义函数。PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Java、PL/Tcl等。

作为一种企业级数据库，PostgreSQL以它所具有的各种高级功能而自豪，像多版本并发控制(MVCC)、按时间点恢复(PITR)、表空间、异步复制、嵌套事务、在线热备、复杂查询的规划和优化以及为容错而进行的预写日志等。它支持国际字符集、多字节编码并支持使用当地语言进行排序、大小写处理和格式化等操作。它也在所能管理的大数据量和所允许的大用户量并发访问时间具有完全的高伸缩性。

2.Postgresql的优势

PostgreSQL数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92的查询语言，至少实现了SQL:2011标准中要求的179项主要功能中的160项（注：目前没有哪个数据库管理系统能完全实现SQL:2011标准中的所有主要功能）。

稳定可靠：PostgreSQL是唯一能做到数据零丢失的开源数据库。目前有报道称国内外有部分银行使用PostgreSQL数据库。

开源省钱： PostgreSQL数据库是开源的、免费的，而且使用的是类BSD协议，在使用和二次开发上基本没有限制。

支持广泛：PostgreSQL 数据库支持大量的主流开发语言，包括C、C++、Perl、Python、Java、Tcl以及PHP等。

PostgreSQL社区活跃：PostgreSQL基本上每3个月推出一个补丁版本，这意味着已知的Bug很快会被修复，有应用场景的需求也会及时得到响应。

3.对比mysql和oracle

3.1Postgresql VS Oracle

Oracle数据库是目前功能最强大的商业数据库；PostgreSQL则是功能最强大的开源数据库

PostgreSQL与Oracle的不同之处在于，PostgreSQL有更多支持互联网特征的功能。如PostgreSQL数据类型支持网络地址类型、XML类型、JSON类型、UUID类型以及数组类型，且有强大的正则表达式函数，如where条件中可以使用正则表达式匹配，也可以使用Python、Perl等语言写存储过程等。另外，PostgreSQL更小巧。

PostgreSQL可以在内存很小的机器上完美运行起来，如在512MB的云主机中；而Oracle数据库基本要在数GB的云主机中才可以运行起来。
Oracle安装包动辄几个GB以上级别；而PostgreSQL的安装包只有几十MB大小。
PostgreSQL在任何一个环境都可以轻松地安装。
Oracle数据库安装花费的时间是在小时级别；而PostgreSQL在分钟级别就可以完成安装。

3.2Postgresql VS MySQL

Postgresql和Mysql都是开源数据库

事务隔离之间的比较

事务隔离级别	postgresql	mysql
读未提交	无法读脏数据	有
读已提交	快照实现	快照实现
可重复读	有，无幻读，发生冲突时，牺牲其中一个事务	已实现，有幻读，悲观锁，因为Gap Lock问题，存在性能问题
可串行化	已实现，通过SSI实现，乐观锁，性能比较好	悲观锁，S2PL，性能不好，实用性比较差

持久化之间的比较

持久化技术	postgresql	mysql
事务的持久化	WAL日志	binlog和innodb的redo log
页断裂问题	full_page_writes	double write
检查块的一致性	checksum	checksum,db_lock_checking
事务同步提交	默认是同步synchronous_commit=on,session级别可以设置，更灵活	sync_binlog=1,innodb_flush_log_at_trx_commit=1全局参数

postgresql中的synchronous_commit

synchronous_commit:同步提交参数，控制事务提交后返回客户端是否成功的策略

选值类型:

on:1)没有开启备库，当wal日志真正刷新到磁盘永久存储后才会返回客户端事务已提交成功；2)开启了同步备库，设置synchronous_standby_names，必须要等事务日志刷新到本地磁盘，并且还要等远程备库页提交到磁盘才能返回客户端已经提交

off:写到缓存中就会向客户端返回提交成功，延迟写入磁盘，延迟时间最大为3倍的wal_writer_delay参数。

remote_write:当事务提交时，不仅要把wal刷新到磁盘，还需要等wal日志发送备库操作系统，但不需要等备库刷新到磁盘，因此如果备库此时发生实例终端下会有数据丢失，因为数据还在操作系统上，而如果操作系统故障，则此部分wal日志还没有来的及就会丢失，备库启动后还需要主库读取wal日志

local:当事务提交时，仅写入本地磁盘即可返回客户端事务提交成功，而不管是否同步备库

PS：如果没有设置同步备库，则on/remote_write/local都是一样的，仅等待事务刷新到本地磁盘

-- session局部设置，异步同步方式
set local synchronous_commit to OFF;

复制之间的比较

postgresql	mysql
支持物理复制和逻辑复制	仅支持逻辑复制
物理复制延迟小	逻辑复制延时大
大更新对复制延迟影响小	大更新对复制延迟的影响很大，很容易导致复制延迟
物理复制主备之间数据绝对一致	逻辑复制可能出现准备数据不一致情况
支持一主多从，支持联级复制，不支持双主架构	支持一主多从，支持联级复制，支持双主架构

postgresql和mysql的优势比较

1）postgresql相对于mysql的优势：

在SQL的标准实现上要比mysql完善，而且功能实现比较严谨
存储过程的功能支持要比mysql好，具备本地缓存执行计划的能力
对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强
pg主表采用堆表存放，mysql采用索引组织表，能够支持比mysql更大的数据量
PG的主备复制属于物理复制，相对于mysql基于的binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小

2）mysql相对于postgresql的优势：

innodb的基于回滚段实现的MVCC机制，相对pg新老数据存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发vacuum，会带来的多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。
mysql采用索引组织表，这种存储方式非常适合基于主键匹配的查询，删改操作，但是对表结构设置存在约束
mysql优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作
MySQL分区表的实现要优于pg的基于继承表的分区实现，主要体现在分区个数的达到上千万后的处理性能差异的较大
mysql的存储引擎插件化机制，使得它的应用场景更加广泛

安装

安装环境

官网：https://ftp.postgresql.org

1.下载安装包

https://ftp.postgresql.org/pub/source/v14.0/postgresql-14.0.tar.gz

2.解压编译

#安装依赖环境
[root@test1 opt]# yum -y install gcc gcc-c++ readline-devel zlib-devel make

#拖入安装包
[root@test1 opt]# rz -E
rz waiting to receive.
[root@test1 opt]# ls
postgresql-14.0.tar.gz  rh

#解压
[root@test1 opt]# tar xf postgresql-14.0.tar.gz 
[root@test1 opt]# ls
postgresql-14.0  postgresql-14.0.tar.gz  rh

#检测依赖环境
[root@test1 opt]# cd postgresql-14.0/
[root@test1 postgresql-14.0]# ls
aclocal.m4  configure.ac  doc             INSTALL   src
config      contrib       GNUmakefile.in  Makefile
configure   COPYRIGHT     HISTORY         README
[root@test1 postgresql-14.0]# ./configure 

#编译安装
[root@test1 postgresql-14.0]# make && make install
#会在 /usr/local/ 目录下生成pgsql目录

3.创建用户并更改目录的属主和属组

[root@test1 postgresql-14.0]# cd /usr/local/
[root@test1 local]# ls
bin  games    lib    libexec  sbin   src
etc  include  lib64  pgsql    share

#添加用户并设置密码
[root@test1 local]# useradd postgres
[root@test1 local]# passwd postgres 
Changing password for user postgres.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.

#修改属主和属组
[root@test1 local]# chown -R postgres:postgres pgsql/

4.初始化数据库

[root@test1 local]# cd /usr/local/pgsql/bin/
[root@test1 bin]# ls
clusterdb          pg_basebackup   pg_receivewal    pg_waldump
createdb           pgbench         pg_recvlogical   postgres
createuser         pg_checksums    pg_resetwal      postmaster
dropdb             pg_config       pg_restore       psql
dropuser           pg_controldata  pg_rewind        reindexdb
ecpg               pg_ctl          pg_test_fsync    vacuumdb
initdb             pg_dump         pg_test_timing
pg_amcheck         pg_dumpall      pg_upgrade
pg_archivecleanup  pg_isready      pg_verifybackup

#切换到postgres用户
[root@test1 bin]# su postgres 
#初始化数据库
[postgres@test1 bin]$ ./initdb /usr/local/pgsql/data