PostgreSQL（pg）数据库简介与安装

摘要：PostgreSQL是一个功能强大的开源对象关系型数据库系统，他使用和扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。

PostgreSQL简介

• 开源
• 对象关系型数据库
• 作为加州大学伯克利分校POSTGRES项目的一部分

PostgreSQL功能

数据类型

• 基本类型：Integer, Numeric, String, Boolean
• 结构类型：Date/Time, Array, Range, UUID
• 文档类型：JSON/JSONB, XML, Key-value(Hstore)
• 几何类型：Point, Line, Circle, Polygon
• 自定义类型：Composite, Custom Types
   

数据的完整性

• 唯一性，不为空
• 主键
• 外键
• 排除约束
• 显式锁定，咨询锁定
   

并发性，性能

• 索引
• 高级索引
• 复杂的查询计划期/优化器
• 交互
• 多版本并发控制(MVCC)
• 读取查询的并行化和构建B树索引
• 表分区
• Sql标准中定义的所有事物隔离级别，包括Serializable
• 即时表达式汇编(JIT)
   

可靠性，灾难恢复

• 预写日志（WAL）
• 复制：异步，同步，逻辑
• 时间点恢复（pitr），主动备用
• 表空间
   

安全性

• 身份验证：GSSAPI, SSPI, LDAP, SCRAM-SHA-256, 证书等
• 强大的访问控制系统
• 列和行级安全性
   

可扩展性

• 存储的功能和程序
• 程序语言：PL/PGSQL, Perl, Python (more)
• 外部数据包装器：使用标准SQL接口连接到其他数据库或流
• 许多提供附加功能的扩展，包括PostGIS
   

国际化，文本搜索

• 支持国际字符集，例如通过ICU校对
• 全文检索

 PostgreSQL优势

• PostgreSQL的稳定性极强
• 多年在GIS领域处于优势地位，实际上不止几何类型，PG中有大量的字典、数组、bitmap等数据类型
• PG的“无锁定”特性非常突出
• PG可以使用函数和条件索引，这使得PG数据库的调优非常灵活
• PG有极其强悍的SQL编程能力，有丰富的统计函数和统计语法支持，比如分析函数（Oracle的叫法，PG里面叫Window函数），还可以用多种语言来写存储过程，对于R的支持也很好
• PG的有很多中集群架构可以选择，plproxy可以支持语句级的镜像或者分片，slony可以进行字段级的同步设置，standby可以构建WAL文件级或者流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单
• 一般关系型数据库的字符串有限定长度8k左右，无限长Text类型的功能受限，只能作为外部大数据访问。而PG的TEXT类型可以直接方法，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库就可以省略了
• Mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate, 是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时pgsql还提供stream复制
• Pgsql对于numa架构的支持要比mysql强一些，比mysql对于读的性能要好些，pgsql提交可以完全异步，而mysql的内存表不够实用（表锁原因）

 PostgreSQL安装(CentOS7.5)

• 下载PostgreSQL 12.2安装包,下载地址: wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz
• 解压安装包: tar zxvf pg.tar.gz
• 安装依赖包,否则无法编译:
  • yum -y install readline readline-devel zlib-devel
• cd postgresql-12.2/
  • ./configure --prefix=/home/hadoop/spark/postgresql
    
    • --with-python 让PG可以使用PL/Python写存储过程
    •  --with-perl   让PG可以使用PL/Perl写存储过程
• make && make install
• 添加Postgres账号
  •  useradd postgres; 
  • echo 'password' | sudo passwd --stdin postgres
• 创建数据目录,并赋权给postgres管理
  • mkdir -p  /home/hadoop/spark/postgresql/data
  • chomod 777 /home/hadoop/spark/postgresql/data
• 添加环境变量 
  • vim ~/.bash_profile
    • export LD_LIBRARY_PATH=/home/hadoop/spark/postgresql/lib
    • export PATH=/home/hadoop/spark/postgresql/bin:$PATH
    • export PGDATA=/home/hadoop/spark/postgresql/data
  • source ~/.bash_profile
    
    
• 初始化数据库
  •   initdb /home/hadoop/spark/postgresql/data/
• 启动DB Server
  • 创建日志目录
    • mkdir -p /home/hadoop/spark/postgresql/pg_log

  • postgres -D /home/hadoop/spark/postgresql/data 
    > /home/hadoop/spark/postgresql/pg_log/start_`date +%Y-%m-%d-%H:%M:%S`.log 2>&1 &

• 关闭PostgreSQL Server
  • pg_ctl stop -D $PGDATA 
• 简单操作
  • 创建数据库(Linux终端)
    • createdb database1
  • 删除数据库(Linux终端)
    • dropdb database1
  • 进入数据库(Linux终端)
    • psql database1
  • 查看版本(pg终端,注意末尾加分号结束)
    • select version();
  • 查看当前日期
    • select current_date;
  • 一些命令帮助
    • \h  语法帮助
    • \q  退出psql命令行.
    • \d  查看当前数据库的表.
    • \d t 查看表t的表结构.
    • \c  切换数据库用户 Host Port
    • \?  查看psql帮助

PostgreSQL参数配置

常见参数

• listen_addresses = '127.0.0.1'  #监听的地址
• max_connections = 1024  #最大连接数
• port = 5432   #监听的端口
• shared_buffers = 4G  #类似Oracle SGA
• work_mem = 1G  #类似Oracle PGA,增加可以提高排序的效率
• huge_pages = try  #尝试使用Huge Page管理内存

 文件参数配置

• listen_addresses = '127.0.0.1' # 改为实际的ip地址
• port = 5432
• max_connections = 1024
• shared_buffers = 4GB
• work_mem = 1GB
• huge_pages = try
• dynamic_shared_memory_type = posix
• logging_collector = on
• log_directory = '/home/hadoop/spark/postgresql/pg_error_log'
• log_filename = 'pg-Port5432-%Y-%m-%d_%H%M%S.log'
• log_truncate_on_rotation = off
• log_rotation_age = 1d
• log_timezone = 'PRC'
• datestyle = 'iso, mdy'
• timezone = 'PRC'
• lc_messages = 'en_US.UTF-8'
• lc_monetary = 'en_US.UTF-8'
• lc_numeric = 'en_US.UTF-8'
• lc_time = 'en_US.UTF-8'
• default_text_search_config = 'pg_catalog.english'
• constraint_exclusion = 'partition'

 参数动态配置(PG终端)

• 查看全部参数
  • show all;
• 查看指定参数
  • show huge_pages;
• 修改参数
  • SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT } 
    
    • SESSION: 对当前会话生效,默认就是Session
    • LOCAL: 仅仅对当前Transaction有效,在ROLLBACK或者COMMIT之后则无效
    • set session shared_buffers = 20000;
    • 修改后重启服务参数生效
  • pg_settings方式修改，和SET的效果相同, 但提供更加详细的参数信息
    • select name, setting from pg_settings where name='shared_buffers';
    • update pg_settings set settings=20000 where name='shared_buffers';
    • 修改后重启服务参数生效
• 启动DB时指定参数
  • postgres -c para_name1='' -c para_name2=''
• 启动DB时指定参数文件及日志文件位置

  • postgres -c config_file=/home/hadoop/spark/postgresql/postgresql.conf 
    -D /home/hadoop/spark/postgresql/data 
    > /home/hadoop/spark/postgresql/pg_log/start_`date +%Y-%m-%d-%H:%M:%S`.log 2>&1 &