《专场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 ServiceCA123NTPDServer5432CMDBCLIGUI3AlertManager300080Yum 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=20F_20I 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 典型操作的理想响应时间0255075100125SELECTDELETEINSERTUPDATE80400600800UINDEXOLTPWOOLTPROOLTPRW740620
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订阅=开源管控软件+专家服务持