标簽
PostgreSQL , edb , ppas , Oracle 相容
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E8%83%8C%E6%99%AF 背景
雲資料庫PPAS版，是阿裡雲與EnterpriseDB公司(簡稱EDB)合作基于PostgreSQL高度相容Oracle文法的資料庫服務，為使用者提供易于操作的遷移工具，相容範圍涵蓋：PL/SQL、資料類型、進階函數、表分區等。
使用者可以直接在阿裡雲
購買PPAS進行使用。
如果在購買PPAS前，想試用一下，可以去EDB網站下載下傳，并部署相關的插件。
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E4%B8%80%E6%B5%8B%E8%AF%95%E7%8E%AF%E5%A2%83%E5%AE%89%E8%A3%85%E9%83%A8%E7%BD%B2edb 一、測試環境安裝部署EDB
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E6%9C%8D%E5%8A%A1%E5%99%A8 伺服器
以阿裡雲ECS為例。
1、CPU
# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 64
On-line CPU(s) list: 0-63
Thread(s) per core: 2
Core(s) per socket: 32
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
Stepping: 4
CPU MHz: 2500.008
BogoMIPS: 5000.01
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 33792K
NUMA node0 CPU(s): 0-63
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl eagerfpu pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1
2、DISK
# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
vda 253:0 0 200G 0 disk
└─vda1 253:1 0 200G 0 part /
vdb 253:16 0 1.8T 0 disk
├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
└─vgdata01-lv04 252:3 0 2T 0 lvm /data04
vdc 253:32 0 1.8T 0 disk
├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
└─vgdata01-lv04 252:3 0 2T 0 lvm /data04
vdd 253:48 0 1.8T 0 disk
├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
└─vgdata01-lv04 252:3 0 2T 0 lvm /data04
vde 253:64 0 1.8T 0 disk
├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
└─vgdata01-lv04 252:3 0 2T 0 lvm /data04
vdf 253:80 0 1.8T 0 disk
├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
└─vgdata01-lv04 252:3 0 2T 0 lvm /data04
vdg 253:96 0 1.8T 0 disk
├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
└─vgdata01-lv04 252:3 0 2T 0 lvm /data04
vdh 253:112 0 1.8T 0 disk
├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
└─vgdata01-lv04 252:3 0 2T 0 lvm /data04
vdi 253:128 0 1.8T 0 disk
├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
└─vgdata01-lv04 252:3 0 2T 0 lvm /data04
3、MEM
# free -m
total used free shared buff/cache available
Mem: 515815 318010 109199 8 88605 194736
Swap: 0 0 0
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F 作業系統
以CentOS 7.x x64為例。
1、存儲
dd if=/dev/zero of=/dev/vdb bs=1024 count=1024
dd if=/dev/zero of=/dev/vdc bs=1024 count=1024
dd if=/dev/zero of=/dev/vdd bs=1024 count=1024
dd if=/dev/zero of=/dev/vde bs=1024 count=1024
dd if=/dev/zero of=/dev/vdf bs=1024 count=1024
dd if=/dev/zero of=/dev/vdg bs=1024 count=1024
dd if=/dev/zero of=/dev/vdh bs=1024 count=1024
dd if=/dev/zero of=/dev/vdi bs=1024 count=1024
pvcreate /dev/vd[b-i]
vgcreate -A y -s 128M vgdata01 /dev/vd[b-i]
lvcreate -A y -i 8 -I 8 -L 4096GiB -n lv01 vgdata01
lvcreate -A y -i 8 -I 8 -L 4096GiB -n lv02 vgdata01
lvcreate -A y -i 8 -I 8 -L 4096GiB -n lv03 vgdata01
2、檔案系統
mkfs.ext4 /dev/mapper/vgdata01-lv01 -m 0 -O extent,uninit_bg -E lazy_itable_init=1,stride=2,stripe_width=16 -b 4096 -T largefile -L lv01
mkfs.ext4 /dev/mapper/vgdata01-lv02 -m 0 -O extent,uninit_bg -E lazy_itable_init=1,stride=2,stripe_width=16 -b 4096 -T largefile -L lv02
mkfs.ext4 /dev/mapper/vgdata01-lv03 -m 0 -O extent,uninit_bg -E lazy_itable_init=1,stride=2,stripe_width=16 -b 4096 -T largefile -L lv03
vi /etc/fstab
LABEL=lv01 /data01 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0
LABEL=lv02 /data02 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0
LABEL=lv03 /data03 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0
mkdir /data01
mkdir /data02
mkdir /data03
mount -a
3、核心參數
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
# 單個程序允許打開的檔案句柄上限
net.ipv4.tcp_max_syn_backlog = 16384
net.core.somaxconn = 16384
# 以下參數請注意
# vm.extra_free_kbytes = 4096000
# vm.min_free_kbytes = 2097152 # vm.min_free_kbytes 建議每32G記憶體配置設定1G vm.min_free_kbytes
# 如果是小記憶體機器，以上兩個值不建議設定
# vm.nr_hugepages = 66536
# 建議shared buffer設定超過64GB時 使用大頁，頁大小 /proc/meminfo Hugepagesize
# vm.lowmem_reserve_ratio = 1 1 1
# 對于記憶體大于64G時，建議設定，否則建議預設值 256 256 32
sysctl -p
4、資源限制
vi /etc/security/limits.d/20-nproc.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
5、關閉透明大頁
vi /etc/rc.local
touch /var/lock/subsys/local
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
su - postgres -c "pg_ctl start"
6、防火牆
iptables 配置，略
7、selinux
vi /etc/selinux/config
SELINUX=disabled
SELINUXTYPE=targeted
# setenforce 0
setenforce: SELinux is disabled
8、依賴包
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 iotop perf cmake3
建議重新開機一下機器。
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#edb-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BD%AF%E4%BB%B6 EDB 資料庫軟體
EDB EPAS 11版本開始，隻支援YUM資源安裝，無法直接下載下傳二進制。
11開始的版本參考如下文檔配置YUM進行安裝(
yum install -y edb-as11-server
)：
《MTK使用 - PG,PPAS,oracle,mysql,ms sql,sybase 遷移到 PG, PPAS (支援跨版本更新)》下面以10.5 版本為例，介紹edb epas 10的安裝過程。
1、下載下傳軟體
https://www.enterprisedb.com/advanced-downloadsOPTION A - EDB POSTGRES™ ADVANCED SERVER
v10.5 , Linux x86-64 , Interactive Installer
注意：
Note: For the Enterprise subscription , choose EDB Postgres™ Advanced Server.
For the Standard subscription, choose PostgreSQL.
For the Developer subscription, choose either database.
The Developer subscription is for use in non-production environments only.
wget https://get.enterprisedb.com/advstacks/edb-as10-server-10.5.12-1-linux-x64.run
2、安裝軟體
su - root
chmod 700 edb-as10-server-10.5.12-1-linux-x64.run
./edb-as10-server-10.5.12-1-linux-x64.run
Press [Enter] to continue:
Do you accept this license? [y/n]: y
同意許可。
指定EDB軟體安裝目錄
Installation Directory [/opt/edb/as10]:
----------------------------------------------------------------------------
Select the components you want to install; clear the components you do not want
to install. Click Next when you are ready to continue.
選擇要安裝的元件
EDB Postgres Advanced Server [Y/n] :Y
pgAdmin 4 [Y/n] :n
StackBuilder Plus [Y/n] :n
Command Line Tools [Y/n] :Y
Is the selection above correct? [Y/n]: Y
2.1、資料、REDO目錄
# mkdir /data04/ppas10
# mkdir /data04/ppas10_wal
2.2、
----------------------------------------------------------------------------
Additional Directories
指定資料、REDO目錄
Please select a directory under which to store your data.
Data Directory [/opt/edb/as10/data]: /data04/ppas10
Please select a directory under which to store your Write-Ahead Logs.
Write-Ahead Log (WAL) Directory [/opt/edb/as10/data/pg_wal]: /data04/ppas10_wal
----------------------------------------------------------------------------
Advanced Server Dialect
EDB Postgres Advanced Server can be configured in one of two "Dialects" - 1) Compatible with Oracle or 2) Compatible with Postgres.
If you select Compatible with Oracle, Advanced Server will be configured with appropriate data type conversions,
time and date formats, Oracle-styled operators, dictionary views and more.
This makes it easier to migrate or write new applications that are more compatible with the Oracle database.
If you select Compatible with Postgres, Advanced Server will be configured with standard PostgeSQL data types, time/date formats and operators.
指定相容PostgreSQL還是相容Oracle模式。
Advanced Server Dialect
[1] Compatible with Oracle
[2] Compatible with Postgres
Please choose an option [1] : 1
----------------------------------------------------------------------------
Please provide a password for the database superuser (enterprisedb). A locked
Unix user account (enterprisedb) will be created if not present.
設定資料庫超級使用者enterprisedb的密碼
Password :
Retype Password :
----------------------------------------------------------------------------
Additional Configuration
Please select the port number the server should listen on.
設定資料庫監聽端口
Port [5444]: 4001
Select the locale to be used by the new database cluster.
Locale
[1] [Default locale]
....
[232] en_US
[233] en_US.iso88591
[234] en_US.iso885915
[235] en_US.utf8
...
[762] zh_CN
[763] zh_CN.gb2312
[764] zh_CN.utf8
[765] zh_HK.utf8
[766] zh_SG
[767] zh_SG.gb2312
[768] zh_SG.utf8
[769] zh_TW.euctw
[770] zh_TW.utf8
[771] zu_ZA
[772] zu_ZA.iso88591
[773] zu_ZA.utf8
設定location, 字元集
Please choose an option [1] : 235
Would you like to install sample tables and procedures?
是否安裝樣例模闆
Install sample tables and procedures. [Y/n]: n
----------------------------------------------------------------------------
Dynatune Dynamic Tuning:
Server Utilization
Please select the type of server to determine the amount of system resources
that may be utilized:
配置資料庫所在伺服器的屬性（獨占，開發模式，混合模式）。
[1] Development (e.g. a developer's laptop)
[2] General Purpose (e.g. a web or application server)
[3] Dedicated (a server running only Advanced Server)
Please choose an option [2] : 3
----------------------------------------------------------------------------
Dynatune Dynamic Tuning:
Workload Profile
Please select the type of workload this server will be used for:
設定資料庫的使用場景，OLTP或OLAP或混合模式
[1] Transaction Processing (OLTP systems)
[2] General Purpose (OLTP and reporting workloads)
[3] Reporting (Complex queries or OLAP workloads)
Please choose an option [1] : 2
----------------------------------------------------------------------------
Pre Installation Summary
The following settings will be used for the installation::
再次确認配置
Installation Directory: /opt/edb/as10
Server Installation Directory: /opt/edb/as10
Data Directory: /data04/ppas10
WAL Directory: /data04/ppas10_wal
Database Port: 4001
Database Superuser: enterprisedb
Operating System Account: enterprisedb
Database Service: edb-as-10
Command Line Tools Installation Directory: /opt/edb/as10
Press [Enter] to continue:
----------------------------------------------------------------------------
Setup is now ready to begin installing EDB Postgres Advanced Server on your
computer.
開始安裝
Do you want to continue? [Y/n]: Y
----------------------------------------------------------------------------
Please wait while Setup installs EDB Postgres Advanced Server on your computer.
Installing EDB Postgres Advanced Server
0% ______________ 50% ______________ 100%
########################################
3、配置enterprisedb使用者環境變量
修改軟體目錄OWNER
# chown -R enterprisedb:enterprisedb /opt/edb/as10
配置enterprisedb使用者的環境變量
# vi /opt/edb/as10/.bash_profile
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=4001
export PGDATA=/data04/ppas10
export LANG=en_US.utf8
export PGHOME=/opt/edb/as10
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=enterprisedb
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
alias cmake=cmake3
unalias vi
4、初始化資料庫叢集(安裝軟體時已初始化，以下略)
如果你需要在一台伺服器上初始化多個資料庫執行個體，可以參照執行：
資料目錄與WAL日志目錄(注意，這些是資料和redo日志目錄，是以必須有别于其他資料庫執行個體已經存在的目錄。)
# mkdir ppas10_8001
# mkdir /data04/ppas10_wal_8001
# chown -R enterprisedb:enterprisedb /data04/ppas10*
# chmod 700 /data04/ppas10_wal_8001
su - enterprisedb
如果你想相容Oracle，使用如下手段初始化
initdb -D ppas10_8001 -E UTF8 --lc-collate=C --lc-ctype=en_US.utf8 -U enterprisedb -W -X /data04/ppas10_wal_8001 --redwood-like
如果你想相容PostgreSQL，使用如下手段初始化
initdb -D ppas10_8001 -E UTF8 --lc-collate=C --lc-ctype=en_US.utf8 -U enterprisedb -W -X /data04/ppas10_wal_8001
當然，在初始化後，還可以通過修改參數來實作相容Oracle或PostgreSQL
《EDB PPAS (PostgreSQL plus advanced server) 10 參數模闆 - 珍藏級》5、安裝Oracle OCI(可選)
安裝Oracle OCI
《PostgreSQL 商用版本EPAS(阿裡雲ppas) - 測試環境部署(EPAS 安裝、配置、管理、Oracle DBLINK、外表)》安裝Oracle OCI。這樣才可以在EDB資料庫中建立ORACLE的DBLINK。
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html選擇下載下傳包含OCI的包（ 需要輸入Oracle的賬号密碼，可以免費注冊。）
将安裝包上傳到伺服器，解壓，放到EDB的PGHOME目錄(本文/opt/edb/as10)，并建立軟鍊。詳情參考
https://www.enterprisedb.com/docs/en/10.0/Ora_Compat_Dev_Guide/Database_Compatibility_for_Oracle_Developers_Guide.1.124.html#操作如下
# cd /opt/edb/as10
[root@digoal ~]# mkdir oci
[root@digoal ~]# mv instantclient-basic-linux.x64-12.2.0.1.0.zip oci/
[root@digoal ~]# cd oci
[root@digoal oci]# ll
total 67356
-rw-r--r-- 1 root root 68965195 Jan 19 11:00 instantclient-basic-linux.x64-12.2.0.1.0.zip
[root@digoal oci]# unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
Archive: instantclient-basic-linux.x64-12.2.0.1.0.zip
inflating: instantclient_12_2/adrci
inflating: instantclient_12_2/BASIC_README
inflating: instantclient_12_2/genezi
inflating: instantclient_12_2/libclntshcore.so.12.1
inflating: instantclient_12_2/libclntsh.so.12.1
inflating: instantclient_12_2/libipc1.so
inflating: instantclient_12_2/libmql1.so
inflating: instantclient_12_2/libnnz12.so
inflating: instantclient_12_2/libocci.so.12.1
inflating: instantclient_12_2/libociei.so
inflating: instantclient_12_2/libocijdbc12.so
inflating: instantclient_12_2/libons.so
inflating: instantclient_12_2/liboramysql12.so
inflating: instantclient_12_2/ojdbc8.jar
inflating: instantclient_12_2/uidrvci
inflating: instantclient_12_2/xstreams.jar
[root@digoal oci]# ll
total 67360
drwxr-xr-x 2 root root 4096 Jan 19 11:02 instantclient_12_2
-rw-r--r-- 1 root root 68965195 Jan 19 11:00 instantclient-basic-linux.x64-12.2.0.1.0.zip
[root@digoal oci]# cd instantclient_12_2/
[root@digoal instantclient_12_2]# ll
total 216696
-rwxrwxr-x 1 root root 44220 Jan 26 2017 adrci
-rw-rw-r-- 1 root root 363 Jan 26 2017 BASIC_README
-rwxrwxr-x 1 root root 57272 Jan 26 2017 genezi
-rwxrwxr-x 1 root root 8033199 Jan 26 2017 libclntshcore.so.12.1
-rwxrwxr-x 1 root root 71638263 Jan 26 2017 libclntsh.so.12.1
-r-xr-xr-x 1 root root 2981501 Jan 26 2017 libipc1.so
-r-xr-xr-x 1 root root 539065 Jan 26 2017 libmql1.so
-r-xr-xr-x 1 root root 6568149 Jan 26 2017 libnnz12.so
-rwxrwxr-x 1 root root 2218687 Jan 26 2017 libocci.so.12.1
-rwxrwxr-x 1 root root 124771800 Jan 26 2017 libociei.so
-r-xr-xr-x 1 root root 158543 Jan 26 2017 libocijdbc12.so
-r-xr-xr-x 1 root root 380996 Jan 26 2017 libons.so
-rwxrwxr-x 1 root root 116563 Jan 26 2017 liboramysql12.so
-r--r--r-- 1 root root 4036257 Jan 26 2017 ojdbc8.jar
-rwxrwxr-x 1 root root 240476 Jan 26 2017 uidrvci
-rw-rw-r-- 1 root root 74230 Jan 26 2017 xstreams.jar
[root@digoal instantclient_12_2]# cp libclntsh.so.12.1 /opt/edb/as10/lib/
[root@digoal instantclient_12_2]# cd /opt/edb/as10/lib
[root@digoal lib]# ln -s libclntsh.so.12.1 libclntsh.so
6、建立DBLINK，建立ORACLE外部表，略。參考上面的連接配接。
7、配置資料庫參數
su - enterprisedb
cd $PGDATA
vi postgresql.conf
listen_addresses = '0.0.0.0'
port = 4001 # 多執行個體時，不同的資料庫端口必須不同。
max_connections = 4000
superuser_reserved_connections = 13
unix_socket_directories = '.,/tmp'
unix_socket_permissions = 0700
row_security = on
tcp_keepalives_idle = 45
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 32GB
huge_pages = try
max_prepared_transactions = 4000
work_mem = 8MB
maintenance_work_mem = 2GB
autovacuum_work_mem = 2GB
max_stack_depth = 4MB
dynamic_shared_memory_type = posix
shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq'
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 5.0
effective_io_concurrency = 0
max_worker_processes = 64
max_parallel_workers_per_gather = 8
max_parallel_workers = 24
wal_level = replica
synchronous_commit = off # 異步送出
full_page_writes = on # 如果檔案系統支援COW，可以關閉。如果塊裝置支援8K（block_size決定）原子寫，也可以關閉。開啟有一定性能影響
wal_buffers = 64MB
wal_writer_delay = 10ms
wal_writer_flush_after = 1MB
checkpoint_timeout = 35min
max_wal_size = 64GB
min_wal_size = 16GB
checkpoint_completion_target = 0.1
archive_mode = on
archive_command = '/usr/bin/true'
max_wal_senders = 16
wal_keep_segments = 2048
wal_sender_timeout = 30s
max_replication_slots = 16
hot_standby = on
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
wal_receiver_status_interval = 1s
wal_receiver_timeout = 30s
max_logical_replication_workers = 16
max_sync_workers_per_subscription = 4
random_page_cost = 1.1
effective_cache_size = 400GB
log_destination = 'csvlog'
logging_collector = on
log_truncate_on_rotation = on
log_min_duration_statement = 10s
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_line_prefix = '%t '
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 0
log_timezone = 'PRC'
track_io_timing = on # 如果不需要跟蹤IO的時間，可以關閉，開啟有一定性能影響
track_functions = all
track_activity_query_size = 2048
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 6
autovacuum_freeze_max_age = 1200000000
autovacuum_multixact_freeze_max_age = 1400000000
autovacuum_vacuum_cost_delay = 0
statement_timeout = 300s
lock_timeout = 15s
idle_in_transaction_session_timeout = 60s
vacuum_freeze_table_age = 1150000000
vacuum_multixact_freeze_table_age = 1150000000
datestyle = 'redwood,show_time'
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.english'
edb_redwood_date = on
edb_redwood_greatest_least = on
edb_redwood_strings = on
db_dialect = 'redwood'
edb_dynatune = 100
edb_dynatune_profile = mixed
timed_statistics = off
8、資料庫防火牆配置
su - enterprisedb
cd $PGDATA
vi pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all 0.0.0.0/0 md5
9、重新開機資料庫
pg_ctl restart -m fast
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E4%BA%8Cide 二、IDE
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#cli cli
https://www.postgresql.org/docs/current/static/app-psql.htmlenterprisedb@pg11-test-> psql
psql.bin (10.5.12)
Type "help" for help.
postgres=# \dt
Did not find any relations.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges
-----------+--------------+----------+------------+------------+-----+-------------------------------
edb | enterprisedb | UTF8 | en_US.utf8 | en_US.utf8 | |
postgres | enterprisedb | UTF8 | en_US.utf8 | en_US.utf8 | |
template0 | enterprisedb | UTF8 | en_US.utf8 | en_US.utf8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | UTF8 | en_US.utf8 | en_US.utf8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
(4 rows)
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#gui gui
1、pem (Enterprisedb提供的GUI工具)
https://www.enterprisedb.com/software-downloads-postgres2、pgadmin
https://www.pgadmin.org/ 《阿裡雲ppas 邏輯備份(導出)、還原 - 導出到本地、從本地導入》https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E4%B8%89%E5%8E%8B%E6%B5%8B 三、壓測
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#tpc-b TPC-B
su - enterprisedb
初始化1億TPC-B資料
pgbench -i -s 1000
隻讀測試
pgbench -M prepared -n -r -P 1 -c 64 -j 64 -T 120 -S
transaction type: <builtin: select only>
scaling factor: 1000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 125854784
latency average = 0.061 ms
latency stddev = 0.062 ms
tps = 1048623.422874 (including connections establishing)
tps = 1048713.313561 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.060 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
讀寫混合測試
pgbench -M prepared -n -r -P 1 -c 64 -j 64 -T 120
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 10363880
latency average = 0.741 ms
latency stddev = 1.315 ms
tps = 86351.412706 (including connections establishing)
tps = 86359.549707 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.050 BEGIN;
0.155 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.077 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.109 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.102 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.083 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.162 END;
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#tpc-c TPC-C
參考
《PostgreSQL 11 tpcc 測試(103萬tpmC on ECS) - use sysbench-tpcc by Percona-Lab》https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E5%9B%9B%E6%8F%92%E4%BB%B6 四、插件
插件介紹、安裝、使用
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E5%9B%BE%E5%83%8F 圖像
[root@pg11-test ~]# cd
[root@pg11-test ~]# . /opt/edb/as10/.bash_profile
-bash: unalias: vi: not found
root@pg11-test-> git clone https://github.com/postgrespro/imgsmlr
Cloning into 'imgsmlr'...
remote: Enumerating objects: 146, done.
remote: Total 146 (delta 0), reused 0 (delta 0), pack-reused 146
Receiving objects: 100% (146/146), 241.11 KiB | 149.00 KiB/s, done.
Resolving deltas: 100% (69/69), done.
root@pg11-test-> cd imgsmlr
root@pg11-test-> USE_PGXS=1 make clean
rm -f imgsmlr.so libimgsmlr.a libimgsmlr.pc
rm -f imgsmlr.o imgsmlr_idx.o
rm -rf data/*.hex
rm -rf results/ regression.diffs regression.out tmp_check/ tmp_check_iso/ log/ output_iso/
root@pg11-test-> USE_PGXS=1 make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -I. -I./ -I/opt/edb/as10/include/server -I/opt/edb/as10/include/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include/libxml2 -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -I/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/include -c -o imgsmlr.o imgsmlr.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -I. -I./ -I/opt/edb/as10/include/server -I/opt/edb/as10/include/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include/libxml2 -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -I/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/include -c -o imgsmlr_idx.o imgsmlr_idx.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -shared -o imgsmlr.so imgsmlr.o imgsmlr_idx.o -L/opt/edb/as10/lib -L/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/lib -L/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/lib -L/opt/local/Current/lib -L/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/lib -Wl,--as-needed -Wl,-rpath,'/opt/edb/as10/lib',--enable-new-dtags -lgd
root@pg11-test-> USE_PGXS=1 make install
/bin/mkdir -p '/opt/edb/as10/lib'
/bin/mkdir -p '/opt/edb/as10/share/extension'
/bin/mkdir -p '/opt/edb/as10/share/extension'
/usr/bin/install -c -m 755 imgsmlr.so '/opt/edb/as10/lib/imgsmlr.so'
/usr/bin/install -c -m 644 .//imgsmlr.control '/opt/edb/as10/share/extension/'
/usr/bin/install -c -m 644 .//imgsmlr--1.0.sql '/opt/edb/as10/share/extension/'
root@pg11-test-> psql
psql.bin (10.5.12)
Type "help" for help.
postgres=# create extension imgsmlr;
CREATE EXTENSION
用法
《PostgreSQL 11 相似圖像搜尋插件 imgsmlr 性能測試與優化 1 - 單機單表 (4億圖像)》https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E4%B8%AD%E6%96%87%E5%88%86%E8%AF%8D 中文分詞
[root@pg11-test ~]# cd
[root@pg11-test ~]# . /opt/edb/as10/.bash_profile
-bash: unalias: vi: not found
git clone https://github.com/jaiminpan/pg_jieba
cd pg_jieba
git submodule update --init --recursive
mkdir build
cd build
cmake3 -DCMAKE_PREFIX_PATH=/opt/edb/as10 ..
make
make install
vi $PGDATA/postgresql.conf
shared_preload_libraries = '$libdir/pg_jieba.so,$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq'
重新開機資料庫
pg_ctl restart -m fast
postgres=# create extension pg_jieba;
CREATE EXTENSION
postgres=# select * from to_tsvector('jiebacfg', '小明碩士畢業于中國科學院計算所，後在日本京都大學深造');
to_tsvector
----------------------------------------------------------------------------------
'中國科學院':5 '小明':1 '日本京都大學':10 '畢業':3 '深造':11 '碩士':2 '計算所':6
(1 row)
postgres=# select * from to_tsvector('jiebacfg', '李小福是創新辦主任也是雲計算方面的專家');
to_tsvector
-------------------------------------------------------------------
'專家':11 '主任':5 '雲計算':8 '創新':3 '辦':4 '方面':9 '李小福':1
(1 row)
https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E6%B5%81%E8%AE%A1%E7%AE%97 流計算
《PostgreSQL pipelinedb 流計算插件 - IoT應用 - 實時軌迹聚合》https://github.com/digoal/blog/blob/master/201811/20181102_02.md#job job
https://www.enterprisedb.com/thank-you-4?anid=1256127https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E5%86%85%E7%BD%AE%E6%8F%92%E4%BB%B6 内置插件
postgres=# create extension 這裡按tab鍵補齊，口可以看到目前支援的插件。
adminpack chkpass dict_xsyn hstore insert_username ltree_plpython2u pg_buffercache pgstattuple plpython3u sslinfo tsm_system_time
amcheck citext earthdistance hstore_plperl intagg ltree_plpython3u pgcrypto pg_trgm pltcl sslutils unaccent
autoinc cube edb_cloneschema hstore_plperlu intarray ltree_plpythonu pg_freespacemap pg_visibility pltclu tablefunc "uuid-ossp"
bloom dblink edb_sharedplancache hstore_plpython2u isn moddatetime pg_prewarm pljava postgres_fdw tcn xml2
btree_gin dbms_scheduler file_fdw hstore_plpython3u lo pageinspect pgrowlocks plperl refint timetravel
btree_gist dict_int fuzzystrmatch hstore_plpythonu ltree parallel_clone pg_stat_statements plperlu seg tsm_system_rows
資料庫内置插件，以及介紹
https://www.postgresql.org/docs/current/static/contrib.htmlhttps://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E4%BA%94%E7%89%A9%E7%90%86%E7%BB%93%E6%9E%84 五、實體結構
PPT内有介紹實體結構
《阿裡雲 PostgreSQL 産品生态；案例、開發實踐、管理實踐、資料庫原理、學習資料、學習視訊 - 珍藏級》https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E5%85%AD%E9%80%BB%E8%BE%91%E7%BB%93%E6%9E%84 六、邏輯結構
《PostgreSQL 邏輯結構 和 權限體系 介紹》https://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E4%B8%83%E4%BD%BF%E7%94%A8 七、使用
1、連接配接資料庫
psql -h HOST_OR_IP -p PORT -U DBUSER -d DBNAME
2、建立使用者
create role rolename1 login encrypted password 'pwd';
3、建立資料庫
create database newdb with template template0 encoding 'UTF8' lc_collate 'C' lc_ctype 'en_US.utf8';
4、克隆資料
create database clonedb1 with template postgres;
5、基本使用
http://www.postgresqltutorial.com/6、進階用法
《PostgreSQL SELECT 的進階用法(CTE, LATERAL, ORDINALITY, WINDOW, SKIP LOCKED, DISTINCT, GROUPING SETS, ...) - 珍藏級》7、應用場景
《PostgreSQL、Greenplum 《如來神掌》 - 目錄 - 珍藏級》8、參考
https://www.postgresql.org/docs/current/static/index.htmlhttps://github.com/digoal/blog/blob/master/201811/20181102_02.md#%E5%85%AB%E7%BB%A7%E7%BB%AD%E9%98%85%E8%AF%BB 八、繼續閱讀
1、HA
patroni
2、隻讀節點
流複制
《PostgreSQL 11 1000億 tpcb、1000W tpcc 性能測試 - on 阿裡雲ECS + ESSD (含quorum based 0丢失多副本配置與性能測試)》 《PostgreSQL 一主多從(多副本,強同步)簡明手冊 - 配置、壓測、監控、切換、防腦裂、修複、0丢失 - 珍藏級》3、容災節點
《PostgreSQL 10 on ECS 實施 流複制備庫鏡像+自動快照備份+自動備份驗證+自動清理備份與歸檔 - 珍藏級》4、持續增量備份
5、恢複到時間點
《阿裡雲ApsaraDB RDS for PostgreSQL 最佳實踐 - 6 任意時間點恢複》6、邏輯備份
pg_dump
psql copy
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
where option can be one of:
FORMAT format_name
OIDS [ boolean ]
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [ boolean ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL ( column_name [, ...] )
FORCE_NULL ( column_name [, ...] )
ENCODING 'encoding_name'
7、邏輯恢複
pg_restore
或
sql文檔直接執行
copy
8、資源回收筒
《PostgreSQL Oracle 相容性之 - 事件觸發器實作類似Oracle的資源回收筒功能》 《PostgreSQL 資源回收筒功能 - 基于HOOK的recycle bin pgtrashcan》9、閃回
《PostgreSQL flashback(閃回) 功能實作與介紹》 《PostgreSQL 閃回 - flash back query emulate by trigger》10、審計
《PostgreSQL 事件觸發器應用 - DDL審計記錄 + 異步通知(notify)》 《PostgreSQL 審計 - pg_audit module》 《PostgreSQL 開啟“審計日志、時間記錄”帶來的性能影響有多少？》![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)