PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户)...
萧越泽
标签
PostgreSQL , Linux , CentOS
背景
新用户部署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
注意某些参数，根据内存大小配置（已说明）
含义详见
# 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 清理redo(xlog,wal,归档)的机制 及 如何手工清理》
《PostgreSQL、Greenplum 宝典《如来神掌》》
mysql_fdw foreign server、user mapping、foreign table的options如下:
mysql_fdw/options.c
/*
* Valid options for mysql_fdw.
*
*/
static struct MySQLFdwOption valid_options[] =
{
/* Connection options */
{ "host", ForeignServerRelationId },
{ "port", ForeignServerRelationId },
{ "init_command", ForeignServerRelationId },
{ "username", UserMappingRelationId },
{ "password", UserMappingRelationId },
{ "dbname", ForeignTableRelationId },
{ "table_name", ForeignTableRelationId },
{ "secure_auth", ForeignServerRelationId },
{ "max_blob_size", ForeignTableRelationId },
{ "use_remote_estimate", ForeignServerRelationId },
{ "ssl_key", ForeignServerRelationId },
{ "ssl_cert", ForeignServerRelationId },
{ "ssl_ca", ForeignServerRelationId },
{ "ssl_capath", ForeignServerRelationId },
{ "ssl_cipher", ForeignServerRelationId },
/* Sentinel */
{ NULL, InvalidOid }
};
mysql与pgsql的类型映射：
WHEN c.DATA_TYPE = 'enum' THEN LOWER(CONCAT(c.COLUMN_NAME, '_t'))"
WHEN c.DATA_TYPE = 'tinyint' THEN 'smallint'"
WHEN c.DATA_TYPE = 'mediumint' THEN 'integer'"
WHEN c.DATA_TYPE = 'tinyint unsigned' THEN 'smallint'"
WHEN c.DATA_TYPE = 'smallint unsigned' THEN 'integer'"
WHEN c.DATA_TYPE = 'mediumint unsigned' THEN 'integer'"
WHEN c.DATA_TYPE = 'int unsigned' THEN 'bigint'"
WHEN c.DATA_TYPE = 'bigint unsigned' THEN 'numeric(20)'"
WHEN c.DATA_TYPE = 'double' THEN 'double precision'"
WHEN c.DATA_TYPE = 'float' THEN 'real'"
WHEN c.DATA_TYPE = 'datetime' THEN 'timestamp'"
WHEN c.DATA_TYPE = 'longtext' THEN 'text'"
WHEN c.DATA_TYPE = 'mediumtext' THEN 'text'"
WHEN c.DATA_TYPE = 'blob' THEN 'bytea'"
WHEN c.DATA_TYPE = 'mediumblob' THEN 'bytea'"
ELSE c.DATA_TYPE"
类似资料：