1-node: supa

Run self-hosting single node supabase
Categories:
Tags:

The meta configuration template is Pigsty’s default template, designed to fulfill Pigsty’s core functionality—deploying PostgreSQL—on a single node.

To maximize compatibility, meta installs only the minimum required software set to ensure it runs across all operating system distributions and architectures.

Overview

  • Conf Name: meta
  • Node Count: 1-node, pigsty/vagrant/spec/meta.rb
  • Description: Default single-node installation template with extensive configuration parameter descriptions.
  • OS Distro: el8, el9, d12, u22, u24
  • OS Arch: x86_64, aarch64
  • Related: rich，pitr，demo
  • Vagrant：单节点

Usage: This is the default config template, so there’s not need to specify -c meta explicitly during configure:

./configure [-i <primary_ip>]

Content

Source: pigsty/conf/meta.yml


all:

  #==============================================================#
  # Clusters, Nodes, and Modules
  #==============================================================#
  children:

    #----------------------------------#
    # infra: monitor, alert, repo, etc..
    #----------------------------------#
    infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }

    #----------------------------------#
    # etcd cluster for HA postgres DCS
    #----------------------------------#
    etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }

    #----------------------------------#
    # minio (OPTIONAL backup repo)
    #----------------------------------#
    #minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }

    #----------------------------------#
    # pgsql (singleton on current node)
    #----------------------------------#
    # this is an example single-node postgres cluster with postgis & timescaledb installed, with one biz database & two biz users
    pg-meta:
      hosts:
        10.10.10.10: { pg_seq: 1, pg_role: primary } # <---- primary instance with read-write capability
        #x.xx.xx.xx: { pg_seq: 2, pg_role: replica } # <---- read only replica for read-only online traffic
        #x.xx.xx.xy: { pg_seq: 3, pg_role: offline } # <---- offline instance of ETL & interactive queries
      vars:
        pg_cluster: pg-meta                 # required identity parameter, usually same as group name

        # define business databases here: https://pigsty.io/docs/pgsql/db/
        pg_databases:                       # define business databases on this cluster, array of database definition
          - name: meta                      # REQUIRED, `name` is the only mandatory field of a database definition
            baseline: cmdb.sql              # optional, database sql baseline path, (relative path among ansible search path, e.g: files/)
            schemas: [ pigsty ]             # optional, additional schemas to be created, array of schema names
            extensions:                     # optional, additional extensions to be installed: array of `{name[,schema]}`
              - { name: vector }            # install pgvector extension on this database by default
            comment: pigsty meta database   # optional, comment string for this database
            #pgbouncer: true                # optional, add this database to pgbouncer database list? true by default
            #owner: postgres                # optional, database owner, postgres by default
            #template: template1            # optional, which template to use, template1 by 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 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 with 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 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 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 }  # define another database

        # define business users here: https://pigsty.io/docs/pgsql/user/
        pg_users:                           # define business users/roles on this cluster, array of user definition
          - 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 default
            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 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 expired (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}
            parameters: {}                  # 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
          - { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly], comment: read-only viewer for meta database }

        # define pg extensions: https://pigsty.io/docs/pgext/ , and available alias: https://ext.pigsty.io/#/list
        pg_libs: 'pg_stat_statements, auto_explain' # add timescaledb to shared_preload_libraries
        pg_extensions: [ pgvector ]

        # define HBA rules here: https://pigsty.io/docs/pgsql/hba/#define-hba
        pg_hba_rules:                       # example hba rules
          - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}

        #pg_vip_enabled: true               # define a L2 VIP which bind to cluster primary instance
        #pg_vip_address: 10.10.10.2/24      # L2 VIP Address and netmask
        #pg_vip_interface: eth1             # L2 VIP Network interface, overwrite on host vars if member have different network interface names
        node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    #----------------------------------#
    # example cluster (3-node pg-test)
    #----------------------------------#
    #pg-test:                          # define the new 3-node cluster pg-test
    #  hosts:
    #    10.10.10.11: { pg_seq: 1, pg_role: primary }   # primary instance, leader of cluster
    #    10.10.10.12: { pg_seq: 2, pg_role: replica }   # replica instance, follower of leader
    #    10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
    #  vars:
    #    pg_cluster: pg-test            # define pgsql cluster name
    #    pg_users:  [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
    #    pg_databases: [{ name: test }] # create a database and user named 'test'
    #    node_tune: tiny
    #    pg_conf: tiny.yml
    #    pg_vip_enabled: true
    #    pg_vip_address: 10.10.10.3/24
    #    pg_vip_interface: eth1
    #    node_crontab:  # make a full backup on monday 1am, and an incremental backup during weekdays
    #      - '00 01 * * 1 postgres /pg/bin/pg-backup full'
    #      - '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'


  #==============================================================#
  # Global Parameters
  #==============================================================#
  vars:

    #----------------------------------#
    # Meta Data
    #----------------------------------#
    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: tiny                   # node tuning specs: oltp,olap,tiny,crit
    pg_conf: tiny.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:pass@proxy.xxx.com
      # https_proxy: # set your proxy here: e.g http://user:pass@proxy.xxx.com
      # all_proxy:   # set your proxy here: e.g http://user:pass@proxy.xxx.com
    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: sss.pigsty  ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }

    #----------------------------------#
    # MinIO Related Options
    #----------------------------------#
    #pgbackrest_method: minio          # if you want to use minio as backup repo instead of 'local' fs, uncomment this
    #minio_users:                      # and configure `pgbackrest_repo` & `minio_users` accordingly
    #  - { access_key: dba , secret_key: S3User.DBA, policy: consoleAdmin }
    #  - { access_key: pgbackrest , secret_key: S3User.Backup, policy: readwrite }
    #pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
    #  minio: ...                      # optional minio repo for pgbackrest ...
    #    s3_key: pgbackrest            # minio user access key for pgbackrest
    #    s3_key_secret: S3User.Backup  # minio user secret key for pgbackrest
    #    cipher_pass: pgBackRest       # AES encryption password, default is 'pgBackRest'
    # if you want to use minio as backup repo instead of 'local' fs, uncomment this, and configure `pgbackrest_repo`
    #pgbackrest_method: minio
    #node_etc_hosts: [ '10.10.10.10 h.pigsty a.pigsty p.pigsty g.pigsty sss.pigsty' ]

    #----------------------------------#
    # Credential: CHANGE THESE PASSWORDS
    #----------------------------------#
    #grafana_admin_username: admin
    grafana_admin_password: pigsty
    #pg_admin_username: dbuser_dba
    pg_admin_password: DBUser.DBA
    #pg_monitor_username: dbuser_monitor
    pg_monitor_password: DBUser.Monitor
    #pg_replication_username: replicator
    pg_replication_password: DBUser.Replicator
    #patroni_username: postgres
    patroni_password: Patroni.API
    #haproxy_admin_username: admin
    haproxy_admin_password: pigsty

    #----------------------------------#
    # Safe Guard
    #----------------------------------#
    # you can enable these flags after bootstrap, to prevent purging running etcd / pgsql instances
    etcd_safeguard: false             # prevent purging running etcd instance?
    pg_safeguard: false               # prevent purging running postgres instance? false by default

    #----------------------------------#
    # Repo, Node, Packages
    #----------------------------------#
    # if you wish to customize your own repo, change these settings:
    repo_modules: infra,node,pgsql    # install upstream repo during repo bootstrap
    repo_remove: true                 # remove existing repo on admin node during repo bootstrap
    node_repo_modules: local          # install the local module in repo_upstream for all nodes
    node_repo_remove: true            # remove existing node repo for node managed by pigsty
    repo_packages: [                  # default packages to be downloaded (if `repo_packages` is not explicitly set)
      node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-common #,docker
    ]
    repo_extra_packages: [            # default postgres packages to be downloaded
      pgsql-main
      #,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,
      #,pg17-core,pg17-time,pg17-gis,pg17-rag,pg17-fts,pg17-olap,pg17-feat,pg17-lang,pg17-type,pg17-func,pg17-admin,pg17-stat,pg17-sec,pg17-fdw,pg17-sim,pg17-etl,
      #,pg16-core,pg16-time,pg16-gis,pg16-rag,pg16-fts,pg16-olap,pg16-feat,pg16-lang,pg16-type,pg16-func,pg16-admin,pg16-stat,pg16-sec,pg16-fdw,pg16-sim,pg16-etl,
    ]

Caveats

To maintain compatibility across operating systems and architectures, the meta template installs only the minimum required software. This is reflected in the choices for repo_packages and repo_extra_packages:

  • Docker is not downloaded by default.
  • Only essential PostgreSQL extensions—pg_repack, wal2json, and pgvector—are downloaded by default.
  • Tools classified under pgsql-utility but not part of pgsql-common—such as pg_activity, pg_timetable, pgFormatter, pg_filedump, pgxnclient, timescaledb-tools, pgcopydb, and pgloader—are not downloaded.