-
Notifications
You must be signed in to change notification settings - Fork 254
PGSQL
Feng Ruohang edited this page Feb 11, 2023
·
5 revisions
The most advanced open-source relational database in the world!
With battery-included observability, reliability, and maintainability powered by Pigsty
Overview of PostgreSQL in Pigsty
- Architecture
- Configuration
- Database
- User
- Service
- Authentication
- Access Control
- Administration
- Backup & PITR
- Monitor
- Migration
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
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
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
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_VIP
: Bind an optional VIP on primary -
PG_DNS
: Register DNS Record to Infra -
PG_EXPORTER
: Add Monitor for PG & PGBOUNCER
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 | G | set to true to enable offline query on this instance |
pg_weight |
PG_ID |
int | G | relative load balance weight in service, 100 by default, 0-255 |
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 |
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_shmem_ratio |
PG_BOOTSTRAP |
float | C | postgres shared 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 |
pg_default_service_dest |
PG_PROVISION |
enum | G/C | default service destination if svc.dest='default' |
pg_default_services |
PG_PROVISION |
service[] | G/C | postgres default service definitions |
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_vip_enabled |
PG_VIP |
bool | C | enable a l2 vip for pgsql primary? false by default |
pg_vip_address |
PG_VIP |
cidr4 | C | vip address in <ipv4>/<mask> format, require if vip is enabled |
pg_vip_interface |
PG_VIP |
string | C/I | vip network interface to listen, eth0 by default |
pg_dns_suffix |
PG_DNS |
string | C | pgsql dns suffix, '' by default |
pg_dns_target |
PG_DNS |
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 |
- 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 ?