PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户) - 珍藏级

14 minute read

背景

新用户部署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* pgbadger pg_top10*  hypopg_10*  citus_10*  cstore_fdw_10*  pg_pathman10* orafce10* pgfincore10*

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*  pgbadger pg_top10* hypopg_10*  citus_10*  cstore_fdw_10* pg_pathman10* orafce10* pgfincore10*

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

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 log分析生成HTML的软件
yum install -y pgbadger
  
PostgreSQL TOP监控工具
yum install -y pg_top10*
  
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*
  
PostgreSQL linux cache管理插件
yum install -y pgfincore10*

PostgreSQL BSON类型、memcache接口、plpgsql调试函数、plV8存储过程语言、DML审计日志、ip地址转经纬度、IP地址范围GiST索引接口、数据空间回收接口、R-Tree空间类型、plR存储过程语言
yum install -y pgbson10*
yum install -y pgmemcache-10*
yum install -y pldebugger10*
yum install -y plv8_10*
yum install -y cyanaudit10*
yum install -y geoip10*
yum install -y ip4r10*
yum install -y pg_repack10*
yum install -y pgsphere10*
yum install -y plr10*

查询软件目录在哪里:

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 --lc-collate=C --lc-ctype=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  .  
#  vm.nr_hugepages = 102352    
#  建议shared buffer设置超过64GB时 使用大页，页大小 /proc/meminfo Hugepagesize
maintenance_work_mem = 64MB     # 系统内存超过32G时，建议设置为1GB。超过64GB时，建议设置为2GB。超过128GB时，建议设置为4GB。  
work_mem = 64MB                        # 1/4 主机内存 / 256 (假设256个并发同时使用work_mem)
wal_buffers = 512MB                    # min( 2047MB, shared_buffers/32 ) 
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                   # 看业务AP和TP的比例，以及AP TP时间交错分配。实际情况调整。例如 主机CPU cores-2
wal_level = replica  
fsync = on  
synchronous_commit = off  
full_page_writes = on                  # 支持原子写超过BLOCK_SIZE的块设备，在对齐后可以关闭。或者支持cow的文件系统可以关闭。
wal_writer_delay = 10ms  
wal_writer_flush_after = 1MB  
checkpoint_timeout = 35min  
max_wal_size = 32GB                    # shared_buffers*2 
min_wal_size = 8GB                     # max_wal_size/4 
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                 # 建议设置为主机内存的5/8。     
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 = 800000000  
vacuum_multixact_freeze_min_age = 50000000  
vacuum_multixact_freeze_table_age = 800000000  
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

根据CSVLOG生成HTML报告

pgbadger

类似LINUX top命令

last pid: 31760;  load avg:  0.19,  0.06,  0.06;       up 7+21:54:21                                                                                                                                                                11:35:13
7 processes: 7 sleeping
CPU states:  1.0% user,  0.0% nice,  0.0% system, 99.0% idle,  0.0% iowait
Memory: 863M used, 129M free, 35M buffers, 678M cached
DB activity:   4 tps,  0 rollbs/s,   0 buffer r/s, 100 hit%,    276 row r/s,    0 row w/s 
DB I/O:     0 reads/s,     0 KB/s,     0 writes/s,     0 KB/s  
DB disk: 199.8 GB total, 178.4 GB free (10% used)
Swap: 

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
18727 postgres  20    0  624M   10M sleep   1:13  0.00%  0.00% postgres: wal writer process   
18726 postgres  20    0  625M  265M sleep   0:50  0.00%  0.00% postgres: writer process   
18728 postgres  20    0  626M 3236K sleep   0:10  0.00%  0.00% postgres: autovacuum launcher process   
18725 postgres  20    0  625M  171M sleep   0:02  0.00%  0.00% postgres: checkpointer process   
18731 postgres  20    0  626M 2748K sleep   0:00  0.00%  0.00% postgres: bgworker: logical replication launcher   
31761 postgres  20    0  627M 8004K sleep   0:00  0.00%  0.00% postgres: postgres postgres [local] idle
31642 postgres  20    0  627M 6956K sleep   0:00  0.00%  0.00% postgres: postgres postgres [local] idle

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 最佳部署手册》