470
技術社區[雲棲]
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
https://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
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"最後更新：2017-10-28 23:34:37