1-node: rich

Downloads all available PostgreSQL extensions and Docker, provisioning a series of databases for software backups.
Categories:
Tags:

The rich template is a 1-node conf aims for running various business software that uses PostgreSQL databases.

If you want to run some business software that uses PostgreSQL databases as the underlying database on a single machine through Docker, such as Odoo, Gitea, Wiki.js, etc., you can consider using this template.

Overview

  • Conf Name: rich
  • Node Count: 1-node, pigsty/vagrant/spec/meta.rb
  • Description: Downloads all available PostgreSQL extensions and Docker, provisioning a series of databases for software backups. in addition to meta
  • Content: pigsty/conf/rich.yml
  • OS Distro: el8, el9, d12, u22, u24
  • OS Arch: x86_64
  • Related: meta

The rich configuration template is designed for single-node deployments. Built upon meta, it downloads all available PostgreSQL extensions and Docker and preconfigures a set of databases to provide an out-of-the-box environment for software integrations.

Compared to meta, this configuration:

  • Downloads Docker packages (docker-ce, docker-compose-plugin).
  • Downloads all available PostgreSQL 17 extensions locally.
  • Sets up six additional dedicated databases and corresponding users within the default pg-meta PostgreSQL cluster for application-specific use.

To enable: Use the -c rich parameter during the configure process:

./configure -c rich [-i <primary_ip>]

Content

Source: pigsty/conf/rich.yml

all:
  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 } }

    # minio cluster, s3 compatible object storage
    minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }

    # postgres example cluster: pg-meta
    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-meta
        pg_users:
          - {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 }
          - {name: dbuser_grafana  ,password: DBUser.Grafana  ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for grafana database    }
          - {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for bytebase database   }
          - {name: dbuser_kong     ,password: DBUser.Kong     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for kong api gateway    }
          - {name: dbuser_gitea    ,password: DBUser.Gitea    ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for gitea service       }
          - {name: dbuser_wiki     ,password: DBUser.Wiki     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for wiki.js service     }
          - {name: dbuser_noco     ,password: DBUser.Noco     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for nocodb service      }
          - {name: dbuser_odoo     ,password: DBUser.Odoo     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for odoo service ,createdb: true} #,superuser: true}
        pg_databases:
          - {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: vector},{name: postgis},{name: timescaledb}]}
          - {name: grafana  ,owner: dbuser_grafana  ,revokeconn: true ,comment: grafana primary database  }
          - {name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
          - {name: kong     ,owner: dbuser_kong     ,revokeconn: true ,comment: kong 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  }
          - {name: noco     ,owner: dbuser_noco     ,revokeconn: true ,comment: nocodb database     }
          - {name: odoo     ,owner: dbuser_odoo     ,revokeconn: true ,comment: odoo main database  }
        pg_hba_rules:
          - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
        pg_libs: 'timescaledb,pg_stat_statements, auto_explain'  # add timescaledb to shared_preload_libraries
        pg_extensions:
          - postgis, pgvector, timescaledb
        node_crontab:  # make one full backup 1 am everyday
          - '00 01 * * * postgres /pg/bin/pg-backup full'

    redis-ms: # redis classic primary & replica
      hosts: { 10.10.10.10: { redis_node: 1 , redis_instances: { 6379: { }, 6380: { replica_of: '10.10.10.10 6379' } } } }
      vars: { redis_cluster: redis-ms ,redis_password: 'redis.ms' ,redis_max_memory: 64MB }

    # To install & enable docker: ./docker.yml -l docker
    docker:
      hosts: { 10.10.10.10: { infra_seq: 1 } }
      vars:
        docker_enabled: true
        #docker_registry_mirrors: ['https://docker.xxxxx.io']   # add your docker mirror/proxy if needed

  vars:                               # global variables
    version: v3.1.0                   # pigsty version string
    admin_ip: 10.10.10.10             # admin node ip address
    region: default                   # upstream mirror region: default|china|europe
    node_tune: oltp                   # node tuning specs: oltp,olap,tiny,crit
    pg_conf: oltp.yml                 # pgsql tuning specs: {oltp,olap,tiny,crit}.yml
    proxy_env:                        # global proxy env when downloading packages
      no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.*,*.myqcloud.com,*.tsinghua.edu.cn"
      # http_proxy:  # set your proxy here: e.g http://user:[email protected]
      # https_proxy: # set your proxy here: e.g http://user:[email protected]
      # all_proxy:   # set your proxy here: e.g http://user:[email protected]
    infra_portal:                     # domain names and upstream servers
      home         : { domain: h.pigsty }
      grafana      : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
      prometheus   : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
      alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
      blackbox     : { endpoint: "${admin_ip}:9115" }
      loki         : { endpoint: "${admin_ip}:3100" }
      minio        : { domain: m.pigsty    ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
      postgrest    : { domain: api.pigsty  ,endpoint: "127.0.0.1:8884" }
      pgadmin      : { domain: adm.pigsty  ,endpoint: "127.0.0.1:8885" }
      pgweb        : { domain: cli.pigsty  ,endpoint: "127.0.0.1:8886" }
      bytebase     : { domain: ddl.pigsty  ,endpoint: "127.0.0.1:8887" }
      jupyter      : { domain: lab.pigsty  ,endpoint: "127.0.0.1:8888", websocket: true }
      gitea        : { domain: git.pigsty  ,endpoint: "127.0.0.1:8889" }
      wiki         : { domain: wiki.pigsty ,endpoint: "127.0.0.1:9002" }
      noco         : { domain: noco.pigsty ,endpoint: "127.0.0.1:9003" }
      supa         : { domain: supa.pigsty ,endpoint: "10.10.10.10:8000", websocket: true }
      dify         : { domain: dify.pigsty ,endpoint: "10.10.10.10:8001", websocket: true }
      odoo         : { domain: odoo.pigsty, endpoint: "127.0.0.1:8069"  , websocket: true }
    nginx_navbar:                    # application nav links on home page
      - { name: PgAdmin4   , url : 'http://adm.pigsty'  , comment: 'PgAdmin4 for PostgreSQL'  }
      - { name: PGWeb      , url : 'http://cli.pigsty'  , comment: 'PGWEB Browser Client'     }
      - { name: ByteBase   , url : 'http://ddl.pigsty'  , comment: 'ByteBase Schema Migrator' }
      - { name: PostgREST  , url : 'http://api.pigsty'  , comment: 'Kong API Gateway'         }
      - { name: Gitea      , url : 'http://git.pigsty'  , comment: 'Gitea Git Service'        }
      - { name: Minio      , url : 'https://m.pigsty'   , comment: 'Minio Object Storage'     }
      - { name: Wiki       , url : 'http://wiki.pigsty' , comment: 'Local Wikipedia'          }
      - { name: Noco       , url : 'http://noco.pigsty' , comment: 'Nocodb Example'           }
      - { name: Odoo       , url : 'http://odoo.pigsty' , comment: 'Odoo - the OpenERP'       }
      - { name: Explain    , url : '/pigsty/pev.html'   , comment: 'pgsql explain visualizer' }
      - { name: Package    , url : '/pigsty'            , comment: 'local yum repo packages'  }
      - { name: PG Logs    , url : '/logs'              , comment: 'postgres raw csv logs'    }
      - { name: Schemas    , url : '/schema'            , comment: 'schemaspy summary report' }
      - { name: Reports    , url : '/report'            , comment: 'pgbadger summary report'  }

    node_etc_hosts: [ "${admin_ip} sss.pigsty" ]
    dns_records: [ "${admin_ip} api.pigsty adm.pigsty cli.pigsty ddl.pigsty lab.pigsty git.pigsty wiki.pigsty noco.pigsty supa.pigsty dify.pigsty odoo.pigsty" ]
    pgbackrest_method: minio          # use minio as backup repo instead of 'local'

    # download docker and pg17 extensions
    repo_modules: infra,node,pgsql,docker
    repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility, docker ]
    repo_extra_packages: [            # download kernel & all available extensions
      pgsql-main,                     # lots of extensions are not available on aarch64 yet, so comment the line below on aarch64
      pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl
    ]

Caveat

Beware that not all extension plugins are available on the aarch64 (arm64) architecture. Therefore, exercise caution when adding the extensions you need when using the ARM architecture.

To replace extensions, refer to the extension alias list: https://ext.pigsty.io and replace a series of wildcard packages such as pg17-core,pg17-time,....


Last modified 2024-11-22: add en conf info (5e78f9b3)