PostgreSQL 本地Slave搭建步骤 PostgreSQL被业界誉为“最先进的开源数据库”，目前阿里云数据库PostgreSQL版具有NoSQL兼容，高效查询，插件化管理，安全稳定的特性。本文档介绍使用阿里云ECS搭建PostgreSQL主从架构的操作步骤。 适用对象 适用于熟悉ECS，熟悉Linux系统，熟悉PostgreSQL的阿里云用户。 基本流程 使用阿里云ECS搭建PostgreSQL主从架构的操作步骤如下： 选购ECS 实例 主节点安装配置 从节点安装配置 检测验证 步骤 1：选购ECS实例 搭建主从复制架构，需要选购2台专有网络类型的云服务器ECS实例，建议不分配公网IP，可按需购买弹性公网IP绑定至对应ECS实例，进行配置操作。后续使用您可以根据实际情况考虑配置升级或者架构调优变更。 步骤2：安装PostgreSQL 在阿里云服务器上安装PostgreSQL有2种方式 镜像部署 手动部署（源码编译安装/YUM安装） 本文档基于yum部署的方式，安装postgresql；您也可以在云市场基础环境中搜索筛选，使用镜像部署，更加快捷方便。 本文环境软件明细：CentOS 7.2 |PostgreSQL (9.5.6) 步骤3：PostgreSQL主节点配置 1、主节点上执行以下命令安装PostgreSQL。 # yum update -y # yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm -y # yum install postgresql95-server postgresql95-contrib -y # /usr/pgsql-9.5/bin/postgresql95-setup initdb # systemctl enable postgresql-9.5.service # systemctl start postgresql-9.5.service 2、主节点上创建进行主从复制的数据库账号，并设置密码及登录和备份权限。 # su - postgres # psql postgres=# CREATE ROLE replica login replication encrypted password 'replica'; CREATE ROLE postgres=# SELECT usename from pg_user ; usename ---------- postgres replica (2 rows) postgres=# SELECT rolname from pg_roles ; rolname ---------- postgres replica (2 rows) 3、修改pg_hba.conf，设置replica用户白名单。 # vim /var/lib/pgsql/9.5/data/pg_hba.conf 在IPv4 local connections段添加下面两行内容 host all all 192.168.1.0/24 md5 允许VPC网段中md5密码认证连接 host replication replica 192.168.1.0/24 md5 允许用户从replication数据库进行数据同步 4、修改postgresql.conf # vim /var/lib/pgsql/9.5/data/postgresql.conf 设置以下参数 wal_level = hot_standby 启用热备模式 synchronous_commit = on 开启同步复制 max_wal_senders = 32 同步最大的进程数量 wal_sender_timeout = 60s 流复制主机发送数据的超时时间 max_connections = 100 最大连接数，从库的max_connections必须要大于主库的 5、重启服务 # systemctl restart postgresql-9.5.service 步骤4：PostgreSQL从节点配置 1、安装postgres。 # yum update -y # yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm -y # yum install postgresql95-server postgresql95-contrib -y 2、使用pg_basebackup基础备份的工具制定备份目录。 # pg_basebackup -D /var/lib/pgsql/9.5/data -h 主节点IP -p 5432 -U replica -X stream -P Password: 30075/30075 kB (100%), 1/1 tablespace 3、添加并修改recovery.conf。 # cp /usr/pgsql-9.5/share/recovery.conf.sample /var/lib/pgsql/9.5/data/recovery.conf # vim /var/lib/pgsql/9.5/data/recovery.conf 设置以下参数。 standby_mode = on # 声明此节点为从库 primary_conninfo = 'host=主节点IP port=5432 user=replica password=replica' # 对应主库的连接信息 recovery_target_timeline = 'latest' # 流复制同步到最新的数据 4、修改postgresql.conf。 # vim /var/lib/pgsql/9.5/data/postgresql.conf 设置以下参数。 max_connections = 1000 # 最大连接数，从节点需设置比主节点大 hot_standby = on # 开启热备 max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间 wal_receiver_status_interval = 1s # 从节点向主节点报告自身状态的最长间隔时间 hot_standby_feedback = on # 如果有错误的数据复制向主进行反馈 5、修改数据目录属组属主。 # chown -R postgres.postgres /var/lib/pgsql/9.5/data 6、启动服务，设置开机自启。 # systemctl start postgresql-9.5.service # systemctl enable postgresql-9.5.service 步骤5：检测验证 1、主节点中可查看到sender进程。 # ps aux |grep sender postgres 2916 0.0 0.3 340388 3220 ? Ss 15:38 0:00 postgres: wal sender process replica 192.168.1.222(49640) streaming 0/F01C1A8 2、从节点中可查看到receiver进程。 # ps aux |grep receiver postgres 23284 0.0 0.3 387100 3444 ? Ss 16:04 0:00 postgres: wal receiver process streaming 0/F01C1A8 3、主库中可查看到从库状态。 replication=# select * from pg_stat_replication; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_locati on | flush_location | replay_location | sync_priority | sync_state ------+----------+---------+------------------+---------------+-----------------+------------- +-------------------------------+--------------+-----------+---------------+------------- ---+----------------+-----------------+---------------+------------ 2916 | 16393 | replica | walreceiver | 192.168.1.222 | | 49640 | 2017-05-02 15:38:06.188988+08 | 1836 | streaming | 0/F01C0C8 | 0/F01C0C8 | 0/F01C0C8 | 0/F01C0C8 | 0 | async (1 rows) 原文链接

PostgreSQL被业界誉为“最先进的开源数据库”，目前阿里云数据库PostgreSQL版具有NoSQL兼容，高效查询，插件化管理，安全稳定的特性。本文档介绍使用阿里云ECS搭建PostgreSQL主从架构的操作步骤。 适用对象 适用于熟悉ECS，熟悉Linux系统，熟悉PostgreSQL的阿里云用户。 基本流程 使用阿里云ECS搭建PostgreSQL主从架构的操作步骤如下： 选购ECS 实例主节点安装配置从节点安装配置检测验证 步骤 1：选购ECS实例 搭建主从复制架构，需要选购2台专有网络类型的云服务器ECS实例，建议不分配公网IP，可按需购买弹性公网IP绑定至对应ECS实例，进行配置操作。后续使用您可以根据实际情况考虑配置升级或者架构调优变更。 步骤2：安装PostgreSQL 在阿里云服务器上安装PostgreSQL有2种方式 镜像部署手动部署（源码编译安装/YUM安装）本文档基于yum部署的方式，安装postgresql；您也可以在云市场基础环境中搜索筛选，使用镜像部署，更加快捷方便。本文环境软件明细：CentOS 7.2 |PostgreSQL (9.5.6) 步骤3：PostgreSQL主节点配置 1、主节点上执行以下命令安装PostgreSQL。# yum update -y # yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm -y# yum install postgresql95-server postgresql95-contrib -y# /usr/pgsql-9.5/bin/postgresql95-setup initdb# systemctl enable postgresql-9.5.service# systemctl start postgresql-9.5.service2、主节点上创建进行主从复制的数据库账号，并设置密码及登录和备份权限。# su - postgres# psqlpostgres=# CREATE ROLE replica login replication encrypted password 'replica';CREATE ROLEpostgres=# SELECT usename from pg_user ;usename  ----------postgresreplica(2 rows)postgres=# SELECT rolname from pg_roles ;rolname  ----------postgresreplica(2 rows)3、修改pg_hba.conf，设置replica用户白名单。# vim /var/lib/pgsql/9.5/data/pg_hba.conf在IPv4 local connections段添加下面两行内容host    all             all             192.168.1.0/24         md5允许VPC网段中md5密码认证连接host    replication     replica         192.168.1.0/24         md5允许用户从replication数据库进行数据同步4、修改postgresql.conf# vim /var/lib/pgsql/9.5/data/postgresql.conf设置以下参数wal_level = hot_standby  启用热备模式synchronous_commit = on  开启同步复制max_wal_senders = 32     同步最大的进程数量wal_sender_timeout = 60s 流复制主机发送数据的超时时间max_connections = 100    最大连接数，从库的max_connections必须要大于主库的5、重启服务# systemctl restart postgresql-9.5.service步骤4：PostgreSQL从节点配置1、安装postgres。# yum update -y# yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm -y# yum install postgresql95-server postgresql95-contrib -y2、使用pg_basebackup基础备份的工具制定备份目录。# pg_basebackup -D /var/lib/pgsql/9.5/data -h 主节点IP -p 5432 -U replica -X stream -PPassword: 30075/30075 kB (100%), 1/1 tablespace3、添加并修改recovery.conf。# cp /usr/pgsql-9.5/share/recovery.conf.sample /var/lib/pgsql/9.5/data/recovery.conf# vim /var/lib/pgsql/9.5/data/recovery.conf设置以下参数。standby_mode = on  # 声明此节点为从库primary_conninfo = 'host=主节点IP port=5432 user=replica password=replica'  # 对应主库的连接信息recovery_target_timeline = 'latest' # 流复制同步到最新的数据4、修改postgresql.conf。# vim /var/lib/pgsql/9.5/data/postgresql.conf设置以下参数。max_connections = 1000             # 最大连接数，从节点需设置比主节点大hot_standby = on                   # 开启热备max_standby_streaming_delay = 30s  # 数据流备份的最大延迟时间wal_receiver_status_interval = 1s  # 从节点向主节点报告自身状态的最长间隔时间hot_standby_feedback = on          # 如果有错误的数据复制向主进行反馈5、修改数据目录属组属主。# chown -R postgres.postgres /var/lib/pgsql/9.5/data6、启动服务，设置开机自启。# systemctl start postgresql-9.5.service# systemctl enable postgresql-9.5.service步骤5：检测验证1、主节点中可查看到sender进程。# ps aux |grep senderpostgres  2916  0.0  0.3 340388  3220 ?        Ss   15:38   0:00 postgres: wal sender process replica 192.168.1.222(49640) streaming 0/F01C1A82、从节点中可查看到receiver进程。# ps aux |grep receiverpostgres 23284  0.0  0.3 387100  3444 ?        Ss   16:04   0:00 postgres: wal receiver process   streaming 0/F01C1A83、主库中可查看到从库状态。replication=# select * from pg_stat_replication;pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state ------+----------+---------+------------------+---------------+-----------------+-------------     +-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------2916 |    16393 | replica | walreceiver      | 192.168.1.222 |                 |       49640 |    2017-05-02 15:38:06.188988+08 |         1836 | streaming | 0/F01C0C8     | 0/F01C0C8   | 0/F01C0C8      | 0/F01C0C8       |             0 | async(1 rows)

PostgreSQL集群篇——1、PG环境安装准备标签PostgreSQL,PG,集群,DBA,架构师,PG安装,编译安装背景PostgreSQL官方文档中讲到了多种高可用、负载均衡和复制特性解决方案，如下图所示：特性共享磁盘故障转移文件系统复制预写式日志传送逻辑复制基于触发器的主-备复制基于语句的复制中间件异步多主控机复制同步多主控机复制最通用的实现NASDRBD内建流复制内建逻辑复制，pglogicalLondiste，Slonypgpool-IIBucardo 通信方法共享磁盘磁盘块WAL逻辑解码表行SQL表行表行和行锁不要求特殊硬件 •••••••允许多个主控机服务器 • •••无主服务器负载• •• • 不等待多个服务器• with sync offwith sync off• • 主控机失效将永不丢失数据••with sync onwith sync on • •复制体接受只读查询 with hot•••••每个表粒度 •• ••不需要冲突解决••• • •我们在集群环境中使用这些技术，首先需要考虑的是我们目前面临的是什么问题，例如我现在面临的就是高并发问题如何来解决，按照上述图表中我选择了流复制解决方案。在流复制解决方案中分为同步、异步两种，异步流复制通常采用的是基于wal日志来传送的方式进行，从节点通常比主节点要少一个wal日志块的数据，这给我们并发查询造成了影响，因此这里我们需要采用同步流复制解决方案，其采用的是数据流的方式，就像小溪一样，水一直流淌着，多条分支最终汇总到一处，同时接收处也在一直存储着。流复制是从2010年推出pg9.0版本以后开始的，其版本到目前经历的阶段如下：版本方式描述PostgreSQL9.0流式物理复制开始支持流式物理复制，用户可以通过流式复制构建只读备库PostgreSQL9.1同步流复制开始支持同步复制，只支持一个同步流复制节点，同步流复制能保证数据的0丢失PostgreSQL9.2级联流复制开始支持联流复制，备库下面还可以再连接备库，形成级联架构PostgreSQL9.2流式虚拟备库开始支持虚拟备库，即备库中没有数据文件，只包含wal文件。PostgreSQL9.4逻辑复制开始支持逻辑复制，逻辑复制可以应对部分表复制的功能。PostgreSQL9.6同步流复制改版同步流复制允许多个备用服务器以提高可靠性。PostgreSQL10 使用发布/订阅进行逻辑复制PostgreSQL11-13 持续优化本次我们将采用PostgreSQL13.1版本进行搭建同步流复制环境，数据库安装我们采用了编译安装，如不清楚怎样安装，请查看之前的文章《PostgreSQL集群篇——1、PG环境安装准备》环境PostgreSQL集群篇整体采用3台虚拟机进行搭建，相关情况如下：服务器IPCPU内存存储作用10.28.1.1814核8G200G主master节点，10.28.1.1824核8G200G从slave节点，10.28.1.2104核8G200GVIP节点，虚拟IP 产品简介内容：安装脚本：1、设置服务器使用的时区并校准时间 sudo tzselect  #输入 4 9 1 1  sudo cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime  sudo apt-get update  sudo apt-get install ntpdate -y  sudo ntpdate us.pool.ntp.org2、安装必要的编译环境 sudo apt-get install gcc make libreadline-dev zlib1g-dev -y3、创建好准备安装的目录并创建pg基础用户，并将pg基础目录授权给该用户sudo mkdir /pg  # 授权pg用户目录操作权限，这里在安装完成后权限应是postgres用户  sudo groupadd postgres  sudo useradd -g postgres postgres -s "/bin/bash" -m  sudo passwd postgres  sudo chown -R postgres:postgres /pg这里我们创建用户的同时对其进行设置了密码，设置一个密码，安全性上更有保障，同时默认密码随机，如果一直使用sudo su则会缺少环境变量，后续会带来不必要的麻烦。同时后续我们切换用户会使用su 直接切换不会增加sudo。4、从官网下载PostgreSQL最新稳定版本，这里使用编译安装进行可控式安装源码下载 su postgres  mkdir /pg/install  cd /pg/install  wget https://ftp.postgresql.org/pub/source/v13.1/postgresql-13.1.tar.gz5、开始进行编译 tar -zxf postgresql-13.1.tar.gz  cd postgresql-13.1/  ./configure --prefix=/pg  # 编译主程序  make  # 编译文档与contrib  make world  # 安装主程序  make install  # 安装全部  make install-world  # 安装完成后清除源码中编译生成的文件，将其还原为发布时的源码包。便于后续时间需要使用时再次编译  make distcleanmake 编译主程序make world 在编译主程序的同时将其文档与contrib共享库包全部编译make install 安装主程序make install-world 安装文档与contrib共享库，当我们把所有共享库都安装上并不代表数据库已经可用这些，而是需要我们在库中根据需要进行create extension启用插件。因此这里我们直接将原带的插件，对其性能不会6、设置环境变量 sudo vim /etc/profile  #在文件最底部增加  export PGHOME=/pg  export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH  export PATH=$PGHOME/bin:$PATH # 主程序目录  export PGDATA=$PGHOME/data # 数据存储目录  export MANPATH=$PGHOME/share/man:$MANPATH # 共享包库  export LANG=en_US.utf8  ## wq 保存  sudo reboot这里我们在设置完成系统环境变量后直接进行了重启，而没有使用当前会话生效命令（ source /etc/profile）是因其下面我们要多次切换用户，这时需要每次都执行一次过于麻烦。索性直接重启一次，所有会话下直接生效。7、开始进行初始化数据库wsu postgres  pg_ctl initdb备注：这里我们进入postgres用户后直接使用了pg_ctl命令，是因为上面设置了系统环境变量PATH，其次我们切换用户时使用的是su，如果使用sudo su 进入用户会导致系统环境变量丢失，如果想使用该命令需要再去单独设置root用户的环境变量。执行到这里后我们可以使用pg_ctl start 与pg_ctl stop 进行测试数据库是否正常启动停止，下面我们将开始对其服务器自启动进行设置，增加到日常linux服务管理中。8、创建日志库，并添加系统启动项 cd /pg  mkdir logfiles9、进行编写服务器启动服务exit # 退出当前的postgres用户 sudo vim /etc/systemd/system/postgresql.service文件内容如下： [Unit]  Description=PostgreSQL database server  Documentation=man:postgres(1)  After=network.target    [Service]  Type=forking  User=postgres  Group=postgres  Environment=PGSTARTTIMEOUT=300  Environment=PGDATA=/pg/data  ExecStart=/pg/bin/pg_ctl start -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT} -l /pg/logfiles/pg_service_log  ExecStop=/pg/bin/pg/pg_ctl stop -D ${PGDATA} -s -m -l /pg/logfiles/pg_service_log  ExecRestart=/pg/bin/pg_ctl restart -D ${PGDATA} -s -l /pg/logfiles/pg_service_log  ExecReload=/pg/bin/pg_ctl reload -D ${PGDATA} -s -l /pg/logfiles/pg_service_log  KillMode=mixed  KillSignal=SIGINT  TimeoutSec=360    [Install]  WantedBy=multi-user.target:wq #保存文件  sudo systemctl daemon-reload10、测试使用postgresql服务进行启动数据库systemctl start postgresql  systemctl restart postgresql  systemctl stop postgresql  systemctl restart postgresqlps -aux|grep postgres  su postgres  tail -1000f /pg/logfiles/pg_service_log # 查看服务启停日志至此集群环境下我们需要使用的基础pg环境已准备就绪，下一篇我们将开始对其这两台进行搭建流复制集群设置。本文声明知识共享许可协议 本作品由 cn華少 采用 知识共享署名-非商业性使用 4.0 国际许可协议 进行许可。

一.背景 众所周知 PostgreSQL 的物理复制以稳定可靠著称，目前经成为默认的高可用方案。但较少有人关注到，PostgreSQL 的逻辑复制经过几个大版本迭代，已异常的强大。它结合了很多黑科技，本文就一一介绍给大家。 二. MVCC 和 snapshot 大家都知道，PostgreSQL 使用 MVCC 和 WAL 两项技术实现 ACID 特性。 MVCC 即多版本并发控制，简单的说，是数据库中的同一份有效数据，会同时保留多个修改版；用 snapshot 即快照，来定义一个事务能看到哪些版本的数据。 2.1 导出一个快照的唯一标识，即 snapshotid 一个连接进行查询或修改数据时会开启一个事务（只读事务或读写事务），事务的可见性由事务中的快照决定。 也就是说，两个事务的快照相同，那么看到的数据是完全相同的。 PostgreSQL 9.3 开始，支持函数：pg_export_snapshot，它能返回一个快照的唯标识，便于其他事务也使用相同的快照查看数据。 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT pg_export_snapshot(); pg_export_snapshot -------------------- 000003A1-1 (1 row) 详见：1.如何导出一个快照 Table 9-64. Snapshot Synchronization Functions 2.2 启动对应快照的事务 从 PostgreSQL 9.3 开始，支持开启一个事务到一个指定的的快照版本。 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION SNAPSHOT '000003A1-1'; 2.开启指定一个存在的快照的详细说明 SET TRANSACTION 2.3 利用导出快照实现严格一致的并行逻辑备份 如果使用多个连接并行的备份数据库中的不同的表，在数据库还在同时修改数据库的状态下是无法获得一个完全一致的数据的。 这时，使用上述连个特性，我们可以在 PostgreSQL 9.3 和以上版本做到拿到一份一致版本的数据。 大致的步骤是 1. 备份程序主进程连接数据库，开启只读事务，并使用函数 pg_export_snapshot 导出对应的snapshotid。 2. 开启 N 个子进程，连接数据库，分别设置主进程中获得的 snapshotid，开始并行备份数据。 实际上 PostgreSQL 自带的逻辑备份和恢复工具在 9.3 和之后的版本就已经这么做了 1) pg_dump 的并行备份开关 -j -j njobs --jobs=njobs Run the dump in parallel by dumping njobs tables simultaneously. This option reduces the time of the dump but it also increases the load on the database server. You can only use this option with the directory output format because this is the only output format where multiple processes can write their data at the same time. 注意：备份的并行粒度是以表为单位，且需要以 directory 模式备份数据，也就是备份数据到一个目录中，目录中每个表的数据都用一个或多个文件存放。 该模式加上备份压缩开关，是效率最高的逻辑备份方式。 3.并行逻辑备份 pg_dump 2) pg_resotre 也支持并行恢复 -j number-of-jobs --jobs=number-of-jobs Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine. 4.并行逻辑恢复 pg_restore 三.严格一致的全量+增量数据迁移 然而，事情到这里还没有结束，如果用户的数据较多（例如TB级或以上），上述存全量的数据迁移方法对业务造成的影响也会很大（业务停机时间较长）。 PostgreSQL 9.4 开始，支持逻辑复制特性，能和上述特性完美的结合起来，做到接近 0 停机时间的业务迁移。 下面，让我慢慢道来。 3.1 逻辑复制原理 PostgreSQL 逻辑复制（logical replication）,原理是类似 MySQL binlog 同步，PostgreSQL 会启动一组后端进程，把数据库产生的 redo 日志（物理日志），解析成逻辑日志，并发送给客户端。转换成的逻辑数据可以通过插件的方式自定义。这个模块叫 logical decoding。 这份逻辑数据是灵活，完全由用户自定义，用户可以根据自己的需求定制 存放一个比较简单的中间数据结构，做 PostgreSQL 到 PostgreSQL 间部分对象的数据迁移 解析成标准的 SQL 语句，做 PostgreSQL 到异构数据库的增量数据迁移或持续同步 5. 逻辑流复制介绍 3.2 全量加增量的结合 PostgreSQL 利用上述技术点，把一致的全量数据迁移和增量逻辑复制结合起来，做到在保证严格的数据一致性基础上的，接近 0 停业务时间的数据迁移服务。 数据迁移的步骤： 1. 迁移程序主进程使用 CREATE_REPLICATION_SLOT 创建用户增量的逻辑 SLOT，记录数据同步的启始一致点（snapshotid）。这是数据一致性迁移的关键点，也是全量迁移和增量迁移的重要衔接点。 注意： 1） MySQL 早期版本中，使用 binlog 中一个 unix 时间做数据一致的位点，后期版本被 GTID 替换。 2） CREATE_REPLICATION_SLOT 不是一个 SQL 语句，是一个前后端的协议命令，它的返回结果集第四列是一个 snapshotid。snapshotid 的获取方法可以参考 pg_recvlogical –create-slot 的实现。 2. 客户端开启 N 个子进程，连接到数据库，设置到主进程中获得的 snapshotid，开始并行数据迁移。 3. 于此同时，为了避免数据库的 redo 日志在主库的堆积，客户端启动一个连接拉取增量日志，并解析成能够被目的数据库识别的数据，且持久化保存。 4. 当任务 2 中的所有的全量同步任务完成之后，开始同步全量同步期间产生的增量数据（任务3中）。直到现存的增量数据都同步完，追平和主库的数据差异。 5. 当数据接近追上时，停止主库写数据。 6. 当数据完全追上后，开始数据校验。 7. 数据校验完成后，应用开始连接目的端数据库，数据迁移完成,开始正常业务。 6. 创建逻辑复制SLOT 7. pg_recvlogical 总结 上述该方案能做到严格数据一致的数据同步，且全量数据迁移和增量数据迁移无缝衔接。 上述解决方案，我们可以用来完成数据迁移和数据长时间同步这两类常用的数据基本操作。但是，它们关注的实现重点不同。 1）数据迁移任务需要做到尽量的高效转移数据，迁移过程中可以不能做 DDL； 2）长时间的数据同步需要保存更多的状态信息，能做到任务的长期可运维，需要做到 DDL 的变更。

一.背景 众所周知 PostgreSQL 的物理复制以稳定可靠著称，目前经成为默认的高可用方案。但较少有人关注到，PostgreSQL 的逻辑复制经过几个大版本迭代，已异常的强大。它结合了很多黑科技，本文就一一介绍给大家。 二. MVCC 和 snapshot 大家都知道，PostgreSQL 使用 MVCC 和 WAL 两项技术实现 ACID 特性。 MVCC 即多版本并发控制，简单的说，是数据库中的同一份有效数据，会同时保留多个修改版；用 snapshot 即快照，来定义一个事务能看到哪些版本的数据。 2.1 导出一个快照的唯一标识，即 snapshotid 一个连接进行查询或修改数据时会开启一个事务（只读事务或读写事务），事务的可见性由事务中的快照决定。 也就是说，两个事务的快照相同，那么看到的数据是完全相同的。 PostgreSQL 9.3 开始，支持函数：pg_export_snapshot，它能返回一个快照的唯标识，便于其他事务也使用相同的快照查看数据。 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT pg_export_snapshot(); pg_export_snapshot -------------------- 000003A1-1 (1 row) 详见：1.如何导出一个快照 Table 9-64. Snapshot Synchronization Functions 2.2 启动对应快照的事务 从 PostgreSQL 9.3 开始，支持开启一个事务到一个指定的的快照版本。 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION SNAPSHOT '000003A1-1'; 2.开启指定一个存在的快照的详细说明 SET TRANSACTION 2.3 利用导出快照实现严格一致的并行逻辑备份 如果使用多个连接并行的备份数据库中的不同的表，在数据库还在同时修改数据库的状态下是无法获得一个完全一致的数据的。 这时，使用上述连个特性，我们可以在 PostgreSQL 9.3 和以上版本做到拿到一份一致版本的数据。 大致的步骤是 备份程序主进程连接数据库，开启只读事务，并使用函数 pg_export_snapshot 导出对应的snapshotid。 开启 N 个子进程，连接数据库，分别设置主进程中获得的 snapshotid，开始并行备份数据。实际上 PostgreSQL 自带的逻辑备份和恢复工具在 9.3 和之后的版本就已经这么做了 1) pg_dump 的并行备份开关 -j -j njobs --jobs=njobs Run the dump in parallel by dumping njobs tables simultaneously. This option reduces the time of the dump but it also increases the load on the database server. You can only use this option with the directory output format because this is the only output format where multiple processes can write their data at the same time. 注意：备份的并行粒度是以表为单位，且需要以 directory 模式备份数据，也就是备份数据到一个目录中，目录中每个表的数据都用一个或多个文件存放。 该模式加上备份压缩开关，是效率最高的逻辑备份方式。3.并行逻辑备份 pg_dump 2) pg_resotre 也支持并行恢复 -j number-of-jobs--jobs=number-of-jobsRun the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine.4.并行逻辑恢复 pg_restore 三.严格一致的全量+增量数据迁移 然而，事情到这里还没有结束，如果用户的数据较多（例如TB级或以上），上述存全量的数据迁移方法对业务造成的影响也会很大（业务停机时间较长）。 PostgreSQL 9.4 开始，支持逻辑复制特性，能和上述特性完美的结合起来，做到接近 0 停机时间的业务迁移。 下面，让我慢慢道来。 3.1 逻辑复制原理 PostgreSQL 逻辑复制（logical replication）,原理是类似 MySQL binlog 同步，PostgreSQL 会启动一组后端进程，把数据库产生的 redo 日志（物理日志），解析成逻辑日志，并发送给客户端。转换成的逻辑数据可以通过插件的方式自定义。这个模块叫 logical decoding。 这份逻辑数据是灵活，完全由用户自定义，用户可以根据自己的需求定制 存放一个比较简单的中间数据结构，做 PostgreSQL 到 PostgreSQL 间部分对象的数据迁移 解析成标准的 SQL 语句，做 PostgreSQL 到异构数据库的增量数据迁移或持续同步 逻辑流复制介绍 3.2 全量加增量的结合 PostgreSQL 利用上述技术点，把一致的全量数据迁移和增量逻辑复制结合起来，做到在保证严格的数据一致性基础上的，接近 0 停业务时间的数据迁移服务。 数据迁移的步骤： 迁移程序主进程使用 CREATE_REPLICATION_SLOT 创建用户增量的逻辑 SLOT，记录数据同步的启始一致点（snapshotid）。这是数据一致性迁移的关键点，也是全量迁移和增量迁移的重要衔接点。注意： 1） MySQL 早期版本中，使用 binlog 中一个 unix 时间做数据一致的位点，后期版本被 GTID 替换。 2） CREATE_REPLICATION_SLOT 不是一个 SQL 语句，是一个前后端的协议命令，它的返回结果集第四列是一个 snapshotid。snapshotid 的获取方法可以参考 pg_recvlogical –create-slot 的实现。 客户端开启 N 个子进程，连接到数据库，设置到主进程中获得的 snapshotid，开始并行数据迁移。 于此同时，为了避免数据库的 redo 日志在主库的堆积，客户端启动一个连接拉取增量日志，并解析成能够被目的数据库识别的数据，且持久化保存。 当任务 2 中的所有的全量同步任务完成之后，开始同步全量同步期间产生的增量数据（任务3中）。直到现存的增量数据都同步完，追平和主库的数据差异。 当数据接近追上时，停止主库写数据。 当数据完全追上后，开始数据校验。 数据校验完成后，应用开始连接目的端数据库，数据迁移完成,开始正常业务。 创建逻辑复制SLOT pg_recvlogical 总结 上述该方案能做到严格数据一致的数据同步，且全量数据迁移和增量数据迁移无缝衔接。 上述解决方案，我们可以用来完成数据迁移和数据长时间同步这两类常用的数据基本操作。但是，它们关注的实现重点不同。 1）数据迁移任务需要做到尽量的高效转移数据，迁移过程中可以不能做 DDL； 2）长时间的数据同步需要保存更多的状态信息，能做到任务的长期可运维，需要做到 DDL 的变更。

创建不用执行sudo可绿色的PostgreSQL 原文键接 感谢原文作者给出的精彩方法 PostgreSQL安装的时候(仅指Linux和MacOSX)，往往会安装系统目录中，这样做有几点不方便。 安装多个版本的时候会冲突，尤其是想安装测试版或不同的小版本的时候。 安装路径散落在系统的好几个目录中，记起来不方便。 因为安装到了系统目录，我们有时候需要用sudo执行或者需要用su 切换到postgres用户权限下去执行。 尤其是pg的开发人员，可能需要安装不同的版本，或者要自己统计不同的时间的测试版，如何快速在的不同的测试版之间进行启停数据库，有没有好办法解决这两个问题呢？ 下面我们演示在Mac OS X 和Linux下解决这些问题的办法。在Mac OS X下(译者亲测 10.11.4) > cd /tmp > mkdir pg-build > wget 下载对应的源码包 > cd postgresql源码目录中 > ./configure --prefix=/tmp/pg-build > make > make install 在ubuntu下(译者亲测ubuntu16.04) > cd /tmp > mkdir pg-build > wget 下载对应的源码包 > cd postgresql源码目录中 > ./configure --prefix=/tmp/pg-build --disable-rpath > export LD_RUN_PATH='$ORIGIN/../lib' > make > make install 然后，我们对编译的动态支持库文件进行谳整引用重定位在Mac OS X下 find /tmp/pg-build/bin -type f | \ xargs -L 1 install_name_tool -change \ /tmp/pg-build/lib/libpq.5.dylib \ '@executable_path/../lib/libpq.5.dylib' 然后就可以了。 > cd $HOME > cp -R /tmp/pg-build pg > mkdir data > ./pg/bin/initdb data > ./pg/bin/pg_ctl -D data start 现在，你的pg就运行起来了，并且你可以把这个目录任意改名，放到你自己的任意目录，都可以运行了。 我一般测试多个版本的pg,都在自己的目录下建立pg931,pg943,pg952,pg6b1这样的目录，想用哪个用哪个。很方便。 为了自动化的实现这个功能，我写了一个小脚本，可以很方便的编译、运行不同时期源码的数据库。代码很短，自己根据自己的需要改改，放到自己的PATH目录下，就会很方便了。 [git-hub] https://github.com/lvbuwei/mypgscript

  10月15日，由山东华鲁科技咨询顾问高强老师在“DBA+济南群”进行了一次关于PostgreSQL数据库初体验的线上主题分享。小编特别整理出其中精华内容，供大家学习交流。   嘉宾简介    高强，“DBA+济南群”联合发起人。现就职于山东华鲁科技发展股份有限公司。擅长Oracle、AIX、Linux、PostgreSQL和DB2等产品的实施、运维和故障处理。曾是一名存储工程师，负责实施存储、双机和备份等产品，在接触到数据库产品后，一发不可收拾的投入了DBA的浩瀚大军中，在众多的项目中，积累了较丰富的数据库、主机以及集群的运维经验。   曾参与多个省级政府单位项目的实施和运维工作，具有丰富的运维经验。经常在个人博客积极分享在项目中遇到的问题解法、钻研的技术、收获的感悟和宝贵资料。热衷于与网友交流技术、行业资讯和从业感悟等想法，希望为行业的发展和繁荣贡献自己微博的力量。   演讲实录    今天的内容是关于一个用萌萌的大象作为Logo的数据库产品，他的名字叫做PostgreSQL，一般都简称他为“PG”。   “PostgreSQL是数据库世界里的"锤子"。它既广为人知，又容易获得，还很坚固，如果你抡得够猛，它所能解决的问题数量惊人。如果不了解这个最常用的工具，你就不可能成为建筑专家。” 摘自《Seven Databases In Seven Weeks》 第2章PostgreSQL       PostgreSQL的起源   1977年，Michael Stonebraker开始和学生一起做关系型数据相关的研究并成立了项目Ingres，“Interactive Graphics and Retrieval System”的缩写，是PostgreSQL数据库的前身。   Stonebraker后来成立了Ingres Corporation公司， 开始做一个叫”post-INGRES”的项目，尝试去解决一些原有关系模型的限制，后来被称作POSTGRES。   POSTGRES 项目是由防务高级研究项目局（DARPA），陆军研究办公室（ARO），国家科学基金（NSF）， 以及 ESL, Inc 共同赞助的。   PostgreSQL是完全由社区驱动的开源项目，由全世界超过1000名贡献者所维护。它提供了单个完整功能的版本，而不像MySQL那样提供了多个不同的社区版、商业版与企业版。PostgreSQL基于自由的BSD/MIT许可，组织可以使用、复制、修改和重新分发代码，只需要提供一个版权声明即可。   可靠性是PostgreSQL的最高优先级。它以坚如磐石的品质和良好的工程化而闻名，支持高事务、任务关键型应用。PostgreSQL的文档非 常精良，提供了大量免费的在线手册，还针对旧版本提供了归档的参考手册。PostgreSQL的社区支持是非常棒的，还有来自于独立厂商的商业支持。      使用限制   PostgreSQL在数据存储方面所能支持的容量是相当大的。        体系架构   像绝大多数数据库产品一样，PostgreSQL也是由数据库实例和相关文件组成，其中实例包括数据库的一些各负其职的进程与内存结构组成，数据库的文件也包含控制文件、WAL文件、数据文件和各种配置文件。   PostgreSQL的基本结构我借用了网上的图并做了标注：     下面我们用实际操作演示的形式简单介绍一下PostgreSQL数据库的日常维护操作和高可用集群方案：   1【实验1】创建数据库   实验内容：在指定路径路径 /pgdata/music下创建数据库music，后续实验都会在该库中进行。   由于现如今存储价格成本降低，数据量增长迅速，所以很多项目中都会采用外挂存储的架构，因此存放数据的核心地带一般都会选择放在外置存储上。所以一般选择自定义的方式指定路径创建数据库。   PG中创建数据库之前需要先在操作系统中建好文件系统路径，然后在指定路径上创建表空间，最后创建数据库即可，命令非常简单。通过本次的实验你可以看到，PG的命令语法与绝大多数的数据库都很相似，他支持标准的SQL，自己的维护命令也有很强的可读性，直观、易理解。     2【实验2】CRUD（增查改删）   实验内容： 在刚才创建的music数据库中创建表，命名summary，并插入四条数据，每条数据的ID都是1，然后修改ID，让每条数据的ID唯一。   CRUD的操作在后续的Hot_standby和PGPOOL中会陆续出现，删除操作在PGPOOL试验中体现。     3【实验3】数据库配置变更   对于DBA来说，在日常数据库维护中，可能需要根据业务需求和客户需求的变化调整数据库参数配置，PostgreSQL的参数调整相对较方便，其配置文件postgresql.conf中有对各个参数的注释解释。   基本上参数配置后有立刻生效和重启生效两种情况。   查看和修改参数的命令非常简单，查看参数可以在数据库中用如 show work_mem 查看，还可以看配置文件，也可以查pg_settings表；修改参数也可以直接调整配置文件，也可以通过命令如 set work_mem = '16MB'生效。   Set仅对当前会话生效，也就是说我们可以对个别的操作或者事务进行定制的配置，如临时给其分配大一点的缓存以供排序等操作。   建议：自己尝试体验一下开启归档模式和归档路径的变更调整，实验如果遇到问题可随时与我沟通交流。   实验内容： 调整work_mem，该参数立刻生效； 调整shared_buffers，该参数需重启生效。   知识补充： PostgreSQL数据库的关闭和重启操作命令： 停库：pg_ctl stop -m fast ，“-m fast”相当于Oracle中的immediate。 启库：pg_ctl start     4性能监控：   PostgreSQL数据库有很多监控软件，有很多类似Oracle AWR的工具，如pgstatspack和pg_statsinfo等等，还有很多图形化界面的实时监控工具。这些工具的数据来源都是PostgreSQL本身丰富的性能表，都是状态收集进程收集并写进去的。   实验内容： 1.查看数据库的相关性能的表，如pg_lock、pg_stat_activity等，远程发起查询并查看视图中的信息同步情况。 2.使用pgstatspack 做数据库快照并生成性能报告，并浏览性能报告的信息。     5备份恢复：   PostgreSQL的备份恢复工具有很多，如PGDUMP、PG_RMAN和BARMAN等。在本次试验中，我们演示pgdump的备份和恢复功能。   Pgdump在较新的版本中可以实现并行备份，以充分利用计算机硬件资源提高备份效率，具体内容可参照我的博文：《PostgreSQL数据库备份之pg_dump并行备份 》   实验内容： 备份数据库后删除刚才实验中创建的表summary，恢复数据库后验证数据是否可用。     6Hot Standby   PostgreSQL的Hot Standby功能可以实现主从数据库的实时同步，可用于容灾、备份、读写分离分担性能压力和数据分析等场景。此实验中使用了Streaming Replication(流复制)方式，同步速度比较快。Hot Standby也支持一主多从的架构，即一台主服务器，多台从服务器，这样的话，可以有多台设备保证数据完整性，也可以在读写分离的场景中有获得更好的的读性能。   PG也可以跟后面要讲到的PGPOOL结合，做一个高可用（故障切换）或者是负载均衡（高性能）的集群。     实验内容： 演示过程中左边的窗口里的库为主库，主机名dbserver1;右边的为从库，主机名dbserver2。窗口上方的标签变为蓝色则表示当前操作窗口。 1.在主库中创建测试表test，备库中验证； 2.主库中插入新的记录，备库中验证。     7PGPOOL   PGPOOL是一个中间件工作在多个PostgreSQL和客户端之间，是PostgreSQL的集群方案之一。它具有连接池、复制、负载均衡、并行查询和高可用等功能。   其实我们刚才一直操作的环境就是在PGPOOL下面的PostgreSQL数据库中操作，当时没有用到PGPOOL的功能是因为我们直接连接的数据库而不是PGPOOL。连接PGPOOL的话一般默认的端口是9999，而连接数据库的话，默认的端口是5432.   PGPOOL的架构图如下： （图中用了2台PG做集群，实际上还可以增加数据库服务器的数量；本次试验中PGPOOL是安装在了主库所在的系统中，即只用了2台服务器搭建环境）：     实验内容： 1.启动PGPOOL： pgpool -n ，可以从输出中看到使用了9999作为PGPOOL的端口并且提供了很多进程以供客户端连接进来；   2.用我自己笔记本上的客户端连接PGPOOL(而不是直接连数据库，可以在图形界面窗口左上方看到连接信息的端口号是9999而不是5432），然后做CRUD（增查改删）操作，并检查主、从数据库的数据同步情况。（依然是左边命令行窗口为主库，右边为从库）   Q&A精选         疑难解答      【问题1】：   我一直在使用PG数据库，在做主备双机的时候有些问题：主备数据库不能在故障时候自动切换，必须手动进行切换，而且PG数据与主流的双机软件如rosemirrorha无法很好的兼容配置。 答：   PostgreSQL HA的话，PG界的大牛德哥有一个方案： 说到rosemirrorha跟pg的问题，最好具体说一下问题或者错误信息。 问：   rosemirrorha一般同步SQL的时候，就是将SQL的三个服务作为条件添加，服务期间使用双绞心跳线作为介质。但是pg在添加服务条件时无法正常添加。PG HA群集的时候，仲裁节点损坏怎么办？ 答：   vote节点确实有单点故障的隐患。这样的架构我在别人博客中看过，但是没有自己经历过。我认为首先在部署的时候，应该选择业务压力比较小的设备作为vote节点，减少其压力和故障率。然后在监控的时候需要关注一下vote设备的状态，这也是运维的及时性的要求。   这个方案我没用过，我觉得vote宕机后，主备的架构可能不会改变，也许业务不会受影响，只是在故障发生的时候可能会导致切换问题，这是我的理解。 问：   运维的时候，PG HA是一个可靠性是个很关键的因素。我们这边使用的PG一直都不错，唯独切换问题一直困扰。 答：   PG的HA方案有很多种选择，据我所知，keepalive+Postgresql是一种，还有用pacemaker+corosync的。 问：   曾经想使用过vote节点，但是考虑到vote节点增加后反而会成为一个新的故障点，不如主备手动切换的可靠。 答：   这个问题我认为就像“50万的车安全，还是5万的车安全？”一样，我觉得设备和架构的安全都是相对的，绝对的安全是运维同事的负责任的态度、对业务和架构的熟练掌握，以及使命感。   即使是有仲裁节点的备机，那如果仲裁节点有问题，或者是双机有一台机器故障了，我们还是需要第一时间去现场处理，避免事态恶化。 问：   运维中故障可靠性处理和及时性是必须的，监测软件和主板切换有时候还是存在不靠谱。我碰到一回ha软件，将损坏的数据库同步到没有损坏的数据库上，结果就是主备全坏。 答：   我之前实施过Rose HA，感觉还是不错的。  问：   比较成熟的ha软件不知道为什么总是跟pg过不去？ 答：   不是说跟它过不去，每一款软件都有他特别擅长、特别针对的对象和场景，也有不是特别擅长的。当然还是得具体问题具体分析，结合你提供的信息和报错截图来分析一下。      【问题2】可以说说pg的应用场景吗，和mysql比较有什么优势？   PostgreSQL数据库大多数OLTP和OLAP场景都适用。很惭愧MySQL和MariaDB我了解的不深入，据我了解2者有很多不同，比如PG用的是进程，MySQL则使用线程；PG的查询优化器很强大；PG的性能统计表比较丰富；PG和MySQL的多版本控制机制也不同，还有很多不同的特性。每种产品都有自己的亮点和短板，MySQL现在有Oracle的研发团队推动的话，感觉上可能会越来越好，因为Oracle的眼光和思路感觉很超前。PostgreSQL的社区和贡献者也是很强大，一直也在为PG注入更多亮点功能以适应发展需要。   DBA+有很多的专家，如果兄弟遇到方案选型的问题，可以来讨论一下，把具体的需求说一下，咱们具体问题具体讨论，条件允许的话，我们可以适当的模拟测试一下，用结果说话，争取为你找到最佳实践。    【问题3】确实没学过pg，除了官方文档外，讲师最推荐看什么书入门，当然，最好中文的。   PostgreSQL国内社区一直都在努力的做文档中文化工作，国内也有出相关的PostgreSQL的中文教材，唐成老师今年出版了一本《PostgreSQL修炼之道》，拜读了一下，感觉写的挺全面的；还有一本《PostgreSQL 9.0性能调教》感觉也挺好。    【问题4】去O说的这么火，用pg来替代O有没有什么坑需要事先考虑？   个人感觉Oracle的功能和产品优势还是很好的，如果经济上允许，Oracle还是最好的选择，RMAN和ASM等功能在日常维护工作中还是比较让人省心，Oracle的RAC和DG也很成熟，很有优势。如果非要去O，感觉还是需要谨慎、循序渐进的处理，具体事情具体对待，可先针对费核心业务业务进行迁移，待PG知识消化和经验积累一段时间后尝试更大业务的迁移，当然目前也有很多PG云平台可供选择。PG转O的话貌似代码修改工作量可能不需要太多，但是两个数据库的某些内部机制有区别，同样的代码在不同的库里跑，可能效果不同，所以需要辩症处理、细致调整。    【问题5】发个BCT问题：数据库的备份策略是第一次全备份后就每天增量备份，且BCT（block_change_tracking）已经打开。如果有人额外手工发起了一个全备份并使用不同的catalog库，那么，下次增量备份的时候会出现什么情况？   这种情况没在项目中对比过，我只知道BCT的记录文件是放在库里的，使用不同的恢复目录是可以看到之前的备份信息的。         各抒已见      【问题1】有个问题问一下，我看pg的支持厂商很多，pg是否有很多分支?各个分支有什么特点?     观点1：PostgreSQL只有单独的一个版本，数据库稳定、功能丰富、支持标准SQL语句，方便下载，思路清晰。    观点2：pg只有社区版，其它厂家很多是基于pg的技术，做自己的特性。但是pg已经非常稳定和可靠，部署也非常多，关于支持，国内做专业支持的不多，做得好并且技术也好的就毛鳞凤角了。   【问题2】每个数据库都有优点和缺点，反过来问pg有什么缺点？     观点1：PostgreSQL是有一些不足，跟Oracle相比的话，没有RMAN这种基于块级别的备份机制，PG_RMAN是通过检验文件更改信息后直接备份整个文件；Oracle RAC这种的架构目前PG好像还没有特别成熟、稳定的产品和支持。可能还有其他的不足，社区一直在努力的让PG更成熟、更强大。原厂服务的话Oracle的知识库和售后还是很给力的。    观点2：我觉得最大的不好就是国内对这个这么优秀的PostgreSQL数据库了解太少，误解太多。实际上pg在中国以外的地区部署量是非常庞大的，并且是在银行、电信、航天、军工里面要求真正7*24*365不能停机的关键应用。   【问题3】我听说pg和oracle比，单表查询效率较高，多表联合不行，老师有这种说法吗？     观点1：具体需要看是什么样的语句，什么类型、规模的表，最好还是实际测试一下。PostgreSQL的优化器还是很强劲的。    观点2：那是mysql。不管是olap，还是oltp应用，还是功能，pg的效率在绝大多数情况下能达到Oracle效能的95%。有些情况下会比Oracle的性能更好。   【问题4】standby中网络问题等会对主库造成影响吗？对数据类型有啥要求不？     观点1：网络问题会有一定程度的影响，这一点不论任何产品都有这可能。stream是目前比较新的复制方案，不需要等待日志写完，提交后即可同步，目前看到的比较成熟的案例都是同构的。    观点2：网络的抖动肯定会影响 PostgreSQL的stream replication跟Oracle的物理复制原理是一样的。  本文来自云栖社区合作伙伴"DBAplus"，原文发布时间：2015-10-15

pg主备库的搭建，首先需在2个节点安装pg软件，然后依次在2个节点配置主备。本文采用os为CentOS7.6，pg版本使用14.2，以下为详细部署步骤。本文两个节点的ip地址如下：[root@node1:0 ~]# cat /etc/hosts #CentOS Linux release 7.6.1810 (Core) 192.168.222.11 node1 192.168.222.12 node2■■■ 安装、配置、启动■■ 官网下载安装【不推荐采用此方式安装】■ Install the repository RPMyum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm■ Install PostgreSQLyum install -y postgresql14-server■ Optionally initialize the database and enable automatic start/usr/pgsql-14/bin/postgresql-14-setup initdbsystemctl start postgresql-14■ Stop and uninstallsystemctl stop postgresql-14yum remove -y postgresql*yum remove -y pgdg*■■ 编译安装【推荐采用此方式安装】可参考pg中文社区cd /u01/pgwget https://ftp.postgresql.org/pub/source/v14.2/postgresql-14.2.tar.bz2 --no-check-certificatetar xvfj postgresql*.bz2cd postgresql-14.2./configure --prefix=/u01/pg/pgsql此时如果缺少readline，则yum install -y readline-develmkdir -p /u01/pg/pgsqlmake install-world■■ 配置adduser postgrespasswd postgresmkdir /u01/pg/pgsql/datachown -R postgres:postgres /u01/pgsu - postgres■ 修改默认数据路径export PGDATA=/u01/pg/pgsql/dataexport PATH=/u01/pg/pgsql/bin:$PATH■ 初始化数据库initdb■ 修改其他默认配置查看配置文件：grep -Pv "^#|^$|^\t" $PGDATA/postgresql.confvim $PGDATA/postgresql.conf# 默认监听在127.0.0.1 #listen_addresses = 'localhost' # what IP address(es) to listen on; # 配置监听在任意ip listen_addresses = '*' # 打开log收集 logging_collector = on 注：日志文件默认在log目录下vim $PGDATA/pg_hba.conf# IPv4 local connections: # 默认配置，只允许本机访问 host all all 127.0.0.1/32 trust # 允许所有ip访问 host all all 0.0.0.0/0 trust # 允许指定网段访问 host all all 192.168.222.0/24 trust■ 启停数据库pg_ctl statuspg_ctl startpg_ctl stop■ 客户端工具使用开源的pgAdminSet Master Password: postgres■■■ 主从配置■■ 主节点■ 创建用于主从访问的用户， 修改postgres用户的密码，用于远程登录su - postgrespsql # 创建 postgres 密码 ALTER USER postgres WITH PASSWORD 'postgres'; # 创建 从库 replica 用户密码 CREATE ROLE replica login replication encrypted password 'replica'; # 检查账号 SELECT usename from pg_user; SELECT rolname from pg_roles;■ 修改 pg_hba.conf 配置vim $PGDATA/pg_hba.conf# 添加从库网段 host all all 0.0.0.0/0 trust # replication privilege. local replication all peer host replication replica 192.168.222.12/24 md5 注意此处 192.168.222.12/24 需修改为从库的 IP 段■ 修改 postgresql.conf 配置vim $PGDATA/postgresql.conflisten_addresses = '*' wal_level = hot_standby synchronous_commit = remote_write # synchronous_commit 参考文档可选其他 on max_wal_senders = 32 #同步最大的进程数量 wal_sender_timeout = 60s #流复制主机发送数据的超时时间 max_connections = 100 #最大连接数，从库的max_connections必须要大于主库的■■ 从节点■ 从主库同步数据pg_basebackup -D $PGDATA -h node1 -p 5432 -U replica -X stream -P注：从库无需初始化■ 修改 postgresql.conf 配置从 PostgreSQL 12 开始已移除了 recovery.conf 文件，相关配置合并到了 postgresql.conf 中，由于从主库同步数据库，其中配置也需要移除和修改vim $PGDATA/postgresql.conf# 移除或注释 wal_level wal_level = xxx # 修改或添加以下 primary_conninfo = 'host=192.168.222.11 port=5432 user=replica password=replica' recovery_target_timeline = 'latest'■ 创建 standby.signal创建 standby.signal 文件，声明从库。vim $PGDATA/standby.signal# 声明从库 standby_mode = on■ 确认数据目录权限，避免踩坑chown -R postgres.postgres $PGDATA■ 启动pg_ctl start■■ 确认同步■ 主库查看ps aux |grep sender # 返回 postgres: walsender replica 192.168.222.12(64218) streaming 3/27000148 select application_name, state, sync_priority, sync_state from pg_stat_replication; application_name | state | sync_priority | sync_state ------------------+-----------+---------------+------------ walreceiver | streaming | 0 | async select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication; pid | state | client_addr | sync_priority | sync_state -------+-----------+----------------+---------------+------------ 33328 | streaming | 192.168.222.12 | 0 | async■ 从库查看ps aux |grep receiver # 返回 postgres: walreceiver streaming 3/27000148■■■ 监控■■ SamplerSampler 是一个用于 shell 命令执行、可视化和警报的工具，配置了一个简单的 YAML 文件。wget https://github.com/sqshq/sampler/releases/download/v1.1.0/sampler-1.1.0-linux-amd64 -O samplerchmod +x sampler■ 在yaml文件中定义shell命令cat > config.yml <<-'EOF' variables: PGPASSWORD: postgres postgres_connection: psql -h localhost -U postgres --no-align --tuples-only runcharts: - title: Data write(Byte) position: [[0, 8], [20, 12]] rate-ms: 500 legend: enabled: true details: false scale: 2 items: - label: background writer color: 178 sample: psql -At -U postgres -c "select 8 * (buffers_checkpoint + buffers_clean + buffers_backend)/1024 as total_writen from pg_stat_bgwriter;" - label: checkpoint write color: 162 sample: psql -At -U postgres -c "select buffers_checkpoint * 8 / (checkpoints_timed + checkpoints_req) as checkpoint_write_avg from pg_stat_bgwriter" - title: PostgreSQL connections position: [[40, 8], [40, 12]] rate-ms: 500 legend: enabled: true details: false scale: 2 items: - label: active connections color: 178 sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where state = 'active' and pid <> pg_backend_pid();" - label: idle connections color: 162 sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where state = 'idle' and pid <> pg_backend_pid();" - label: idle in transaction connections color: 32 sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where state = 'idle in transaction' and pid <> pg_backend_pid();" barcharts: - title: PostgreSQL Database Status position: [[0, 0], [40, 8]] rate-ms: 500 scale: 0 items: - label: tuple insert init: $postgres_connection sample: select tup_inserted from pg_stat_database where datname = current_database(); - label: tuple delete init: $postgres_connection sample: select tup_deleted from pg_stat_database where datname = current_database(); - label: tuple update init: $postgres_connection sample: select tup_updated from pg_stat_database where datname = current_database(); - label: tuple fetch init: $postgres_connection sample: select tup_fetched from pg_stat_database where datname = current_database(); gauges: - title: PostgreSQL Database Age position: [[0, 32], [40, 8]] rate-ms: 500 scale: 2 color: 122 percent-only: false cur: sample: psql -At -U postgres -c "select age(datfrozenxid) from pg_database where datname = current_database();" max: sample: psql -At -U postgres -c "select 210000" min: sample: psql -At -U postgres -c "select 100" sparklines: - title: CPU usage position: [[0, 20], [40, 12]] rate-ms: 200 scale: 0 sample: ps -A -o %cpu | awk '{s+=$1} END {print s}' - title: PostgreSQL cache hit ratio position: [[40, 20], [40, 12]] init: $postgres_connection sample: select round(sum(blks_hit)*100/sum(blks_hit+blks_read),2)::numeric from pg_stat_database where datname = current_database(); - title: PostgreSQL transaction commit ratio position: [[40, 0], [40, 8]] init: $postgres_connection sample: select round(100*(xact_commit::numeric/(case when xact_commit > 0 then xact_commit else 1 end + xact_rollback)),2)::numeric as commit_ratio from pg_stat_database where datname = current_database(); textboxes: - title: Server status position: [[20, 8], [20, 12]] rate-ms: 500 sample: top -bn 1 | head -n 5 asciiboxes: - title: PostgreSQL Version position: [[40, 32], [40, 8]] rate-ms: 500 color: 43 sample: psql -At -U postgres -c "select version()" border: false font: 2d EOF■ 运行/u01/pg/monitor/sampler -c /u01/pg/monitor/config.yml

数据库技术丛书 PostgreSQL修炼之道：从小工到专家 　 唐成著             图书在版编目（CIP）数据 PostgreSQL修炼之道：从小工到专家/唐成著. —北京：机械工业出版社，2015.4 （数据库技术丛书） ISBN 978-7-111-49872-8 I. P…　II. 唐…　III. 关系数据库系统　IV. TP311.132.3 中国版本图书馆CIP数据核字（2015）第063966号 PostgreSQL修炼之道：从小工到专家 出版发行：机械工业出版社（北京市西城区百万庄大街22号　邮政编码：100037） 责任编辑：杨绣国陈佳媛 责任校对：董纪丽 印　　刷： 版　　次：2015年4月第1版第1次印刷 开　　本：186mm×240mm　1/16 印　　张：33.5 书　　号：ISBN 978-7-111-49872-8 定　　价：79.00元 凡购本书，如有缺页、倒页、脱页，由本社发行部调换 客服热线：（010）88378991　88361066 投稿热线：（010）88379604 购书热线：（010）68326294　88379649　68995259 读者信箱：hzjsj@hzbook.com 版权所有·侵权必究 封底无防伪标均为盗版 本书法律顾问：北京大成律师事务所　韩光/邹晓东 Preface  前　　言 为什么要写这本书 PostgreSQL数据库是目前功能最强大的开源数据库，它基本包含了其他所有商业或开源的数据库中能找到的功能，甚至还包含了一些商业数据库中没有的功能。它是最接近工业标准SQL92的查询语言，并且正在实现新的功能以兼容最新的SQL标准：SQL2003。PostgreSQL也获得数个奖项，曾三次被评为Linux Journal杂志编辑评选的“最佳数据库奖”（2000年、2003年和2004年），并获2004年度的Linux新媒体最佳数据库系统奖。 PostgreSQL目前在国外很流行，特别是近两年，使用PostgreSQL数据库的公司越来越多，如提供网络电话功能的skype和著名的图片分享网站Instagram。2012年，美国联邦机构全面转向PostgreSQL阵营；法国也正推动政府机构积极采用PostgreSQL数据库取代商业数据库；世界最大的CRM软件服务提供商Salesforce同样开始大量使用 PostgreSQL。在DB-Engine 发布的历年数据库排名中，PostgreSQL自从2013年3月上升到第四名后，一直稳定在第四名，排在很多知名的商业数据库如DB2、Sybase之前，也排在所有NoSQL数据库如Cassandra、Redis等之前。 虽然在国外使用PostgreSQL 数据库的人很多，但在国内，PostgreSQL中文的学习资料并不多，因此我就想到写一本关于PostgreSQL的书，让国内更多的人加入到学习PostgreSQL数据库的队伍中来。 读者对象 适合阅读本书的用户： 数据库入门者。学习本书和相关的数据库知识，可以让一个对数据库了解不是很深的数据库爱好者成为数据库专家。 非PostgreSQL数据库的DBA。可以让非PostgreSQL的DBA 快速掌握PostgreSQL数据库相关知识，成为一名合格的PostgreSQL DBA。 PostgreSQL DBA。本书的一些章节对熟悉PostgreSQL数据库的DBA也有很大指导作用，可以使读者的知识更充实。 开发人员。通过此书可以快速掌握PostgreSQL数据库方面的知识，提高开发人员的数据库水平。 如何阅读本书 本书分为四大部分，分别为准备篇、基础篇、提高篇和第三方开源软件及架构篇。准备篇是为没有数据库基础的读者准备的，如果你已经具备了一定的数据库基础，可以跳过其中的一些内容。基础篇介绍了PostgreSQL数据库中的一些基础内容，学完此篇可以完成基本的PostgreSQL数据库的日常操作。提高篇讲解了一些更深的内容，如PostgreSQL的一些技术内幕、特色功能、优化等方面的内容，仔细阅读此篇可使你早日成为PostgreSQL数据库高手。第三方开源软件及架构篇讲解了与PostgreSQL数据库配套使用的一些常用的开源软件及架构设计方面的内容，通过阅读此篇，可以开阔大家的眼界，提高数据库架构设计能力。 本书中有大量的例子，读者边阅读此书边按例子进行实际的操作，将获得最佳的学习效果。 勘误和支持 由于作者的水平有限，编写的时间也很仓促，书中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。你可以将书中的错误，遇到的问题及宝贵意见发送邮件至我的邮箱chengdata@gmail.com，我很期待听到你们的真挚反馈。 致谢 首先要感谢国内PostgreSQL数据库的爱好者，他们已经整理了很多PostgreSQL的文章，翻译了PostgreSQL的官方手册，让我可以站在前人的肩膀上。大家可以在PostgreSQL在中国的维基主页http://www.pgsqldb.org/mwiki/index.php/上看到前人的成果。 感谢机械工业出版社华章公司的编辑杨绣国老师，感谢她一年多来始终支持我的写作，她的鼓励和帮助引导我能顺利完成全部书稿。 最后要感谢我的妻子，她一直支持和鼓励我，让我能坚持把这本书写完。 谨以此书，献给众多热爱PostgreSQL的朋友们。 唐成（osdba） 中国，杭州，2015年1月 Contents  目　　录  前言 第一篇准备篇 第1章PostgreSQL简介2 1.1什么是PostgreSQL2 1.1.1PostgreSQL概述2 1.1.2PostgreSQL的发展历史2 1.1.3　PostgreSQL数据库的优势3 1.1.4　PostgreSQL应用现状和发展趋势4 1.2　PostgreSQL数据库与其他数据库的对比4 1.2.1PostgreSQL与MySQL数据库的对比4 1.2.2　PostgreSQL与Oracle数据库的对比6 1.3　小结6 第2章PostgreSQL安装与配置7 2.1　从发行版本安装7 2.1.1　在Debian或Ubuntu下的安装7 2.1.2在Redhat、CentOS或Fedora下的安装9 2.1.3　在Windows下的安装12 2.1.4　发行版安装总结16 2.2　从源码安装16 2.2.1　编译安装过程介绍16 2.2.2　下载源代码17 2.2.3　编译及安装18 2.2.4　安装后的配置20 2.2.5　创建数据库簇21 2.2.6　安装contrib目录下的工具21 2.2.7　启动和停止数据库21 2.2.8　编译安装时的常见问题及解决方法22 2.3　安装技巧介绍24 2.3.1　在Redhat、CentOS下使用二进制包安装较新版本的方法24 2.3.2　如何使用较大的数据块提高I/O性能25 2.4　PostgreSQL的简单配置25 2.4.1　修改监听的IP和端口25 2.4.2　与数据库log相关的参数25 2.4.3　内存参数的设置26 2.5　小结26 第3章SQL语言入门27 3.1　SQL语句语法简介27 3.1.1　语句的分类27 3.1.2　词法结构27 3.2　DDL语句28 3.2.1　建表语句28 3.2.2　删除表语句30 3.3　DML语句30 3.3.1　插入语句30 3.3.2　更新语句31 3.3.3　删除语句31 3.4　查询语句31 3.4.1　单表查询语句31 3.4.2　过滤条件的查询32 3.4.3　排序32 3.4.4　分组查询33 3.4.5　表join34 3.5　其他SQL语句36 3.5.1　INSERT INTO... SELECT语句36 3.5.2　UNION语句36 3.5.3　TRUNCATE TABLE语句37 3.6　小结37 第二篇基础篇 第4章psql工具的使用介绍40 4.1　psql介绍40 4.2　psql的简单使用40 4.3　psql的常用命令42 4.3.1　\d命令42 4.3.2　指定字符集编译的命令45 4.3.3　\pset命令46 4.3.4　\x命令46 4.3.5　执行存储在外部文件中的SQL命令47 4.3.6　显示信息的命令48 4.3.7　更多的命令49 4.4　psql的使用技巧和注意事项50 4.4.1　历史命令与补全的功能50 4.4.2　自动提交方面的技巧50 4.4.3　如何得到psql中命令实际执行的SQL51 4.5　小结53 第5章数据类型54 5.1　类型介绍54 5.1.1　类型的分类54 5.1.2　类型输入与转换55 5.2　布尔类型56 5.2.1　布尔类型解释56 5.2.2　布尔类型的操作符58 5.3　数值类型59 5.3.1　数值类型解释59 5.3.2　整数类型 59 5.3.3　精确的小数类型59 5.3.4　浮点数类型60 5.3.5　序列类型61 5.3.6　货币类型61 5.3.7　数学函数和操作符62 5.4　字符串类型64 5.4.1　类型解释64 5.4.2　字符串函数和操作符65 5.5　二进制数据类型67 5.5.1　二进制数据类型解释67 5.5.2　二进制数据类型转义表示67 5.5.3　二进制数据类型的函数68 5.6　位串类型69 5.6.1　位串类型解释69 5.6.2　位串类型的使用69 5.6.3　位串的操作符及函数70 5.7　日期/时间类型71 5.7.1　日期/时间类型详解71 5.7.2　日期输入72 5.7.3　时间输入73 5.7.4　特殊值75 5.7.5　函数和操作符列表75 5.7.6　时间函数77 5.7.7　extract和date_part函数80 5.8　枚举类型81 5.8.1　枚举类型的使用81 5.8.2　枚举类型的说明82 5.8.3　枚举类型的函数83 5.9　几何类型84 5.9.1　几何类型概况84 5.9.2　几何类型的输入84 5.9.3　几何类型的操作符89 5.9.4　几何类型的函数97 5.10　网络地址类型98 5.10.1　网络地址类型概况98 5.10.2　inet与cidr类型98 5.10.3　macaddr类型101 5.10.4　网络地址类型的操作符101 5.10.5　网络地址类型的函数102 5.11　复合类型103 5.11.1　复合类型的定义103 5.11.2　复合类型的输入104 5.11.3　访问复合类型105 5.11.4　修改复合类型105 5.11.5　复合类型的输入与输出106 5.12　XML类型107 5.12.1　XML类型的输入107 5.12.2　字符集的问题108 5.12.3　XML类型的函数109 5.13　JSON类型114 5.13.1　JSON类型简介115 5.13.2　JSON类型的输入与输出115 5.13.3　JSON类型的操作符116 5.13.4　JSON类型的函数118 5.13.5　JSON类型的索引121 5.14　Range类型125 5.14.1　Range类型简介125 5.14.2　创建Range类型126 5.14.3　Range类型的输入与输出127 5.14.4　Range类型的操作符130 5.14.5　Range类型的函数130 5.14.6　Range类型的索引和约束131 5.15　数组类型132 5.15.1　数组类型的声明132 5.15.2　如何输入数组值133 5.15.3　访问数组135 5.15.4　修改数组137 5.15.5　数组的操作符138 5.15.6　数组的函数139  5.16　伪类型142  5.17　其他类型143 5.17.1　UUID类型143 5.17.2　pg_lsn 类型143 第6章逻辑结构管理145 6.1　数据库逻辑结构介绍145 6.2　数据库基本操作145 6.2.1　创建数据库145 6.2.2　修改数据库146 6.2.3　删除数据库147 6.2.4　常见问题及解答147 6.3　模式148 6.3.1　模式的定义148 6.3.2　模式的使用148 6.3.3　公共模式150 6.3.4　模式的搜索路径150 6.3.5　模式的权限151 6.3.6　模式的移植性151 6.4　表152 6.4.1　创建表152 6.4.2　表的存储属性154 6.4.3　临时表156 6.4.4　默认值158 6.4.5　约束159 6.4.6　修改表163 6.4.7　表继承及分区表167 6.4.8　分区表168 6.5　触发器173 6.5.1　创建触发器173 6.5.2　语句级触发器与行级触发器175 6.5.3　BEFORE触发器与AFTER触发器177 6.5.4　删除触发器178 6.5.5　触发器的行为179 6.5.6　触发器函数中的特殊变量180 6.6　事件触发器180 6.6.1　创建事件触发器183 6.6.2　修改事件触发器186 6.7　表空间186 6.7.1　表空间的定义186 6.7.2　表空间的使用186 6.8　视图187 6.8.1　视图的定义187 6.8.2　创建视图188 6.8.3　可更新视图189 6.9　索引191 6.9.1　索引简介191 6.9.2　索引的分类192 6.9.3　创建索引192 6.9.4　并发创建索引193 6.9.5　修改索引196 6.9.6　删除索引196 6.10　用户及权限管理197 6.10.1　用户和角色197 6.10.2　创建用户和角色198 6.10.3　权限的管理199 6.10.4　函数和触发器的权限202 6.10.5　权限的总结202 6.10.6　权限的示例202 6.11　事务、并发、锁203 6.11.1　ACID203 6.11.2　DDL事务204 6.11.3　事务的使用204 6.11.4　SAVEPOINT205 6.11.5　事务隔离级别206 6.11.6　两阶段提交207 6.11.7　锁机制209 6.11.8　死锁及防范212 6.11.9　表级锁命令LOCK TABLE213 6.11.10　行级锁命令213 6.11.11　锁的查看214 第7章PostgreSQL的核心架构221 7.1　应用程序的访问接口221 7.1.1　访问接口总体图221 7.1.2　不同编辑语言的PostgreSQL驱动介绍222 7.2　进程及内存结构223 7.2.1　进程和内存架构图223 7.2.2　主进程Postmaster224 7.2.3　SysLogger（系统日志）进程224 7.2.4　BgWriter（后台写）进程225 7.2.5　WalWriter（预写式日志写）进程225 7.2.6　PgArch（归档）进程225 7.2.7　AutoVacuum（自动清理）进程225 7.2.8　PgStat（统计数据收集）进程226 7.2.9　共享内存226 7.2.10　本地内存226 7.3　目录结构227 7.3.1　安装目录的结构227 7.3.2　数据目录的结构227 7.3.3　表空间的目录228 第8章服务管理229 8.1　服务的启停和创建229 8.1.1　启停方法229 8.1.2　pg_ctl230 8.1.3　信号234 8.1.4　postgres及单用户模式234 8.2　服务配置介绍235 8.2.1　配置参数235 8.2.2　连接配置项237 8.2.3　内存配置项240 8.2.4　预写式日志的配置项241 8.2.5　错误报告和日志项243 8.3　访问控制配置文件246 8.3.1　pg_hba.conf 文件247 8.3.2　认证方法介绍248 8.3.3　认证方法实战249 8.4　备份和还原249 8.4.1　逻辑备份249 8.4.2　pg_dump命令250 8.4.3　pg_restore命令254 8.4.4　pg_dump和pg_restore使用举例257 8.4.5　物理备份258 8.4.6　使用LVM快照进行热备份259 8.5　常用的管理命令261 8.5.1　查看系统信息的常用命令261 8.5.2　系统维护常用命令267 第三篇提高篇 第9章PostgreSQL中执行计划270 9.1　执行计划的解释270 9.1.1　EXPLAIN命令270 9.1.2　EXPLAIN输出结果解释271 9.1.3　EXPLAIN使用示例272 9.1.4　全表扫描275 9.1.5　索引扫描275 9.1.6　位图扫描275 9.1.7　条件过滤276 9.1.8　Nestloop Join277 9.1.9　Hash Join277 9.1.10　Merge Join278 9.2　与执行计划相关的配置项279 9.2.1　ENABLE_*参数 279 9.2.2　COST基准值参数279 9.2.3　基因查询优化的参数280 9.2.4　其他执行计划配置项281 9.3　统计信息的收集282 9.3.1　统计信息收集器的配置项282 9.3.2　SQL执行的统计信息输出283 9.3.3　手工收集统计信息283 第10章PostgreSQL中的技术内幕285 10.1　表中的系统字段285 10.1.1　oid286 10.1.2　ctid288 10.1.3　xmin、xmax、cmin、cmax289 10.2　多版本并发控制290 10.2.1　多版本并发控制的原理290 10.2.2　PostgreSQL中的多版本并发控制291 10.2.3　PostgreSQL多版本的优劣分析293 10.3　物理存储结构293 10.3.1　PostgreSQL中的术语293 10.3.2　数据块结构293 10.3.3　Tuple结构294 10.3.4　数据块空闲空间管理296 10.3.5　可见性映射表文件298 10.4　技术解密298 10.4.1　Index-only scans298 10.4.2　Heap-Only Tuples300 第11章PostgreSQL的特色功能302 11.1　规则系统302 11.1.1　SELECT规则302 11.1.2　更新规则303 11.1.3　规则和权限306 11.1.4　规则和命令状态307 11.1.5　规则与触发器的比较308 11.2　模式匹配和正则表达式308 11.2.1　PostgreSQL中的模式匹配和正则表达式介绍308 11.2.2　传统SQL的LIKE 操作符309 11.2.3　SIMILAR TO 正则表达式310 11.2.4　POSIX 正则表达式312 11.2.5　模式匹配函数 substring313 11.3　listen与notify315 11.3.1　listen与notify的简单示例315 11.3.2listen与notify的相关命令316 11.3.3　listen与notify的使用详解317 11.4　索引的特色320 11.4.1　表达式上的索引320 11.4.2　部分索引320 11.4.3　GiST索引323 11.4.4　SP-GiST索引325 11.4.5　GIN索引326 11.5　序列的使用328 11.5.1　序列的创建328 11.5.2　序列的使用及相关的函数329 11.5.3　常见问题及解答331 11.6　咨询锁的使用333 11.6.1　咨询锁的定义333 11.6.2　咨询锁的函数及使用333 11.6.3　常见问题及解答337 11.7　SQL/MED338 11.7.1　SQL/MED的介绍338 11.7.2　外部数据包装器对象339 11.7.3　外部服务器对象340 11.7.4　用户映射对象341 11.7.5　外部表对象341 11.7.6　file_fdw使用实例342 11.7.7　postgres_fdw使用实例345 第12章数据库优化347 12.1　优化准则和方法347 12.1.1　优化准则347 12.1.2　优化方法348 12.2　硬件知识348 12.2.1　CPU及服务器体系结构348 12.2.2　内存349 12.2.3　硬盘350 12.3　文件系统及I/O调优352 12.3.1　文件系统的崩溃恢复352 12.3.2　Ext2文件系统353 12.3.3　Ext3文件系统353 12.3.4　Ext4文件系统354 12.3.5　XFS文件系统355 12.3.6　Barriers I/O355 12.3.7　I/O调优的方法356 12.4　性能监控359 12.4.1　数据库性能视图359 12.4.2　Linux监控工具362 12.5数据库配置优化364 12.5.1内存配置优化364 12.5.2　关于双缓存的优化366 12.5.3　vacuum中的优化367 12.5.4　预写式日志写优化369 第13章Standby数据库的搭建371 13.1　Standby数据库原理371 13.1.1PITR原理371 13.1.2WAL日志归档372 13.1.3流复制372 13.1.4Standby的运行原理373 13.1.5　创建Standby的步骤373 13.2　pg_basebackup命令行工具374 13.2.1　pg_basebackup介绍374 13.2.2　pg_basebackup的命令行参数375 13.2.3　pg_basebackup使用示例376 13.3　异步流复制Hot Standby的示例377 13.3.1　配置环境377 13.3.2　主数据库的配置378 13.3.3　在Standby上生成基础备份378 13.3.4　启动Standby379 13.4　同步流复制的Standby数据库380 13.4.1　同步流复制的架构380 13.4.2　同步复制的配置381 13.4.3　配置实例381 13.5　检查备库及流复制情况383 13.5.1　检查异步流复制的情况383 13.5.2　检查同步流复制的情况384 13.5.3　视图pg_stat_replication详解385 13.5.4　查看备库的状态385 13.6　Hot Standby的限制387 13.6.1　Hot Standby的查询限制387 13.6.2　Hot Standby的查询冲突处理389 13.7　恢复配置详解390 13.7.1　归档恢复配置的配置项390 13.7.2　Recovery Target配置391 13.7.3　Standby Server配置 391 13.8　流复制的注意事项392 13.8.1　wal_keep_segments参数的配置392 13.8.2　vacuum_defer_cleanup_age参数的配置392 第四篇第三方开源软件及架构篇 第14章PgBouncer394 14.1　PgBouncer 介绍394 14.2　PgBouncer中的概念395 14.3　PgBouncer的安装方法395 14.4　PgBouncer的简单使用395 14.4.1　简单配置方法395 14.4.2　启动PgBouncer396 14.4.3　停止PgBouncer397 14.4.4　查看连接池信息397 14.5　PgBouncer的配置文件详解399 14.5.1　“[databases]”部分的配置项399 14.5.2　“[pgbouncer]”部分的配置项399 14.5.3　用户密码文件403 第15章Slony-I的使用404 15.1Slony-I中的概念404 15.1.1　集群404 15.1.2　节点405 15.1.3　复制集合405 15.1.4　数据原始生产者、数据提供者和数据订阅者405 15.1.5　slon守护程序405 15.1.6　slonik配置程序405 15.2　Slony-I复制的限制405 15.3　在Windows下使用pgAdminIII安装配置Slony-I406 15.3.1　Windows下安装Slony-I406 15.3.2Windows配置 Slony-I同步实例407 15.4在Linux下安装配置Slony-I419 15.4.1编译安装Slony-I419 15.4.2配置Slony-I复制421 第16章Bucardo的使用426 16.1Bucardo中的概念426 16.1.1Bucardo介绍426 16.1.2Bucardo FAQ426 16.1.3　Bucardo 同步中定义的概念427 16.2Bucardo的安装方法427 16.2.1Bucardo的安装步骤427 16.2.2安装Test-Simple、ExtUtils-MakeMaker、version428 16.2.3安装DBI及DBD::Pg428 16.2.4安装DBIx-Safe429 16.2.5安装Bucardo源码包429 16.3Bucardo同步配置429 16.3.1示例环境429 16.3.2配置同步的简要过程430 16.3.3　bucardo_ctl install431 16.3.4bucardo_ctl add db433 16.3.5　bucardo_ctl add table433 16.3.6　bucardo_ctl add herd433 16.3.7　bucardo_ctl add sync433 16.3.8　bucardo_ctl start434 16.4Bucardo的日常维护434 16.4.1Bucardo的触发器日志清理434 16.4.2临时停止和启动同步的方法435 16.4.3新增表到同步的方法435 16.4.4移除某个表或序列的方法435 第17章PL/Proxy的使用436 17.1PL/Proxy中的概念436 17.1.1PL/Proxy的定义436 17.1.2PL/Proxy的特性说明438 17.2PL/Proxy安装及配置438 17.2.1编译安装438 17.2.2安装规划439 17.2.3配置过程439 17.3PL/Proxy的集群配置详解443 17.3.1Cluster configuration API方式444 17.3.2SQL/MED方式配置集群446 17.4PL/Proxy语言详解446 17.4.1CONNECT447 17.4.2CLUSTER447 17.4.3RUN ON447 17.4.4SPLIT448 17.4.5TARGET448 17.5PL/Proxy的一个高可用方案448 17.5.1方案介绍448 17.5.2方案架构449 17.5.3具体实施步骤449 第18章pgpool-II的使用458 18.1　pgpool-II中的概念458 18.1.1　pgpool-II的定义458 18.1.2　pgpool-II的架构459 18.1.3　pgpool-II的工作模式460 18.1.4　pgpool-II的程序模块461 18.2　pgpool-II安装方法462 18.2.1　源码安装462 18.2.2　安装 pgpool_regclass463 18.2.3　建立 insert_lock 表463 18.2.4　安装C语言函数463 18.3　pgpool-II配置快速入门463 18.3.1　pgpool-II的配置文件及启停方法464 18.3.2　复制和负载均衡的示例466 18.3.3　使用流复制的主备模式的示例467 18.3.4　show命令468 18.4　pgpool-II高可用配置方法471 18.4.1　pgpool-II高可用切换及恢复的原理471 18.4.2　pgpool-II的健康检查473 18.4.3　复制和负载均衡模式的高可用示例473 18.4.4　使用流复制的主备模式下的高可用示例488 18.5　pgpool-II的总结492 第19章Postgres-XC的使用493 19.1　Postgres-XC中的概念493 19.1.1　Postgres-XC的定义493 19.1.2　Postgres-XC的特点493 19.1.3　Postgres-XC的性能494 19.1.4　Postgres-XC的组件494 19.2　Postgres-XC的安装495 19.2.1　源码安装方法495 19.2.2　Postgres-XC目录及程序说明496 19.3　配置Postgres-XC集群497 19.3.1　集群规划497 19.3.2　初始化GTM498 19.3.3　初始化GTM的备库498 19.3.4　初始化GTM Proxy499 19.3.5　初始化Coordinators、数据节点499 19.3.6　启动集群500 19.3.7　停止集群501 19.3.8　配置集群节点信息502 19.4　Postgres-XC的使用503 19.4.1　建表详解503 19.4.2　使用限制509 19.4.3　重新分布数据510 19.4.4　增加Coordinator节点的方法512 19.4.5　移除Coordinator节点的方法513 19.4.6　增加Datanode节点的方法513 19.4.7　移除Datanode节点的方法514 第20章高可用性方案设计516 20.1　高可用架构基础516 20.1.1　各种高可用架构介绍516 20.1.2　服务的可靠性设计517 20.1.3　数据可靠性设计517 20.2　基于共享存储的高可用方案517 20.2.1　SAN存储的方案517 20.2.2　DRBD的方案518 20.3　WAL日志同步或流复制同步的方案519 20.3.1　持续复制归档的standby的方法519 20.3.2　异步流复制的方案519 20.3.3　基于同步流复制方案519 20.4　基于触发器的同步方案520 20.4.1　方案的特点520 20.4.2　基于触发器方案的同步软件介绍520 20.5　基于语句中间件的高可用方案520 20.5.1　方案的特点520 20.5.2　基于语句中间件的开源软件介绍521 第一篇 准　备　篇 第1章PostgreSQL简介 第2章PostgreSQL安装与配置 第3章SQL语言入门 第1章 PostgreSQL简介 本章将着重介绍PostgreSQL数据库的相关知识，让没有接触过PostgreSQL的读者对它有一个初步的了解。 1.1什么是PostgreSQL 1.1.1PostgreSQL概述 PostgreSQL数据库是目前功能最强大的开源数据库，支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。而且它提供了丰富的接口，可以很容易地扩展它的功能，如可以在GiST框架下实现自己的索引类型等，它还支持使用C语言写自定义函数、触发器，也支持使用流行的语言写自定义函数，比如其中的PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Tcl，等等。 1.1.2PostgreSQL的发展历史 前身Ingres：PostgreSQL的前身是伯克利源于1977 年的 Ingres 项目。这个项目是由著名的数据库科学家Michael Stonebraker领导的。1982年，Michael Stonebraker离开伯克利大学，把Ingres商业化，使之成为 Relational Technologies 公司的一个产品。后来 Relational Tecchnologies被Computer Associates（CA）收购。Ingres 是一个非关系型的数据库。 伯克利的 Postgres 项目：20世纪80年代，数据库系统中的一个主要问题是数据关系维护。在1985年Michael Stonebraker回到伯克利后，为了解决Ingres中的数据关系维护问题，启动了一个后Ingres（post-Ingres）的项目，这就是 POSTGRES 的开端。 POSTGRES项目是由防务高级研究项目局（DARPA）、陆军研究办公室（ARO）、国家科学基金（NSF） 以及 ESL公司共同赞助的。从1986年开始，Michael Stonebraker 教授发表了一系列论文，探讨了新的数据库的结构设计和扩展设计。第一个“演示性”系统在 1987 年便可使用了，并且在 1988 年的数据管理国际会议（ACM-SIGMOD）上展出。1989年6月发布了版本 1给一些外部的用户使用。由于源代码维护的时间日益增加，占用了太多本应用于数据库研究的时间，为减少支持的负担，伯克利的POSTGRES 项目在版本 4.2 时正式终止。  Postgres95：在 1994 年，来自中国香港的两名伯克利的研究生Andrew Yu 和 Jolly Chen 向 POSTGRES 中增加了现在SQL 语言的解释器，将Postgres改名为 Postgres95，随后将 Postgres95 源代码发布到互联网上供大家使用。它成为一个开放源码的POSTGRES 代码的继承者。  PostgreSQL6.X：到了 1996 年，很明显地看出“Postgres95”这个名字已经经不起时间的考验。于是起了一个新名字 PostgreSQL，为Postgres与SQL的缩写，即增加了SQL功能的Postgres的意思。同时版本号也沿用伯克利 POSTGRES 项目的顺序，从6.0开始。 PostgreSQL7.1：PostgreSQL 7.1是继6.5版本之后又一个有巨大变化的版本，首先它引入了预写式日志的功能。这样，事务就拥有了完善的日志机制，可以提供更好的性能，还可以实现更优良的备份和灾难恢复的能力（比如联机热备份和宕机后的自动恢复）。其次是不再限制文本类型的数据段长度，这从很大程度上解决了PostgreSQL大对象的问题。  Postgres8.X：该版本可以在Windows下运行，它具有一些新的特性。比如具有事务保存点功能、改变字段的类型、表空间、即时恢复（即时恢复允许对服务器进行连续的备份。既可以恢复到失败那个点，也可以恢复到以前的任意事务）等功能。并且开始支持Perl 服务器端编程语言。  PostgreSQL9.X：进入9.X版本，标识着PostgreSQL进入了黄金发展阶段。2010年9月20日发布了PostgreSQL 9.0，大大增强了复制的功能（replication），比如增加了流复制功能（stream replicaction）和HOT standby功能。从9.0开始，可以很方便地搭建主从数据库。2011年9月12日发布了PostgreSQL9.1，在该版本中增加了同步复制功能（synchronous replication）；2012年9月10发布了PostgreSQL9.2，增加了级连复制的功能。  1.1.3　PostgreSQL数据库的优势 PostgreSQL有以下优势： PostgreSQL数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92的查询语言，并且正在实现新的功能以兼容最新的SQL标准：SQL2003。 稳定可靠：PostgreSQL是唯一能做到数据零丢失的开源数据库。有报道称国外的部分银行也在使用PostgreSQL数据库。 开源省钱： PostgreSQL数据库是开源的、免费的，而且是BSD协议，在使用和二次开发上基本没有限制。 支持广泛：PostgreSQL 数据库支持大量的主流开发语言，包括C、C++、Perl、Python、Java、Tcl，以及PHP等。 PostgreSQL社区活跃：PostgreSQL基本上每三个月推出一个补丁版本，这意味着已知的BUG很快会被修复，有应用场景的需求也会及时得到响应。 1.1.4　PostgreSQL应用现状和发展趋势 PostgreSQL目前在国外很流行，特别是近几年使用PostgreSQL数据库的公司越来越多。比如，日本电信（NTT） 大量使用PostgreSQL替代Oracle数据库，并且在 PostgreSQL之上二次开发了Postgres-XC，Postgres-XC是对使用者完全兼容PostgreSQL接口的share-nothing 架构的数据库集群。网络电话公司Skype 也大量使用PostgreSQL，并贡献了一些与PostgreSQL数据库配套的开源软件： PL/Proxy：PostgreSQL中的数据水平拆分软件   pgQ：使用PostgreSQL的消息队列软件  Londiste：用C语言实现的在PostgreSQL数据库之间进行逻辑同步的软件 全球最大的CRM软件服务提供商Salesforce也开始使用PostgreSQL，并招募了PostgreSQL内核开发者Tom lane。 2012年，美国联邦机构全面转向PostgreSQL阵营；法国也正积极推动政府机构采用PostgreSQL数据库，从而取代商业数据库。 在国内，越来越多的公司开始使用PostgreSQL，如斯凯网络（股票代码：MOBI）的后台数据库基本使用的都是PostgreSQL数据库，去哪儿网（qunar.com）也大量使用了PostgreSQL数据库。 主流的云服务提供商如亚马逊、阿里云的RDS（关系型数据库服务）同样提供了PostgreSQL的支持。 更多的使用PostgreSQL数据库的情况可以见PostgreSQL官方网站（http://www.postgresql.org/about/users/）。 1.2　PostgreSQL数据库与其他数据库的对比 1.2.1PostgreSQL与MySQL数据库的对比 可能有人会问，既然已经有一个人气很高的开源数据库MySQL了，为什么还要使用PostgreSQL？这主要是因为在一些应用场景中，使用MySQL有以下几个缺点： 功能不够强大：MySQL的多表连接查询方式只支持“Nest Loop”，不支持“hash join”和“sort merge join”。不仅如此，还有很多SQL语法它也不支持，子查询性能比较低。由于它不支持sequence，有公司还为此专门开发了统一序号分发中心的软件。 性能优化工具和度量信息不足：MySQL在运行过程中如果出现问题，只产生很少的性能数据，很难让维护人员准确定位问题产生的原因。MySQL的复制是异步的，无法通过Master/Slave做到数据零丢失。一些第三方公司也有改造MySQL源代码实现同步复制，但这些方案要么是没有开源，要么已开源却又不是很稳定，所以，对于普通大众来说，如何实现同步复制成了一个令人头疼的问题。 在线操作功能较弱：如果在MySQL表中加列，基本上是新建一个表，而且建索引时也会锁定整张表，即在建索引的过程中，表不能做任何操作。一些大的互联网公司或者是修改MySQL源码来实现在线DDL的功能，或者是通过上层架构来解决这个问题，如先在slave数据库上把DDL做完，然后把应用从master库切换到slave，再把原先的master上把DDL做完。第一种方法，需要公司有很强的MySQL研发能力，第二种方法需要公司有较强的开发能力，能设计出较强的应用架构。这对于一些中小型公司来说不太容易实现。 相对这些MySQL的弱点，PostgreSQL有以下几个优点： PostgreSQL功能强大：支持所有主流的多表连接查询的方式（如：“Nest loop”、“ hash join”“sort merge join”等）；支持绝大多数的SQL语法（如：with子句）。PostgreSQL是笔者见过的对正则表达式支持最强、内置函数也是最丰富的数据库。字段类型还支持数组类型。除了可以使用PL/PGSQL写存储过程外，还可以使用各种主流开发语言的语法（如：Python语言的PL/Python、Perl语言的PL/Perl来写存储过程）。这些强大的功能可以大大地节约开发资源。很多开发人员在PostgreSQL上做开发时，会发现数据库已帮自己实现了很多功能，甚至有一些业务功能都可直接使用数据库的功能解决，不再需要写代码来实现了。 性能优化工具与度量信息丰富：PostgreSQL数据库中有大量的性能视图，可方便地定位问题（比如：可看到正在执行的SQL，可通过锁视图看到谁在等待、哪条记录被锁定等）。PostgreSQL中设计了专门的架构和进程用于收集性能数据，既有物理I/O方面的统计，也有表扫描及索引扫描方面的性能数据。 在线操作功能好：PostgreSQL增加空值的列时，本质上只是在系统表上把列定义上，无须对物理结构做更新，这就让PostgreSQL在加列时可以做到瞬间完成。PostgreSQL还支持在线建索引的功能，建索引的过程可以不锁更新操作。 从PostgreSQL9.1开始，支持同步复制功能（synchronous replication），通过master和slave之间的复制可以实现零数据丢失的高可用方案。 另外，由于MySQL对SQL语法支持的功能较弱，基本上不适合做数据仓库。虽然也有些厂商开发了基于MySQL的数据仓库存储引擎（如Infobright），但这个方案只是解决了部分数据仓库的问题，SQL功能弱的问题仍无法完全解决。另外，Infobright的社区版本功能上还有很多的限制，如不支持数据更新，不支持太多的并发执行（最多支持十几个）等。而PostgreSQL不仅支持复杂的SQL，还支持大量的分析函数，非常适合做数据仓库。 PostgreSQL数据库中还有一些支持移动互联网时代的新功能，如空间索引。PostGIS是最著名的一个开源GIS系统，它是PostgreSQL中的一个插件，通过它可以很方便地解决LBS中的一些位置计算问题。 综上所述，PostgreSQL数据库是一个功能强大，又带有移动互联网特征的开源数据库。如果你仅仅是想把数据库作为一个简单的存储功能使用（如一些大的互联网公司），一些较复杂的功能都想放在应用中来实现，那么选择MySQL或一些NoSQL产品都是合适的；如果你应用的数据访问很简单（如大多数的blog系统），那么后端使用MySQL也是很合适的。但如果你的应用不像blog系统那么简单，而你又不想消耗太多的开发资源，那么PostgreSQL是一个明智的选择。最有说服力的例子就是图片分享公司instagram，在使用python+PostgreSQL架构后，只是十几个人就支持起了整个公司的业务。在数据库中使用PostgreSQL的感觉，就像在开发语言中使用python，会让你的工作变得简洁和高效。 1.2.2　PostgreSQL与Oracle数据库的对比 从功能上说，PostgreSQL要比Oracle数据库稍弱，如不支持索引组织表等。毕竟Oracle数据库是目前功能最强大的商业数据库，但PostgreSQL算是功能最强大的开源数据库。 PostgreSQL与Oracle有很多相似之处：都是使用共享内存的进程结构，客户端与数据库服务器建立一个连接后，数据库服务器就启动一个进程为这个连接服务，这与MySQL的线程模型不一样。此外，PostgreSQL的WAL日志与Oracle的Redo日志都是记录物理块数据变化的，这与MySQL的binlog也不同。 PostgreSQL与Oracle的不同之处在于：PostgreSQL有更多的支持互联网特征的功能。如PostgreSQL数据类型支持网络地址类型、XML类型、JSON类型、UUID类型，以及数组类型，有强大的正则表达式函数，where条件中可以使用正则表达式匹配，可以使用Python、Perl等语言写存储过程等。 另外，PostgreSQL更小巧。Oracle安装包动则几个GB以上，PostgreSQL安装包只有几十MB大小。在任何一个环境都可以容易地安装PostgreSQL。 1.3　小结 本章主要给大家介绍了什么是PostgreSQL数据库，它有哪些强大的功能，以及目前的一些应用情况，以便大家对PostgreSQL有一个初步的认识。 第2章 PostgreSQL安装与配置 本章将着重介绍PostgreSQL数据库的安装和配置方法。安装方法分为两类：一种是从二进制安装包进行安装，另一种是从源码安装。各个Linux的发行版本中，都内置了PostgreSQL的二进制安装包，但内置的版本可能较旧。二进制包安装的方法一般都是通过不同发行版本的Linux下的包管理器进行的，如在Redhat下是yum，在Ubuntu下是apt-get。使用源码安装更灵活，用户可以有更多的选择，可以选择较新的版本、配置不同的编译选项，编译出用户需要的功能。 2.1　从发行版本安装 2.1.1　在Debian或Ubuntu下的安装 在Debian和Ubuntu下一般使用apt-get命令或aptitude命令来安装软件，命令如下： sudo apt-get install postgresql 安装完毕后，PostgreSQL数据库就启动了，如果想进入数据库，需要切换到postgres用户下： su - postgres 然后使用psql连接到数据库中，从操作系统下的“postgres”用户（注意这里指的不是数据库中的postgres用户）连接数据库是不需要密码的，如下： root@osdba-laptop:~# sudo su - postgres postgres@osdba-laptop:~$ psql psql (9.1.9) Type "help" for help. postgres=# \l                                  List of databases    Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access  privileges    -----------+----------+----------+------------+------------+--------------------  postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |   template0  | postgres | UTF8    　| en_US.utf8 | en_US.utf8 | =c/postgres +            |          |          |            |            |  postgres=CTc/postgres  template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +            |          |          |            |            |  postgres=CTc/postgres (3 rows) postgres=# \q 在上面的示例中，使用psql命令连接PostgreSQL数据库，psql是PostgreSQL中的客户端工具。“\l”是列出所有数据库的命令，“\q”是退出psql的命令，在后面的章节中还会详细介绍psql的使用方法。 在Debian或Ubuntu下，使用apt-get安装完成的PostgreSQL数据库的数据目录在/var/lib/postgresql/<dbversion>/main目录下： postgres@osdba-laptop:~$ cd /var/lib/postgresql/9.1/main postgres@osdba-laptop:~/9.1/main$ ls -l total 56 drwx------ 5 postgres postgres 4096 Jun 29 11:55 base drwx------ 2 postgres postgres 4096 Jun 29 11:55 global drwx------ 2 postgres postgres 4096 Jun 29 11:55 pg_clog drwx------ 4 postgres postgres 4096 Jun 29 11:55 pg_multixact drwx------ 2 postgres postgres 4096 Jun 29 11:55 pg_notify drwx------ 2 postgres postgres 4096 Jun 29 11:55 pg_serial drwx------ 2 postgres postgres 4096 Jun 29 12:00 pg_stat_tmp drwx------ 2 postgres postgres 4096 Jun 29 11:55 pg_subtrans drwx------ 2 postgres postgres 4096 Jun 29 11:55 pg_tblspc drwx------ 2 postgres postgres 4096 Jun 29 11:55 pg_twophase -rw------- 1 postgres postgres    4 Jun 29 11:55 PG_VERSION drwx------ 3 postgres postgres 4096 Jun 29 11:55 pg_xlog -rw------- 1 postgres postgres  133 Jun 29 11:55 postmaster.opts -rw------- 1 postgres postgres  100 Jun 29 11:55 postmaster.pid lrwxrwxrwx 1 root     root      36 Jun 29 11:55 server.crt ->  /etc/ssl/certs/ssl-cert-snakeoil.pem lrwxrwxrwx 1 root     root     38 Jun 29 11:55 server.key ->  /etc/ssl/private/ssl- cert-snakeoil.key 安装完成后，可以使用Linux下的服务管理命令service来启停数据库： osdba@osdba-laptop:~$ sudo service postgresql status 9.1/main (port 5432): online osdba@osdba-laptop:~$ sudo service postgresql stop  * Stopping PostgreSQL 9.1 database server [ OK ] osdba@osdba-laptop:~$ sudo service postgresql start  * Starting PostgreSQL 9.1 database server [ OK ]  osdba@osdba-laptop:~$ 2.1.2在Redhat、CentOS或Fedora下的安装 在Redhat、CentOS或Fedora下可使用yum工具来安装PostgreSQL，但这些系统的软件库中自带的PostgreSQL版本较低，其版本情况见表2-1。 表2-1Linux发行版本自带的PostgreSQL版本 Linux发行版本 自带的PostgreSQL版本 RHEL/CentOS/SL 5 8.1 (also supplies package postgresql84) RHEL/CentOS/SL 6 8.4 Fedora 16、Fedora 17 9.1 Fedora 18 9.2 如果上面的PostgreSQL版本能满足你的需要，可按下面的步骤进行安装，否则就参照2.3.1节中的内容进行安装。 安装命令如下： yum install postgresql-server.x86_64 具体安装过程如下： [root@localhost ~]# yum install postgresql-server.x86_64 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile  * base: mirrors.163.com  * extras: mirrors.163.com  * updates: mirrors.163.com Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package postgresql-server.x86_64 0:8.4.13-1.el6_3 will be installed --> Processing Dependency: postgresql-libs(x86-64) = 8.4.13-1.el6_3 for package: postgresql-server-8.4.13-1.el6_3.x86_64 --> Processing Dependency: postgresql(x86-64) = 8.4.13-1.el6_3 for package: postgresql-server-8.4.13-1.el6_3.x86_64 --> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-server-8.4.13-1.el6_3.x86_64 --> Running transaction check ---> Package postgresql.x86_64 0:8.4.13-1.el6_3 will be installed ---> Package postgresql-libs.x86_64 0:8.4.13-1.el6_3 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================  Package                                  Arch　　　　　 Version                               Repository                 　  Size ================================================================================ Installing:  postgresql-server                        x86_64      8.4.13-1.el6_3                         base                        3.4 M Installing for dependencies:  postgresql               　　　           x86_64                         8.4.13-1.el6_3                         base                        2.8 M  postgresql-libs                          x86_64                8.4.13-1.el6_3                         base                        200 k Transaction Summary ================================================================================ Install       3 Package(s) Total size: 6.4 M Installed size: 29 M Is this ok [y/N]: Y yum还会让你选择是否把一些依赖的包也安装上，当然要选择“Y”： Downloading Packages: warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 Importing GPG key 0xC105B9DE:  Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org>  Package: centos-release-6-4.el6.centos.10.x86_64   (@anaconda-CentOS-201303020151.x86_64/6.4)  From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 Is this ok [y/N]: Y Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction   Installing : postgresql-libs-8.4.13-1.el6_3.x86_64                                                                                          1/3    Installing : postgresql-8.4.13-1.el6_3.x86_64                                                                                               2/3    Installing : postgresql-server-8.4.13-1.el6_3.x86_64                                                                                        3/3    Verifying  : postgresql-server-8.4.13-1.el6_3.x86_64 1/3    Verifying  : postgresql-libs-8.4.13-1.el6_3.x86_64                                                                                          2/3    Verifying  : postgresql-8.4.13-1.el6_3.x86_64                                                                                               3/3  Installed:   postgresql-server.x86_64 0:8.4.13-1.el6_3                                                                                                       Dependency Installed:   postgresql.x86_64 0:8.4.13-1.el6_3 postgresql-libs.x86_64 0:8.4.13-1.el6_3 Complete! [root@localhost ~]# 这样就安装好了。 在RedHat下，安装好后，PostgreSQL服务并没有启动： [root@localhost ~]# service postgresql status postmaster is stopped 直接启动会报错： [root@localhost ~]# service postgresql start /var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.                                                                [FAILED] 上面是在提示数据库还没有初使用化，请按提示把数据库初使用化： [root@localhost ~]# service postgresql initdb Initializing database:                                     [  OK  ] 再启动数据库： [root@localhost ~]# service postgresql status postmaster (pid  1345) is running... 切换到操作系统下的“postgres”用户，登录数据库： [root@localhost ~]# su - postgres -bash-4.1$ psql psql (8.4.13) Type "help" for help. postgres=# \l                                   List of databases     Name   |  Owner   | Encoding |  Collation  |    Ctype    |   Access  privileges    -----------+----------+----------+-------------+-------------+------------------  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |   template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                                                              :  postgres=CTc/postgres  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                                                              :  postgres=CTc/postgres (3 rows) postgres=# 这样就可以了。 也可以使用下面的命令把第三方贡献的软件包安装上： yum install postgresql-contrib.x86_64 在RedHat或CentOS下，默认安装上的PostgreSQL的数据目录在/var/lib/pgsql/data目录下，如下： -bash-4.1$ ls -l /var/lib/pgsql/data total 80 drwx------. 5 postgres postgres  4096 Jun 30 11:07 base drwx------. 2 postgres postgres  4096 Jun 30 11:08 global drwx------. 2 postgres postgres  4096 Jun 30 11:07 pg_clog -rw-------. 1 postgres postgres  3411 Jun 30 11:07 pg_hba.conf -rw-------. 1 postgres postgres  1631 Jun 30 11:07 pg_ident.conf 图 2-1 PostgreSQL官方网站drwx------. 4 postgres postgres  4096 Jun 30 11:07 pg_multixact drwx------. 2 postgres postgres  4096 Jun 30 11:13 pg_stat_tmp drwx------. 2 postgres postgres  4096 Jun 30 11:07 pg_subtrans drwx------. 2 postgres postgres  4096 Jun 30 11:07 pg_tblspc drwx------. 2 postgres postgres  4096 Jun 30 11:07 pg_twophase -rw-------. 1 postgres postgres     4 Jun 30 11:07 PG_VERSION drwx------. 3 postgres postgres  4096 Jun 30 11:07 pg_xlog -rw-------. 1 postgres postgres 16886 Jun 30 11:07 postgresql.conf -rw-------. 1 postgres postgres    57 Jun 30 11:08 postmaster.opts -rw-------. 1 postgres postgres    45 Jun 30 11:08 postmaster.pid 2.1.3　在Windows下的安装 在Windows安装时，先要到官网上下载PostgreSQL的Windows安装包，如图2-1所示。 图 2-1　PostgreSQL官方网站 点页面中的“download”，进入下载页面，如图2-2所示。 图2-2　PostgreSQL官方下载页面 然后选择下载安装包的类型为“Windows”，进入“Windows”安装包的下载界面，如图2-3所示。 图2-3　PostgreSQL Windows版本下载页面 这里需要根据你的Windows是32位的还是64位来选择下载合适的安装包，如图2-4所示。 图2-4　PostgreSQL Windows版本下的各种类型下载页面 因为Windows版本的安装包，是Enterprise DB公司制作的，所以下载时会显示EnterpriseDB公司的界面，如图2-5所示。 图2-5　PostgreSQL Windows版本下载转到EnterpriseDB公司页面 如果在可以下载时就选择运行，那么在下载后，就会直接运行安装程序，当然也可以在下载完后，再双击安装程序，如图2-6所示。 安装程序运行后，即会显示出安装向导，点“Next”即可，如图2-7所示。   进入选择安装目录界面，一般选择默认安装目录即可，可直接点“Next”，如图2-8所示。 进入选择数据目录的界面，根据实际需要，选择具体的目录，然后点“Next”，如图2-9所示。 进入数据库超级用户postgres的密码设置界面，以后可以使用这个超级用户增加更多的数据库用户，设置完后点“Next”，如图2-10所示。  然后进入选择数据库监听端口的界面，如果5432这个端口没有被别的应用程序占用，那么可使用这个默认端口，点“Next”，如图2-11所示。 选择语言，使用默认设置就可以了，点“Next”，如图2-12所示。 进入准备安装的界面，点“Next”，如图2-13所示。 开始安装，如图2-14所示。 安装完毕后，会问你是否需要使用“Stack Builder”安装一些附加的软件。Stack Builder是一个安装PostgreSQL附加软件的图形化工具。如果不需要，可以把选择框中的勾去掉，直接结束安装。     如果没有去掉，则会出现“Stack Builder”的界面，在该界面中会提示你为哪一个PostgreSQL安装附加软件（出现此提示的原因是可以安装多个不同版本的PostgreSQL），如图2-16所示。 这时会出现一个可以选择安装附加软件的界面，根据需要选择了附加软件后，点“Next”，如图2-17所示。   如果是第一次安装，不知道要安装哪些附加软件，可以点取消。以后需要时可以再次运行“Stack Builder”安装相应的附加软件。 2.1.4　发行版安装总结 Windows下的安装是比较简单的，只需要运行图形界面，基本上在安装的过程中点选“Next”项就可以完成安装。 Linux下各发行版本则使用相应版本的包管理器来进行安装即可。 前面曾提到，从发行版本安装PostgreSQL，所安装的一般不是最新版本，如果想安装最新版本的PostgreSQL，则要看下面介绍的方法或见2.3.1节的技巧。 2.2　从源码安装 2.2.1　编译安装过程介绍 这里先把大致的安装过程介绍一下。 第一步：下载源代码。 第二步：编译安装。过程与Linux下其他软件的编译安装过程相同，都是“三板斧”： ./configure  make make install 第三步：编译安装完成后执行如下步骤。 1）使用initdb命令初使用化数据库簇。 2）启动数据库实例。 3）创建一个应用使用的数据库。 数据库簇是指数据库实例管理的系统文件和各个数据库文件的一个集合。 2.2.2　下载源代码 打开PostgreSQL的官方网站www.postgresql.org，如图2-18所示。 图2-18　PostgreSQL官方网站界面 点击网站菜单中的download，进入下载页面，如图2-19所示。 图2-19　PostgreSQL官方网站中的源代码下载界面 在下载页面中点左侧的“Source”，进入源代码下载页面，如图2-20所示。 图2-20　PostgreSQL官方源码下载中的选择版本界面 在源代码页面中选择合适的版本，比如v9.2.4，如图2-21所示。 图2-21　PostgreSQL官方源码下载中的选择v9.2.4版本界面 然后，在上面的页面中选择合适的压缩包下载就可以了，一般选择bz2的压缩包，因为这种格式体积较小。 2.2.3　编译及安装 默认情况下，安装会用到数据库中的压缩功能，而这个功能的实现需要第三方的压缩开发包zlib支持，在不同的Linux发行版本下，此包的名字会不太一样，但包的名字一般都含有“zlib”和“dev”两个字符串，“dev”是“develop”即开发包的意思。如在Ubuntu12.04下，可以使用下面的方法查找包的名称： osdba@osdba-laptop:~$ aptitude search zlib |grep dev p   libghc-bzlib-dev                - Haskell bindings to the bzip2 library      p   libghc-bzlib-dev:i386           - Haskell bindings to the bzip2 library      v   libghc-bzlib-dev-0.5.0.3-77459: -                                            v   libghc-bzlib-dev-0.5.0.3-f7d98  -                                            p   libghc-zlib-bindings-dev        - low-level bindings to zlib                 p   libghc-zlib-bindings-dev:i386   - low-level bindings to zlib                 v   libghc-zlib-bindings-dev-0.1.0. -                                            v   libghc-zlib-bindings-dev-0.1.0. -                                            p   libghc-zlib-conduit-dev         - streaming compression/decompression via co p   libghc-zlib-conduit-dev:i386    - streaming compression/decompression via co v   libghc-zlib-conduit-dev-0.4.0.1 -                                            v   libghc-zlib-conduit-dev-0.4.0.1 -                                            p   libghc-zlib-dev                 - Compression and decompression in the gzip  p   libghc-zlib-dev:i386            - Compression and decompression in the gzip  v   libghc-zlib-dev-0.5.3.3-78ddb:i -                                            v   libghc-zlib-dev-0.5.3.3-7baa4   -                                            p   libghc-zlib-enum-dev            - enumerator interface for zlib compression  p   libghc-zlib-enum-dev:i386       - enumerator interface for zlib compression  v   libghc-zlib-enum-dev-0.2.2.1-16 -                                            v   libghc-zlib-enum-dev-0.2.2.1-f8 -                                            p   libghc6-bzlib-dev               - transitional dummy package                 p   libghc6-zlib-dev                - transitional dummy package                 p   lua-zlib-dev                    - zlib development files for the Lua languag p   lua-zlib-dev:i386               - zlib development files for the Lua languag p   zlib1g-dbg                      - compression library - development          p   zlib1g-dbg:i386                 - compression library - development          i A zlib1g-dev                      - compression library - development          p   zlib1g-dev:i386                 - compression library - development 从上面列出的包来看，只有“zlib1g-dev”的名称与我们需要的zlib开发包最接近，从而确定在Ubuntu12.10上（当然这还需要一些经验）应该安装这个包。 如果想要方便地在psql中使用上下键翻查历史命令，按照PostgreSQL官方手册的说明，还需要安装readline的开发包。与上面的方法类似，先查找包含“readline”和“dev”的包： osdba@osdba-laptop:~$ aptitude search readline |grep dev v   lib32readline-dev               -                                            p   lib32readline-gplv2-dev         - GNU readline and history libraries, develo p   lib32readline6-dev              - GNU readline and history libraries, develo v   lib64readline-dev:i386          -                                            p   lib64readline-gplv2-dev:i386    - GNU readline and history libraries, develo p   lib64readline6-dev:i386         - GNU readline and history libraries, develo p   libghc-readline-dev             - Interface to the GNU readline library      p   libghc-readline-dev:i386        - Interface to the GNU readline library      v   libghc-readline-dev-1.0.1.0-52b -                                            v   libghc-readline-dev-1.0.1.0-69f -                                            i   libreadline-dev                 - GNU readline and history libraries, develo p   libreadline-dev:i386            - GNU readline and history libraries, develo p   libreadline-gplv2-dev           - GNU readline and history libraries, develo p   libreadline-gplv2-dev:i386      - GNU readline and history libraries, develo i A libreadline6-dev                - GNU readline and history libraries, develo p   libreadline6-dev:i386           - GNU readline and history libraries, develo 从上面列出的包来看，只有“libreadline6-dev”的名称与我们需要的readline开发包最接近，所以应该安装这个包。 把前面下载的压缩包解压，如果该压缩包名称为postgresql-9.2.4.tar.bz2，解压命令则为： tar xvf postgresql-9.2.4.tar.bz2 对于PostgreSQL8.X的版本，一般编译安装的第一板斧是使用configure命令，如下： ./configure --prefix=/usr/local/pgsql8.4.17 --enable-thread-safety --with-perl --with-python 对于PostgreSQL9.X的版本，一般编译安装的命令如下： ./configure --prefix=/usr/local/pgsql9.2.4 --with-perl --with-python 对比可以发现，在PostgreSQL8.X中，编译命令里有“--enable-thread-safety”选项，而在PostgreSQL9.X中没有这个选项。因为在日常使用中，一般要求客户端是线程安全的，PostgreSQL9.X考虑到这个问题，默认为线程安全的了，而PostgreSQL8.X没有，所以要加上这个选项。 再看看下面两个选项： --with-perl： 加上这个选项，才能使用perl语法的PL/Perl过程语言写自定义函数，一般都需要。使用这个选项要求先安装perl开发包，该包在Ubuntu或Debian下的名称为：libperl-dev，可使用apt-get install libperl-dev安装。 --with-python：加上这个选项，才能使用python语法的PL/Python过程语言写自定义函数，一般都需要。使用这个选项要求先安装python-dev开发包，该包在Ubuntu或Debian下的名称为：python-dev，可使用apt-get install python-dev安装。 编译安装的第二板斧是运行make命令，这个命令没有什么好说的，直接运行就可以了： make 按官方文档要求，使用make命令时，其版本要在gmake3.8以上，目前绝大多数的Linux发行版本都满足要求，所以在Linux下一般不需要检查make的版本，但如果是在其他非Linux的UNIX平台上，还是先检查一下make的版本比较好，命令如下： osdba@osdba-laptop:~$ make --version GNU Make 3.81 Copyright (C) 2006  Free Software Foundation, Inc. 在其他的UNIX平台上，有可能存在非GNU的make，这时GNU的make的名称会是gmake。 编译安装的第三板斧是运行make install命令。如果是在一般用户下进行编译的，可能对/usr/local目录没有写的权限，而运行make install命令时是需要使用root权限的，所以在Debian或Ubuntu下可以使用sudo: sudo make install 在前面我们看到--prefix设置的路径为“/usr/local/pgsql9.2.4”，如果不设置这个路径，默认的路径将是“/usr/local”，为什么要在此路径上加上PostgreSQL的版本号呢？这是为了升级方便。make install命令运行完之后，还要进入/usr/local目录下，为/usr/local/pgsql9.2.4建立一个/usr/local/pgsql的链接： cd /usr/localL9.2. sudo ln -sf /usr/local/pgsql9.2.4 /usr/local/pgsql 如果PostgreSQL9.2.5发布了，在编译PostgreSQL9.2.5后，只需把现有的数据库停掉，然后把链接/usr/local/pgsql指向先前的版本/usr/local/pgsql9.2.5即可完成升级。是不是觉得很方便呢？ 2.2.4　安装后的配置 安装完后，需要设置PostgreSQL可执行文件的路径： export PATH=/usr/local/pgsql/bin:$PATH 还要设置共享库的路径： export LD_LIBRARY_PATH=/usr/local/pgsql/lib 如果想让以上配置对所有的用户生效，可以把以上内容加到/etc/profile文件中，/etc/profile中的内容看起来类似如下： ... ... ... if [ -d /etc/profile.d ]; then   for i in /etc/profile.d/*.sh; do     if [ -r $i ]; then       . $i     fi   done   unset i fi export PATH=/usr/local/pgsql/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH 如果想让以上配置对当前用户生效，在Linux下可以把以上内容加到.bashrc文件中，在其他UNIX下可以加到.profile文件中。 有人问在Linux下为何不加到.profile文件或.bash_profile文件中，这是因为有时在图形界面下打开一个终端，.profile或.bash_profile不会生效。 2.2.5　创建数据库簇 先设定数据库中数据目录的环境变量： export PGDATA=/home/osdba/pgdata 执行下面的命令创建数据库簇： initdb 这样就完成了。 2.2.6　安装contrib目录下的工具 contrib下面有一些工具比较实用，一般都会安装上，其安装方法也与Linux下的编译过程相同，如下： cd postgresql-9.2.3/contrib make sudo make install 2.2.7　启动和停止数据库 启动数据库的命令为： pg_ctl start -D $PGDATA 其中，环境变量$PGDATA指向具体的PostgreSQL数据库的数据目录，看以下示例： osdba@osdba-laptop:~$ pg_ctl start -D /home/osdba/pgdata  server starting 停止数据库的命令如下： pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE] 其中-m是指定数据库的停止方法，有以下三种： smart：等所有的连接中止后，关闭数据库。如果客户端连接不终止，则无法关闭数据库。 fast：快速关闭数据库，断开客户端的连接，让已有的事务回滚，然后正常关闭数据库。相当于Oracle数据库关闭时的immediate模式。 immediate：立即关闭数据库，相当于数据库进程立即停止，直接退出，下次启动数据库需要进行恢复。相当于Oracle数据库关闭时的abort模式。 PostgreSQL下的immediate关机模式是相当于Oracle中的Abort的关机模式，而Oracle下的immediate关机模式实际上对应的是PostgreSQL下的fast，Oracle DBA尤其要注意这一点。 其中，比较常用的关闭数据库的方法是“fast”方法。 2.2.8　编译安装时的常见问题及解决方法 问题一：./configure时报“error: zlib library not found”错误是怎么回事？报错信息如下： osdba@ubuntu01:~/src/postgresql-9.2.3$ ./configure  --prefix=/usr/local/pgsql9.2.3 --with-perl --with-python checking build system type... x86_64-unknown-linux-gnu .... .... checking for inflate in -lz... no configure: error: zlib library not found If you have zlib already installed, see config.log for details on the failure.  It is possible the compiler isn't looking in the proper directory. Use --without-zlib to disable zlib support. 答：这是因为没有安装zlib开发包，安装后将不再报错。 问题二：已安装了“libreadline6”的包，但./configure时仍报“error: readline library not found”错误是怎么回事？报错信息如下： osdba@ubuntu01:~/src/postgresql-9.2.3$ ./configure  --prefix=/usr/local/pgsql9.2.3 --with-perl --with-python checking build system type... x86_64-unknown-linux-gnu ... ... checking for library containing readline... no configure: error: readline library not found If you have readline already installed, see config.log for details on the failure.  It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support. 答：包安装错了，是需要安装开发包，即安装libreadline6-dev这个包，而不是libreadline6这个包。 问题三：在运行./configure命令时报以下警告，是否会导致编译出来的PostgreSQL的功能缺失？警告信息如下： checking for bison... no configure: WARNING: *** Without Bison you will not be able to build PostgreSQL from Git nor *** change any of the parser definition files.  You can obtain Bison from *** a GNU mirror site.  (If you are using the official distribution of *** PostgreSQL then you do not need to worry about this, because the Bison *** output is pre-generated.) checking for flex... no configure: WARNING: *** Without Flex you will not be able to build PostgreSQL from Git nor *** change any of the scanner definition files.  You can obtain Flex from *** a GNU mirror site.  (If you are using the official distribution of *** PostgreSQL then you do not need to worry about this because the Flex *** output is pre-generated.) 答：不会影响编译出来的PostgreSQL功能。这个警告的意思是没有bison和flex是无法使用git方式编译的。这里没有使用git，所以没有关系。bison是自动生成语法分析器的程序，flex则是自动生成词法分析器的程序，在PostgreSQL主要用于SQL的词法解析和语法解析。因为在源码包中已经生成了词法解析和语法解析的C源代码，所以没有bison和flex，也能正常编译。当然也可以把bison和flex这两个工具安装上，命令如下： sudo aptitude install bison flex 问题四：在运行make时报“cannot find -lperl”的错误为什么？报错信息如下： gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith  -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard  -fpic -shared -o plperl.so plperl.o SPI.o Util.o -L../../../src/port  -Wl,--as-needed -Wl,-rpath,'/usr/lib/perl/5.14/CORE',--enable-new-dtags   -fstack-protector -L/usr/local/lib  -L/usr/lib/perl/5.14/CORE -lperl -ldl -lm  -lpthread -lc -lcrypt  /usr/bin/ld: cannot find -lperl collect2: error: ld returned 1 exit status make[3]: *** [plperl.so] Error 1 make[3]: Leaving directory `/home/osdba/src/postgresql-9.2.3/src/pl/plperl' make[2]: *** [all-plperl-recurse] Error 2 make[2]: Leaving directory `/home/osdba/src/postgresql-9.2.3/src/pl' make[1]: *** [all-pl-recurse] Error 2 make[1]: Leaving directory `/home/osdba/src/postgresql-9.2.3/src' make: *** [all-src-recurse] Error 2 答：这是在./configure时加了--with-perl，却没有安装perl开发包导致的。注意，若没有安装perl开发包，在运行./configure时并不报错，而是到运行make命令的时候才报错。在Debian或Ubuntu下，只要安装libperl-dev包即可： sudo aptitude install libperl-dev 2.3　安装技巧介绍 2.3.1　在Redhat、CentOS下使用二进制包安装较新版本的方法 如果认为Redhat或CentOS上自带的PostgreSQL版本太低，想要使用新版本，可以使用下面的方法安装。 使用PostgreSQL官方的RPM包，将新版本信息加到版本库中： rpm -i  http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm 然后使用yum install 命令安装新版本： yum install postgresql92-server.x86_64 再使用1.1.2节介绍的方法安装新版本的PostgreSQL: [root@localhost ~]# service postgresql-9.2 status  is stopped  [root@localhost ~]# service postgresql-9.2 initdb Initializing database:                                     [  OK  ] [root@localhost ~]# service postgresql-9.2 start Starting postgresql-9.2 service:                           [  OK  ] 登录数据库： [root@localhost ~]# su - postgres -bash-4.1$ psql  psql (9.2.4) Type "help" for help. postgres=# \l                                   List of databases     Name   |  Owner   | Encoding |   Collate   |    Ctype    |   Access  privileges    -----------+----------+----------+-------------+-------------+------------------  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |   template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +            |          |          |             |             |  postgres=CTc/postgres  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +            |          |          |             |             |  postgres=CTc/postgres (3 rows) postgres=# \q 使用下面命令把相关的第三方贡献包也安装上： yum install postgresql92-contrib.x86_64 新版本的PostgreSQL的数据目录在/var/lib/pgsql/<version>/data目录下，“version”代表PostgreSQL的版本，如9.2版本就安装在/var/lib/pgsql/9.2/data目录下。 2.3.2　如何使用较大的数据块提高I/O性能 在数据仓库中使用PostgreSQL时，若希望使用较大的数据块提高I/O性能怎么办？要解决这类问题，只能采用从源码安装的方法，在执行./configure命令时指定较大的数据块，一般也需要指定较大的WAL日志块和WAL日志文件的大小。如想指定128KB的数据块、128KB的WAL日志块、64MB的WAL日志文件，则configure命令如下： ./configure --prefix=/usr/local/pgsql9.2.4 --with-perl --with-python  --with-blocksize=128 --with-wal-blocksize=128 --with-wal-segsize=64 使用此时编译出来的PostgreSQL程序创建的PostgreSQL数据库，不能使用其他块大小的PostgreSQL程序启动。 2.4　PostgreSQL的简单配置 本节将简单介绍PostgreSQL的配置方法，更具体的配置操作会在后面的章节中介绍。PostgreSQL数据库的配置主要是通过修改数据目录下的postgresql.conf文件来实现的。 2.4.1　修改监听的IP和端口 在数据目录下编辑postgresql.conf文件，找到如下内容： #listen_addresses = 'localhost'         # what IP address(es) to listen on; #port = 5432                            # (change requires restart) 其中，参数“listen_addresses”表示监听的IP地址，默认是在“localhost”处监听，也就是“127.0.0.1”的IP地址上监听，这会让远程的主机无法登录这台数据库，如果想从其他的机器上登录这台数据库，需要把监听地址改成实际网络的地址，一种简单的方法是，把这个地址改成“*”，表示在本地的所有地址上监听。 参数“port”表示监听的数据库端口，默认为“5432”，可以不更改。如果一台机器上安装了几个数据库实例（如安装了几个不同版本的PostgreSQL），可以设置为不同的端口。 修改了这两个参数后，需要重启数据库才能生效。 2.4.2　与数据库log相关的参数 来看看与log相关的几个参数： 日志的收集一般是要打开的，所以需要设置： logging_collector = on 日志的目录一般使用默认值就可以了： log_directory = 'pg_log' 日志的切换和是否选择覆盖则可以使用如下几种方案。 方案一：每天生成一个新的日志文件。 配置方法如下： log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = off log_rotation_age = 1d log_rotation_size = 0 方案二：每当日志写满一定的大小（如10MB空间），则切换一个日志。 配置方法如下： log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = off log_rotation_age = 0 log_rotation_size = 10M 方案三：只保留7天的日志，进行循环覆盖。 配置方法如下： log_filename = 'postgresql-%a.log'                                        #  log_truncate_on_rotation = on  log_rotation_age = 1d log_rotation_size = 0  2.4.3　内存参数的设置 PostgreSQL安装完毕后，可以修改以下两个主要内存参数。 shared_buffers：共享内存的大小，主要用于共享数据块。 work_mem：单个SQL执行时，排序、hash join所使用的内存，SQL运行完后，内存就释放了。 shared_buffers默认值为32MB，work_mem为1MB，如果你的机器上有足够的内存，可以把这个参数改得大一些，这样数据库就可以缓存更多的数据块，当读取数据时，就可以从共享内存中读，而不需要再从文件上去读取。 work_mem设置大一些，会让排序操作快一些。 2.5　小结 本章讲解了PostgreSQL在不同平台下的二进制安装方法，以及从源代码进行安装的方法。从前面的叙述中可以看出，在多数Linux发行版本中都自带了PostgreSQL的二进制安装包，可以直接使用Linux发行版本中的包管理器进行安装。不过这些自带的PostgreSQL版本都比较旧，如果想安装较新的PostgreSQL版本，可以从源代码进行编译安装。源代码编译安装也比较简单，要注意的是，需要先把一些依赖的开发包安装上。 第3章 SQL语言入门 SQL是结构化查询语言（STRUCTURED QUERY LANGUAGE）的简称，它是最重要的关系型数据库操作语言，并且它的影响已经超出了数据库领域。比如，在Hadoop中的Hive就是一个SQL接口。 本章将介绍一些通用的、最基础的SQL语法知识，以便于没有接触过数据库的读者能掌握最基础的数据库知识。这些使用语法不仅适用于PostgreSQL数据库，也适用于其他关系型数据库，如MySQL、Oracle。本章是为从没有接触过SQL的读者准备的，对于已有SQL基础的读者，可以略过此章。 3.1　SQL语句语法简介 3.1.1　语句的分类 SQL命令一般分为DQL、DML、DDL几类。 DQL：数据查询语句，基本就是SELECT查询命令，用于数据查询。 DML：Data Manipulation Language的简称，即数据操纵语言，主要用于插入、更新、删除数据，所以也分为INSERT、UPDATE、DELETE三种语句。 DDL：Data Definition Language的缩写，即数据定义语言，主要用于创建、删除，以及修改表、索引等数据库对象语言。 3.1.2　词法结构 每次执行的SQL可以由多条SQL命令组成。多条SQL命令之间由分号（“;”）分隔。 每个SQL命令由一系列的记号组成，这些记号可以由关键字、标识符、双引号包围的标识符、常量、单引号包围的文本常量和特殊的字符等组成。在 SQL 命令里可以有注释，这些注释在PostgreSQL中等效于空白。 举个例子，下面的命令从SQL的语法上来说是合法的： SELECT * FROM OSDBA_TABLE01; UPDATE OSDBA_TABLE SET COL1 = 614; INSERT INTO OSDBA_TABLE VALUES (232, 'hello osdba'); 该SQL由三条命令组成。在SQL中，多行命令可以在一行中，也可以在多行中。此外，单条命令也可以占用多行。 SQL命令并未严格地像计算机语言一样明确标识哪些是命令、哪些是操作数或参数。SQL的语法主要是让你比较直观地理解其意思。比如，查询一个表的数据，就是由“SELECT”+“要查询的各列”+“FROM 表”这样的语法组成的。后面的几节会详细叙述SQL的用法。 3.2　DDL语句 DDL语句是创建、修改和删除表的语句，想要掌握SQL语言，必须对它有一定的了解。 3.2.1　建表语句 表是关系型数据库中最基本的对象，数据库中的表与实际生活中的二维表格很相似，有很多列也有很多行，每一列有一个名称，不同的列有不同的数据类型，比如，列可能是数字、文本字符串，也可能是日期类型。建表语句的一个简单语法如下： CREATE TABLE table_name ( col01_namme data_type, col02_namme data_type, col03_namme data_type, col04_namme data_type, }; 其中“CREATE”、“TABLE”为关键字，是不变的，从字面上也很好理解，表示创建表。“table_name”表示表名，“col01_name”、 “col02_name”、 “col03_name”、 “col04_name”分别表示列名。“data_type”表示数据类型，不同的数据库系统有不同的数据类型名称，即使是相同意思的整数类型，在不同的数据库系统中也有不同的类型名称。变长的字符串在大多数数据库中都可使用 “varchar”类型，比如PostgreSQL、MySQL和Oracle数据库等。整形数据在PostgreSQL和MySQL都可以使用“int”类型。日期类型的名称一般为“date”。例如，要创建一张分数表score，包括“学生名称（student_name）”、“语文成绩（chinese_score）”、“数学成绩（math_score）”、“考试日期（test_date）”四列，则创建这个表的SQL如下： CREATE TABLE score ( student_name varchar(40), chinese_score int, math_score int, test_date date ); 如果按前面的安装步骤安装完了数据库，之后就可以使用psql工具连接到PostgreSQL数据库了，执行上面的建表语句，如下： osdba=# CREATE TABLE score ( osdba(# student_name varchar(40), osdba(# chinese_score int, osdba(# math_score int, osdba(# test_date date osdba(# ); CREATE TABLE osdba=# 在psql中，可使用\d显示数据库中有哪些表，如下： osdba=# \d        List of relations  Schema | Name  | Type  | Owner  --------+-------+-------+-------  public | score | table | osdba (1 row) 这样就看到了我们建的表。 使用“\d score”可以显示这张表的定义情况： osdba=# \d score                Table "public.score"     Column     |         Type          | Modifiers  ---------------+-----------------------+-----------  student_name  | character varying(40) |   chinese_score | integer               |   math_score    | integer               |   test_date     | date                  | 显示列的类型“character varying(40)”实际上与“varchar(40)”的意思是完全一样的，“int”与“integer”的意思也是一样的。 在建表的时候，可以指定表的主键，主键是表中行的唯一标识，这个唯一标识是不能重复的。在创建表的语句中，可以在列定义后面用“primary key”来指定这一列为主键，如下面的学生表： CREATE TABLE student(no int primary key, student_name varchar(40), age int); 在该表中，学号(no)为主键，则在该列的定义后面加了“primary key”。在psql中演示如下： osdba=# CREATE TABLE student(no int primary key, student_name varchar(40), age int); NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student" CREATE TABLE 细心的读者会注意到“NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student"”这句提醒，它表示系统为主键自动创建了一个隐含的索引“student_pkey”。 3.2.2　删除表语句 删除表的语法比较简单，如下： DROP TABLE table_name; 其中“table_name”表示要删除的表名。假设要删除前面创建的表student，则可以使用下面的SQL： DROP TABLE student; 3.3　DML语句 DML用于插入、更新和删除数据。主要包含INSERT语句、UPDATE语句和DELETE语句。 3.3.1　插入语句 可以使用下面的语句往前面创建的学生表（student）中插入数据： INSERT INTO student VALUES(1, '张三', 14); 由此可以看出，INSERT语句的语法为：以“INSERT INTO”关键字为首，后面跟表名，然后再跟“VALUES”关键字，最后是由小括号包围起来的以逗号分隔的各列数据，数据的顺序与表定义时表列的顺序是一样的。当然也可以在表名后指定要插入数据列的顺序，如下所示： INSERT INTO student(no, age, student_name)  VALUES(2, 13, '李四'); 在插入数据时，也可以指定某些列不插入数据，这时这些列的数据会被置为空，如下： INSERT INTO student(no, student_name)  VALUES(2, '王二'); 如果在psql中执行了下面的语句，使用SELECT语句就可以查询出数据，查询的语句为： SELECT * FROM student; SELECT语句的具体用法会在后面的章节中介绍，现在只需要知道这么用就可以了。 我们查看到的数据如下： osdba=# select * from student;  no | student_name | age  ----+--------------+-----   1 | 张三         |  14   2 | 李四         |  13   3 | 王二         |     (3 rows) 从上面可以看出，在插入数据时，没有提供的列数据会被置为NULL。 3.3.2　更新语句 假设要把student表中所有学生的年龄（age）更新为“15”，则更新语句如下： UPDATE student SET age = 15; 从上面的语句可以看出，更新语句以“UPDATE” 关键字开始，后面跟表名，然后是“SET”关键字，表示要设置的数据，再后面就是要设置的数据的表达式“age = 15”，设置数据的表达式也很简单，就是“列名=数据”的格式。 实际执行的效果如下： osdba=# UPDATE student SET age = 15; UPDATE 3 osdba=# select * from student;  no | student_name | age  ----+--------------+-----   1 | 张三          |  15   2 | 李四          |  15   3 | 王二          |  15 (3 rows) 在更新数据时，还可以指定过滤表达式“WHERE”，从而指定更新哪条数据或哪些数据，比如，要更新学号(no)为3的学生年龄为14岁，则使用下面的语句： UPDATE student SET age =14 WHERE no = 3; 在SET子句中，还可以同时更新多个列的值，如下所示： UPDATE student SET age =13, student_name='王明充' WHERE no = 3; 3.3.3　删除语句 如果想删除学号（no）为3的记录，语句如下： DELETE FROM student WHERE no = 3; 由此可见删除语句比较简单，以“DELETE FROM”开始，后面跟表名，然后加“WHERE”子句用于指定要删除的记录。 当然也可以没有“WHERE”子句，这表明要删除整个表的数据，删除表student中所有数据的语句如下： DELETE FROM student; 3.4　查询语句 3.4.1　单表查询语句 查询student表中所有数据的语句为： select no, student_name, age from student; 其中“SELECT”是关键字，表示要查询，后面跟多个列名，各列之间使用逗号分隔。其后的“FROM”是关键字，后面跟表的名称。各个列可以是表的列名，也可以是一个表达式，如下： select age+5 from student; 表达式中可以包括表的列，也可以只是一个与表列无关的表达式，如下： select no, 3+5 from student; 当表达式与表的列无关时，在PostgreSQL和MySQL中可以不使用“FROM 表名”，这样一来，就可以当作计算器使用了： osdba=# select 55+88;  ?column?  ----------       143 (1 row) osdba=# select 10*2,3*5+2;  ?column? | ?column?  ----------+----------        20 |       17 (1 row) 如果想查询表中所有列的数据，则可以使用“*”代表所有列，如下： select * from student; 3.4.2　过滤条件的查询 SELECT语句后面可以通过指定WHERE子句来指定要查询哪条记录或哪些记录。比如，要查询学号为3的记录，其SQL语句为： osdba=# SELECT * FROM student where no=3;  no | student_name | age  ----+--------------+-----   3 | 王明充        |  13 (1 row) 在WHERE条件中也可以使用大于、小于的表达式。比如，想查询年龄大于等于15岁的学生，其语句如下： osdba=# SELECT * FROM student where age >= 15;  no | student_name | age  ----+--------------+-----   1 | 张三         |  15   2 | 李四         |  15 (2 rows) 3.4.3　排序 使用排序子句可以对查询出的数据进行排序，排序子句是在SELECT语句后面再加上“ORDER BY”子句。比如，希望查询出来的结果按年龄排序，则查询语句如下： osdba=# SELECT * FROM student ORDER BY age;  no | student_name | age  ----+--------------+-----   3 | 王明充        |  13   1 | 张三          |  15   2 | 李四          |  15 (3 rows) 排序子句“ORDER BY”应该在“WHERE”子句之前，如果顺序错了，会报错： osdba=# SELECT * FROM student ORDER BY age WHERE age >= 15; ERROR:  syntax error at or near "WHERE" LINE 1: SELECT * FROM student ORDER BY age WHERE age >= 15; 把“ORDER BY”子句放到“WHERE”子句后面就不报错了： osdba=# SELECT * FROM student WHERE age >= 15 ORDER BY age;  no | student_name | age  ----+--------------+-----   1 | 张三 |  15   2 | 李四          |  15 (2 rows) 还可以按多个列进行排序。比如，根据“age”和“student_name”两个列来排序： osdba=# SELECT * FROM student ORDER BY age,student_name;  no | student_name | age  ----+--------------+-----   3 | 王明充        |  13   2 | 李四          |  15   1 | 张三          |  15 (3 rows) 也可以在排序子句的列名后加“DESC”进行倒序排序： osdba=# SELECT * FROM student ORDER BY age DESC;  no | student_name | age  ----+--------------+-----   1 | 张三          |  15   2 | 李四         |  15   3 | 王明充        |  13 (3 rows) osdba=# SELECT * FROM student ORDER BY age DESC,student_name;  no | student_name | age  ----+--------------+-----   2 | 李四          |  15   1 | 张三          |  15   3 | 王明充        |  13 (3 rows) 3.4.4　分组查询 如果需要统计不同年龄的学生人数，可以使用分组查询，分组查询子句的关键字为“GROUP BY”，用法如下： osdba=# SELECT age, count(*) FROM student GROUP BY age;  age | count  -----+-------   15 |     2   13 |     1 (2 rows) 从上面可以看出，使用“GROUP BY”语句时，需要使用聚合函数，常用的聚合函数为“count”、“sum”等。 3.4.5　表join 表join也称为多表关联查询。假设有一张班级表class，建表语句为： CREATE TABLE class(no int primary key, class_name varchar(40)); 表中的“no”表示班级编号，“class_name”表示班级名称。 插入一些测试数据： osdba=# INSERT INTO class VALUES(1,'初二(1)班'); INSERT 0 1 osdba=# INSERT INTO class VALUES(2,'初二(2)班'); INSERT 0 1 osdba=# INSERT INTO class VALUES(3,'初二(3)班'); INSERT 0 1 osdba=# INSERT INTO class VALUES(4,'初二(4)班'); INSERT 0 1 osdba=# SELECT * FROM class;  no | class_name  ----+------------   1 | 初二(1)班   2 | 初二(2)班   3 | 初二(3)班   4 | 初二(4)班 (4 rows) 还有另一张学生表student，建表语句为： CREATE TABLE student(no int primary key, student_name varchar(40), age int, class_no int); 也插入一些数据： osdba=# INSERT INTO student VALUES(1, '张三', 14, 1); INSERT 0 1 osdba=# INSERT INTO student VALUES(2, '吴二', 15, 1); INSERT 0 1 osdba=# INSERT INTO student VALUES(3, '李四', 13, 2); INSERT 0 1 osdba=# INSERT INTO student VALUES(4, '吴三', 15, 2); INSERT 0 1 osdba=# INSERT INTO student VALUES(5, '王二', 15, 3); INSERT 0 1 osdba=# INSERT INTO student VALUES(6, '李三', 14, 3); INSERT 0 1 osdba=# INSERT INTO student VALUES(7, '吴二', 15, 4); INSERT 0 1 osdba=# INSERT INTO student VALUES(8, '张四', 14, 4); INSERT 0 1 osdba=# SELECT * FROM student;  no | student_name | age | class_no  ----+--------------+-----+----------   1 | 张三          |  14 |        1   2 | 吴二          |  15 |        1   3 | 李四          |  13 |        2   4 | 吴三          |  15 |        2   5 | 王二          |  15 |        3   6 | 李三          |  14 |        3   7 | 吴二          |  15 |        4   8 | 张四          |  14 |        4 若现在想查询出每个学生与班级的关系，那么此时就需要关联查询两张表： SELECT student_name, class_name FROM student, class   WHERE student.class_no = class.no; 查询出来的结果如下： osdba=# SELECT student_name, class_name FROM student, class   WHERE student.class_no = class.no;  student_name | class_name  --------------+------------  张三          | 初二(1)班  吴二          | 初二(1)班  李四          | 初二(2)班  吴三          | 初二(2)班  王二          | 初二(3)班  李三          | 初二(3)班  吴二          | 初二(4)班  张四          | 初二(4)班 (8 rows) 表关联查询就是在WHERE条件上加上需要关联的条件（两张表关联）： WHERE student.class_no = class.no; 由于在两张表中，有一些列的名称是重复的，如在表student中no表示学生号，而在表class中表示班级号，所以在关键条件中要明确使用“表名”加“列名”来唯一定位这个列。如果输入的表名比较长，不是很方便，这时可以给表起个别名，如下所示： SELECT student_name, class_name FROM student a, class b  WHERE a.class_no = b.no; 上面的语句中，给表“student”起的别名为“a”，表“class”的别名为“b”，这时条件表达式中“b.no”就代表了表“class”中的“no”列。 在关联查询的WHERE子句中可以再加上其他的过滤条件，如下： osdba=# SELECT student_name, class_name FROM student a, class b  WHERE a.class_no = b.no AND a.age > 14;   student_name | class_name  ---------------+------------   吴二          | 初二(1)班   吴三          | 初二(2)班   王二          | 初二(3)班   吴二          | 初二(4)班 (4 rows) 3.5　其他SQL语句 3.5.1　INSERT INTO... SELECT语句 使用INSERT INTO... SELECT语句可以把数据从一张表插入到另一张表中，这个语句属于DML语句。 假设建了一张学生表的备份表：student_bak CREATE TABLE student_bak(no int primary key, student_name varchar(40), age int, class_no int); 可以使用下面的语句把数据备份到下面这张备份表中： INSERT INTO student_bak SELECT * FROM student; 实际演示如下： osdba=# INSERT INTO student_bak SELECT * FROM student; INSERT 0 8 osdba=# SELECT * FROM student_bak;  no | student_name | age | class_no  ----+--------------+-----+----------   1 | 张三          |  14 |        1   2 | 吴二          |  15 |        1   3 | 李四          |  13 |        2   4 | 吴三          |  15 |        2   5 | 王二          |  15 |        3   6 | 李三          |  14 |        3   7 | 吴二          |  15 |        4   8 | 张四          |  14 |        4 (8 rows) 3.5.2　UNION语句 可以将从两张表查询出来的数据整合在一个结果集下，如： SELECT * FROM student WHERE no = 1 UNION SELECT * FROM student_bak where no = 2; 这里的语法比较简单，把两个SQL用“UNION”关键字连接起来就可以了。 结果如下： osdba=# SELECT * FROM student WHERE no = 1 UNION SELECT * FROM student_bak where no = 2;  no | student_name | age | class_no  ----+--------------+-----+----------   1 | 张三          |  14 |        1   2 | 吴二          |  15 |        1 (2 rows) 注意，UNION可以把结果集中相同的两条记录合并成一条： osdba=# SELECT * FROM student WHERE no = 1 UNION SELECT * FROM student_bak where no = 1;  no | student_name | age | class_no  ----+--------------+-----+----------   1 | 张三          |  14 |        1 (1 row) 如果不想合并，请使用UNION ALL，如下： osdba=# SELECT * FROM student WHERE no = 1 UNION ALL SELECT * FROM student_bak where no = 1;  no | student_name | age | class_no  ----+--------------+-----+----------   1 | 张三          |  14 |        1   1 | 张三          |  14 |        1 (2 rows) 3.5.3　TRUNCATE TABLE语句 TRUNCATE TABLE语句的用途是清空表内容。不带WHERE条件子句的DELETE 语句也表示清空表的内容。从执行结果看，两者实现了相同的功能，但两者实现的原理是不一样的。 TRUNCATE TABLE是DDL语句，即数据定义语句，相当于用重新定义一个新表的方法把原先表的内容直接丢弃了，所以TRUNCATE TABLE执行起来很快，而DELETE 是DML语句，可以认为DELETE 是把数据一条一条地删除，若要删除很多行数据，就会比较慢。 如果想把表student_bak中的数据清理掉，则可以使用如下命令： TRUNCATE TABLE student_bak; 3.6　小结 从前面的叙述可以看出，SQL语言是一种声明式编程语言，与命令式编程语言有较大的差异。声明式编程语言主要是描述用户需要做什么，需要得到什么结果的，不像命令式编辑需要描述怎么做，过程是什么。SQL语言能够智能地实现用户的需要，而不需要用户去关心具体的运行过程。完全用SQL写一个应用程序是不可能的，但非计算机专业人士也可以使用SQL，因为SQL是直观易懂的。

GitLab 官方安装配置要求服务器最低2核4G,如果服务器低于这个要求就不用安装了，因为用户体验超级超级不好。服务器CPU 推荐2核到4核完全就够了在多也是浪费，但内存越大越好最好超过4G。如果服务器内存刚好4G ，GitLab 也能跑起来但是如果你使用的是云计算机的话，你每天都会收到服务器内存告警的消息（并且这台服务器就只跑着一个GitLab ,内存已经见底了）如下图。 我们还是做点什么！比如从启服务器但是很快就发现服务器又报警了看来还是要找个永久解决的办法。 一、修改GitLab 的配置文件 vim /etc/gitlab/gitlab.rb 修改以下项： postgresql['shared_buffers'] //减少数据库缓存(默认为256MB 改为128MB) postgresql['max_worker_processes'] //减少数据库并发数(默认为8 改为4） sidekiq['concurrency'] //减少sidekiq并发数（默认为25 改为 15） 使修改后的配置生效： gitlab-ctl reconfigure gitlab-ctl restart 上述参数可以根据实际公司使用人数进行调整（调整后的参数如下） 二、验证结果 在服务器后台可以明显看到服务器内存使用量明显下降，内存报警也不在被触发了（见下图） （服务器内存使用量将长期在80%，给个建议可以定时重启服务器释放内存，如果长时间不重启服务器内存使用量还是会升到100% 并触发内存报警）