專場9.5-開源RDS替代：開箱即?、?動駕駛的數據庫發?版 Pigsty-馮若航.pdf

《專場9.5-開源RDS替代：開箱即?、?動駕駛的數據庫發?版 Pigsty-馮若航.pdf》由會員分享，可在線閱讀，更多相關《專場9.5-開源RDS替代：開箱即?、?動駕駛的數據庫發?版 Pigsty-馮若航.pdf（74頁珍藏版）》請在三個皮匠報告上搜索。

1、開源RDS PG替代：PIGSTY馮若航 磐吉云數CEO,PostgreSQL中社區開源技術委員公有云上什么最貴？存儲計算資源貴嗎？式單價IDC建機房（獨占物理機 A1:64C384G）19IDC建機房（獨占物理機 B1:40C64G）26IDC建機房（獨占物理機 C2:8C16G）38IDC建機房（容器，超賣200%）17IDC建機房（容器，超賣500%）7Ucloud 彈性虛擬機（8C16G，有超賣）25阿云 彈性服務器 2x內存（獨占超賣）107阿云 彈性服務器 4x內存（獨占超賣）138阿云 彈性服務器 8x內存（獨占超賣）180AWS C5D.METAL 96C 200G(按預付)1

2、00AWS C5D.METAL 96C 200G(預付3年)80不算很貴1核算 使1 塊到上百塊式單價AWS RDS PostgreSQL db.T2(4x)440AWS RDS PostgreSQL db.M5(4x)611AWS RDS PostgreSQL db.R6G(8x)786AWS RDS PostgreSQL db.M5 24xlarge1328阿云 RDS PG 2x內存（獨占）260阿云 RDS PG 4x內存（獨占）320阿云 RDS PG 8x內存（獨占）410ORACLE數據庫授權10000云數據庫貴的離譜！那貴的是什么？1核算 使1 把同樣的硬件賣出倍甚倍的天價！P

3、igstyIaaS 獲客 RDS 殺豬云數據庫為什么這么貴？PigstyRDS =開源數據庫+云管控軟件商業開源內核管控1234“整”“免費攢”“租”“動駕駛”發動機/汽司機/動駕駛2.0 開源數據庫3.0 云數據庫4.0 云原數據庫1.0 商業數據庫軟件太貴了，專家解決專家太貴了，外包共享DBA解決太貴了，軟件解決外包不得勁，本地案解決Pigstysysbench 橫向對Postgres In Great STYlePigsty:全盛狀態的 PostgreSQL從 開源數據庫 到 開源RDSPostgreSQL 是世界上最先進的開源關系型數據庫，前途量！參考閱讀：為什么PostgreSQL前

4、途量？PostgreSQL是最受全球開發者喜愛的數據庫PostgreSQL是專多的超融合全棧數據庫PostgreSQL是歷史悠久的祖師爺級開源項PostgreSQL是數“國產數據庫”的PostgreSQL是唯能威脅 Oracle 的數據庫PostgreSQL是數應軟件們的底層數據庫https:/ 專多全棧都能，的還不賴！32%國產數據庫的直系祖先！開源版“Oracle”熱度歌猛進，勢不可擋！最為流：職業開發者使率最的數據庫 最受喜愛：開發者鐘愛例最，恐懼例最低 最想學習：開發者最想要學習的數據庫技能 最感興趣：所有數據庫戶群體最感興趣的數據庫 薪資最：主流關系數據庫技能中薪資最世界上 最成功

5、的數據庫！StackOverflow 開發者調研 2022.05最流最需求最喜愛裸奔狀態的PostgreSQL裸奔狀態的 PostgreSQLyum install postgresql*systemctl start postgresqlSoftware ToolkitProvisioning/InfrastructurePlatform/GUI/CLI/APIMonitoring/LoggingAlerting/ReportingService DiscoveryHA/ScalabilityLB/PoolingACL/AuditSecurityBackupPITRKernelPostgre

6、SQLTimescalePostGISCitusPipeline可觀測性 可靠性 可維護性 可性 可擴展性 互操作性 pgBackRestObservabilityAvailbilityInteroperabilityExtensibilityMaintainabilityReliabilityDNSMASQLOCAL REPO君六藝從裸奔到六邊形戰Raw PostgreSQLRDS PostgreSQLPigstyBasicCMDBAdminCloudObserva-bilityRepoDNSNODEPGSQLACCESSadmininvoketime-syncconsensusleader

7、 inquirytraffic adminlogsmetricsscrapescrapehealth checksupervisepoolingdcs accessWeb PortalDatabase ServiceCA123NTPDServer5432CMDBCLIGUI310090909093AlertManager300080Yum Repo8053DNSMASQ9080Promtail6432Pgbouncer8008Patroni9100NodeExporter9630PGExporter9631PGBExporter543354345436543891015432ChronydDN

8、Svip-managerpgBackRest23799101DNS9000bash-c$(curl-fsSL http:/download.pigsty.cc/get)cd/pigsty./bootstrap ./configure./install.yml可觀測性 你法控制不可觀測之物現代可觀測性技術棧INFRA31009093AlertManager300080h.pigsty80Yum Repoh.pigstyg.pigstya.pigsty9090p.pigsty9091Push Gateway9115Blackbox Exporter53DNSMASQ9323PostgreSQL 可

9、觀測性 INFRA807NODE734PGSQL528ETCD179REDIS247觀測 到 洞察 PigstyDashboards http:/demo.pigsty.cc有效的復雜系統總是從簡單的系統演化來，從零造的復雜輪沒個好使的 John Gall,Systemantics(1975)可靠性Leader electionConfig ManagementHA AgentStreaming replicationDatabaseConnection poolerStatistic collectorMiddlewareIdempotent LB that expose services

10、Service are distinguished by portsLoad BalancerL2 VIP managed by vip-managerTied to primary via dcs infoVIPpg-test resolve to VIPor fixed load balancerDNSApplicationAccess via pg-testDistinguish services via portBackup,PITR,Repo StorageETCD cluster for consensusInfranode-3 10.10.10.13pg-test-3replic

11、anode-2 10.10.10.12pg-test-2replicanode-110.10.10.11pg-test-1primaryAccess Layerreplicationsupervisepoolingactive/backuptrafficreplicareplicationprimarysuperviseofflinedefaultcheckconsensusinquirypgBackRestpitrPITRconsensushealthcheckinquiryrepo storagelocal/s3/minio543354345436543854335434543654385

12、433543454365438Pgbouncer6432Pgbouncer6432Pgbouncer64325432replica5432replica5432primary8008Patroni8008Patroni8008Patronivip-manager10.10.10.3DNSpg-test2379RTO 1min,RPO=0產級可數據庫架構(主庫故障)(同步提交)硬件故障：從庫影響，主庫1分鐘內動切換數據損失：致性優先模式，數據不丟不錯共識選主：ETCD故障切換：Patroni流量池化：Pgbouncer負載均衡化：HAProxy接層：DNS+VIP集群中有任意實例存活即可完整對外

13、服務分布式使體驗：各實例對外表現冪等規模產環境考驗 13K 核 PostgreSQL 數據庫+12K核 Redis數據庫 x 30個典型主庫故障，從庫故障監控現場。PITR pgBackRest#restore to the latest available point(e.g.hardware failure)pgbackrest-stanza=pg-meta restore#PITR to specific time point(e.g.drop table by accident)pgbackrest-stanza=pg-meta-type=time-target=2022-11-08

14、10:58:48 -target-action=promote restore#restore specific backup point and then promote(or pause|shutdown)pgbackrest-stanza=pg-meta-type=immediate-target-action=promote -set=20221108-105325F_20221108-105938I restore#shortcut alias:pgbr-backup#make full backup if not exists,otherwise incr backuppgbr-f

15、ull#make a full backup(usually every week)pgbr-incr#make an incremental backup(usually every day)pgbr-diff#make a differential backup(not often used)異步歸檔 WAL，并執基礎備份三種存儲模式：本地件系統，專備份服務器，Minio/S3開箱即的傻式 PITR，快速回滾過往任意時間點三種備份模式：全量備份，差異備份，持增量備份與恢復可性sysbench 橫向對Leader electionConfig ManagementHA AgentStream

16、ing replicationDatabaseConnection poolerStatistic collectorMiddlewareIdempotent LB that expose services Service are distinguished by portsLoad BalancerL2 VIP managed by vip-managerTied to primary via dcs infoVIPpg-test resolve to VIPor fixed load balancerDNSApplicationAccess via pg-testDistinguish s

17、ervices via portBackup,PITR,Repo StorageETCD cluster for consensusInfranode-3 10.10.10.13pg-test-3replicanode-2 10.10.10.12pg-test-2replicanode-110.10.10.11pg-test-1primaryAccess Layerreplicationsupervisepoolingactive/backuptrafficreplicareplicationprimarysuperviseofflinedefaultcheckconsensusinquiry

18、pgBackRestpitrPITRconsensushealthcheckinquiryrepo storagelocal/s3/minio543354345436543854335434543654385433543454365438Pgbouncer6432Pgbouncer6432Pgbouncer64325432replica5432replica5432primary8008Patroni8008Patroni8008Patronivip-manager10.10.10.3DNSpg-test2379服務接 任意實例均可對外提供冪等服務故障切換對戶側客戶端感知提供多種接式與不同服務

19、全鏈路 SSL，流量加密死pg_default_services:#postgres default service definitions -name:primary,port:5433,dest:pgbouncer,check:/primary ,selector:-name:replica,port:5434,dest:pgbouncer,check:/read-only,selector:,backup:?pg_role=primary|pg_role=offline -name:default,port:5436,dest:postgres ,check:/primary ,sele

20、ctor:-name:offline,port:5438,dest:postgres ,check:/replica ,selector:?pg_role=offline|pg_offline_query ,backup:?pg_role=replica&!pg_offline_query服務Streaming replicationDatabaseConnection poolerStatistic collectorMiddlewareIdempotent LB that expose services Service are distinguished by portsLoad Bala

21、ncerL2 VIP managed by vip-managerTied to primary via dcs infoVIPpg-test resolve to VIPor fixed load balancerDNSApplicationAccess via pg-testDistinguish services via portAccess Layerreplicationpoolingactive/backup543354345436543854335434543654385433543454365438Pgbouncer6432Pgbouncer6432Pgbouncer64325

22、432replica5432replica5432primaryvip-manager10.10.10.3DNSpg-test可維護性我們必須跳出電腦指令序列的窠。敘述定義、描述元數據、梳理關系，不是編寫過程。Grace Murray Hopper，未來的計算機及其管理（1962）Database as Code consul/etcd clusterDistributed Configuration SystemDCSLeader electionConfig ManagementHA AgentStreaming replicationDatabaseConnection poolerSt

23、atistic collectorMiddlewareIdempotent LB that expose services Service are distinguished by portsLoad BalancerL2 VIP managed by vip-managerTied to primary via dcs infoVIPpg-test resolve to VIPor fixed load balancerDNSApplicationAccess via pg-testDistinguish services via portnode-3 10.10.10.13pg-test-

24、3replicanode-2 10.10.10.12pg-test-2replicanode-110.10.10.11pg-test-1primaryAccess Layerreplicationconsensussupervisepoolingactive/backuptrafficreplicareplicationprimarysuperviseofflinedefaultcheckconsensusinquiryreplicaprimaryofflinedefaultpostgres:/testpg-test:5434/testpostgres:/dbuser_dbapg-test:5

25、436/testdbpostgres:/dbuser_statspg-test:5438/testdbnon-interactive read-write access via poolnon-interactive read-only access via poolinteractive primary direct access(DDL,DML,Admin)interactive offline direct access(ETL/SAGA/Personal)postgres:/testpg-test:5433/testpg-test:#define new cluster pg-test

26、 hosts:#with 3 instances on 3 nodes 10.10.10.11:pg_seq:1,pg_role:primary 10.10.10.12:pg_seq:2,pg_role:replica 10.10.10.13:pg_seq:3,pg_role:offline vars:#configure pgsql with 100+params pg_cluster:pg-test vip_address:10.10.10.3 pg_users:name:test pg_databases:name:test ConfigApplypgsql.yml-l pg-test5

27、43354345436543854335434543654385433543454365438Pgbouncer6432Pgbouncer6432Pgbouncer64325432replica5432replica5432primary8008Patroni8008Patroni8008Patroni8500Agent8500Agent8500Agentvip-manager10.10.10.3DNSpg-test數據庫即代碼，聲明式接 pg_users:#define business users/roles on this cluster,array of user definition

28、 -name:dbuser_meta#REQUIRED,name is the only mandatory field of a user definition password:DBUser.Meta#optional,password,can be a scram-sha-256 hash string or plain text login:true#optional,can log in,true by default (new biz ROLE should be false)superuser:false#optional,is superuser?false by defaul

29、t createdb:false#optional,can create database?false by default createrole:false#optional,can create role?false by default inherit:true#optional,can this role use inherited privileges?true by default replication:false#optional,can this role do replication?false by default bypassrls:false#optional,can

30、 this role bypass row level security?false by default pgbouncer:true#optional,add this user to pgbouncer user-list?false by default(production user should be true explicitly)connlimit:-1#optional,user connection limit,default-1 disable limit expire_in:3650#optional,now+n days when this role is expir

31、ed(OVERWRITE expire_at)expire_at:2030-12-31#optional,YYYY-MM-DD timestamp when this role is expired (OVERWRITTEN by expire_in)comment:pigsty admin user#optional,comment string for this user/role roles:dbrole_admin#optional,belonged roles.default roles are:dbrole_admin,readonly,readwrite,offline para

32、meters:#optional,role level parameters with ALTER ROLE SET pool_mode:transaction#optional,pgbouncer pool mode at user level,transaction by default pool_connlimit:-1#optional,max database connections at user level,default-1 disable limit search_path:public#key value config parameters according to pos

33、tgresql documentation(e.g:use pigsty as default search_path)-name:dbuser_view ,password:DBUser.Viewer ,pgbouncer:true,roles:dbrole_readonly,comment:read-only viewer for meta database pg_databases:#define business databases on this cluster,array of database definition -name:meta#REQUIRED,name is the

34、only mandatory field of a database definition baseline:cmdb.sql#optional,database sql baseline path,(relative path among ansible search path,e.g files/)pgbouncer:true#optional,add this database to pgbouncer database list?true by default schemas:pigsty#optional,additional schemas to be created,array

35、of schema names extensions:name:postgis#optional,additional extensions to be installed:array of name,schema comment:pigsty meta database#optional,comment string for this database owner:postgres#optional,database owner,postgres by default template:template1#optional,which template to use,template1 by

36、 default encoding:UTF8#optional,database encoding,UTF8 by default.(MUST same as template database)locale:C#optional,database locale,C by default.(MUST same as template database)lc_collate:C#optional,database collate,C by default.(MUST same as template database)lc_ctype:C#optional,database ctype,C by

37、 default.(MUST same as template database)tablespace:pg_default#optional,default tablespace,pg_default by default.allowconn:true#optional,allow connection,true by default.false will disable connect at all revokeconn:false#optional,revoke public connection privilege.false by default.(leave connect wit

38、h grant option to owner)register_datasource:true#optional,register this database to grafana datasources?true by default connlimit:-1#optional,database connection limit,default-1 disable limit pool_auth_user:dbuser_meta#optional,all connection to this pgbouncer database will be authenticated by this

39、user pool_mode:transaction#optional,pgbouncer pool mode at database level,default transaction pool_size:64#optional,pgbouncer pool size at database level,default 64 pool_size_reserve:32#optional,pgbouncer pool size reserve at database level,default 32 pool_size_min:0#optional,pgbouncer pool size min

40、 at database level,default 0 pool_max_db_conn:100#optional,max database connections at database level,default 100 -name:grafana ,owner:dbuser_grafana ,revokeconn:true,comment:grafana primary database -name:bytebase,owner:dbuser_bytebase,revokeconn:true,comment:bytebase primary database -name:kong ,o

41、wner:dbuser_kong ,revokeconn:true,comment:kong the api gateway database -name:gitea ,owner:dbuser_gitea ,revokeconn:true,comment:gitea meta database -name:wiki ,owner:dbuser_wiki ,revokeconn:true,comment:wiki meta database#=#VARS:PGSQL#=#-#PG_IDENTITY#-#pg_cluster:#CLUSTER#pgsql cluster name,require

42、d identity parameter#pg_seq:0#INSTANCE#pgsql instance seq number,required identity parameter#pg_role:replica#INSTANCE#pgsql role,required,could be primary,replica,offline#pg_instances:#INSTANCE#define multiple pg instances on node in port:ins_vars format#pg_upstream:#INSTANCE#repl upstream ip addr f

43、or standby cluster or cascade replica#pg_shard:#CLUSTER#pgsql shard name,optional identity for sharding clusters#pg_sindex:0#CLUSTER#pgsql shard index,optional identity for sharding clusters#gp_role:master#CLUSTER#greenplum role of this cluster,could be master or segmentpg_offline_query:false#INSTAN

44、CE#set to true to enable offline query on this instancepg_weight:100#INSTANCE#relative load balance weight in service,100 by default,0-255#-#PG_BUSINESS#-#postgres business object definition,overwrite in group varspg_users:#postgres business userspg_databases:#postgres business databasespg_services:

45、#postgres business servicespg_hba_rules:#business hba rules for postgrespgb_hba_rules:#business hba rules for pgbouncer#global credentials,overwrite in global varspg_replication_username:replicatorpg_replication_password:DBUser.Replicatorpg_admin_username:dbuser_dbapg_admin_password:DBUser.DBApg_mon

46、itor_username:dbuser_monitorpg_monitor_password:DBUser.Monitor#-#PG_INSTALL#-pg_dbsu:postgres#os dbsu name,postgres by default,better not change itpg_dbsu_uid:26#os dbsu uid and gid,26 for default postgres users and groupspg_dbsu_sudo:limit#dbsu sudo privilege,none,limit,all,nopass.limit by defaultp

47、g_dbsu_home:/var/lib/pgsql#postgresql home directory,/var/lib/pgsql by defaultpg_dbsu_ssh_exchange:true#exchange postgres dbsu ssh key among same pgsql clusterpg_version:15#postgres major version to be installed,15 by defaultpg_bin_dir:/usr/pgsql/bin#postgres binary dir,/usr/pgsql/bin by defaultpg_l

48、og_dir:/pg/log/postgres#postgres log dir,/pg/log/postgres by defaultpg_packages:#pg packages to be installed,$pg_version will be replaced -postgresql$pg_version*-pgbouncer pg_exporter pgbadger vip-manager patroni patroni-etcd pgbackrestpg_extensions:#pg extensions to be installed,$pg_version will be

49、 replaced -postgis33_$pg_version*pg_repack_$pg_version wal2json_$pg_version#citus111_$pg_version timescaledb-2-postgresql-$pg_version#-#PG_BOOTSTRAP#-pg_safeguard:false#prevent purging running postgres instance?false by defaultpg_clean:true#purging existing postgres during pgsql init?true by default

50、pg_data:/pg/data#postgres data directory,/pg/data by defaultpg_fs_main:/data#mountpoint/path for postgres main data,/data by defaultpg_fs_bkup:/data/backups#mountpoint/path for pg backup data,data/backup by defaultpg_storage_type:SSD#storage type for pg main data,SSD,HDD,SSD by defaultpg_dummy_files

51、ize:64MiB#size of/pg/dummy,hold 64MB disk space for emergency usepg_listen:0.0.0.0#postgres listen address,0.0.0.0(all ipv4 addr)by defaulpg_port:5432#postgres listen port,5432 by defaultpg_localhost:/var/run/postgresql#postgres unix socket dir for localhost connectionpg_namespace:/pg#top level key

52、namespace in etcd,used by patroni&vippatroni_enabled:true#if disabled,no postgres cluster will be created during initpatroni_mode:default#patroni working mode:default,pause,removepatroni_port:8008#patroni listen port,8008 by defaultpatroni_log_dir:/pg/log/patroni#patroni log dir,/pg/log/patroni by d

53、efaultpatroni_ssl_enabled:false#secure patroni RestAPI communications with SSL?patroni_watchdog_mode:off#patroni watchdog mode:automatic,required,off.off by defaultpatroni_username:postgres#patroni restapi username,postgres by defaultpatroni_password:Patroni.API#patroni restapi password,Patroni.API

54、by defaultpg_conf:oltp.yml#config template:oltp,olap,crit,tiny.oltp.yml by defaultpg_rto:30#recovery time objective in seconds,30s by defaultpg_rpo:1048576#recovery point objective in bytes,1MiB at most by defaultpg_libs:pg_stat_statements,auto_explain#preloaded libraries,pg_stat_statements,auto_exp

55、lain by defaultpg_delay:0#replication apply delay for standby cluster leaderpg_checksum:false#enable data checksum for postgres cluster?pg_pwd_enc:scram-sha-256#passwords encryption algorithm:md5,scram-sha-256pg_encoding:UTF8#database cluster encoding,UTF8 by defaultpg_locale:C#database cluster loca

56、l,C by defaultpg_lc_collate:C#database cluster collate,C by defaultpg_lc_ctype:en_US.UTF8#database character type,en_US.UTF8 by defaultpgbouncer_enabled:true#if disabled,pgbouncer will not be launched on pgsql hostpgbouncer_port:6432#pgbouncer listen port,6432 by defaultpgbouncer_log_dir:/pg/log/pgb

57、ouncer#pgbouncer log dir,/pg/log/pgbouncer by defaultpgbouncer_auth_query:false#query postgres to retrieve unlisted business users?pgbouncer_poolmode:transaction#pooling mode:transaction,session,statement,transaction by defaultpgbouncer_sslmode:disable#pgbouncer client ssl mode,disable by default#-#

58、PG_PROVISION#-pg_provision:true#provision postgres cluster after bootstrappg_init:pg-init#provision init script for cluster template,pg-init by defaultpg_default_roles:#default roles and users in postgres cluster -name:dbrole_readonly ,login:false,comment:role for global read-only access -name:dbrol

59、e_offline ,login:false,comment:role for restricted read-only access -name:dbrole_readwrite,login:false,roles:dbrole_readonly ,comment:role for global read-write access -name:dbrole_admin ,login:false,roles:pg_monitor,dbrole_readwrite ,comment:role for object creation -name:postgres ,superuser:true ,

60、comment:system superuser -name:replicator,replication:true ,roles:pg_monitor,dbrole_readonly ,comment:system replicator -name:dbuser_dba ,superuser:true ,roles:dbrole_admin ,pgbouncer:true,pool_mode:session,pool_connlimit:16,comment:pgsql admin user -name:dbuser_monitor ,roles:pg_monitor,dbrole_read

61、only,pgbouncer:true,parameters:log_min_duration_statement:1000 ,pool_mode:session,pool_connlimit:8,comment:pgsql monitor user pg_default_privileges:#default privileges when created by admin user -GRANT USAGE ON SCHEMAS TO dbrole_readonly -GRANT SELECT ON TABLES TO dbrole_readonly -GRANT SELECT ON SE

62、QUENCES TO dbrole_readonly -GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly -GRANT USAGE ON SCHEMAS TO dbrole_offline -GRANT SELECT ON TABLES TO dbrole_offline -GRANT SELECT ON SEQUENCES TO dbrole_offline -GRANT EXECUTE ON FUNCTIONS TO dbrole_offline -GRANT INSERT ON TABLES TO dbrole_readwrite -GRANT

63、UPDATE ON TABLES TO dbrole_readwrite -GRANT DELETE ON TABLES TO dbrole_readwrite -GRANT USAGE ON SEQUENCES TO dbrole_readwrite -GRANT UPDATE ON SEQUENCES TO dbrole_readwrite -GRANT TRUNCATE ON TABLES TO dbrole_admin -GRANT REFERENCES ON TABLES TO dbrole_admin -GRANT TRIGGER ON TABLES TO dbrole_admin

64、 -GRANT CREATE ON SCHEMAS TO dbrole_adminpg_default_schemas:monitor#default schemas to be createdpg_default_extensions:#default extensions to be created -name:adminpack ,schema:pg_catalog -name:pg_stat_statements,schema:monitor -name:pgstattuple ,schema:monitor -name:pg_buffercache ,schema:monitor -

65、name:pageinspect ,schema:monitor -name:pg_prewarm ,schema:monitor -name:pg_visibility ,schema:monitor -name:pg_freespacemap ,schema:monitor -name:postgres_fdw ,schema:public -name:file_fdw ,schema:public -name:btree_gist ,schema:public -name:btree_gin ,schema:public -name:pg_trgm ,schema:public -nam

66、e:intagg ,schema:public -name:intarray ,schema:public -name:pg_repack pg_reload:true#reload postgres after hba changespg_default_hba_rules:#postgres default host-based authentication rules -user:$dbsu ,db:all ,addr:local ,auth:ident,title:dbsu access via local os user ident -user:$dbsu ,db:replicati

67、on,addr:local ,auth:ident,title:dbsu replication from local os ident -user:$repl ,db:replication,addr:localhost,auth:pwd ,title:replicator replication from localhost -user:$repl ,db:replication,addr:intra ,auth:pwd ,title:replicator replication from intranet -user:$repl ,db:postgres ,addr:intra ,aut

68、h:pwd ,title:replicator postgres db from intranet -user:$monitor,db:all ,addr:localhost,auth:pwd ,title:monitor from localhost with password -user:$monitor,db:all ,addr:infra ,auth:pwd ,title:monitor from infra host with password -user:$admin ,db:all ,addr:infra ,auth:ssl ,title:admin infra nodes wi

69、th pwd&ssl -user:$admin ,db:all ,addr:world ,auth:cert ,title:admin everywhere with ssl&cert -user:+dbrole_readonly,db:all ,addr:localhost,auth:pwd ,title:pgbouncer read/write via local socket -user:+dbrole_readonly,db:all ,addr:intra ,auth:pwd ,title:read/write biz user via password -user:+dbrole_o

70、ffline,db:all ,addr:intra ,auth:pwd ,title:allow etl offline tasks from intranetpgb_default_hba_rules:#pgbouncer default host-based authentication rules -user:$dbsu ,db:pgbouncer ,addr:local ,auth:peer ,title:dbsu local admin access with os ident -user:all ,db:all ,addr:localhost,auth:pwd ,title:all

71、ow all user local access with pwd -user:$monitor,db:pgbouncer ,addr:intra ,auth:pwd ,title:monitor access via intranet with pwd -user:$monitor,db:all ,addr:world ,auth:deny ,title:reject all other monitor access addr -user:$admin ,db:all ,addr:intra ,auth:pwd ,title:admin access via intranet with pw

72、d -user:$admin ,db:all ,addr:world ,auth:deny ,title:reject all other admin access addr -user:all ,db:all ,addr:intra ,auth:pwd ,title:allow all user intra access with pwd pg_default_services:#postgres default service definitions -name:primary,port:5433,dest:pgbouncer,check:/primary ,selector:-name:

73、replica,port:5434,dest:pgbouncer,check:/read-only,selector:,backup:?pg_role=primary|pg_role=offline -name:default,port:5436,dest:postgres ,check:/primary ,selector:-name:offline,port:5438,dest:postgres ,check:/replica ,selector:?pg_role=offline|pg_offline_query ,backup:?pg_role=replica&!pg_offline_q

74、uery#-#PG_BACKUP#-pgbackrest_enabled:true#enable pgbackrest on pgsql host?pgbackrest_clean:false#remove existing pgbackrest data during init?pgbackrest_log_dir:/pg/log/pgbackrest#pgbackrest log dir,/pg/log/pgbackrest by defaultpgbackrest_repo:|#pgbackrest backup repo config,local repo on primary by

75、default repo1-path=/pg/backup/repo1-retention-full-type=time repo1-retention-full=14 repo1-retention-diff=3#-#PG_VIP#-pg_vip_enabled:false#enable a l2 vip for pgsql primary?false by defaultpg_vip_address:127.0.0.1/24#vip address in/format,require if vip is enabledpg_vip_interface:eth0#vip network in

76、terface to listen,eth0 by default#-#PG_DNS#-pg_dns_suffix:#pgsql dns suffix,by defaultpg_dns_target:auto#auto,primary,vip,none,or ad hoc ip#-#PG_EXPORTER#-pg_exporter_enabled:true#enable pg_exporter on pgsql hosts?pg_exporter_config:pg_exporter.yml#pg_exporter configuration file namepg_exporter_port

77、:9630#pg_exporter listen port,9630 by defaultpg_exporter_params:sslmode=disable#extra url parameters for pg_exporter connstrpg_exporter_url:#overwrite auto-generate pg connstr if specifiedpg_exporter_auto_discovery:true#enable audo database discovery?enabled by defaultpg_exporter_exclude_database:te

78、mplate0,template1,postgres#csv of database that WILL NOT be monitored during auto-discoverypg_exporter_include_database:#csv of database that WILL BE monitored during auto-discoverypg_exporter_options:-connect-timeout=200-log.level=info-log.format=logger:syslog?appname=pg_exporter&local=7pgbouncer_e

79、xporter_enabled:true#enable pgbouncer_exporter on pgsql hosts?pgbouncer_exporter_port:9631#pgbouncer_exporter listen port,9631 by defaultpgbouncer_exporter_url:#overwrite auto-generate pgbouncer connstr if specifiedpgbouncer_exporter_options:-log.level=info-log.format=logger:syslog?appname=pgbouncer

80、_exporter&local=7all:children:#infra cluster for proxy,monitor,alert,etc.infra:hosts:10.10.10.10:infra_seq:1#etcd cluster for ha postgres etcd:hosts:10.10.10.10:etcd_seq:1,vars:etcd_cluster:etcd#postgres cluster pg-meta pg-meta:hosts:10.10.10.10:pg_seq:1,pg_role:primary vars:pg_cluster:pg-meta pg_us

81、ers:-name:dbuser_meta,password:DBUser.Meta ,pgbouncer:true,roles:dbrole_admin ,comment:pigsty admin user -name:dbuser_view,password:DBUser.Viewer,pgbouncer:true,roles:dbrole_readonly,comment:read-only viewer for meta database pg_databases:-name:meta,baseline:cmdb.sql,comment:pigsty meta database,sch

82、emas:pigsty,extensions:name:postgis,schema:public vars:#global parameters version:v2.0.0-b2#pigsty version string admin_ip:10.10.10.10#admin node ip address region:default#upstream mirror region:default,china,europe最完整配置仿真沙箱 node-310.10.10.13node-210.10.10.12node-110.10.10.11meta 10.10.10.10Database

83、 Servicepg-metaDatabase Servicepg-test9101NODES9080Promtail9100NodeExporterChronydnode_cluster:pg-testnodename:pg-test-19101NODES9080Promtail9100NodeExporterChronydnode_cluster:pg-testnodename:pg-test-29101NODES9080Promtail9100NodeExporterChronydnode_cluster:pg-testnodename:pg-test-3INFRA31009093Ale

84、rtManager300080h.pigsty80Yum Repoh.pigstyg.pigstya.pigsty9090p.pigsty9091Push Gateway9115Blackbox Exporter53DNSMASQ9323PGSQL6432Pgbouncer8008Patroni9630PGExporter9631PGBExporter543354345436543891015432pg_cluster:pg-metapg_role:primarypg_instance:pg-meta-1pg_service:pg-meta-primary8500ServerNODES9080

85、Promtail9100NodeExporternode_cluster:pg-metanodename:pg-meta-1123NTPDServerPGSQL6432Pgbouncer8008Patroni9630PGExporter9631PGBExporter543354345436543891015432pg_cluster:pg-testpg_role:primarypg_instance:pg-test-1pg_service:pg-test-primaryPGSQL6432Pgbouncer8008Patroni9630PGExporter9631PGBExporter54335

86、4345436543891015432pg_cluster:pg-testpg_role:replicapg_instance:pg-test-2pg_service:pg-test-replicaPGSQL6432Pgbouncer8008Patroni9630PGExporter9631PGBExporter543354345436543891015432pg_cluster:pg-testpg_role:replicapg_instance:pg-test-3pg_service:pg-test-replicaDNSpg-metaL2 VIP10.10.10.25433543454365

87、438DNSpg-testL2 VIP10.10.10.35433543454365438可擴展性 PG 不擴展，就像喝腐腦不加鹽地理信息數據庫事實標準時序數據庫擴展將 PG 原地改造為分布式數據庫#citus coordinator nodepg-meta:hosts:10.10.10.10:pg_seq:1,pg_role:primary,pg_offline_query:true vars:pg_cluster:pg-meta pg_users:name:citus,password:citus,pgbouncer:true,roles:dbrole_admin pg_databases

88、:-name:meta,schemas:pigsty ,extensions:name:postgis,schema:public,name:citus ,baseline:cmdb.sql#citus data node 1,2,3pg-node1:hosts:10.10.10.11:pg_seq:1,pg_role:primary vars:pg_cluster:pg-node1 pg_users:name:citus,password:citus,pgbouncer:true,roles:dbrole_admin pg_databases:name:meta,owner:citus,ex

89、tensions:name:citus,name:postgis,schema:public pg-node2:hosts:10.10.10.12:pg_seq:1,pg_role:primary ,pg_offline_query:true vars:pg_cluster:pg-node2 pg_users:name:citus,password:citus,pgbouncer:true,roles:dbrole_admin pg_databases:name:meta,owner:citus,extensions:name:citus,name:postgis,schema:public

90、pg-node3:hosts:10.10.10.13:pg_seq:1,pg_role:primary ,pg_offline_query:true vars:pg_cluster:pg-node3 pg_users:name:citus,password:citus,pgbouncer:true,roles:dbrole_admin pg_databases:name:meta,owner:citus,extensions:name:citus,name:postgis,schema:public -orafce_15*mysqlcompat_15 mongo_fdw_15*tds_fdw_

91、15*mysql_fdw_15-hdfs_fdw_15 sqlite_fdw_15 pgbouncer_fdw_15 pg_dbms_job_15-pg_stat_kcache_15*pg_stat_monitor_15*pg_qualstats_15 pg_top_15-pg_track_settings_15 pg_wait_sampling_15 system_stats_15 logerrors_15-plprofiler_15*plproxy_15 plsh_15*pldebugger_15 plpgsql_check_15*-pgtt_15 pgq_15*pgsql_tweaks_

92、15 count_distinct_15 hypopg_15-timestamp9_15*semver_15*prefix_15*rum_15 geoip_15 periods_15-ip4r_15 tdigest_15 hll_15 pgmp_15 extra_window_functions_15 topn_15-pg_comparator_15 pg_ivm_15*pgsodium_15*pgfincore_15*ddlx_15-credcheck_15 postgresql_anonymizer_15*postgresql_faker_15 safeupdate_15-pg_fkpar

93、t_15 pg_jobmon_15 pg_partman_15 pg_permissions_15 pgaudit17_15-pgexportdoc_15 pgimportdoc_15 pg_statement_rollback_15*-pg_cron_15 pg_background_15 e-maj_15 pg_catcheck_15 pg_prioritize_15 pgcopydb_15 pg_filedump_15 pgcryptokey_15FDWGraphFederatedDistributiveGeoSpectialTimeSeriesWareHouse集成海量擴展4互操作性N

94、ode個籬笆三個樁，個好漢三個幫 Chinese Verb多種數據庫持MatrixDB(Greenplum)ETCD,Minio除了 PostgreSQL，還有 Redis完備的數據棧：RDS+緩存+數倉+對象存儲+消息隊列后續將有更多品類持！Docker 應模板統統 docker-compose up 把梭拉起Grafana 可視化極速低代碼開發數據應原型集成Echarts：快速呈現數據直接訪問 PostgreSQL 數據源瀏覽數據庫錄與活動等等，還有性能？過早優化是惡之源 德納衡量性能的標準是延遲1ns10ns100ns1s10s100s1ms10ms100ms1s寄存器L1/L2緩存，分

95、支預測失效L3緩存訪問，Apple M1 內存訪問內存訪問，簡單系統調用，md5(int64)線程上下文切換，64K內存拷貝處理HTTP請求，內存順序讀1MB，SSD訪問8K頁同機房內網絡RT，SSD寫入1頁，完整Redis請求跨可用區網絡RT，磁盤尋道內存順序讀1GB，國內RT，EBS 8K訪問，DynamoDB訪問TLS握手，跨洲網絡RT，SSD順序讀1GB網絡傳輸1GBPostgreSQL 典型操作的理想響應時間0255075100125SELECTDELETEINSERTUPDATE8012075300200400600800UINDEXOLTPWOOLTPROOLTPRW740620

96、25080個操作需要多少微秒？200萬 點查 QPS14萬 TPSUPDATE pgbench_accounts SET abalance=abalance+:delta WHERE aid=:aid;SELECT abalance FROM pgbench_accounts WHERE aid=:aid;UPDATE pgbench_tellers SET tbalance=tbalance+:delta WHERE tid=:tid;UPDATE pgbench_branches SET bbalance=bbalance+:delta WHERE bid=:bid;INSERT INTO

97、 pgbench_history(tid,bid,aid,delta,mtime)VALUES(:tid,:bid,:aid,:delta,CURRENT_TIMESTAMP);（約合70w點寫QPS）sysbench-db-driver=pgsql-pgsql-host=/tmp-pgsql-user=postgres -pgsql-password=postgres-pgsql-db=postgres -table_size=10000000-tables=8-report-interval=1-time=300-threads=Sysbench48核下種數據庫吞吐量近似對 除 Postg

98、reSQL 外采官評數據 PostgreSQL vs MySQL（閾值 2000萬 vs 百億）分布式數據庫祛魅以及 成本由軟件并免費Pigsty典型案例：TT數據庫：1.3萬核規模Oracle數據庫:15億/年亞遜云數據庫：2.6億/年探探是個活千萬的知名互聯應。在探探，有規模達 1萬3千核的 數據庫。像這樣的量級，如果使 商業數據庫 Oracle，要15億年，使亞遜云數據庫便宜些，兩三億。國內的阿云數據庫更便宜點，五千萬年。阿云數據庫：5千萬/年Pigsty+IDC建 500W/年（含）Pigsty但是，我們通過 Pigsty 建 PostgreSQL 數據庫。僅300萬的成本，兩個專職數

99、據庫管理員，就維護好論成千上萬的實例。我們可以幫助探探做到這點，然也可以幫助更多企業完成這過程。成本核算參考值（1核算!1價格）部署式單價IDC建機房（獨占物理機 A1:64C384G）19IDC建機房（獨占物理機 B1:40C64G）26IDC建機房（獨占物理機 C2:8C16G）38IDC建機房（容器，超賣200%）17IDC建機房（容器，超賣500%）7Ucloud 彈性虛擬機（8C16G，有超賣）25阿云 彈性服務器 2x內存（獨占超賣）107阿云 彈性服務器 4x內存（獨占超賣）138阿云 彈性服務器 8x內存（獨占超賣）180阿云 云數據庫 2x內存（獨占超賣）260阿云 云數據庫 4x內存（獨占超賣）320阿云 云數據庫 8x內存（獨占超賣）410ORACLE數據庫授權10000成本模型Pigsty但是，我們通過 Pigsty 建 PostgreSQL 數據庫。僅300萬的成本，兩個專職數據庫管理員，就維護好論成千上萬的實例。我們可以幫助探探做到這點，然也可以幫助更多企業完成這過程。AGPL v3.0 協議RDS=管控軟件+共享DBAPigsty訂閱=開源管控軟件+專家服務持