我的书签
添加书签
移除书签Overview
Overview of PostgreSQL in Pigsty
Concept
Overview of PostgreSQL in Pigsty
- Architecture
- Configuration
- Database
- User
- Service
- Authentication
- Access Control
- Administration
- Backup & PITR
- Monitor
- Migration
Configuration
Describe the cluster you want
- Identity: Parameters used for describing a PostgreSQL cluster
- Primary: Define a single instance cluster
- Replica: Define a basic HA cluster with one primary & one replica
- Offline: Define a dedicated instance for OLAP/ETL/Interactive queries.
- Sync Standby: Enable synchronous commit to ensure no data loss
- Quorum Commit: Use quorum sync commit for an even higher consistency level
- Standby Cluster: Clone an existing cluster and follow it
- Delayed Cluster: Clone an existing cluster for emergency data recovery
- Citus Cluster: Define a Citus distributed database cluster
Playbooks
Materialize the cluster you described
- pgsql.yml : Init HA PostgreSQL clusters or adding new replicas.
- pgsql-rm.yml : Remove PostgreSQL cluster, or remove replicas
- pgsql-user.yml : Add new business user to existing PostgreSQL cluster
- pgsql-db.yml : Add new business database to existing PostgreSQL cluster
- pgsql-monitor.yml : Monitor remote postgres instance with local exporters
- pgsql-migration.yml : Generate Migration manual & scripts for existing PostgreSQL
Dashboards
There are 23 default grafana dashboards about PostgreSQL and categorized into 4 levels, check PGSQL Monitor for details.
Administration
Admin your existing clusters
- Admin Cheatsheet
- Create Cluster
- Create User
- Create Database
- Reload Service
- Reload HBARule
- Config Cluster
- Append Replica
- Remove Replica
- Remove Cluster
- Switchover Cluster
- Backup Cluster
- Restore Cluster
Parameters
API Reference for PGSQL module:
- PG_ID : Calculate & Check Postgres Identity
- PG_BUSINESS : Postgres Business Object Definition
- PG_INSTALL : Install PGSQL Packages & Extensions
- PG_BOOTSTRAP : Init a HA Postgres Cluster with Patroni
- PG_PROVISION : Create users, databases, and in-database objects
- PG_BACKUP : Setup backup repo with pgbackrest
- PG_SERVICE : Exposing pg service, bind vip and register DNS
- PG_EXPORTER : Add Monitor for PGSQL Instance
Parameters
| Parameter | Section | Type | Level | Comment |
|---|---|---|---|---|
| pg_mode | PG_ID | enum | C | pgsql cluster mode: pgsql,citus,gpsql |
| pg_cluster | PG_ID | string | C | pgsql cluster name, REQUIRED identity parameter |
| pg_seq | PG_ID | int | I | pgsql instance seq number, REQUIRED identity parameter |
| pg_role | PG_ID | enum | I | pgsql role, REQUIRED, could be primary,replica,offline |
| pg_instances | PG_ID | dict | I | define multiple pg instances on node in {port:ins_vars} format |
| pg_upstream | PG_ID | ip | I | repl upstream ip addr for standby cluster or cascade replica |
| pg_shard | PG_ID | string | C | pgsql shard name, optional identity for sharding clusters |
| pg_group | PG_ID | int | C | pgsql shard index number, optional identity for sharding clusters |
| gp_role | PG_ID | enum | C | greenplum role of this cluster, could be master or segment |
| pg_exporters | PG_ID | dict | C | additional pg_exporters to monitor remote postgres instances |
| pg_offline_query | PG_ID | bool | I | set to true to enable offline query on this instance |
| pg_users | PG_BUSINESS | user[] | C | postgres business users |
| pg_databases | PG_BUSINESS | database[] | C | postgres business databases |
| pg_services | PG_BUSINESS | service[] | C | postgres business services |
| pg_hba_rules | PG_BUSINESS | hba[] | C | business hba rules for postgres |
| pgb_hba_rules | PG_BUSINESS | hba[] | C | business hba rules for pgbouncer |
| pg_replication_username | PG_BUSINESS | username | G | postgres replication username, replicator by default |
| pg_replication_password | PG_BUSINESS | password | G | postgres replication password, DBUser.Replicator by default |
| pg_admin_username | PG_BUSINESS | username | G | postgres admin username, dbuser_dba by default |
| pg_admin_password | PG_BUSINESS | password | G | postgres admin password in plain text, DBUser.DBA by default |
| pg_monitor_username | PG_BUSINESS | username | G | postgres monitor username, dbuser_monitor by default |
| pg_monitor_password | PG_BUSINESS | password | G | postgres monitor password, DBUser.Monitor by default |
| pg_dbsu_password | PG_BUSINESS | password | G/C | dbsu password, empty string means no dbsu password by default |
| pg_dbsu | PG_INSTALL | username | C | os dbsu name, postgres by default, better not change it |
| pg_dbsu_uid | PG_INSTALL | int | C | os dbsu uid and gid, 26 for default postgres users and groups |
| pg_dbsu_sudo | PG_INSTALL | enum | C | dbsu sudo privilege, none,limit,all,nopass. limit by default |
| pg_dbsu_home | PG_INSTALL | path | C | postgresql home directory, /var/lib/pgsql by default |
| pg_dbsu_ssh_exchange | PG_INSTALL | bool | C | exchange postgres dbsu ssh key among same pgsql cluster |
| pg_version | PG_INSTALL | enum | C | postgres major version to be installed, 15 by default |
| pg_bin_dir | PG_INSTALL | path | C | postgres binary dir, /usr/pgsql/bin by default |
| pg_log_dir | PG_INSTALL | path | C | postgres log dir, /pg/log/postgres by default |
| pg_packages | PG_INSTALL | string[] | C | pg packages to be installed, ${pg_version} will be replaced |
| pg_extensions | PG_INSTALL | string[] | C | pg extensions to be installed, ${pg_version} will be replaced |
| pg_safeguard | PG_BOOTSTRAP | bool | G/C/A | prevent purging running postgres instance? false by default |
| pg_clean | PG_BOOTSTRAP | bool | G/C/A | purging existing postgres during pgsql init? true by default |
| pg_data | PG_BOOTSTRAP | path | C | postgres data directory, /pg/data by default |
| pg_fs_main | PG_BOOTSTRAP | path | C | mountpoint/path for postgres main data, /data by default |
| pg_fs_bkup | PG_BOOTSTRAP | path | C | mountpoint/path for pg backup data, /data/backup by default |
| pg_storage_type | PG_BOOTSTRAP | enum | C | storage type for pg main data, SSD,HDD, SSD by default |
| pg_dummy_filesize | PG_BOOTSTRAP | size | C | size of /pg/dummy, hold 64MB disk space for emergency use |
| pg_listen | PG_BOOTSTRAP | ip | C | postgres listen address, 0.0.0.0 (all ipv4 addr) by default |
| pg_port | PG_BOOTSTRAP | port | C | postgres listen port, 5432 by default |
| pg_localhost | PG_BOOTSTRAP | path | C | postgres unix socket dir for localhost connection |
| pg_namespace | PG_BOOTSTRAP | path | C | top level key namespace in etcd, used by patroni & vip |
| patroni_enabled | PG_BOOTSTRAP | bool | C | if disabled, no postgres cluster will be created during init |
| patroni_mode | PG_BOOTSTRAP | enum | C | patroni working mode: default,pause,remove |
| patroni_port | PG_BOOTSTRAP | port | C | patroni listen port, 8008 by default |
| patroni_log_dir | PG_BOOTSTRAP | path | C | patroni log dir, /pg/log/patroni by default |
| patroni_ssl_enabled | PG_BOOTSTRAP | bool | G | secure patroni RestAPI communications with SSL? |
| patroni_watchdog_mode | PG_BOOTSTRAP | enum | C | patroni watchdog mode: automatic,required,off. off by default |
| patroni_username | PG_BOOTSTRAP | username | C | patroni restapi username, postgres by default |
| patroni_password | PG_BOOTSTRAP | password | C | patroni restapi password, Patroni.API by default |
| pg_conf | PG_BOOTSTRAP | enum | C | config template: oltp,olap,crit,tiny. oltp.yml by default |
| pg_max_conn | PG_BOOTSTRAP | int | C | postgres max connections, auto will use recommended value |
| pg_shared_buffer_ratio | PG_BOOTSTRAP | float | C | postgres shared buffer memory ratio, 0.25 by default, 0.1~0.4 |
| pg_rto | PG_BOOTSTRAP | int | C | recovery time objective in seconds, 30s by default |
| pg_rpo | PG_BOOTSTRAP | int | C | recovery point objective in bytes, 1MiB at most by default |
| pg_libs | PG_BOOTSTRAP | string | C | preloaded libraries, pg_stat_statements,auto_explain by default |
| pg_delay | PG_BOOTSTRAP | interval | I | replication apply delay for standby cluster leader |
| pg_checksum | PG_BOOTSTRAP | bool | C | enable data checksum for postgres cluster? |
| pg_pwd_enc | PG_BOOTSTRAP | enum | C | passwords encryption algorithm: md5,scram-sha-256 |
| pg_encoding | PG_BOOTSTRAP | enum | C | database cluster encoding, UTF8 by default |
| pg_locale | PG_BOOTSTRAP | enum | C | database cluster local, C by default |
| pg_lc_collate | PG_BOOTSTRAP | enum | C | database cluster collate, C by default |
| pg_lc_ctype | PG_BOOTSTRAP | enum | C | database character type, en_US.UTF8 by default |
| pgbouncer_enabled | PG_BOOTSTRAP | bool | C | if disabled, pgbouncer will not be launched on pgsql host |
| pgbouncer_port | PG_BOOTSTRAP | port | C | pgbouncer listen port, 6432 by default |
| pgbouncer_log_dir | PG_BOOTSTRAP | path | C | pgbouncer log dir, /pg/log/pgbouncer by default |
| pgbouncer_auth_query | PG_BOOTSTRAP | bool | C | query postgres to retrieve unlisted business users? |
| pgbouncer_poolmode | PG_BOOTSTRAP | enum | C | pooling mode: transaction,session,statement, transaction by default |
| pgbouncer_sslmode | PG_BOOTSTRAP | enum | C | pgbouncer client ssl mode, disable by default |
| pg_provision | PG_PROVISION | bool | C | provision postgres cluster after bootstrap |
| pg_init | PG_PROVISION | string | G/C | provision init script for cluster template, pg-init by default |
| pg_default_roles | PG_PROVISION | role[] | G/C | default roles and users in postgres cluster |
| pg_default_privileges | PG_PROVISION | string[] | G/C | default privileges when created by admin user |
| pg_default_schemas | PG_PROVISION | string[] | G/C | default schemas to be created |
| pg_default_extensions | PG_PROVISION | extension[] | G/C | default extensions to be created |
| pg_reload | PG_PROVISION | bool | A | reload postgres after hba changes |
| pg_default_hba_rules | PG_PROVISION | hba[] | G/C | postgres default host-based authentication rules |
| pgb_default_hba_rules | PG_PROVISION | hba[] | G/C | pgbouncer default host-based authentication rules |
| pgbackrest_enabled | PG_BACKUP | bool | C | enable pgbackrest on pgsql host? |
| pgbackrest_clean | PG_BACKUP | bool | C | remove pg backup data during init? |
| pgbackrest_log_dir | PG_BACKUP | path | C | pgbackrest log dir, /pg/log/pgbackrest by default |
| pgbackrest_method | PG_BACKUP | enum | C | pgbackrest repo method: local,minio,etc… |
| pgbackrest_repo | PG_BACKUP | dict | G/C | pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository |
| pg_weight | PG_SERVICE | int | I | relative load balance weight in service, 100 by default, 0-255 |
| pg_service_provider | PG_SERVICE | enum | G/C | dedicate haproxy node group name, or empty string for local nodes by default |
| pg_default_service_dest | PG_SERVICE | enum | G/C | default service destination if svc.dest=‘default’ |
| pg_default_services | PG_SERVICE | service[] | G/C | postgres default service definitions |
| pg_vip_enabled | PG_SERVICE | bool | C | enable a l2 vip for pgsql primary? false by default |
| pg_vip_address | PG_SERVICE | cidr4 | C | vip address in <ipv4>/<mask> format, require if vip is enabled |
| pg_vip_interface | PG_SERVICE | string | C/I | vip network interface to listen, eth0 by default |
| pg_dns_suffix | PG_SERVICE | string | C | pgsql dns suffix, ’’ by default |
| pg_dns_target | PG_SERVICE | enum | C | auto, primary, vip, none, or ad hoc ip |
| pg_exporter_enabled | PG_EXPORTER | bool | C | enable pg_exporter on pgsql hosts? |
| pg_exporter_config | PG_EXPORTER | string | C | pg_exporter configuration file name |
| pg_exporter_cache_ttls | PG_EXPORTER | string | C | pg_exporter collector ttl stage in seconds, ‘1,10,60,300’ by default |
| pg_exporter_port | PG_EXPORTER | port | C | pg_exporter listen port, 9630 by default |
| pg_exporter_params | PG_EXPORTER | string | C | extra url parameters for pg_exporter dsn |
| pg_exporter_url | PG_EXPORTER | pgurl | C | overwrite auto-generate pg dsn if specified |
| pg_exporter_auto_discovery | PG_EXPORTER | bool | C | enable auto database discovery? enabled by default |
| pg_exporter_exclude_database | PG_EXPORTER | string | C | csv of database that WILL NOT be monitored during auto-discovery |
| pg_exporter_include_database | PG_EXPORTER | string | C | csv of database that WILL BE monitored during auto-discovery |
| pg_exporter_connect_timeout | PG_EXPORTER | int | C | pg_exporter connect timeout in ms, 200 by default |
| pg_exporter_options | PG_EXPORTER | arg | C | overwrite extra options for pg_exporter |
| pgbouncer_exporter_enabled | PG_EXPORTER | bool | C | enable pgbouncer_exporter on pgsql hosts? |
| pgbouncer_exporter_port | PG_EXPORTER | port | C | pgbouncer_exporter listen port, 9631 by default |
| pgbouncer_exporter_url | PG_EXPORTER | pgurl | C | overwrite auto-generate pgbouncer dsn if specified |
| pgbouncer_exporter_options | PG_EXPORTER | arg | C | overwrite extra options for pgbouncer_exporter |
Tutorials
- Fork an existing PostgreSQL cluster.
- Create a standby cluster of an existing PostgreSQL cluster.
- Create a delayed cluster of another pgsql cluster?
- Monitoring an existing postgres instance?
- Migration from an external PostgreSQL with logical replication?
- Use MinIO as a central pgBackRest repo.
- Use dedicate etcd cluster for DCS?
- Use dedicated haproxy for exposing PostgreSQL service.
- Deploy a multi-node MinIO cluster?
- Use CMDB instead of Config as inventory.
- Use PostgreSQL as grafana backend storage ?
- Use PostgreSQL as prometheus backend storage ?
Last modified 2023-02-27: refresh en docs to v2.0 (e82b371)
