PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户) / iVAN

背景

新用户部署PostgreSQL以及空间数据库插件PostGIS的指南。

内网环境RPM打包(可选项)

如果是内网环境，可以将包全部下载到本地再用rpm安装。

安装epel和postgresql yum rpm两个包后再执行：

1、使用yum-utils的yumdownloader下载需要的安装包，包括依赖包。

yum install -y yum-utils

yumdownloader --resolve --destdir=/data01/pg_rpm coreutils glib2 lrzsz mpstat dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex  openjade bzip2 postgresql10*  postgis24_10*   pgrouting_10*   osm2pgrouting_10*   plpgsql_check_10*   powa_10*  hypopg_10*  citus_10*  cstore_fdw_10*  pg_pathman10* orafce10*

2、其他命令（使用yumdownloader下载包到本地后，下面的可以忽略...）。

例子(安装epel和postgresql yum rpm两个包后再执行) , 注意本地已经安装的包不会被下载，所以假设你就是想下载的话，可以先yum remove一下已安装的包，然后再执行：

mkdir -p /data01/pg_rpm

yum install --downloadonly --downloaddir=/data01/pg_rpm coreutils glib2 lrzsz dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex  openjade bzip2 postgresql10*  postgis24_10*   pgrouting_10*   osm2pgrouting_10*   plpgsql_check_10*   powa_10*  hypopg_10*  citus_10*  cstore_fdw_10* pg_pathman10* orafce10*

列出要安装的包的所有依赖，例如:

yum deplist postgresql10* .................

例子

yum deplist postgresql10* |grep provider|sort|uniq
   provider: bash.x86_64 4.2.46-29.el7_4
   provider: chkconfig.x86_64 1.7.4-1.el7
   provider: glibc.i686 2.17-196.el7
   provider: glibc.x86_64 2.17-196.el7
   provider: krb5-libs.x86_64 1.15.1-8.el7
   provider: libicu-devel.i686 50.1.2-15.el7
   provider: libicu-devel.x86_64 50.1.2-15.el7
   provider: libicu.i686 50.1.2-15.el7
   provider: libicu.x86_64 50.1.2-15.el7
   provider: libselinux.x86_64 2.5-11.el7
   provider: libuuid.x86_64 2.23.2-43.el7
   provider: libxml2.x86_64 2.9.1-6.el7_2.3
   provider: libxslt.x86_64 1.1.28-5.el7
   provider: openldap.x86_64 2.4.44-5.el7
   provider: openssl-libs.x86_64 1:1.0.2k-8.el7
   provider: pam.x86_64 1.1.8-18.el7
   provider: perl-libs.x86_64 4:5.16.3-292.el7
   provider: perl.x86_64 4:5.16.3-292.el7
   provider: postgresql10-devel.x86_64 10.0-1PGDG.rhel7
   provider: postgresql10-libs.x86_64 10.0-1PGDG.rhel7
   provider: postgresql10-server.x86_64 10.0-1PGDG.rhel7
   provider: postgresql10.x86_64 10.0-1PGDG.rhel7
   provider: postgresql-libs.x86_64 9.2.23-1.el7_4
   provider: python-libs.x86_64 2.7.5-58.el7
   provider: readline.x86_64 6.2-10.el7
   provider: shadow-utils.x86_64 2:4.1.5.1-24.el7
   provider: sqlite.x86_64 3.7.17-8.el7
   provider: systemd-libs.x86_64 219-42.el7_4.1
   provider: systemd-sysv.x86_64 219-42.el7_4.1
   provider: systemd.x86_64 219-42.el7_4.1
   provider: tcl.i686 1:8.5.13-8.el7
   provider: tcl.x86_64 1:8.5.13-8.el7
   provider: unixODBC.x86_64 2.3.1-11.el7
   provider: zlib.x86_64 1.2.7-17.el7

环境

1、阿里云ECS

2、OS：CentOS 7.x x64

3、本地盘（40 GB）

4、云盘（200GB），建议根据实际情况配置云盘。建议多块云盘做LVM条带，提高吞吐和IOPS。

5、PostgreSQL 10

6、PostGIS 2.4

安装依赖包

# yum -y install coreutils glib2 lrzsz dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex  openjade bzip2

配置OS内核

1. sysctl

注意某些参数，根据内存大小配置（已说明）

含义详见

《DBA不可不知的操作系统内核参数》

# vi /etc/sysctl.conf  

# add by digoal.zhou
fs.aio-max-nr = 1048576
fs.file-max = 76724600  

# 可选：kernel.core_pattern = /data01/corefiles/core_%e_%u_%t_%s.%p
# /data01/corefiles 事先建好，权限777，如果是软链接，对应的目录修改为777  

kernel.sem = 4096 2147483647 2147483646 512000
# 信号量, ipcs -l 或 -u 查看，每16个进程一组，每组信号量需要17个信号量。  

kernel.shmall = 107374182
# 所有共享内存段相加大小限制 (建议内存的80%)，单位为页。
kernel.shmmax = 274877906944
# 最大单个共享内存段大小 (建议为内存一半), >9.2的版本已大幅降低共享内存的使用，单位为字节。
kernel.shmmni = 819200
# 一共能生成多少共享内存段，每个PG数据库集群至少2个共享内存段  

net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144
# The default setting of the socket receive buffer in bytes.
net.core.rmem_max = 4194304
# The maximum receive socket buffer size in bytes
net.core.wmem_default = 262144
# The default setting (in bytes) of the socket send buffer.
net.core.wmem_max = 4194304
# The maximum send socket buffer size in bytes.
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 1
# 开启SYN Cookies。当出现SYN等待队列溢出时，启用cookie来处理，可防范少量的SYN攻击
net.ipv4.tcp_timestamps = 1
# 减少time_wait
net.ipv4.tcp_tw_recycle = 0
# 如果=1则开启TCP连接中TIME-WAIT套接字的快速回收，但是NAT环境可能导致连接失败，建议服务端关闭它
net.ipv4.tcp_tw_reuse = 1
# 开启重用。允许将TIME-WAIT套接字重新用于新的TCP连接
net.ipv4.tcp_max_tw_buckets = 262144
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216  

net.nf_conntrack_max = 1200000
net.netfilter.nf_conntrack_max = 1200000  

vm.dirty_background_bytes = 409600000
#  系统脏页到达这个值，系统后台刷脏页调度进程 pdflush（或其他） 自动将(dirty_expire_centisecs/100）秒前的脏页刷到磁盘
#  默认为10%，大内存机器建议调整为直接指定多少字节  

vm.dirty_expire_centisecs = 3000
#  比这个值老的脏页，将被刷到磁盘。3000表示30秒。
vm.dirty_ratio = 95
#  如果系统进程刷脏页太慢，使得系统脏页超过内存 95 % 时，则用户进程如果有写磁盘的操作（如fsync, fdatasync等调用），则需要主动把系统脏页刷出。
#  有效防止用户进程刷脏页，在单机多实例，并且使用CGROUP限制单实例IOPS的情况下非常有效。    

vm.dirty_writeback_centisecs = 100
#  pdflush（或其他）后台刷脏页进程的唤醒间隔， 100表示1秒。  

vm.swappiness = 0
#  不使用交换分区  

vm.mmap_min_addr = 65536
vm.overcommit_memory = 0
#  在分配内存时，允许少量over malloc, 如果设置为 1, 则认为总是有足够的内存，内存较少的测试环境可以使用 1 .    

vm.overcommit_ratio = 90
#  当overcommit_memory = 2 时，用于参与计算允许指派的内存大小。
vm.swappiness = 0
#  关闭交换分区
vm.zone_reclaim_mode = 0
# 禁用 numa, 或者在vmlinux中禁止.
net.ipv4.ip_local_port_range = 40000 65535
# 本地自动分配的TCP, UDP端口号范围
fs.nr_open=20480000
# 单个进程允许打开的文件句柄上限  

# 以下参数请注意
# vm.extra_free_kbytes = 4096000
# vm.min_free_kbytes = 2097152
# 如果是小内存机器，以上两个值不建议设置
# vm.nr_hugepages = 66536
#  建议shared buffer设置超过64GB时 使用大页，页大小 /proc/meminfo Hugepagesize
# vm.lowmem_reserve_ratio = 1 1 1
# 对于内存大于64G时，建议设置，否则建议默认值 256 256 32

2. 生效配置

sysctl -p

配置OS资源限制

# vi /etc/security/limits.conf  

# nofile超过1048576的话，一定要先将sysctl的fs.nr_open设置为更大的值，并生效后才能继续设置nofile.  

* soft    nofile  1024000
* hard    nofile  1024000
* soft    nproc   unlimited
* hard    nproc   unlimited
* soft    core    unlimited
* hard    core    unlimited
* soft    memlock unlimited
* hard    memlock unlimited

最好再关注一下/etc/security/limits.d目录中的文件内容，会覆盖/etc/security/limits.conf的配置。

已有进程的ulimit请查看/proc/pid/limits，例如

Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            10485760             unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             11286                11286                processes
Max open files            1024                 4096                 files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       11286                11286                signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us

如果你要启动其他进程，建议退出SHELL再进一遍，确认ulimit环境配置已生效，再启动。

配置OS防火墙

（建议按业务场景设置，这里先清掉）

iptables -F

配置范例：

# 私有网段
-A INPUT -s 192.168.0.0/16 -j ACCEPT
-A INPUT -s 10.0.0.0/8 -j ACCEPT
-A INPUT -s 172.16.0.0/16 -j ACCEPT

selinux

如果没有这方面的需求，建议禁用

# vi /etc/sysconfig/selinux   

SELINUX=disabled
SELINUXTYPE=targeted

关闭不必要的OS服务

chkconfig --list|grep on

关闭不必要的, 例如

chkconfig iscsi off

配置SSD盘的IO调度策略(注意区分grub1和grub2)

1、grub1代采用这种方法：加上前面的默认IO调度，如下：

如果所有盘都是SSD，可以这样。

vi /boot/grub.conf  

elevator=deadline numa=off transparent_hugepage=never

2、如果只是某些盘是SSD，那么只对这些盘设置为deadline。或者如果用的是grub2：

chmod +x /etc/rc.d/rc.local

vi /etc/rc.local  

# 追加  

echo deadline > /sys/block/vda/queue/scheduler
echo deadline > /sys/block/vdb/queue/scheduler
# 其他盘, ......

关闭透明大页

1、grub1代采用这种方法：加上前面的默认IO调度，如下：

vi /boot/grub.conf  

elevator=deadline numa=off transparent_hugepage=never

2、grub2代，可以使用rc.local。

chmod +x /etc/rc.d/rc.local

vi /etc/rc.local  

# 追加  

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi

当场生效：

echo never > /sys/kernel/mm/transparent_hugepage/enabled  

cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]

部署块设备(多块云盘请参考多云盘部署文档)

1、用parted分区，可以自动对齐。

2、如果是多块云盘，建议使用LVM条带，提高读写吞吐和IOPS。

多块云盘的配置请参考：《PostgreSQL on ECS多云盘的部署、快照备份和恢复》

例子(注意你的块设备名字可能不一样，建议 lslbk 看一下块设备名字)：

parted -s /dev/sda mklabel gpt
parted -s /dev/sda mkpart primary 1MiB 100%

部署文件系统(多块云盘请参考多云盘部署文档)

1、如果是条带，注意创建文件系统时，也要使用条带。

2、EXT4例子：

mkfs.ext4 /dev/sda1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -T largefile -L data01

3、建议使用的ext4 mount选项

# mkdir /data01  

# vi /etc/fstab  

LABEL=data01 /data01     ext4        defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback    0 0  

# mount -a

安装epel和PostgreSQL YUM

1、安装EPEL

http://fedoraproject.org/wiki/EPEL

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm  

rpm -ivh epel-release-latest-7.noarch.rpm

2、安装PostgreSQL yum

https://yum.postgresql.org/repopackages.php#pg96

wget https://download.postgresql.org/pub/repos/yum/testing/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm  

rpm -ivh pgdg-centos10-10-2.noarch.rpm

yum search all postgresql -v  

yum search all postgis -v

安装数据库软件

数据库软件
yum install -y postgresql10*   

PostGIS空间数据库插件
yum install -y postgis24_10*   

道路路由插件
yum install -y pgrouting_10*   

可选：  

openstreetmap导入pgrouting的工具
yum install -y osm2pgrouting_10*   

plpgsql函数调试工具，支持pgadmin调试PLPGSQL函数
yum install -y plpgsql_check_10*   

PostgreSQL 图形化监控软件
yum install -y powa_10*  

PostgreSQL 虚拟索引插件
yum install -y hypopg_10*  

PostgreSQL 分布式插件
yum install -y citus_10*  

PostgreSQL 列存储插件
yum install -y cstore_fdw_10*  

PostgreSQL pg_pathman高效分区插件
yum install -y pg_pathman10*

PostgreSQL orafce Oracle兼容包
yum install -y orafce10*

查询软件目录在哪里:

rpm -ql postgresql10-server

配置OS用户环境变量

su - postgres  

vi ~/.bash_profile  

export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1921
export PGDATA=/data01/pg_root$PGPORT
export LANG=en_US.utf8
export PGHOME=/usr/pgsql-10
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
unalias vi

规划数据库目录

mkdir /data01/pg_root1921
chown postgres:postgres /data01/pg_root1921

初始化数据库

su - postgres  

initdb -D $PGDATA -U postgres --locale=en_US.UTF8 -E UTF8

配置数据库配置文件

su - postgres  

cd $PGDATA

1、postgresql.conf

vi postgresql.conf  

listen_addresses = '0.0.0.0'
port = 1921  # 监听端口
max_connections = 2000  # 最大允许的连接数
superuser_reserved_connections = 10
unix_socket_directories = '.'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 60
tcp_keepalives_count = 10
shared_buffers = 256MB          # 共享内存，建议设置为系统内存的1/4  .
maintenance_work_mem = 64MB     # 系统内存超过32G时，建议设置为1GB。超过64GB时，建议设置为2GB。超过128GB时，建议设置为4GB。
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 5.0
effective_io_concurrency = 0
max_worker_processes = 128
max_parallel_workers_per_gather = 32        # 建议设置为主机CPU核数的一半。
max_parallel_workers = 32                   # 建议设置为主机CPU核数的一半。
wal_level = replica
fsync = on
synchronous_commit = off
full_page_writes = on
wal_writer_delay = 10ms
wal_writer_flush_after = 1MB
checkpoint_timeout = 35min
max_wal_size = 8GB                          # 建议设置为max(8GB, shared_buffers*2)
min_wal_size = 80MB
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 10
max_replication_slots = 10
wal_receiver_status_interval = 1s
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2
random_page_cost = 1.2
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
effective_cache_size = 10GB                 # 建议设置为主机内存的3/4。
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 5s
log_checkpoints = on
log_connections = on                            # 如果是短连接，并且不需要审计连接日志的话，建议OFF。
log_disconnections = on                         # 如果是短连接，并且不需要审计连接日志的话，建议OFF。
log_error_verbosity = verbose
log_line_prefix = '%m [%p] '
log_lock_waits = on
log_statement = 'ddl'
log_timezone = 'PRC'
log_autovacuum_min_duration = 0
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 1000000000
autovacuum_multixact_freeze_max_age = 1200000000
autovacuum_vacuum_cost_delay = 0
statement_timeout = 0                                # 单位ms, s, min, h, d.  表示语句的超时时间，0表示不限制。
lock_timeout = 0                                     # 单位ms, s, min, h, d.  表示锁等待的超时时间，0表示不限制。
idle_in_transaction_session_timeout = 2h             # 单位ms, s, min, h, d.  表示空闲事务的超时时间，0表示不限制。
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 500000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_freeze_table_age = 500000000
datestyle = 'iso, ymd'
timezone = 'PRC'
lc_messages = 'en_US.UTF8'
lc_monetary = 'en_US.UTF8'
lc_numeric = 'en_US.UTF8'
lc_time = 'en_US.UTF8'
default_text_search_config = 'pg_catalog.simple'
shared_preload_libraries='pg_stat_statements,pg_pathman'

2、pg_hba.conf (数据库ACL访问控制列表,防火墙)

追加如下，表示允许所有用户从任意地方访问任意数据库，这个是偷懒的做法。

host all all 0.0.0.0/0 md5

格式

# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
详见pg_hba.conf文件内容说明

配置自动启动数据库脚本

vi /etc/rc.local  

# 追加  

su - postgres -c "pg_ctl start"

重启ECS验证

reboot  

su - postgres  

psql  

postgres=# show max_connections ;
 max_connections
-----------------
 2000
(1 row)

创建数据库用户

su - postgres  

createuser -d -l -P -S digoal
Enter password for new role:
Enter it again:

创建数据库

su - postgres  

psql   

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 digoal    | Create DB                                                  | {}  

postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create database db1 with owner digoal;
CREATE DATABASE

创建postgis空间数据库插件

连接到PG集群，在需要使用空间数据的DB中，使用超级用户，创建空间数据库插件。

psql  

\c db1 postgres  

db1=# create extension postgis;
CREATE EXTENSION
db1=# create extension postgis_sfcgal;
CREATE EXTENSION
db1=# create extension postgis_tiger_geocoder cascade;
注意:  正在安装所需的扩展 "fuzzystrmatch"
CREATE EXTENSION
db1=# create extension postgis_topology ;
CREATE EXTENSION
db1=# create extension pgrouting ;
CREATE EXTENSION
postgres=# create extension pg_pathman ;
CREATE EXTENSION
postgres=# create extension orafce ;
CREATE EXTENSION

《PostgreSQL + PostGIS + SFCGAL 优雅的处理3D数据》

验证1、空间数据库PostGIS的使用

psql 

\c db1 digoal

db1=> select st_geohash(st_setsrid(st_makepoint(120,70),4326),20);
      st_geohash
----------------------
 ysmq4xj7d9v2fsmq4xj7
(1 row)

验证2、高效分区的使用

https://postgrespro.com/docs/postgresproee/9.6/pg-pathman

验证3、Sharding的使用，参考下一篇文档。

分区功能的抉择

如果要高效率，就选pg_pathman。（它使用custom scan，避免了inherit元数据的LOCK）

如果需要持久的兼容，建议使用PostgreSQL 10的原生语法。

这里有VS。《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》

开发者可以使用pgadmin连接数据库

pgadmin4较重（采用WEB服务，含监控功能），建议下载pgadmin3。

https://www.pgadmin.org/download/

其他

1、备份

2、监控

powa  

zabbix  

nagios  

pgstatsinfo

3、容灾

4、HA

5、时间点恢复

6、数据迁移

7、数据导入

8、日常维护

请参考

《PostgreSQL、Greenplum 宝典《如来神掌》》

性能诊断

1、简单性能测试

-- 写入1000万数据

pgbench -i -s 100

-- 4个连接，压测120秒
pgbench -M prepared -n -r -P 1 -c 4 -j 4 -T 120

-- 单核的ECS，不要指望性能。
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 120 s
number of transactions actually processed: 244443
latency average = 1.964 ms
latency stddev = 1.572 ms
tps = 2036.951685 (including connections establishing)
tps = 2037.095995 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.002  \set aid random(1, 100000 * :scale)
         0.000  \set bid random(1, 1 * :scale)
         0.000  \set tid random(1, 10 * :scale)
         0.000  \set delta random(-5000, 5000)
         0.105  BEGIN;
         1.111  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.127  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.135  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.169  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.169  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.144  END;

2、诊断

su - postgres  

psql  

postgres=# create extension pg_stat_statements;
CREATE EXTENSION  

postgres=# select total_time tt_ms,calls,total_time/calls rt_ms,query from pg_stat_statements order by 1 desc limit 10;
      tt_ms       | calls  |        rt_ms         |                                                query
------------------+--------+----------------------+------------------------------------------------------------------------------------------------------
 246755.477457998 | 244443 |     1.00946019095658 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
     28533.312025 |      2 |        14266.6560125 | vacuum analyze pgbench_accounts
     27666.358572 |      2 |         13833.179286 | copy pgbench_accounts from stdin
     15536.583254 |      2 |          7768.291627 | alter table pgbench_accounts add primary key (aid)
 4240.94766099985 | 244443 |   0.0173494338598358 | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
 3673.14024000005 | 244443 |   0.0150265715933778 | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
 2388.27852699992 | 244443 |  0.00977028807124736 | SELECT abalance FROM pgbench_accounts WHERE aid = $1
 1435.52010299995 | 244443 |  0.00587261694137263 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)
 136.597061999987 | 244443 | 0.000558809464783147 | END
       136.452912 |      1 |           136.452912 | SELECT n.nspname as "Schema",                                                                       +
                  |        |                      |   p.proname as "Name",                                                                              +
                  |        |                      |   pg_catalog.pg_get_function_result(p.oid) as "Result data type",                                   +
                  |        |                      |   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",                             +
                  |        |                      |  CASE                                                                                               +
                  |        |                      |   WHEN p.proisagg THEN $1                                                                           +
                  |        |                      |   WHEN p.proiswindow THEN $2                                                                        +
                  |        |                      |   WHEN p.prorettype = $3::pg_catalog.regtype THEN $4                                                +
                  |        |                      |   ELSE $5                                                                                           +
                  |        |                      |  END as "Type"                                                                                      +
                  |        |                      | FROM pg_catalog.pg_proc p                                                                           +
                  |        |                      |      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace                                  +
                  |        |                      | WHERE pg_catalog.pg_function_is_visible(p.oid)                                                      +
                  |        |                      |       AND n.nspname <> $6                                                                           +
                  |        |                      |       AND n.nspname <> $7                                                                           +
                  |        |                      | ORDER BY 1, 2, 4
(10 rows)

3、函数性能诊断

《PostgreSQL 函数调试、诊断、优化 & auto_explain》

安装mysql_fdw(可以用mysql_fdw在PostgreSQL直接读写mysql的数据)

1、安装mysql_fdw插件。

su - root 

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

rpm -ivh mysql57-community-release-el7-11.noarch.rpm

yum install -y mysql-community* --skip-broken

git clone https://github.com/EnterpriseDB/mysql_fdw

cd mysql_fdw

. /var/lib/pgsql/.bash_profile

USE_PGXS=1 make clean

USE_PGXS=1 make

USE_PGXS=1 make install

2、在数据库中加载mysql_fdw插件(在需要使用mysql_fdw的database中创建)。

su - postgres
psql -U username -d dbname

-- load extension first time after install, 使用超级用户创建
CREATE EXTENSION mysql_fdw;

3、使用举例。

-- 超级用户执行
-- create server object
-- 替换成mysql的真实IP和端口

CREATE SERVER mysql_server1
     FOREIGN DATA WRAPPER mysql_fdw
     OPTIONS (host '127.0.0.1', port '3306');

-- 超级用户执行
-- create user mapping
-- pguser 替换成需要查询MYSQL表的PG数据库普通用户

CREATE USER MAPPING FOR pguser
SERVER mysql_server
OPTIONS (username 'foo', password 'bar');

-- 超级用户执行
-- pguser 替换成需要查询MYSQL表的PG数据库普通用户

grant usage ON FOREIGN SERVER mysql_server to pguser;

-- 普通用户执行
-- create foreign table
-- 创建与mysql结构一样的表, 参数中指定表名和库名

\c dbname pguser

CREATE FOREIGN TABLE warehouse(
     warehouse_id int,
     warehouse_name text,
     warehouse_created datetime)
SERVER mysql_server
     OPTIONS (dbname 'db1', table_name 'warehouse');

-- insert new rows in table
-- 支持写MYSQL远程表, 如果要回收写权限，建议使用超级用户建foreign table，并且将select权限赋予给普通用户。  

INSERT INTO warehouse values (1, 'UPS', sysdate());
INSERT INTO warehouse values (2, 'TV', sysdate());
INSERT INTO warehouse values (3, 'Table', sysdate());

-- select from table

SELECT * FROM warehouse;

warehouse_id | warehouse_name | warehouse_created  

--------------+----------------+--------------------
        1 | UPS            | 29-SEP-14 23:33:46
        2 | TV             | 29-SEP-14 23:34:25
        3 | Table          | 29-SEP-14 23:33:49

-- delete row from table

DELETE FROM warehouse where warehouse_id = 3;

-- update a row of table

UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- explain a table

EXPLAIN SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;

                                   QUERY PLAN
Limit  (cost=10.00..11.00 rows=1 width=36)
->  Foreign Scan on warehouse  (cost=10.00..13.00 rows=3 width=36)
     Local server startup cost: 10
     Remote query: SELECT warehouse_id, warehouse_name FROM db.warehouse WHERE ((warehouse_name like 'TV'))
Planning time: 0.564 ms (5 rows)

4、小技巧，一次导入目标端的所有表或指定多个表作为本地外部表，结构一样，本地表名一样。

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

克隆ECS

克隆，便于下次部署。

建议

1、对于可以预计算的AP应用，使用PostgreSQL 10完全满足需求，这类应用PostgreSQL可以OLTP和OLAP一把抓。

2、对于不能预计算的AP应用，视数据量和运算量，PostgreSQL结合强悍的硬件（IO能力强，CPU核数多），同样可以搞定。

3、对于不能预计算，并且运算量和数据量超出了单机硬件能力能承受的范畴，再考虑Greenplum、Citus、PG-XL这样的产品。

参考

《PostgreSQL on ECS多云盘的部署、快照备份和恢复》

《PostgreSQL on Linux 最佳部署手册》