Skip to content
Docs Extensions Blog Pricing 中文 GitHub

This is the multi-page printable view of this section. .

Return to the regular view of this page.

Applications

Pigsty application templates and data applets: run stateless apps with Docker Compose and host state in external PostgreSQL and S3-compatible storage.

Pigsty “applications” fall into two categories:

  • Software Templates: Docker Compose templates under ~/pigsty/app/<name> for stateless business components.
  • Data Applets: PostgreSQL + Grafana analytics demos, mainly for learning and showcase use.

Application Model

The recommended application deployment workflow is:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c <template>     # e.g. app/dify, app/immich, app/jumpserver, app/maybe, supabase
vi pigsty.yml                 # edit passwords, domains, IPs, and secrets
./deploy.yml                  # deploy infrastructure and databases
./docker.yml                  # install Docker
./app.yml                     # launch applications

app.yml copies app/<name> templates to /opt/<name>, overwrites .env with apps.<name>.conf, then runs docker compose up -d.

Maintained Config Templates

The following app config templates are actively maintained (conf/app/*.yml, conf/supabase.yml, and the conf/app/supa.yml symlink):

  • app/dify
  • app/odoo
  • app/teable
  • app/mattermost
  • app/electric
  • app/maybe
  • app/immich
  • app/jumpserver
  • app/registry
  • app/insforge
  • app/hindsight
  • supabase

These templates work out of the box and align with the ./configure -c ... + ./app.yml workflow.

Lightweight Compose Apps

For apps like bytebase, gitea, jupyter, kong, metabase, minio, nocodb, pgadmin, pgweb, postgrest, pg_exporter, and wiki, you can also use the per-app Compose templates directly.

FerretDB is provided as the Docker APP layer of PostgreSQL Mongo mode. Deploy it from the mongo configuration template with docker.yml and app.yml.

cd ~/pigsty/app/<name>
make up

If you want to manage them uniformly via Pigsty IaC:

./app.yml -e app=<name>

Legacy Applets

Data applets like pglog, covid, db-engine, sf-survey, cloud, and isd are kept as reference examples for data modeling and visualization ideas.

They are no longer the primary application delivery path. Prefer the software template workflow above.

1 - Enterprise Self-Hosted Supabase

Self-host enterprise-grade Supabase with Pigsty, featuring monitoring, high availability, PITR, IaC, and 575 PostgreSQL extensions.

Supabase is great, but having your own Supabase is even better. Pigsty can help you deploy enterprise-grade Supabase on your own servers (physical, virtual, or cloud) with a single command — more extensions, better performance, deeper control, and more cost-effective.

As of August 2026, the official Supabase self-hosting guide recommends Docker and classifies other implementations as community projects. Pigsty is an independent community integration and is not currently listed on that page.

This tutorial requires basic Linux knowledge. Otherwise, consider using Supabase cloud or plain Docker Compose self-hosting.

TL;DR

Prepare a Linux server, follow the Pigsty standard single-node installation process with the supabase config template:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./configure -c supabase    # Use supabase config (change credentials in pigsty.yml)
vi pigsty.yml              # Edit domain, passwords, keys...
./deploy.yml               # Standard single-node Pigsty deployment
./docker.yml -l supabase   # Install Docker on the supabase group
./app.yml -l supabase      # Start stateless containers on the supabase group (may be slow)

After installation, access Supa Studio on port 8000 with username supabase and password pigsty.

Supabase
demo/supabase.cast

Checklist

Table of Contents

What is Supabase?

Supabase is a BaaS (Backend as a Service), an open-source Firebase alternative, and the most popular database + backend solution in the AI Agent era. Supabase wraps PostgreSQL and provides authentication, messaging, edge functions, object storage, and automatically generates REST APIs based on your database schema. After enabling pg_graphql on demand, it can also provide GraphQL APIs.

Supabase aims to provide developers with a one-stop backend solution, reducing the complexity of developing and maintaining backend infrastructure. It allows developers to skip most backend development work — you only need to understand database design and frontend to ship quickly! Developers can use vibe coding to create a frontend and database schema to rapidly build complete applications.

Supabase is one of the most popular open-source projects in the PostgreSQL ecosystem. As of August 2026, its GitHub repository has more than 100,000 stars. Supabase also offers a free hosted tier. The current Free plan includes shared CPU, 500 MB of RAM, a 500 MB database quota, and 1 GB of file storage; consult the official page for later changes.

Why Self-Host?

If Supabase cloud is so attractive, why self-host?

The most obvious reason is what we discussed in “Is Cloud Database an IQ Tax?”: costs can rise quickly once data, compute, or availability requirements outgrow a managed-service tier. And nowadays, reliable local enterprise NVMe SSDs have three to four orders of magnitude cost advantage over cloud storage, and self-hosting can better leverage this.

Another important reason is functionality — Supabase cloud features are limited. Many powerful PostgreSQL extensions aren’t available in cloud services due to multi-tenant security challenges and licensing. Despite extensions being a core PostgreSQL feature, Supabase currently promises only more than 50 preconfigured extensions; the exact list changes with platform releases. Self-hosted Supabase with Pigsty provides up to 576 ready-to-use PostgreSQL extensions.

Additionally, self-control and vendor lock-in avoidance are important reasons for self-hosting. Although Supabase aims to provide a vendor-lock-free open-source Google Firebase alternative, self-hosting enterprise-grade Supabase is not trivial. Supabase includes PostgreSQL extensions that it develops and maintains. It acquired the Oriole team in 2024 and offers OrioleDB as an optional Public Alpha; OrioleDB is not the production default and should not be described as a confirmed replacement for native PostgreSQL. Some Supabase extensions and patched kernels are not distributed by the official PGDG repository.

This is implicit vendor lock-in, preventing users from self-hosting in ways other than the supabase/postgres Docker image. Pigsty provides an open, transparent, and universal solution. We package the 10 missing Supabase extensions as ready-to-use RPM/DEB packages for the current supabase template matrix: EL 8/9, Debian 12, and Ubuntu 22.04/24.04/26.04 on x86_64 and aarch64. See supported Linux distributions.

ExtensionDescription
pg_graphqlGraphQL support in PostgreSQL (Rust), provided by PIGSTY, enabled on demand
pg_jsonschemaJSON Schema validation (Rust), provided by PIGSTY
wrappersSupabase foreign data wrapper bundle (Rust), provided by PIGSTY
index_advisorQuery index advisor (SQL), provided by PIGSTY
pg_netAsync non-blocking HTTP/HTTPS requests (C), provided by PIGSTY
vaultStore encrypted credentials in Vault (C), provided by PIGSTY
pgjwtJSON Web Token API implementation (SQL), provided by PIGSTY
pgsodiumTable data encryption TDE, provided by PIGSTY
supautilsSecurity utilities for cloud environments (C), provided by PIGSTY
pg_plan_filterFilter queries by execution plan cost (C), provided by PIGSTY

We also install most extensions by default in Supabase deployments. You can enable them as needed. Newer templates install the pg_graphql package but no longer create the pg_graphql extension object by default. If you need GraphQL APIs, run CREATE EXTENSION IF NOT EXISTS pg_graphql; in the target database; the event trigger in the template will rebuild the graphql_public.graphql entry point and permissions.

Pigsty also handles the underlying highly available PostgreSQL cluster, highly available Silo object storage cluster, and even Docker deployment, Nginx reverse proxy, domain configuration, and HTTPS certificate issuance. You can spin up any number of stateless Supabase container clusters using Docker Compose and store state in external Pigsty-managed database services.

With this self-hosted architecture, you can choose among the PostgreSQL majors supported by the current template (15-18, default 18), install 576 extensions, and scale Supabase, PostgreSQL, and Silo independently. Compared with a managed service, you also assume responsibility for operating and securing that infrastructure.

Single-Node Quick Start

Let’s start with single-node Supabase deployment. We’ll cover multi-node high availability later.

Prepare a fresh Linux server, use the Pigsty supabase configuration template for standard installation, then run docker.yml and app.yml to start stateless Supabase containers (default ports 8000/8443).

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./configure -c supabase    # Use supabase config (change credentials in pigsty.yml)
vi pigsty.yml              # Edit domain, passwords, keys...
./deploy.yml               # Install Pigsty
./docker.yml -l supabase   # Install Docker on the supabase group
./app.yml -l supabase      # Start stateless containers on the supabase group

Before deploying Supabase, modify the auto-generated pigsty.yml configuration file (domain and passwords) according to your needs. For local development/testing, you can skip this and customize later.

If configured correctly, after about ten minutes, you can access the Supabase Studio GUI at http://<your_ip_address>:8000 on your local network. Default username and password are supabase and pigsty.

Supabase

Notes:

  • In mainland China, Pigsty uses 1Panel and 1ms DockerHub mirrors by default, which may be slow.
  • You can configure your own proxy and registry mirror, then manually pull images with cd /opt/supabase; docker compose pull. We also offer expert consulting services including complete offline installation packages.
  • If you need object storage functionality, you must access Supabase via domain and HTTPS, otherwise errors will occur.
  • For serious production deployments, always change all default passwords!

Key Technical Decisions

Here are some key technical decisions for self-hosting Supabase:

Single-node deployment doesn’t provide PostgreSQL/Silo high availability. However, single-node deployment still has significant advantages over the official pure Docker Compose approach: out-of-the-box monitoring, freedom to install extensions, component scaling capabilities, and point-in-time recovery as a safety net.

Pigsty’s Supabase template does not start the upstream Compose db or supavisor containers, and does not use Supabase’s bundled connection pooler. Stateless containers connect directly to the PostgreSQL service managed by Pigsty; the single-node template uses service port 5436 by default, which always routes to the current primary.

Logflare / Analytics in the template no longer writes to postgres._analytics in the application database. Instead, it uses the separate _supabase database and its _analytics schema. This prevents internal scheduling tables such as oban_jobs and oban_peers from being created in the project database’s public schema and triggering Supabase Advisor RLS warnings. LOGFLARE_DB and LOGFLARE_SCHEMA control these locations.

The Query Performance page in Supabase Studio accesses pg_stat_statements with a public, extensions search path. Pigsty keeps the pg_stat_statements extension objects in the monitor schema for compatibility with pg_exporter and existing monitoring dashboards. The template creates a compatibility view and functions in the extensions schema for Studio.

If you only have one server or choose to self-host on cloud servers, Pigsty recommends using external S3 instead of local Silo for object storage to hold PostgreSQL backups and Supabase Storage. This provides an hour-scale recovery path after a single-node failure. Actual RPO depends on the newest recoverable backup, WAL archiving state, and object-storage availability; the topology alone does not guarantee a fixed value.

For serious production deployments, Pigsty recommends at least 3-4 nodes, ensuring both Silo and PostgreSQL use enterprise-grade multi-node high availability deployments. You’ll need more nodes and disks, adjusting cluster configuration in pigsty.yml and Supabase cluster configuration to use high availability endpoints.

Some Supabase features require sending emails, so SMTP service is needed. Unless purely for internal use, production deployments should use SMTP cloud services. Self-hosted mail servers’ emails are often marked as spam.

If your service is directly exposed to the public internet, we strongly recommend using real domain names and HTTPS certificates via Nginx Portal.

Next, we’ll discuss advanced topics for improving Supabase security, availability, and performance beyond single-node deployment.

Advanced: Security Hardening

Pigsty Components

For serious production deployments, we strongly recommend changing Pigsty component passwords. These defaults are public and well-known — going to production without changing passwords is like running naked:

These are Pigsty component passwords. Strongly recommended to set before installation.

Supabase Keys

Besides Pigsty component passwords, you need to change Supabase keys, including:

Please follow the Supabase tutorial: Securing your services:

  • Generate a JWT_SECRET with at least 40 characters, then use the tutorial tools to issue ANON_KEY and SERVICE_ROLE_KEY JWTs.
  • Use the tutorial tools to generate an ANON_KEY JWT based on JWT_SECRET and expiration time — this is the anonymous user credential.
  • Use the tutorial tools to generate a SERVICE_ROLE_KEY — this is the higher-privilege service role credential.
  • If you use newer opaque API keys or asymmetric JWTs, also generate and fill SUPABASE_PUBLISHABLE_KEY, SUPABASE_SECRET_KEY, JWT_KEYS, JWT_JWKS, and the corresponding asymmetric ANON_KEY / SERVICE_ROLE_KEY.
  • Specify a random string of at least 32 characters for PG_META_CRYPTO_KEY to encrypt Studio UI and meta service interactions.
  • SECRET_KEY_BASE must be at least 64 characters; REALTIME_DB_ENC_KEY must be exactly 16 characters.
  • Generate separate random credentials for S3_PROTOCOL_ACCESS_KEY_ID and S3_PROTOCOL_ACCESS_KEY_SECRET; do not reuse an object-storage administrator password.
  • If using different PostgreSQL business user passwords, modify POSTGRES_PASSWORD accordingly.
  • If your object storage uses different passwords, modify S3_ACCESS_KEY and S3_SECRET_KEY accordingly.
  • If Edge Functions are exposed to untrusted clients, set FUNCTIONS_VERIFY_JWT to true as needed.
  • API_EXTERNAL_URL should now be the external Auth service URL, retaining the /auth/v1 suffix, for example https://supa.pigsty.io/auth/v1; keep SITE_URL and SUPABASE_PUBLIC_URL at the site root URL.
  • The current template defaults PGRST_DB_SCHEMAS to public,graphql_public; the storage schema is used by the Storage API and is no longer exposed through PostgREST by default.

After modifying Supabase credentials, restart Docker Compose to apply:

./app.yml -l supabase -t app_config,app_launch   # Using playbook
cd /opt/supabase; make up            # Manual execution

Advanced: Domain Configuration

If using Supabase locally or on LAN, you can directly connect to Kong’s HTTP port 8000 via IP:Port.

You can use an internal static-resolved domain, but for serious production deployments, we recommend using a real domain + HTTPS to access Supabase. In this case, your server should have a public IP, you should own a domain, use cloud/DNS/CDN provider’s DNS resolution to point to the node’s public IP (optional fallback: local /etc/hosts static resolution).

The simple approach is to batch-replace the placeholder domain (supa.pigsty) with your actual domain, e.g., supa.pigsty.io:

sed -ie 's/supa.pigsty/supa.pigsty.io/g' ~/pigsty/pigsty.yml

If not configured beforehand, reload Nginx and Supabase configuration:

make cert       # Request certbot free HTTPS certificate
./app.yml -l supabase -t app_config,app_launch  # Reload Supabase configuration

The modified configuration should look like:

all:
  vars:
    certbot_sign: true                # Use certbot to sign real certificates
    infra_portal:
      home: { domain: i.pigsty.io }   # Replace with your domain!
      supa:
        domain: supa.pigsty.io        # Replace with your domain!
        endpoint: "10.10.10.10:8000"
        websocket: true
        certbot: supa.pigsty.io       # Certificate name, usually same as domain

  children:
    supabase:
      vars:
        apps:
          supabase:                                         # Supabase app definition
            conf:                                           # Override /opt/supabase/.env
              SITE_URL: https://supa.pigsty.io              # <------- Change to your external domain name
              API_EXTERNAL_URL: https://supa.pigsty.io/auth/v1 # <--- Auth external URL; keep /auth/v1
              SUPABASE_PUBLIC_URL: https://supa.pigsty.io   # <------- Don't forget to set this in infra_portal!

For complete domain/HTTPS configuration, see Certificate Management. You can also use Pigsty’s built-in local static resolution and self-signed HTTPS certificates as fallback.

Advanced: External Object Storage

You can use S3 or S3-compatible services for PostgreSQL backups and Supabase object storage. Here we use Alibaba Cloud OSS as an example.

Pigsty provides a terraform/spec/aliyun-s3.tf template for provisioning a server and OSS bucket on Alibaba Cloud.

First, modify the S3 configuration in all.children.supabase.vars.apps.supabase.conf to point to Alibaba Cloud OSS:

# if using s3/minio as file storage
S3_BUCKET: pigsty-supa                     # Legacy compatibility; keep aligned with GLOBAL_S3_BUCKET
GLOBAL_S3_BUCKET: pigsty-supa              # Bucket actually used by Supabase Storage
S3_ENDPOINT: https://oss-cn-beijing.aliyuncs.com
S3_ACCESS_KEY: <your_access_key>
S3_SECRET_KEY: <your_secret_key>
S3_FORCE_PATH_STYLE: false                 # Alibaba Cloud OSS uses host-style URIs
S3_PROTOCOL: https
S3_REGION: oss-cn-beijing                  # Legacy compatibility; keep aligned with REGION
REGION: oss-cn-beijing                     # Region actually used by Supabase Storage
STORAGE_TENANT_ID: pigsty                  # Supabase Storage tenant id
S3_PROTOCOL_ACCESS_KEY_ID: <independent_access_key>
S3_PROTOCOL_ACCESS_KEY_SECRET: <independent_secret_key>

Reload Supabase configuration:

./app.yml -l supabase -t app_config,app_launch

You can also use S3 as PostgreSQL backup repository. Add an aliyun backup repository definition in all.vars.pgbackrest_repo:

all:
  vars:
    pgbackrest_method: aliyun          # pgbackrest backup method: local,minio,[user-defined repos...]
    pgbackrest_repo:                   # pgbackrest backup repo: https://pgbackrest.org/configuration.html#section-repository
      aliyun:                          # Define new backup repo 'aliyun'
        type: s3                       # Alibaba Cloud OSS is S3-compatible
        s3_endpoint: oss-cn-beijing-internal.aliyuncs.com
        s3_region: oss-cn-beijing
        s3_bucket: pigsty-oss
        s3_key: xxxxxxxxxxxxxx
        s3_key_secret: xxxxxxxx
        s3_uri_style: host
        path: /pgbackrest
        bundle: y                         # bundle small files into a single file
        bundle_limit: 20MiB               # Limit for file bundles, 20MiB for object storage
        bundle_size: 128MiB               # Target size for file bundles, 128MiB for object storage
        cipher_type: aes-256-cbc          # enable AES encryption for remote backup repo
        cipher_pass: pgBackRest.MyPass    # Set encryption password for pgBackRest backup repo
        retention_full_type: time         # retention full backup by time on minio repo
        retention_full: 14                # keep full backup for the last 14 days

Then select aliyun with all.vars.pgbackrest_method. Check the current backup first, run check mode, and only after explicit authorization re-render pgBackRest configuration on the named cluster, initialize the stanza, and establish a new full recovery point:

pig pb info
./pgsql.yml -t pg_backup -l pg-meta
pg-backup full

Backups in the old repository are not migrated automatically, and a recovery-window gap remains until the first full backup in the new repository succeeds. See Switching Repositories for the complete procedure.

Advanced: Using SMTP

You can use SMTP for sending emails. Modify the supabase app configuration with SMTP information:

all:
  children:
    supabase:        # supa group
      vars:          # supa group vars
        apps:        # supa group app list
          supabase:  # the supabase app
            conf:    # the supabase app conf entries
              SMTP_HOST: smtpdm.aliyun.com
              SMTP_PORT: 80
              SMTP_USER: [email protected]
              SMTP_PASS: your_email_user_password
              SMTP_SENDER_NAME: MySupabase
              SMTP_ADMIN_EMAIL: [email protected]
              ENABLE_ANONYMOUS_USERS: false

Reload the configuration with ./app.yml -l supabase -t app_config,app_launch.

Advanced: True High Availability

After these configurations, you have enterprise-grade Supabase with public domain, HTTPS certificate, SMTP, PITR backup, monitoring, IaC, and access to 576 PostgreSQL extensions (basic single-node version). For high availability configuration, see other Pigsty documentation. We offer expert consulting services for hands-on Supabase self-hosting — $400 USD to save you the hassle.

Single-node RTO/RPO relies on external object storage as a safety net. If your node fails, backups in external S3 storage let you redeploy Supabase on a new node and restore from backup. This provides an hour-scale recovery fallback, but RPO depends on the actual backup and WAL-archive state and must be proven through restore drills. “MB-level” is not a fixed guarantee.

For a target RTO below 30 seconds while preserving acknowledged transactions, use multi-node, explicitly select the fast RTO preset and the crit.yml strict-synchronous policy, and validate them with failure drills. The default norm preset targets RTO below 45 seconds, while asynchronous replication does not promise RPO=0:

  • ETCD: DCS needs three or more nodes to tolerate one node failure.
  • PGSQL: PostgreSQL synchronous commit (no data loss) mode recommends at least three nodes.
  • INFRA: Monitoring infrastructure failure has less impact; production recommends dual replicas.
  • Supabase stateless containers can also be multi-node replicas for high availability.

In this case, you also need to modify PostgreSQL and Silo endpoints to use DNS / L2 VIP / HAProxy high availability endpoints. For these parts, follow the documentation for each Pigsty module. Reference conf/ha/trio.yml and conf/ha/safe.yml for upgrading to three or more nodes.

2 - Odoo: Self-Hosted Open Source ERP

How to spin up an out-of-the-box enterprise application suite Odoo and use Pigsty to manage its backend PostgreSQL database.

Odoo is an open-source enterprise resource planning (ERP) software that provides a full suite of business applications, including CRM, sales, purchasing, inventory, production, accounting, and other management functions. Odoo is a typical web application that uses PostgreSQL as its underlying database.

All your business on one platform — Simple, efficient, yet affordable

Public Demo (may not always be available): http://odoo.pigsty.io, username: [email protected], password: pigsty

Quick Start

On a fresh Linux x86/ARM server running a compatible operating system:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap                # Install Ansible
./configure -c app/odoo    # Use Odoo configuration (change credentials in pigsty.yml)
./deploy.yml               # Install Pigsty
./docker.yml               # Install Docker Compose
./app.yml                  # Start Odoo stateless components with Docker

Odoo listens on port 8069 by default. Access http://<ip>:8069 in your browser. The default username and password are both admin.

You can add a DNS resolution record odoo.pigsty pointing to your server in the browser host’s /etc/hosts file, allowing you to access the Odoo web interface via http://odoo.pigsty.

If you want to access Odoo via SSL/HTTPS, you need to use a real SSL certificate or trust the self-signed CA certificate automatically generated by Pigsty. (In Chrome, you can also type thisisunsafe to bypass certificate verification)

Configuration Template

conf/app/odoo.yml defines a template configuration file containing the resources required for a single Odoo instance.

all:
  children:

    # Odoo application (default username and password: admin/admin)
    odoo:
      hosts: { 10.10.10.10: {} }
      vars:
        app: odoo   # Specify app name to install (in apps)
        apps:       # Define all applications
          odoo:     # App name, should have corresponding ~/pigsty/app/odoo folder
            file:   # Optional directories to create
              - { path: /data/odoo         ,state: directory, owner: 100, group: 101 }
              - { path: /data/odoo/webdata ,state: directory, owner: 100, group: 101 }
              - { path: /data/odoo/addons  ,state: directory, owner: 100, group: 101 }
            conf:   # Override /opt/<app>/.env config file
              PG_HOST: 10.10.10.10            # PostgreSQL host
              PG_PORT: 5432                   # PostgreSQL port
              PG_USERNAME: odoo               # PostgreSQL user
              PG_PASSWORD: DBUser.Odoo        # PostgreSQL password
              ODOO_PORT: 8069                 # Odoo app port
              ODOO_DATA: /data/odoo/webdata   # Odoo webdata
              ODOO_ADDONS: /data/odoo/addons  # Odoo plugins
              ODOO_DBNAME: odoo               # Odoo database name
              ODOO_VERSION: 19.0              # Odoo image version

    # Odoo database
    pg-odoo:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-odoo
        pg_users:
          - { name: odoo    ,password: DBUser.Odoo ,pgbouncer: true ,roles: [ dbrole_admin ] ,createdb: true ,comment: admin user for odoo service }
          - { name: odoo_ro ,password: DBUser.Odoo ,pgbouncer: true ,roles: [ dbrole_readonly ]  ,comment: read only user for odoo service  }
          - { name: odoo_rw ,password: DBUser.Odoo ,pgbouncer: true ,roles: [ dbrole_readwrite ] ,comment: read write user for odoo service }
        pg_databases:
          - { name: odoo ,owner: odoo ,revokeconn: true ,comment: odoo main database  }
        pg_hba_rules:
          - { user: all ,db: all ,addr: 172.17.0.0/16  ,auth: pwd ,title: 'allow access from local docker network' }
          - { user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes' }
        pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ] # Full backup daily at 1am

    infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
    etcd:  { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
    #minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }

  vars:                               # Global variables
    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

    docker_enabled: true              # Enable docker on app group
    #docker_registry_mirrors: ["https://docker.1panel.live","https://docker.1ms.run","https://docker.xuanyuan.me","https://registry-1.docker.io"]

    proxy_env:                        # Global proxy env for downloading packages & pulling docker images
      no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.*,*.tsinghua.edu.cn"
      #http_proxy:  127.0.0.1:12345   # Add proxy env here for downloading packages or pulling images
      #https_proxy: 127.0.0.1:12345   # Usually format is http://user:[email protected]
      #all_proxy:   127.0.0.1:12345

    infra_portal:                      # Domain names and upstream servers
      home  : { domain: i.pigsty }
      minio : { domain: m.pigsty ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
      odoo:                            # Nginx server config for odoo
        domain: odoo.pigsty            # REPLACE WITH YOUR OWN DOMAIN!
        endpoint: "10.10.10.10:8069"   # Odoo service endpoint: IP:PORT
        websocket: true                # Add websocket support
        certbot: odoo.pigsty           # Certbot cert name, apply with `make cert`

    repo_enabled: false
    node_repo_modules: node,infra,pgsql
    pg_version: 18

    #----------------------------------#
    # Credentials: MUST CHANGE THESE!
    #----------------------------------#
    grafana_admin_password: pigsty
    grafana_view_password: DBUser.Viewer
    pg_admin_password: DBUser.DBA
    pg_monitor_password: DBUser.Monitor
    pg_replication_password: DBUser.Replicator
    patroni_password: Patroni.API
    haproxy_admin_password: pigsty
    minio_secret_key: S3User.MinIO
    etcd_root_password: Etcd.Root

Basics

Check the configurable environment variables in the .env file:

# https://hub.docker.com/_/odoo#
PG_HOST=10.10.10.10
PG_PORT=5432
PG_USER=dbuser_odoo
PG_PASS=DBUser.Odoo
ODOO_PORT=8069

Then start Odoo with:

make up  # docker compose up

Access http://odoo.pigsty or http://10.10.10.10:8069

Makefile

make up         # Start Odoo with docker compose in minimal mode
make run        # Start Odoo with docker, local data directory and external PostgreSQL
make view       # Print Odoo access endpoints
make log        # tail -f Odoo logs
make info       # Inspect Odoo with jq
make stop       # Stop Odoo container
make clean      # Remove Odoo container
make pull       # Pull latest Odoo image
make rmi        # Remove Odoo image
make save       # Save Odoo image to /tmp/docker/odoo.tgz
make load       # Load Odoo image from /tmp/docker/odoo.tgz

Using External PostgreSQL

You can use external PostgreSQL for Odoo. Odoo will create its own database during setup, so you don’t need to do that.

pg_users: [ { name: dbuser_odoo ,password: DBUser.Odoo ,pgbouncer: true ,roles: [ dbrole_admin ]    ,comment: admin user for odoo database } ]
pg_databases: [ { name: odoo ,owner: dbuser_odoo ,revokeconn: true ,comment: odoo primary database } ]

Create the business user and database with:

bin/pgsql-user  pg-meta  dbuser_odoo
#bin/pgsql-db    pg-meta  odoo     # Odoo will create the database during setup

Check connectivity:

psql postgres://dbuser_odoo:[email protected]:5432/odoo

Expose Odoo Service

Expose the Odoo web service via Nginx portal:

    infra_portal:                     # Domain names and upstream servers
      home         : { domain: i.pigsty }
      odoo         : { domain: odoo.pigsty, endpoint: "127.0.0.1:8069", websocket: true }  # <------ Add this line
./infra.yml -t nginx   # Setup nginx infra portal

Odoo Addons

There are many Odoo modules available in the community. You can install them by downloading and placing them in the addons folder.

volumes:
  - ./addons:/mnt/extra-addons

You can mount the ./addons directory to /mnt/extra-addons in the container, then download and extract addons to the addons folder.

To enable addon modules, first enter Developer mode:

Settings -> General Settings -> Developer Tools -> Activate the developer mode

Then go to Apps -> Update Apps List, and you’ll find the extra addons available to install from the panel.

Frequently used free addons: Accounting Kit

Demo

Check the public demo: http://odoo.pigsty.io, username: [email protected], password: pigsty

If you want to access Odoo via SSL, you must trust files/pki/ca/ca.crt in your browser (or use the dirty hack thisisunsafe in Chrome).

3 - Dify: AI Workflow Platform

How to self-host the AI Workflow LLMOps platform — Dify, using external PostgreSQL, PGVector, and Redis for storage with Pigsty?

Dify is a Generative AI Application Innovation Engine and open-source LLM application development platform. It provides capabilities from Agent building to AI workflow orchestration, RAG retrieval, and model management, helping users easily build and operate generative AI native applications.

Pigsty provides support for self-hosted Dify, allowing you to deploy Dify with a single command while storing critical state in externally managed PostgreSQL. You can use pgvector as a vector database in the same PostgreSQL instance, further simplifying deployment.

app/dify template latest verified Dify version: v1.15.0 (2026-07-09). The template includes a Dify migration compatibility patch for PostgreSQL 18’s built-in uuidv7().

Quick Start

On a fresh Linux x86/ARM server running a compatible operating system:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap                # Install Pigsty dependencies
./configure -c app/dify    # Use Dify configuration template
vi pigsty.yml              # Edit passwords, domains, keys, etc.

./deploy.yml               # Install Pigsty
./docker.yml               # Install Docker and Compose
./app.yml                  # Install Dify

Dify listens on port 5001 by default. Access http://<ip>:5001 in your browser and set up your initial user credentials to log in.

Once Dify starts, you can install various extensions, configure system models, and start using it!

Why Self-Host

There are many reasons to self-host Dify, but the primary motivation is data security. The Docker Compose template provided by Dify uses basic default database images, lacking enterprise features like high availability, disaster recovery, monitoring, IaC, and PITR capabilities.

Pigsty provides declarative Dify deployment and can use mirrors to address image access in China. The template puts PostgreSQL and pgvector under Pigsty management and deploys Compose Redis, VictoriaMetrics/Grafana monitoring, and an Nginx reverse proxy. It can request a Let’s Encrypt certificate after public DNS, ports, and Certbot are configured. Files are stored in DIFY_DATA (/data/dify) by default, with optional Silo/S3 object storage.

The current template places PostgreSQL/pgvector in an externally managed Pigsty database and directs API files and plugin data to DIFY_DATA (/data/dify by default). However, the built-in Compose Redis data remains under /opt/dify/volumes/redis/data, while Sandbox dependencies and Certbot data are also stored under /opt/dify/volumes/. The complete application stack is therefore not fully stateless, and a backup cannot retain only the database.

Installation

Let’s start with single-node Dify deployment. We’ll cover production high-availability deployment methods later.

First, use Pigsty’s standard installation process to install the PostgreSQL instance required by Dify:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap               # Prepare Pigsty dependencies
./configure -c app/dify   # Use Dify application template
vi pigsty.yml             # Edit configuration file, modify domains and passwords
./deploy.yml              # Install Pigsty and various databases

When you use the ./configure -c app/dify command, Pigsty automatically generates a configuration file based on the conf/app/dify.yml template and your current environment. You should modify passwords, domains, and other relevant parameters in the generated pigsty.yml configuration file according to your needs, then run ./deploy.yml to execute the standard installation process.

Next, run docker.yml to install Docker and Docker Compose, then use app.yml to complete Dify deployment:

./docker.yml -l dify      # Install Docker and Docker Compose on Dify nodes
./app.yml -l dify         # Deploy Dify application components with Docker

You can access the Dify Web admin interface at http://<your_ip_address>:5001 on your local network.

The first login will prompt you to set up default username, email, and password.

You can also use the locally resolved placeholder domain dify.pigsty, or follow the configuration below to use a real domain with an HTTPS certificate.

Configuration

When you run ./configure -c app/dify, Pigsty generates a configuration file from the conf/app/dify.yml template and the current environment. The snapshot below matches the v4.5.0 source template:

---
#==============================================================#
# File      :   dify.yml
# Desc      :   pigsty config for running 1-node dify app
# Ctime     :   2025-02-24
# Mtime     :   2026-07-09
# Docs      :   https://pigsty.io/docs/app/dify
# License   :   Apache-2.0 @ https://pigsty.io/docs/about/license/
# Copyright :   2018-2026  Ruohang Feng / Vonng ([email protected])
#==============================================================#
# Last Verified Dify Version: v1.15.0 on 2026-07-09
# tutorial: https://pigsty.io/docs/app/dify
# how to use this template:
#
#  curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
# ./bootstrap               # prepare local repo & ansible
# ./configure -c app/dify   # use this dify config template
# vi pigsty.yml             # IMPORTANT: CHANGE CREDENTIALS!!
# ./deploy.yml              # install pigsty & pgsql
# ./docker.yml              # install docker & docker-compose
# ./app.yml                 # install dify with docker-compose
#
# To replace domain name:
#   sed -ie 's/dify.pigsty/dify.pigsty.cc/g' pigsty.yml


all:
  children:

    # the dify application
    dify:
      hosts: { 10.10.10.10: {} }
      vars:
        app: dify   # specify app name to be installed (in the apps)
        apps:       # define all applications
          dify:     # app name, should have corresponding ~/pigsty/app/dify folder
            file:   # data directory to be created
              - { path: /data/dify ,state: directory ,mode: 0755 }
            conf:   # override /opt/dify/.env config file

              # change domain, mirror, proxy, secret key
              NGINX_SERVER_NAME: dify.pigsty
              # A secret key for signing and encryption, gen with `openssl rand -base64 42` (CHANGE PASSWORD!)
              SECRET_KEY: sk-somerandomkey
              # expose DIFY nginx service with port 5001 by default
              DIFY_PORT: 5001
              # where to store dify files? the default is ./volume, we'll use another volume created above
              DIFY_DATA: /data/dify
              # enable the upstream websocket sidecar, while keeping PostgreSQL/pgvector external
              COMPOSE_PROFILES: collaboration
              NEXT_PUBLIC_SOCKET_URL: ws://dify.pigsty
              TRIGGER_URL: http://dify.pigsty
              ENDPOINT_URL_TEMPLATE: http://dify.pigsty/e/{hook_id}

              # proxy and mirror settings
              #PIP_MIRROR_URL: https://pypi.tuna.tsinghua.edu.cn/simple
              #SANDBOX_HTTP_PROXY: http://10.10.10.10:12345
              #SANDBOX_HTTPS_PROXY: http://10.10.10.10:12345

              # database credentials
              DB_TYPE: postgresql
              DB_USERNAME: dify
              DB_PASSWORD: difyai123456
              DB_HOST: 10.10.10.10
              DB_PORT: 5432
              DB_DATABASE: dify
              DB_SSL_MODE: disable
              VECTOR_STORE: pgvector
              PGVECTOR_HOST: 10.10.10.10
              PGVECTOR_PORT: 5432
              PGVECTOR_USER: dify
              PGVECTOR_PASSWORD: difyai123456
              PGVECTOR_DATABASE: dify
              PGVECTOR_MIN_CONNECTION: 2
              PGVECTOR_MAX_CONNECTION: 10

              # optional MinIO/S3 storage, disabled by default to avoid touching backup MinIO
              #STORAGE_TYPE: s3
              #S3_ENDPOINT: http://10.10.10.10:9000
              #S3_BUCKET_NAME: dify
              #S3_ACCESS_KEY: dify
              #S3_SECRET_KEY: S3User.Dify
              #S3_REGION: us-east-1
              #S3_ADDRESS_STYLE: path

    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-meta
        pg_extensions: [ pgvector ]
        pg_users:
          - { name: dify ,password: difyai123456 ,pgbouncer: true ,roles: [ dbrole_admin ] ,superuser: true ,comment: dify superuser }
        pg_databases:
          - { name: dify        ,owner: dify ,extensions: [ { name: vector } ] ,comment: dify main database  }
          - { name: dify_plugin ,owner: dify ,comment: dify plugin daemon database }
        pg_hba_rules:
          - { user: dify ,db: all ,addr: 172.16.0.0/12  ,auth: pwd ,title: 'allow dify access from local docker networks' }
        pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ] # make a full backup every 1am

    infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
    etcd:  { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
    #minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }

  vars:                               # global variables
    version: v4.5.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

    docker_enabled: true              # enable docker on app group
    #docker_registry_mirrors: ["https://docker.1panel.live","https://docker.1ms.run","https://docker.xuanyuan.me","https://registry-1.docker.io"]

    proxy_env:                        # global proxy env when downloading packages & pull docker images
      no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.*,*.tsinghua.edu.cn"
      #http_proxy:  127.0.0.1:12345 # add your proxy env here for downloading packages or pull images
      #https_proxy: 127.0.0.1:12345 # usually the proxy is format as http://user:[email protected]
      #all_proxy:   127.0.0.1:12345

    infra_portal:                     # domain names and upstream servers
      home   :  { domain: i.pigsty }
      #minio :  { domain: m.pigsty    ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
      dify:                            # nginx server config for dify
        domain: dify.pigsty            # REPLACE WITH YOUR OWN DOMAIN!
        endpoint: "10.10.10.10:5001"   # dify service endpoint: IP:PORT
        websocket: true                # add websocket support
        certbot: dify.pigsty           # certbot cert name, apply with `make cert`

    repo_enabled: false
    node_repo_modules: node,infra,pgsql
    # Dify v1.15.0 is patched in app/dify/patches for PostgreSQL 18's built-in uuidv7().
    pg_version: 18

    #----------------------------------------------#
    # PASSWORD : https://pigsty.io/docs/setup/security/
    #----------------------------------------------#
    grafana_admin_password: pigsty
    grafana_view_password: DBUser.Viewer
    pg_admin_password: DBUser.DBA
    pg_monitor_password: DBUser.Monitor
    pg_replication_password: DBUser.Replicator
    patroni_password: Patroni.API
    haproxy_admin_password: pigsty
    minio_secret_key: S3User.MinIO
    etcd_root_password: Etcd.Root
...

Checklist

Here’s a checklist of configuration items you need to pay attention to:

  • Hardware/Software: Prepare required machine resources: Linux x86_64/arm64 server, fresh installation of a mainstream Linux OS
  • Network/Permissions: SSH passwordless login access, user with sudo privileges without password
  • Ensure the machine has a static IPv4 network address on the internal network and can access the internet
  • If accessing via public network, ensure you have a domain pointing to the node’s public IP address
  • Ensure you use the app/dify configuration template and modify parameters as needed
    • configure -c app/dify, enter the node’s internal primary IP address, or specify via -i <primary_ip> command line parameter
  • In production, have you changed every example password, application secret, and database credential? [Required]
  • Have you changed the PostgreSQL cluster business user password and application configurations using these passwords?
    • Default username dify and password difyai123456 are generated by Pigsty for Dify; modify according to your needs
    • In the Dify configuration block, modify DB_USERNAME, DB_PASSWORD, PGVECTOR_USER, PGVECTOR_PASSWORD accordingly
  • Have you changed Dify’s default encryption key?
    • You can randomly generate a password string with openssl rand -base64 42 and fill in the SECRET_KEY parameter
  • Have you changed the domain used by Dify?
    • Replace placeholder domain dify.pigsty with your actual domain, e.g., dify.pigsty.io
    • You can use sed -ie 's/dify.pigsty/dify.pigsty.io/g' pigsty.yml to modify Dify’s domain

Domain and SSL

If you want to use a real domain with an HTTPS certificate, you need to modify the pigsty.yml configuration file:

  • The dify domain in the infra_portal parameter
  • It’s best to specify an email address certbot_email for certificate expiration notifications
  • Configure Dify’s NGINX_SERVER_NAME parameter to specify your actual domain
all:
  children:                            # Cluster definitions
    dify:                              # Dify group
      vars:                            # Dify group variables
        apps:                          # Application configuration
          dify:                        # Dify application definition
            conf:                      # Dify application configuration
              NGINX_SERVER_NAME: dify.pigsty

  vars:                                # Global parameters
    #certbot_sign: true                # Use Certbot for free HTTPS certificate
    certbot_email: [email protected]      # Email for certificate requests, for expiration notifications, optional
    infra_portal:                      # Configure Nginx servers
      dify:                            # Dify server definition
        domain: dify.pigsty            # Replace with your own domain here!
        endpoint: "10.10.10.10:5001"   # Specify Dify's IP and port here (auto-configured by default)
        websocket: true                # Dify requires websocket enabled
        certbot: dify.pigsty           # Specify Certbot certificate name

Use the following commands to request Nginx certificates:

# Request and load the certificate on the explicitly limited infra group
./infra.yml -l infra -t nginx_certbot,nginx_reload -e certbot_sign=true

Run the app.yml playbook to redeploy Dify service for the NGINX_SERVER_NAME configuration to take effect:

./app.yml -l dify -t app_config,app_launch

File Backup

You can use restic to back up Dify’s file state. The current template requires at least /data/dify, /opt/dify/.env, and /opt/dify/volumes/; the latter contains Compose Redis, Sandbox dependencies, and any Certbot data. Dify data in PostgreSQL should still be backed up separately with Pigsty/pgBackRest.

export RESTIC_REPOSITORY=/data/backups/dify   # Specify dify backup directory
export RESTIC_PASSWORD=some-strong-password   # Specify backup encryption password
mkdir -p ${RESTIC_REPOSITORY}                 # Create dify backup directory
restic init

After creating the Restic backup repository, you can backup Dify with:

export RESTIC_REPOSITORY=/data/backups/dify   # Specify dify backup directory
export RESTIC_PASSWORD=some-strong-password   # Specify backup encryption password

restic backup /data/dify /opt/dify/.env /opt/dify/volumes
restic snapshots                              # View backup snapshot list
restic restore 0b11f778 --target /tmp/dify-restore  # Restore to a temporary directory first, verify, then copy back
restic check                                  # Periodically check repository integrity

Another option is to place /data/dify on a shared filesystem managed by the JUICE module. File data can live in Silo/S3 or in a PostgreSQL jfs_blob table; the latter is not PostgreSQL large-object storage.

To use PostgreSQL for both JuiceFS metadata and file data, first declare a dedicated database and least-privilege user in pg_databases, then declare the instance on the Dify node. Passwords below are placeholders and must not be used in production:

pg_databases:
  - { name: dify_fs, owner: dify, comment: JuiceFS metadata and data for Dify }

juice_instances:
  dify:
    path: /data/dify
    meta: postgres://dify:<password>@10.10.10.10:5432/dify_fs
    data: --storage postgres --bucket 10.10.10.10:5432/dify_fs --access-key dify --secret-key <password>
    owner: 1001
    group: 1001
    port: 9567

Handle database creation and JUICE deployment separately. After confirming the exact targets, run the playbooks:

./pgsql-db.yml -l pg-meta -e dbname=dify_fs
./juice.yml -l dify -e fsname=dify

Database creation, initial filesystem formatting, and mounting all change the target environment. Confirm the backup, database name, and host group before executing them. Start Dify only after the mount is ready; see JUICE configuration and its PITR consistency boundary. Before mounting over a nonempty /data/dify, stop Dify and plan migration of the existing files.

Reference

Dify Self-Hosting FAQ

4 - InsForge: AI Backend-as-a-Service

Self-host InsForge OSS with Pigsty and let Pigsty manage PostgreSQL, backups, monitoring, and ingress.

InsForge is an open-source Backend-as-a-Service platform for AI coding agents. Built around PostgreSQL, it provides authentication, database APIs, file storage, a model gateway, edge functions, site deployment, payment integrations, and more, allowing applications to skip most backend boilerplate.

Pigsty provides the app/insforge configuration template, which runs InsForge OSS stateless services in Docker Compose while placing the most critical state in PostgreSQL managed by Pigsty. This lets you continue using Pigsty’s high availability, backup and recovery, monitoring, ingress domains, and infrastructure capabilities instead of hiding the database in an ephemeral container volume.

The current template targets InsForge OSS v2.2.6, uses external PostgreSQL, and exposes three services by default: the InsForge Dashboard/API, PostgREST, and Deno Runtime.

Quick Start

Run the following on a fresh Linux x86 / ARM server with a compatible operating system:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap                  # Install Pigsty dependencies
./configure -c app/insforge  # Use the InsForge configuration template
vi pigsty.yml                # Required: keys, admin account, database password, domain

./deploy.yml                 # Install Pigsty, Infra, Etcd, and PostgreSQL
./docker.yml                 # Install Docker and Compose
./app.yml                    # Install InsForge

After installation, the default access URLs are:

  • http://<your_ip_address>:7130
  • http://isf.pigsty

The default administrator account is [email protected] / pigsty. In production, you must change the administrator account, administrator password, JWT secret, encryption key, and database password.

To access InsForge through isf.pigsty, add the following entry to /etc/hosts on the machine running your browser:

10.10.10.10 isf.pigsty

To expose the service to the public internet, use a real domain and HTTPS certificate, and modify infra_portal as described below.

Checklist

  • Prepare a fresh Linux server with at least 2C4G; use an SSD/NVMe disk.
  • Confirm that the server has a static private IPv4 address and can access GHCR / Docker Hub, or that a registry mirror is configured.
  • After running ./configure -c app/insforge, change the default passwords, domains, and keys in pigsty.yml.
  • Generate new JWT_SECRET and ENCRYPTION_KEY values separately with openssl rand -base64 32.
  • Keep POSTGRES_PASSWORD consistent with the password of dbuser_insforge in pg_users.
  • For public access, configure a real domain, an HTTPS certificate, and firewall access rules.
  • Confirm that PostgreSQL extension packages are installed correctly, and run pig pb info after deployment.

Architecture

The InsForge template separates the database from the application containers by default:

                    ┌──────────────────────────────────────────────┐
                    │                 InsForge Stack               │
┌──────────┐       │  ┌──────────┐  ┌──────────┐  ┌───────────┐  │
│  Nginx   │──────▶│  │ InsForge │  │PostgREST │  │   Deno    │  │
│ (Pigsty) │  :7130│  │ App+Web  │  │ REST API │  │  Runtime  │  │
└──────────┘       │  │  :7130   │  │  :5430   │  │   :7133   │  │
                    │  └────┬─────┘  └────┬─────┘  └─────┬─────┘  │
                    │       │             │              │         │
                    └───────┼─────────────┼──────────────┼─────────┘
                            │             │              │
                            ▼             ▼              ▼
                    ┌──────────────────────────────────────────────┐
                    │  PostgreSQL (Pigsty Managed HA Cluster)      │
                    │  Patroni + pgBackRest + pgBouncer + HAProxy  │
                    └──────────────────────────────────────────────┘

Components:

  • InsForge App: ghcr.io/insforge/insforge-oss:v2.2.6, providing the Dashboard and main API on port 7130.
  • PostgREST: postgrest/postgrest:v12.2.12, generating REST APIs from PostgreSQL schemas; exposed on host port 5430.
  • Deno Runtime: ghcr.io/insforge/deno-runtime:latest, the edge function runtime, listening on port 7133.
  • PostgreSQL: Managed by Pigsty for persistent state, backup, monitoring, and high availability.

Configuration Template

conf/app/insforge.yml defines a single-node, self-hosted InsForge template. The default topology includes:

  • insforge: The node running the InsForge, PostgREST, and Deno Runtime containers.
  • pg-meta: The PostgreSQL database cluster managed by Pigsty, including the roles, database, and extensions required by InsForge.
  • infra: Infrastructure services such as Nginx ingress, Grafana, and VictoriaMetrics.
  • etcd: The distributed configuration store required by Patroni.

Key configuration excerpts:

insforge:
  hosts: { 10.10.10.10: {} }
  vars:
    app: insforge
    apps:
      insforge:
        conf:
          JWT_SECRET: your-secret-key-here-must-be-32-char-or-above
          ENCRYPTION_KEY: your-encryption-key-here-must-be-32-char-or-above
          ROOT_ADMIN_USERNAME: [email protected]
          ROOT_ADMIN_PASSWORD: pigsty
          POSTGRES_HOST: 10.10.10.10
          POSTGRES_PORT: 5432
          POSTGRES_DB: insforge
          POSTGRES_USER: dbuser_insforge
          POSTGRES_PASSWORD: DBUser.Insforge

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - { name: dbuser_insforge ,password: DBUser.Insforge ,pgbouncer: true ,roles: [dbrole_admin] ,superuser: true ,comment: 'insforge superuser' }
      - { name: anon            ,login: false ,comment: 'insforge anonymous role for PostgREST' }
      - { name: authenticated   ,login: false ,comment: 'insforge authenticated role' }
      - { name: project_admin   ,login: false ,bypassrls: true ,comment: 'insforge project admin with RLS bypass' }
    pg_databases:
      - name: insforge
        owner: dbuser_insforge
        baseline: insforge.sql
        extensions: [pgcrypto, http, pg_cron]
    pg_libs: 'pg_cron, pg_stat_statements, auto_explain'
    pg_parameters:
      cron.database_name: insforge
      app.encryption_key: your-encryption-key-here-must-be-32-char-or-above
      insforge.policy_grant_role: project_admin
      insforge.internal_schemas: 'ai,auth,compute,deployments,email,functions,memory,payments,realtime,schedules,storage,system'
    pg_extensions: [ pg_cron, pg_http ]
    pg_hba_rules:
      - { user: dbuser_insforge ,db: all ,addr: 172.16.0.0/12 ,auth: pwd ,title: 'allow insforge access from local docker networks' }

Important Parameters

app.yml copies the app/insforge directory to /opt/insforge and uses apps.insforge.conf to override /opt/insforge/.env. Common parameters include:

ParameterDefaultDescription
JWT_SECRETSample secretJWT signing secret; must be replaced in production, preferably with a random value of at least 32 characters
ENCRYPTION_KEYSample secretEncrypts runtime credentials; must be replaced in production and managed separately from JWT_SECRET
ROOT_ADMIN_USERNAME[email protected]Root administrator account
ROOT_ADMIN_PASSWORDpigstyRoot administrator password; must be replaced in production
POSTGRES_HOST / POSTGRES_PORT10.10.10.10 / 5432Pigsty PostgreSQL endpoint
POSTGRES_DBinsforgeInsForge database
POSTGRES_USERdbuser_insforgeApplication user that connects InsForge to PostgreSQL
POSTGRES_PASSWORDDBUser.InsforgeApplication user password; must be replaced in production
INSFORGE_VERSIONv2.2.6InsForge OSS image tag
DENO_RUNTIME_VERSIONlatestDeno Runtime image tag
APP_PORT7130External InsForge App port
POSTGREST_PORT5430External PostgREST port
DENO_PORT7133External Deno Runtime port
ACCESS_API_KEYEmptyOptional MCP / API access key; generated by InsForge when empty
ACCESS_ANON_KEYEmptyOptional anonymous frontend access key; should begin with anon_ when set
OPENROUTER_API_KEYEmptyOptional OpenRouter model gateway key
AWS_S3_BUCKET / S3_*EmptyOptional object storage configuration
DENO_DEPLOY_TOKENEmptyOptional Deno Deploy integration
VERCEL_TOKEN / FLY_API_TOKENEmptyOptional site deployment and compute platform integrations
STRIPE_* / RAZORPAY_*EmptyOptional payment integrations

Generate production secrets with:

openssl rand -base64 32

When changing the database password, keep the application and database definitions synchronized. For example:

apps:
  insforge:
    conf:
      POSTGRES_PASSWORD: <new-password>

pg_users:
  - { name: dbuser_insforge ,password: <new-password> ,pgbouncer: true ,roles: [dbrole_admin] ,superuser: true }

Once ENCRYPTION_KEY has been used for production data, keep it stable. Changing it may make stored runtime credentials such as API keys, OAuth tokens, and function secrets impossible to decrypt.

Database and Permissions

The default InsForge database is named insforge and owned by dbuser_insforge. The template creates the following roles:

RoleLoginPurpose
dbuser_insforgeYesApplication user that connects InsForge to PostgreSQL; granted superuser in the template
anonNoPostgREST anonymous role
authenticatedNoPostgREST authenticated-user role
project_adminNoAdministrative role with service-key semantics and BYPASSRLS

files/insforge.sql configures PostgREST role privileges, default privileges, ACLs for project_admin, and the helper function public.reload_postgrest_schema(). The function executes:

NOTIFY pgrst, 'reload schema';

This notifies PostgREST to reload its schema.

The upstream InsForge PostgreSQL image preinstalls insforge_pg_utils. The current Pigsty template does not depend on this extension package. Instead, it uses pgcrypto, http, and pg_cron already provided by Pigsty, while application migrations by InsForge manage runtime schemas.

Service Ports and Persistence

The default Docker Compose services are:

ServiceContainerHost PortDescription
InsForge Appinsforge7130Dashboard and main API
PostgRESTinsforge-postgrest5430Automatically generated REST API
Deno Runtimeinsforge-deno7133Edge function runtime

The default Docker volumes are:

VolumeMount PointDescription
storage-data/insforge-storageLocal file storage
insforge-logs/insforge-logsApplication logs
deno_cache/deno-dirDeno module cache

By default, Docker containers connect to the Pigsty node at 10.10.10.10:5432. The template HBA rule permits 172.16.0.0/12, covering Docker’s default bridge and common custom bridge networks.

Domain and Ingress

The template adds an InsForge entry to infra_portal by default:

infra_portal:
  home    : { domain: i.pigsty }
  insforge:
    domain: isf.pigsty
    endpoint: "10.10.10.10:7130"
    websocket: true
    certbot: isf.pigsty

To use a real domain such as insforge.example.com, replace the placeholder in bulk:

sed -ie 's/isf.pigsty/insforge.example.com/g' pigsty.yml

Then apply the Nginx ingress configuration:

./infra.yml -t nginx

To request an HTTPS certificate, first ensure that the domain resolves to the current node, then add or modify certbot under infra_portal.insforge:

insforge:
  domain: insforge.example.com
  endpoint: "10.10.10.10:7130"
  websocket: true
  certbot: insforge.example.com

Then run:

make cert
./infra.yml -t nginx

Operations

InsForge is installed in /opt/insforge. Common commands include:

cd /opt/insforge

make up       # Start services
make view     # Print access URLs
make info     # Show container status
make log      # Follow logs
make restart  # Restart containers
make down     # Stop containers
make clean    # Remove stopped containers
make edit     # Edit .env

For offline environments, save the images in advance:

cd /opt/insforge
make pull
make save
make tarball

Default artifact locations:

  • /tmp/docker/insforge/postgrest.tgz
  • /tmp/docker/insforge/insforge.tgz
  • /tmp/docker/insforge/deno.tgz
  • /tmp/insforge.tgz

Load the images on the target machine:

cd /opt/insforge
make load
make up

Data and Backup

InsForge state is divided into two categories:

  • Business data, users, project metadata, permissions, and related state are stored in the Pigsty PostgreSQL database insforge.
  • Local files and logs are stored in the Docker volumes storage-data and insforge-logs.

By default, the template schedules a full PostgreSQL backup every day at 1:00 AM:

pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ]

After deployment, check the backup status:

pig pb info

If file storage is configured to use S3 / MinIO, file objects are managed by that object storage system. If you keep local volumes, include them separately in your host-level backup strategy.

Troubleshooting

Check container status:

cd /opt/insforge
make info

View logs:

sudo docker logs insforge
sudo docker logs insforge-postgrest
sudo docker logs insforge-deno

Check whether PostgreSQL is reachable from the container:

sudo docker exec insforge-postgrest bash -c '</dev/tcp/10.10.10.10/5432'

Check whether the HBA rules include the Docker network:

sudo -iu postgres psql -d insforge -c "TABLE pg_hba_file_rules;"

Check whether the roles exist:

sudo -iu postgres psql -d insforge -c "SELECT rolname, rolcanlogin, rolbypassrls FROM pg_roles WHERE rolname IN ('anon','authenticated','project_admin');"

Check for port conflicts:

ss -tlnp | grep -E '7130|7133|5430'

Common issues:

  • Cannot log in: Confirm that ROOT_ADMIN_USERNAME / ROOT_ADMIN_PASSWORD were written to /opt/insforge/.env by the template, and check the insforge container logs.
  • PostgREST fails to start: Confirm that the anon, authenticated, and project_admin roles exist, and that JWT_SECRET matches the InsForge App setting.
  • Database connection fails: Confirm that POSTGRES_HOST, POSTGRES_PORT, and POSTGRES_PASSWORD match the pg_users configuration, and check that HBA permits the Docker network.
  • File upload or download errors: When using S3 / MinIO, check AWS_S3_BUCKET, S3_ENDPOINT_URL, S3_FORCE_PATH_STYLE, and the access-key configuration.

References

5 - Hindsight: AI Long-Term Memory

Self-host Hindsight with Pigsty and store long-term memory in external PostgreSQL.

Hindsight is a PostgreSQL-native long-term memory service for AI agents.

Pigsty provides the app/hindsight configuration template (conf/app/hindsight.yml), using Pigsty-managed PostgreSQL by default instead of Hindsight’s built-in development database.

Quick Start

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c app/hindsight
vi pigsty.yml                 # edit domain, database password, and LLM settings
./deploy.yml
./docker.yml
./app.yml

Default access URLs:

  • UI: http://hs.pigsty or http://<IP>:9999
  • API: http://hs-api.pigsty or http://<IP>:8888

Key Settings

conf/app/hindsight.yml overrides /opt/hindsight/.env through apps.hindsight.conf. Key parameters:

  • HINDSIGHT_API_PUBLISH_PORT: public API port, default 8888
  • HINDSIGHT_UI_PUBLISH_PORT: public UI port, default 9999
  • HINDSIGHT_DB_HOST / HINDSIGHT_DB_PORT / HINDSIGHT_DB_NAME / HINDSIGHT_DB_USER / HINDSIGHT_DB_PASSWORD
  • HINDSIGHT_API_VECTOR_EXTENSION: default pgvector
  • HINDSIGHT_API_TEXT_SEARCH_EXTENSION: default native
  • HINDSIGHT_API_LLM_PROVIDER: default none

The default none LLM provider only ensures the service can start. Fact extraction, reflection, and consolidation require configuring Ollama or an OpenAI-compatible API.

Operations

cd /opt/hindsight
make up
make log
make info
make down
make pull

References

6 - FerretDB: MongoDB Protocol

Deploy a stateless FerretDB proxy on PostgreSQL and DocumentDB managed by Pigsty.

FerretDB provides a MongoDB-compatible protocol over PostgreSQL and the DocumentDB extension. Pigsty’s app/ferretdb runs only the stateless protocol proxy; PostgreSQL, the postgres database, the DocumentDB extensions, and the backend PostgreSQL login role must already exist. The mongo configuration template prepares these dependencies together.

Quick Start

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c mongo
./deploy.yml
./docker.yml -l pg-meta
./app.yml -l pg-meta

After installing mongosh or another MongoDB-compatible client separately, connect with the dedicated login declared by the template:

mongosh 'mongodb://mongod:[email protected]:27017/'

Key Configuration

Override template variables through apps.ferretdb.conf in the inventory. Do not edit the deployed /opt/ferretdb/.env directly:

app: ferretdb
apps:
  ferretdb:
    conf:
      FERRETDB_IMAGE: ghcr.io/ferretdb/ferretdb:2.7.0
      FERRETDB_POSTGRESQL_URL: 'postgres://mongod:[email protected]:5436/postgres?pool_min_conns=1&pool_max_conns=20'
      FERRETDB_BIND_ADDR: 127.0.0.1
      FERRETDB_PORT: 27017
      FERRETDB_AUTH: true
      FERRETDB_TELEMETRY: disabled

Port 5436 is Pigsty’s direct-to-current-primary PostgreSQL service. Linux host-gateway mapping lets the container connect through the local host while preserving Pigsty’s primary routing. The MongoDB port listens on loopback by default; change FERRETDB_BIND_ADDR explicitly only when remote clients require access.

FerretDB authenticates users through PostgreSQL, but it does not currently implement MongoDB authorization semantics, so MongoDB roles cannot provide access isolation. The template also does not enable MongoDB client TLS. Before exposing the service to an untrusted network, configure certificates and the FERRETDB_LISTEN_TLS* variables.

FerretDB releases specify a preferred DocumentDB version, while Pigsty may ship a newer compatible package. After upgrading either component, rerun an authenticated CRUD smoke test.

Optional Three-Node Topology

The mongo template includes a commented three-node PostgreSQL / DocumentDB cluster. Each node runs a FerretDB container, and HAProxy exposes standard port 27017 at the floating endpoint 10.10.10.4:27017 (mongo.pigsty). After enabling the complete pg-mongo cluster and the additional etcd members, run:

./configure -c mongo
./deploy.yml
./docker.yml -l pg-mongo
./app.yml -l pg-mongo
mongosh 'mongodb://mongod:[email protected]:27017/'

HAProxy uses TCP health checks to exclude stopped or unreachable FerretDB processes; the image’s built-in health check continues to verify the backend connection. Production deployments should also use a shared Silo or external S3-compatible pgBackRest repository so the same backup history remains available after a PostgreSQL primary failover.

References

7 - Teable: AI No-Code Database

Self-host Teable with Pigsty, external PostgreSQL, and Silo.

Teable is a no-code database platform for team collaboration.

Pigsty provides the app/teable template (conf/app/teable.yml) and depends on PostgreSQL + Silo + Docker by default (no Redis dependency).

Quick Start

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c app/teable
vi pigsty.yml                 # update passwords, domain, and mail settings
./deploy.yml                  # deploy infra, PostgreSQL, and Silo
./docker.yml
./app.yml

Default endpoints:

  • http://<IP>:8890
  • http://tea.pigsty

Key Settings

The template writes the following into /opt/teable/.env:

  • POSTGRES_HOST/POSTGRES_PORT/POSTGRES_DB/POSTGRES_USER/POSTGRES_PASSWORD
  • PRISMA_DATABASE_URL
  • PUBLIC_ORIGIN (public URL)
  • PUBLIC_DATABASE_PROXY
  • TEABLE_PORT (default 8890)

Operations

cd /opt/teable
make up
make log
make down

References

8 - Gitea: Self-Hosted Git Service

Deploy Gitea with Pigsty’s Compose template and connect it to external PostgreSQL.

Gitea is a lightweight open-source Git hosting platform.

Pigsty’s app/gitea template uses external PostgreSQL mode by default, configured via GITEA_DB_* values in .env.

Quick Start

cd ~/pigsty/app/gitea
vi .env         # check domain, ports, database settings
make up

Default endpoints:

  • Web: http://git.pigsty or http://<IP>:8889
  • SSH: <IP>:2222

Database Preparation

bin/pgsql-user pg-meta dbuser_gitea
bin/pgsql-db   pg-meta gitea

Connection string example:

postgres://dbuser_gitea:[email protected]:5432/gitea

Common Commands

make up
make log
make stop
make clean

References

9 - NocoDB: Open-Source Airtable

Use NocoDB to transform PostgreSQL databases into smart spreadsheets, a no-code database application platform.

NocoDB is an open-source Airtable alternative that turns any database into a smart spreadsheet.

It provides a rich user interface that allows you to create powerful database applications without writing code. NocoDB supports PostgreSQL, MySQL, SQL Server, and more, making it ideal for building internal tools and data management systems.

Quick Start

Pigsty provides a Docker Compose configuration file for NocoDB in the software template directory:

cd ~/pigsty/app/nocodb

Review and modify the .env configuration file (adjust database connections as needed).

Start the service:

make up     # Start NocoDB with Docker Compose

Access NocoDB:

  • Default URL: http://nocodb.pigsty
  • Alternate URL: http://10.10.10.10:8080
  • First-time access requires creating an administrator account

Management Commands

Pigsty provides convenient Makefile commands to manage NocoDB:

make up      # Start NocoDB service
make run     # Start with Docker (connect to external PostgreSQL)
make view    # Display NocoDB access URL
make log     # View container logs
make info    # View service details
make stop    # Stop the service
make clean   # Stop and remove containers
make pull    # Pull the latest image
make rmi     # Remove NocoDB image
make save    # Save image to /tmp/nocodb.tgz
make load    # Load image from /tmp/nocodb.tgz

Connect to PostgreSQL

NocoDB can connect to PostgreSQL databases managed by Pigsty.

When adding a new project in the NocoDB interface, select “External Database” and enter the PostgreSQL connection information:

Host: 10.10.10.10
Port: 5432
Database Name: your_database
Username: your_username
Password: your_password
SSL: Disabled (or enable as needed)

After successful connection, NocoDB will automatically read the database table structure, and you can manage data through the visual interface.

Features

  • Smart Spreadsheet Interface: Excel/Airtable-like user experience
  • Multiple Views: Grid, form, kanban, calendar, gallery views
  • Collaboration Features: Team collaboration, permission management, comments
  • API Support: Auto-generated REST API
  • Integration Capabilities: Webhooks, Zapier integrations
  • Import/Export: CSV, Excel format support
  • Formulas and Validation: Complex data calculations and validation rules

Configuration

NocoDB configuration is in the .env file:

# Database connection (NocoDB metadata storage)
NC_DB=pg://postgres:[email protected]:5432/nocodb

# JWT secret (recommended to change)
NC_AUTH_JWT_SECRET=your-secret-key

# Other settings
NC_PUBLIC_URL=http://nocodb.pigsty
NC_DISABLE_TELE=true

Data Persistence

NocoDB metadata is stored by default in an external PostgreSQL database, and application data can also be stored in PostgreSQL.

If using local storage, data is saved in the /data/nocodb directory.

Security Recommendations

  1. Change Default Secret: Modify NC_AUTH_JWT_SECRET in the .env file
  2. Use Strong Passwords: Set strong passwords for administrator accounts
  3. Configure HTTPS: Enable HTTPS for production environments
  4. Restrict Access: Limit access through firewall or Nginx
  5. Regular Backups: Regularly back up the NocoDB metadata database

Related Links

10 - Mattermost: Open-Source Team Collaboration

Deploy Mattermost with Pigsty and store state in external PostgreSQL.

Mattermost is an open-source team collaboration platform and a private alternative to Slack.

Pigsty provides app/mattermost (conf/app/mattermost.yml), which stores app state in external PostgreSQL and persists file directories on host paths.

Quick Start

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c app/mattermost
vi pigsty.yml                 # update passwords and domain
./deploy.yml
./docker.yml
./app.yml

Default endpoints:

  • http://<IP>:8065
  • http://mm.pigsty

On first access, initialize the admin account in the web UI.

Default Storage and Connections

Default template settings include:

  • PostgreSQL URL: POSTGRES_URL=postgres://dbuser_mattermost:DBUser.Mattermost@<IP>:5432/mattermost?...
  • Persistent directories: /data/mattermost/{config,data,logs,plugins,client/plugins,bleve-indexes}

Operations

cd /opt/mattermost
make up
make restart
make log
make stop

References

11 - Wiki.js: OSS Wiki Software

How to self-hosting your own wikipedia with Wiki.js and use Pigsty managed PostgreSQL as the backend database

Public Demo: http://wiki.pigsty.cc

Wiki.js

TL; DR

cd ~/pigsty/app/wiki && docker compose up -d

Postgres Preparation

# postgres://dbuser_wiki:[email protected]:5432/wiki
- { name: wiki, owner: dbuser_wiki, revokeconn: true , comment: wiki the api gateway database }
- { name: dbuser_wiki, password: DBUser.Wiki , pgbouncer: true , roles: [ dbrole_admin ] }
bin/pgsql-user pg-meta dbuser_wiki
bin/pgsql-db   pg-meta wiki

Configuration

version: "3"
services:
  wiki:
    container_name: wiki
    image: requarks/wiki:2
    environment:
      DB_TYPE: postgres
      DB_HOST: 10.10.10.10
      DB_PORT: 5432
      DB_USER: dbuser_wiki
      DB_PASS: DBUser.Wiki
      DB_NAME: wiki
    restart: unless-stopped
    ports:
      - "9002:3000"

Access

  • Default Port for wiki: 9002
# add to nginx_upstream
- { name: wiki  , domain: wiki.pigsty.cc , endpoint: "127.0.0.1:9002"   }
./infra.yml -t nginx_config,nginx_reload -l infra

12 - Maybe: Self-Hosted Personal Finance

Self-host the Maybe personal finance application with Pigsty and let Pigsty manage PostgreSQL, backups, monitoring, and ingress.

Maybe is an open-source personal finance application for managing accounts, transactions, budgets, investments, and household financial views. Maybe is a typical Rails web application: it stores business data in PostgreSQL in production and uses Redis for background job queues.

Pigsty provides the app/maybe template, which connects the stateless Maybe Web / Worker containers to PostgreSQL managed by Pigsty and uses local Redis for the Sidekiq queue. This keeps your most important financial data in a PostgreSQL cluster that can be backed up, monitored, and recovered, rather than in an ephemeral Docker data volume.

The upstream Maybe repository has been archived, and its latest release is v0.6.0. GHCR currently publishes stable and latest image tags. Pigsty uses stable by default, which tracks the latest release image.

Quick Start

Run the following on a fresh Linux x86 / ARM server with a compatible operating system:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap                # Install Pigsty dependencies
./configure -c app/maybe   # Use the Maybe configuration template
vi pigsty.yml              # Required: SECRET_KEY_BASE, database password, domain

./deploy.yml               # Install Pigsty, Infra, Etcd, and PostgreSQL
./docker.yml               # Install Docker and Compose
./app.yml                  # Install Maybe

Maybe listens on port 5002 by default. After installation, you can access it at:

  • http://<your_ip_address>:5002
  • http://maybe.pigsty

On your first visit, select the option to create an account on the login page, then register your first household account to get started.

To access Maybe through maybe.pigsty, add the following entry to /etc/hosts on the machine running your browser:

10.10.10.10 maybe.pigsty

To expose the service to the public internet, use a real domain and HTTPS certificate, and modify infra_portal as described below.

Checklist

  • Prepare a fresh Linux server with at least 2C4G; use an SSD/NVMe disk.
  • Confirm that the server has a static private IPv4 address and can access GHCR / Docker Hub, or that a registry mirror is configured.
  • After running ./configure -c app/maybe, change the default passwords, domain, and secrets in pigsty.yml.
  • Generate a new SECRET_KEY_BASE with openssl rand -hex 64.
  • Keep POSTGRES_PASSWORD consistent with the password of the maybe user in pg_users.
  • For public access, configure a real domain, an HTTPS certificate, and firewall access rules.
  • Confirm that PostgreSQL backup jobs run correctly, and check pig pb info after deployment.

Configuration Template

conf/app/maybe.yml defines a single-node, self-hosted Maybe template. The default topology includes:

  • maybe: The node running the Maybe Web / Worker / Redis containers.
  • pg-maybe: The PostgreSQL database cluster managed by Pigsty.
  • infra: Infrastructure services such as Nginx ingress, Grafana, and VictoriaMetrics.
  • etcd: The distributed configuration store required by Patroni.

Key configuration excerpts:

maybe:
  hosts: { 10.10.10.10: {} }
  vars:
    app: maybe
    apps:
      maybe:
        file:
          - { path: /data/maybe             ,state: directory ,mode: 0755 }
          - { path: /data/maybe/storage     ,state: directory ,owner: 1000 ,group: 1000 ,mode: 0755 }
          - { path: /data/maybe/redis       ,state: directory ,mode: 0755 }
        conf:
          MAYBE_IMAGE: ghcr.io/maybe-finance/maybe
          MAYBE_VERSION: stable
          MAYBE_PORT: 5002
          MAYBE_DATA: /data/maybe
          APP_DOMAIN: maybe.pigsty
          SECRET_KEY_BASE: 2f1e4c3d5b6a79808796a5b4c3d2e1f00123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef01234567
          DB_HOST: 10.10.10.10
          DB_PORT: 5432
          POSTGRES_USER: maybe
          POSTGRES_PASSWORD: MaybeFinance2026
          POSTGRES_DB: maybe_production
          REDIS_VERSION: 7-alpine

pg-maybe:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-maybe
    pg_users:
      - { name: maybe ,password: MaybeFinance2026 ,pgbouncer: true ,roles: [ dbrole_admin ] ,comment: admin user for maybe service }
    pg_databases:
      - { name: maybe_production ,owner: maybe ,revokeconn: true ,comment: maybe main database }
    pg_hba_rules:
      - { user: maybe ,db: maybe_production ,addr: 172.16.0.0/12 ,auth: pwd ,title: 'allow maybe access from local docker network' }
    pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ]

Here, maybe_production is the default production database name used upstream by Maybe / Rails. You can rename it to maybe, but you must update POSTGRES_DB, pg_databases.name, pg_hba_rules.db, and every reference in documentation and operational scripts at the same time.

Important Parameters

app.yml copies the app/maybe directory to /opt/maybe and uses apps.maybe.conf to override /opt/maybe/.env. Common parameters include:

ParameterDefaultDescription
MAYBE_IMAGEghcr.io/maybe-finance/maybeMaybe image repository
MAYBE_VERSIONstableImage tag; keep stable for production
MAYBE_PORT5002Host port exposed by Maybe
MAYBE_DATA/data/maybePersistent directory on the host
APP_DOMAINmaybe.pigstyPlaceholder for the default Maybe ingress domain
SECRET_KEY_BASESample random stringRails encryption and signing secret; must be replaced in production
DB_HOST / DB_PORT10.10.10.10 / 5432Pigsty PostgreSQL endpoint
POSTGRES_USERmaybeApplication user that connects Maybe to PostgreSQL
POSTGRES_PASSWORDMaybeFinance2026Application user password; must be replaced in production
POSTGRES_DBmaybe_productionMaybe production database
REDIS_VERSION7-alpineLocal Redis image tag

Generate a production secret with:

openssl rand -hex 64

When changing the password, keep the application and database definitions synchronized. For example:

apps:
  maybe:
    conf:
      POSTGRES_PASSWORD: <new-password>

pg_users:
  - { name: maybe ,password: <new-password> ,pgbouncer: true ,roles: [ dbrole_admin ] }

Domain and Ingress

The template adds a Maybe entry to infra_portal by default:

infra_portal:
  home  : { domain: i.pigsty }
  maybe:
    domain: maybe.pigsty
    endpoint: "10.10.10.10:5002"
    websocket: true

To use a real domain such as finance.example.com, replace the placeholder in bulk:

sed -ie 's/maybe.pigsty/finance.example.com/g' pigsty.yml

Then apply the Nginx ingress configuration:

./infra.yml -t nginx

To request an HTTPS certificate, first ensure that the domain resolves to the current node, then add certbot under infra_portal.maybe:

maybe:
  domain: finance.example.com
  endpoint: "10.10.10.10:5002"
  websocket: true
  certbot: finance.example.com

Then run:

make cert
./infra.yml -t nginx

Operations

Maybe is installed in /opt/maybe. Common commands include:

cd /opt/maybe

make up        # Start Maybe
make run       # Start in the foreground and show logs
make restart   # Restart containers
make down      # Stop containers
make status    # Show container status
make log       # Follow logs
make health    # Check the Rails /up health endpoint
make migrate   # Run Rails db:prepare manually
make console   # Enter the Rails console
make exec      # Enter a shell in the maybe-web container

The web container automatically runs db:prepare at startup, so manual migration is not usually required. If the startup logs report a database migration problem after an image upgrade, inspect the logs, then run:

cd /opt/maybe
make pull
make up
make log

Data and Backup

Maybe state is divided into two categories:

  • Business data is stored in the Pigsty PostgreSQL database maybe_production.
  • Attachments and cache are stored in the host directories /data/maybe/storage and /data/maybe/redis.

By default, the template schedules a full PostgreSQL backup every day at 1:00 AM:

pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ]

After deployment, check the backup status:

pig pb info

If you use Maybe to store real financial data in production, at minimum:

  • Regularly verify that PostgreSQL backups succeed.
  • Place the pgBackRest repository on reliable storage or object storage.
  • Include /data/maybe/storage in file-level backups; for example, use restic to back it up to S3.
  • Do not expose SECRET_KEY_BASE, database passwords, or API keys in a public repository.

Security Recommendations

Maybe manages highly sensitive personal and household financial data. For production use:

  • Change all Pigsty default passwords, especially pg_admin_password, pg_monitor_password, patroni_password, and haproxy_admin_password.
  • Change the Maybe database user password in POSTGRES_PASSWORD.
  • Use a new SECRET_KEY_BASE; do not retain the sample value from the template.
  • Enable HTTPS for public access and restrict access to administration ports.
  • If you enable OPENAI_ACCESS_TOKEN or SYNTH_API_KEY, assess both external API costs and the boundary of data exposure.

The upstream Maybe repository has been archived. It is suitable for users who are satisfied with the existing feature set and prefer long-term local ownership. If you need continuously evolving features or automatic bank synchronization, evaluate the upstream maintenance status before adopting it.

References

13 - Immich: Self-Hosted Photo and Video Library

Self-host Immich, the open-source Google Photos alternative, with Pigsty managing its metadata, vector search, backups, and ingress.

Immich is a high-performance, self-hosted photo and video management application and one of the most popular open-source alternatives to Google Photos. It provides web and mobile uploads, album sharing, timelines, maps, EXIF and RAW support, LivePhoto, semantic search, facial recognition, and automatic backups under the AGPL-3.0 license.

Pigsty’s app/immich template runs the Immich application layer with Docker Compose and stores metadata in Pigsty-managed PostgreSQL. The current template follows the Immich v3 layout and uses VectorChord by default for similar-image search, smart search, and face-search vectors.

Unlike Immich’s official single-node Compose template, PostgreSQL does not run in an application container. Pigsty manages it instead, providing monitoring, backups, PITR, extension management, and high-availability access.

Quick Start

Immich recommends at least 2 CPU cores and 6 GB of RAM, or 4 CPU cores and 8 GB of RAM for smooth operation. The v3 amd64 machine-learning image requires the x86-64-v2 instruction set. Linux with local SSD storage is recommended for production.

On a fresh x86 or ARM Linux server running a compatible operating system:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c app/immich
vi pigsty.yml              # Required: change passwords, domains, and media paths

./deploy.yml               # Install Pigsty and PostgreSQL
./docker.yml               # Install Docker and Compose
./app.yml                  # Install Immich

Default endpoints:

http://photo.pigsty
http://10.10.10.10:2283

If you use photo.pigsty, add a hosts entry on the browser host or replace the template domain with a real domain.

Template Structure

conf/app/immich.yml defines a single-node self-hosted Immich template. The default topology includes:

  • immich: the node running Immich Server, Machine Learning, and local Valkey/Redis containers.
  • pg-immich: the Pigsty-managed PostgreSQL cluster.
  • infra: Nginx ingress, Grafana, VictoriaMetrics, and other infrastructure.
  • etcd: the distributed configuration store required by Patroni.

Application containers include:

  • immich-server: API, Web UI, and background jobs, exposed on host port 2283 by default.
  • immich-machine-learning: CLIP and facial-recognition model inference.
  • redis: local Valkey queues and cache.

The template does not start the PostgreSQL container from Immich’s upstream Compose file. Pigsty provides the database through DB_URL.

Data Storage Strategy

Immich state is divided into three layers that must be managed separately:

  • Media files: original photos and videos, thumbnails, transcoded videos, avatars, and the upload queue, stored in the host directory specified by UPLOAD_LOCATION.
  • PostgreSQL: metadata for users, albums, assets, EXIF, file paths, job state, people, faces, and smart-search vectors.
  • Valkey/Redis: queues, cache, and runtime state; it must not be treated as an authoritative data source.

Photo and video files are not stored in PostgreSQL. Conversely, Immich cannot reconstruct its complete application state simply by rescanning the media directory; the paths and metadata in the database are equally important.

A recoverable Immich backup must therefore include both PostgreSQL and the media directory. Backing up only the database loses the photos, while backing up only the files cannot reliably restore albums, users, shares, faces, and search state.

PostgreSQL

Default database connection and vector extension:

DB_URL=postgresql://dbuser_immich:[email protected]:5432/immich
DB_VECTOR_EXTENSION=vectorchord

The template installs the extension packages on the pg-immich cluster and creates the database extensions:

CREATE EXTENSION vchord CASCADE;
CREATE EXTENSION earthdistance CASCADE;

vchord must be added to shared_preload_libraries through pg_libs; the template already configures it:

pg_extensions: [ pgvector, vchord ]
pg_libs: 'vchord.so, pg_stat_statements, auto_explain'

The default connection goes directly to PostgreSQL on port 5432. Do not point Immich at PgBouncer transaction pooling. Direct PostgreSQL connections or session pooling are more reliable for migrations, indexes, and prepared statements.

Immich still treats pre-existing PostgreSQL as an advanced deployment option, but explicitly notes that it enables WAL-based backup tools such as pgBackRest and Barman. The Pigsty template uses a non-superuser path: Pigsty creates the database, user, and extensions in advance, so the Immich application user does not need PostgreSQL superuser privileges.

Media Files

Uploaded photos, videos, thumbnails, transcoded files, and avatars are stored in the host directory specified by UPLOAD_LOCATION:

/data/immich/library

PostgreSQL stores only metadata and file paths. Media files are not stored in PostgreSQL and are not automatically protected by pgBackRest.

A production deployment must back up at least two data layers:

  • PostgreSQL: use Pigsty pgBackRest / PITR.
  • Media files: perform a complete file-level backup of /data/immich/library, including originals, the upload queue, thumbnails, transcoded files, avatars, and other generated assets.

For a more consistent combined backup, stop immich-server before backing up the database and media directory together. If the service cannot be stopped, back up the database first, followed by the filesystem.

Images and Networking

The default images come from GHCR and Docker Hub:

docker pull ghcr.io/immich-app/immich-server:v3
docker pull ghcr.io/immich-app/immich-machine-learning:v3
docker pull docker.io/valkey/valkey:9

If image pulls are slow or restricted, configure proxy_env or docker_registry_mirrors in pigsty.yml.

Operations

After installation, enter /opt/immich:

cd /opt/immich

make up       # Start Immich
make logs     # Follow logs
make info     # Show container status
make pull     # Pull images
make restart  # Restart containers
make down     # Stop and remove containers

To pin a specific Immich version, set the following in pigsty.yml:

IMMICH_VERSION: v3.0.1

Read the upstream release notes before upgrading Immich or VectorChord. After upgrading the VectorChord package, you will usually also need to update the extension and rebuild the related indexes in the immich database:

ALTER EXTENSION vchord UPDATE;
REINDEX INDEX face_index;
REINDEX INDEX clip_index;

Rebuilding indexes for a large library can take a long time. Confirm that you have a backup and a maintenance window before proceeding.

References

14 - Kong: API Gateway

Deploy Kong with Pigsty Compose templates and PostgreSQL backend storage.

Kong is an open-source API gateway.

Pigsty’s app/kong template stores configuration in PostgreSQL and runs a one-time migration job (kong-migration) automatically.

Quick Start

cd ~/pigsty/app/kong
vi .env         # check KONG_PG_* and port settings
make

Default ports:

  • Proxy HTTP: 8000
  • Proxy HTTPS: 8443
  • Admin API: 8001

Database Preparation

bin/pgsql-user pg-meta dbuser_kong
bin/pgsql-db   pg-meta kong

Connection string example:

postgres://dbuser_kong:[email protected]:5432/kong

Common Commands

make log
make stop
make clean
make pull

References

15 - Metabase: BI Analytics Tool

Use Metabase for rapid business intelligence analysis with a user-friendly interface for team self-service data exploration.

Metabase is a fast, easy-to-use open-source business intelligence tool that lets your team explore and visualize data without SQL knowledge.

Metabase provides a friendly user interface with rich chart types and supports connecting to various databases, making it an ideal choice for enterprise data analysis.

Quick Start

Pigsty provides a Docker Compose configuration file for Metabase in the software template directory:

cd ~/pigsty/app/metabase

Review and modify the .env configuration file:

vim .env    # Check configuration, recommend changing default credentials

Start the service:

make up     # Start Metabase with Docker Compose

Access Metabase:

  • Default URL: http://metabase.pigsty
  • Alternate URL: http://10.10.10.10:3001
  • First-time access requires initial setup

Management Commands

Pigsty provides convenient Makefile commands to manage Metabase:

make up      # Start Metabase service
make run     # Start with Docker (connect to external PostgreSQL)
make view    # Display Metabase access URL
make log     # View container logs
make info    # View service details
make stop    # Stop the service
make clean   # Stop and remove containers
make pull    # Pull the latest image
make rmi     # Remove Metabase image
make save    # Save image to file
make load    # Load image from file

Connect to PostgreSQL

Metabase can connect to PostgreSQL databases managed by Pigsty.

During Metabase initialization or when adding a database, select “PostgreSQL” and enter the connection information:

Database Type: PostgreSQL
Name: Custom name (e.g., "Production Database")
Host: 10.10.10.10
Port: 5432
Database Name: your_database
Username: dbuser_meta
Password: DBUser.Meta

After successful connection, Metabase will automatically scan the database schema, and you can start creating questions and dashboards.

Features

  • No SQL Required: Build queries through visual interface
  • Rich Chart Types: Line, bar, pie, map charts, and more
  • Interactive Dashboards: Create beautiful data dashboards
  • Auto Refresh: Schedule data and dashboard updates
  • Permission Management: Fine-grained user and data access control
  • SQL Mode: Advanced users can write SQL directly
  • Embedding: Embed charts into other applications
  • Alerting: Automatic notifications on data changes

Configuration

Metabase configuration is in the .env file:

# Metabase metadata database (PostgreSQL recommended)
MB_DB_TYPE=postgres
MB_DB_DBNAME=metabase
MB_DB_PORT=5432
MB_DB_USER=dbuser_metabase
MB_DB_PASS=DBUser.Metabase
MB_DB_HOST=10.10.10.10

# Application configuration
JAVA_OPTS=-Xmx2g

Recommended: Use a dedicated PostgreSQL database for storing Metabase metadata.

Data Persistence

Metabase metadata (users, questions, dashboards, etc.) is stored in the configured database.

If using H2 database (default), data is saved in the /data/metabase directory. Using PostgreSQL as the metadata database is strongly recommended for production environments.

Performance Optimization

  • Use PostgreSQL: Replace the default H2 database
  • Increase Memory: Add JVM memory with JAVA_OPTS=-Xmx4g
  • Database Indexes: Create indexes for frequently queried fields
  • Result Caching: Enable Metabase query result caching
  • Scheduled Updates: Set reasonable dashboard auto-refresh frequency

Security Recommendations

  1. Change Default Credentials: Modify metadata database username and password
  2. Enable HTTPS: Configure SSL certificates for production
  3. Configure Authentication: Enable SSO or LDAP authentication
  4. Restrict Access: Limit access through firewall
  5. Regular Backups: Back up the Metabase metadata database

Related Links

16 - Registry: Container Image Cache

Deploy a Docker Registry pull-through cache and optional web UI with Pigsty.

Pigsty provides the app/registry template (conf/app/registry.yml) for:

  • Docker Registry cache service (default 5000)
  • Optional management UI (default 5080)

Quick Start

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c app/registry
vi pigsty.yml                 # update domains, certs, and ports if needed
./deploy.yml
./docker.yml
./app.yml

Default endpoints:

  • Registry API: http://<IP>:5000 or http://d.pigsty
  • Registry UI: http://<IP>:5080 or http://dui.pigsty

Image data is stored in /data/registry by default.

Docker Client Configuration

If you run HTTP without TLS, Docker must trust the registry explicitly:

{
  "registry-mirrors": ["http://d.pigsty"],
  "insecure-registries": ["d.pigsty:5000"]
}

After editing /etc/docker/daemon.json, restart Docker:

systemctl restart docker

Operations

app/registry/Makefile runs in /opt/registry by default:

cd /opt/registry
make up
make status
make health
make log

References

17 - JumpServer: Open-Source Bastion Host

Self-host JumpServer Community Edition with Pigsty managing its PostgreSQL backend, backups, ingress, and operations.

JumpServer is an open-source PAM and bastion-host system for centralized access management of SSH, RDP, database, and web assets. Pigsty’s app/jumpserver template runs the JumpServer Community Edition application layer with Docker Compose and uses Pigsty-managed PostgreSQL as its persistent backend database.

The current template is based on JumpServer v4.10.16-ce. It retains the Community Edition core, celery, koko, lion, chen, and web services plus local Redis, while removing the built-in PostgreSQL service from the upstream installer. Pigsty manages PostgreSQL, backups, monitoring, Nginx ingress, and the database lifecycle.

Quick Start

On a fresh x86 or ARM Linux server running a compatible operating system:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c app/jumpserver
vi pigsty.yml                 # Required: change passwords, keys, domains, and IPs

./deploy.yml                  # Install Pigsty, Infra, Etcd, and PostgreSQL
./docker.yml                  # Install Docker and Compose
./app.yml                     # Install JumpServer

Run the database migration once after the containers start:

cd /opt/jumpserver
make migrate
make health

Default endpoints:

http://jump.pigsty
http://10.10.10.10:8080
ssh -p 2222 [email protected]

Default web login:

admin / ChangeMe

Change the administrator password immediately after the first login.

Pre-Deployment Checklist

  • Prepare a fresh Linux server with at least 2C4G; use more memory and configure swap for production.
  • Verify that the server IP, DNS, NTP, SSH, and sudo work correctly.
  • Verify access to Docker Hub, or configure docker_registry_mirrors / proxy_env.
  • After running ./configure -c app/jumpserver, change the default passwords, SECRET_KEY, BOOTSTRAP_TOKEN, and DOMAINS in pigsty.yml.
  • Ensure that DOMAINS contains the hostname or IP actually used by the browser, such as 10.10.10.10:8080.
  • Ensure that PostgreSQL backup jobs work; check pig pb info or pgbackrest info after deployment.

Configuration Template

conf/app/jumpserver.yml defines a single-node self-hosted JumpServer template. The default topology includes:

  • jumpserver: the node running JumpServer application containers and local Redis.
  • pg-jumpserver: the Pigsty-managed PostgreSQL cluster.
  • infra: Nginx ingress, Grafana, VictoriaMetrics, and other infrastructure.
  • etcd: the distributed configuration store required by Patroni.

app.yml copies app/jumpserver to /opt/jumpserver, overwrites /opt/jumpserver/.env with apps.jumpserver.conf, and then runs docker compose up -d.

Core application services:

  • jms_core: Django API and web backend, listening on container port 8080.
  • jms_celery: asynchronous jobs, scheduled jobs, and background queues.
  • jms_web: Nginx web ingress, mapped to host port 8080 by default.
  • jms_koko: SSH / SFTP terminal endpoint, mapped to host port 2222 by default.
  • jms_lion: Web Terminal component.
  • jms_chen: WebSocket and database-terminal support component.
  • jms_redis: local Redis for caching, queues, and the Channels backend.

Key Parameters

Common parameters:

ParameterDefaultDescription
JUMPSERVER_VERSIONv4.10.16-ceJumpServer Community Edition image version
JUMPSERVER_DATA/data/jumpserverPersistent application directory
DOMAINS10.10.10.10:8080,10.10.10.10,jump.pigstyTrusted domains and IPs allowed for login
SECRET_KEYExample valueEncryption key; replace and retain it for production
BOOTSTRAP_TOKENExample valueComponent registration token; replace and retain it for production
DB_HOST / DB_PORT10.10.10.10 / 5432Pigsty PostgreSQL endpoint
DB_USER / DB_NAMEjumpserver / jumpserverApplication user and database
DB_PASSWORDDBUser.JumpServerApplication user password; replace it for production
DOCKER_SUBNET192.168.250.0/24Internal JumpServer Compose subnet
REDIS_HOST192.168.250.2Fixed IP of the local Redis container
CORE_HOSThttp://192.168.250.4:8080Internal JumpServer core endpoint
HTTP_PORT8080Web ingress port
SSH_PORT2222Koko SSH/SFTP endpoint port
CORE_WORKER2Number of core Gunicorn workers, sized for a 2C4G sandbox
CELERY_WORKER_COUNT2Worker concurrency for each Celery queue

Example commands for generating production keys:

openssl rand -base64 36 | tr -dc A-Za-z0-9 | head -c 48; echo
openssl rand -base64 24 | tr -dc A-Za-z0-9 | head -c 24; echo
Production keys must remain stable

Do not change SECRET_KEY or BOOTSTRAP_TOKEN after production data exists. Store them together with database backups, /data/jumpserver, and /opt/jumpserver/.env. Losing the original SECRET_KEY may make encrypted account credentials in the database unrecoverable.

Password character restrictions

Do not use single or double quotes in DB_PASSWORD or REDIS_PASSWORD. This matches the behavior of the official JumpServer installer.

Login and DOMAINS

JumpServer validates trusted access domains during login. DOMAINS must include the Host actually used by the browser:

DOMAINS=10.10.10.10:8080,10.10.10.10,jump.pigsty

Normal login URL:

http://10.10.10.10:8080/core/auth/login/?admin=1

If the login page shows:

There is a problem with the configuration file; unable to log in...
DOMAINS=10.10.10.10:8080

Check /opt/jumpserver/.env and the container environment:

cd /opt/jumpserver
grep '^DOMAINS=' .env
docker exec jms_core env | grep '^DOMAINS='

Recreate the core container after correcting it:

sudo sed -i 's#^DOMAINS=.*#DOMAINS=10.10.10.10:8080,10.10.10.10,jump.pigsty#' /opt/jumpserver/.env
docker compose up -d --force-recreate core

Also update pigsty.yml or conf/app/jumpserver.yml; otherwise, the next ./app.yml run will overwrite /opt/jumpserver/.env again.

Do not use an old URL containing csrf_failure=1 to determine whether the configuration is still wrong. That page displays the DOMAINS=... warning using the failed request context. Retest with the normal login page and use an incognito window if necessary.

Docker Network

The template uses a fixed Docker bridge subnet:

DOCKER_SUBNET=192.168.250.0/24
REDIS_IP=192.168.250.2
CELERY_IP=192.168.250.3
CORE_IP=192.168.250.4
LION_IP=192.168.250.5
CHEN_IP=192.168.250.6
KOKO_IP=192.168.250.7
WEB_IP=192.168.250.8

Fixed IPs serve two purposes:

  • Avoid transient Docker DNS resolution failures while JumpServer Python and Java components start.
  • Allow PostgreSQL HBA rules to permit the application subnet precisely.

PostgreSQL sees container clients as coming from 192.168.250.0/24, so the template includes:

pg_hba_rules:
  - { user: jumpserver ,db: jumpserver ,addr: 192.168.250.0/24 ,auth: pwd ,order: 560 ,title: 'allow jumpserver access from docker bridge' }
pgb_hba_rules:
  - { user: jumpserver ,db: jumpserver ,addr: 192.168.250.0/24 ,auth: pwd ,order: 390 ,title: 'allow jumpserver pgbouncer access from docker bridge' }

If 192.168.250.0/24 conflicts with an existing network, update all of the following together:

  • DOCKER_SUBNET and the fixed IP for each component.
  • REDIS_HOST and CORE_HOST.
  • pg_hba_rules.addr and pgb_hba_rules.addr.
  • Recreate the Compose network and containers.

Do not set DB_HOST to 127.0.0.1; inside a container, that address refers to the container itself. Use the host’s private IP or the Pigsty L2 VIP.

PostgreSQL

JumpServer 4.x uses PostgreSQL and requires PostgreSQL 16 or later. The template uses Pigsty to create the database, user, HBA rules, backup schedule, and optional PgBouncer endpoint.

The application database does not require additional PostgreSQL extensions:

pg_extensions: []
pg_users:
  - { name: jumpserver ,password: DBUser.JumpServer ,pgbouncer: true ,pool_mode: session ,roles: [ dbrole_admin ] }
pg_databases:
  - { name: jumpserver ,owner: jumpserver }

The template’s pg_version can be pinned to 16, 17, or a later major version for your environment; JumpServer requires at least PostgreSQL 16. For a high-availability database deployment, follow the commented pg_vip_enabled example in the template and point the application’s DB_HOST to the primary-database VIP.

Do not use PgBouncer transaction pooling

JumpServer’s Django migrations and Celery Beat are unsuitable for PgBouncer transaction pooling. The default is a direct PostgreSQL connection on port 5432. If you use PgBouncer, configure session pooling for the jumpserver user.

Deployment Verification

After installation, run:

cd /opt/jumpserver
make status
make health
make migrate

Expected results:

  • jms_core, jms_celery, jms_web, jms_redis, jms_koko, jms_lion, and jms_chen are all healthy.
  • make health returns status=true, db_status=true, and redis_status=true.
  • make migrate prints No migrations to apply or completes any pending migrations.

Database checks:

sudo -iu postgres patronictl -c /etc/patroni/patroni.yml list
sudo -iu postgres psql -Atqc "select current_setting('server_version'), count(*) from information_schema.tables where table_schema='public'" jumpserver
sudo -iu postgres pgbackrest --stanza=pg-jumpserver info

Expected results:

  • The Patroni cluster Leader is running.
  • PostgreSQL is version 16 or later.
  • The public schema contains about 168 tables after JumpServer migrations.
  • The pgBackRest stanza status is ok.

Troubleshooting

Login Page Reports a DOMAINS Configuration Error

Verify that both configuration layers match:

cd /opt/jumpserver
grep '^DOMAINS=' .env
docker exec jms_core env | grep '^DOMAINS='

You must recreate the core container after changing .env:

docker compose up -d --force-recreate core

Also update apps.jumpserver.conf.DOMAINS in the Pigsty inventory; otherwise, the next ./app.yml run will overwrite it.

admin / ChangeMe Cannot Log In

First distinguish between two kinds of errors:

  • A red DOMAINS=... box at the top of the page indicates a domain / CSRF configuration problem, not a password problem.
  • A form message reporting an incorrect username or password indicates a credential problem.

You can verify inside the container whether the default password is still valid:

docker exec -w /opt/jumpserver/apps jms_core python -c '
import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "jumpserver.settings")
import django; django.setup()
from django.contrib.auth import get_user_model
u = get_user_model().objects.get(username="admin")
print(u.is_active, u.check_password("ChangeMe"))
'

Web Returns 502

Check core and web:

cd /opt/jumpserver
docker compose ps
docker logs --tail 100 jms_core
docker logs --tail 100 jms_web

If the problem is a race over the core log directory during the first start, confirm that /data/jumpserver/core/data/logs exists. The template creates this directory in advance.

Containers Keep Restarting or Health Checks Time Out

JumpServer uses substantial memory on a 2C4G node. The template defaults to:

CORE_WORKER=2
CELERY_WORKER_COUNT=2

If Gunicorn worker timeouts, SIGKILL, or system memory exhaustion still occur, add memory or swap, or reduce the worker counts further. Check resource usage with:

free -h
docker stats --no-stream

Operations

After installation, enter /opt/jumpserver:

cd /opt/jumpserver

make up        # Start JumpServer
make down      # Stop JumpServer
make restart   # Restart containers
make status    # Show container status
make log       # Follow logs
make health    # Check HTTP / DB / Redis health
make migrate   # Run ./jms upgrade_db
make exec      # Enter the core container

When upgrading JumpServer, do not only change the image tag; you must run database migrations:

cd /opt/jumpserver
make pull
make down
docker compose up -d redis core
make migrate
make up

Keep SECRET_KEY and BOOTSTRAP_TOKEN unchanged during the upgrade.

Backup and Restore

JumpServer state has two layers:

  • PostgreSQL database: back up with Pigsty pgBackRest / PITR.
  • Application files: /data/jumpserver and /opt/jumpserver/.env, which contain logs, recordings, component data, Redis persistence, and keys.

Restore the database, .env, and file directory from the same environment:

# 1. Restore PostgreSQL with Pigsty pgBackRest / PITR
# 2. Restore /opt/jumpserver/.env and /data/jumpserver
# 3. Start the containers and run migrations
cd /opt/jumpserver
make up
make migrate
make health

Restoring PostgreSQL without the original SECRET_KEY prevents JumpServer from decrypting stored account credentials correctly.

Community Edition Scope

This template uses PostgreSQL as JumpServer’s own backend database and follows the self-hosted Community Edition deployment path. JumpServer’s “database asset management” is a separate product capability and is not the same as this backend database.

References

18 - ByteBase: Schema Migration

Deploy Bytebase with Pigsty’s Docker Compose template and connect it to external PostgreSQL.

Bytebase is a database schema change and version management tool.

Pigsty provides a ready-to-use Compose template in app/bytebase. It listens on 8887 by default and connects to external PostgreSQL via BB_PGURL.

Quick Start

cd ~/pigsty/app/bytebase
vi .env         # check BB_PORT / BB_DOMAIN / BB_PGURL
make up

Access:

  • http://ddl.pigsty
  • http://<IP>:8887

After first startup, initialize the admin account using the Bytebase setup wizard.

External PostgreSQL

Default connection string example:

postgresql://dbuser_bytebase:[email protected]:5432/bytebase?sslmode=prefer

You can create the database user and database in Pigsty first:

bin/pgsql-user pg-meta dbuser_bytebase
bin/pgsql-db   pg-meta bytebase

Common Commands

make up
make log
make info
make stop
make clean

References

19 - pgAdmin: PostgreSQL GUI

Deploy pgAdmin4 with Pigsty’s Docker Compose template and safely load the PostgreSQL server inventory.

pgAdmin is an open-source PostgreSQL administration and development GUI. Pigsty v4.5.0 provides the app/pgadmin Docker Compose template and can generate a server list and password file from the current inventory.

Change the defaults first

The template login is [email protected] with password pigsty. It is suitable only for a local demo. Before deployment on a shared network or the Internet, change the credentials, restrict port access, and configure HTTPS.

Quick Start

conf/meta.yml declares pgAdmin on the app group by default. Deploy it against an explicitly limited target:

./docker.yml -l app
./app.yml -l app -e app=pgadmin

The default port is 8885; use http://<app_ip>:8885. http://adm.pigsty works only after infra_portal, Nginx, and DNS are configured.

The first container start can take tens of seconds. Check it on the application node:

cd /opt/pgadmin
make info
make log

Application Configuration

Override .env through apps.pgadmin.conf on the app group in pigsty.yml:

all:
  children:
    app:
      hosts: { 10.10.10.10: {} }
      vars:
        docker_enabled: true
        app: pgadmin
        apps:
          pgadmin:
            conf:
              PGADMIN_DEFAULT_EMAIL: [email protected]
              PGADMIN_DEFAULT_PASSWORD: <strong-random-password>
              PGADMIN_LISTEN_ADDRESS: 0.0.0.0
              PGADMIN_PORT: 8885
              PGADMIN_SERVER_JSON_FILE: /pgadmin4/servers.json
              PGADMIN_REPLACE_SERVERS_ON_STARTUP: true

app.yml copies the template to /opt/pgadmin and writes overrides to /opt/pgadmin/.env. This file contains the login password and should remain mode 0600.

The current template uses the unpinned dpage/pgadmin4 image. For production, pin a tested version or digest in docker-compose.yml and validate image upgrades as separate changes.

Load the Server List

env_pgadmin generates:

  • /infra/pgadmin/servers.json: PostgreSQL instance list
  • /infra/pgadmin/pgpass: database administrator password file

In the default conf/meta.yml, the infra and app groups point to the same host, so pgAdmin can bind-mount both files read-only. If pgAdmin and Infra run on different hosts, the application node does not automatically have /infra/pgadmin/; securely distribute equivalent files or customize the mounts instead of assuming that a local path is shared across hosts.

For the default colocated topology, regenerate the files and then ask the running container to import the list and password:

./infra.yml -l infra -t env_pgadmin

./app.yml -l app -e app=pgadmin -t app_launch -e app_args=reload

pgpass contains credentials for pg_admin_username. Restrict access to the files, backups, and application host. If pgAdmin should not hold DBA credentials, generate connection definitions for a dedicated least-privilege role instead.

Domain and HTTPS

Add an entry to infra_portal:

all:
  vars:
    infra_portal:
      pgadmin:
        domain: adm.pigsty
        endpoint: "10.10.10.10:8885"

Update Nginx on the explicitly limited Infra group:

./infra.yml -l infra -t nginx

For a real public domain, point DNS at the server and set certbot on the portal entry:

infra_portal:
  pgadmin:
    domain: adm.example.com
    endpoint: "10.10.10.10:8885"
    certbot: adm.example.com
./infra.yml -l infra -t nginx_certbot,nginx_reload -e certbot_sign=true

See CA and Certificates for prerequisites and renewal. The directly exposed port 8885 is not an HTTPS endpoint.

State and Management

From /opt/pgadmin:

make up       # docker compose up -d
make view     # show access endpoints
make log      # follow container logs
make info     # docker inspect
make conf     # re-import server list and pgpass
make stop     # stop the container
make restart  # restart the container

The Compose template does not persist /var/lib/pgadmin. Pigsty can re-import its generated server list, but preferences, users, and other state created in the pgAdmin UI may be lost when the container is recreated. If that state matters, add a protected persistent volume for the directory and include it in backups after validating the template change.

pgAdmin

Security Checklist

  • Change the default pgAdmin login and never distribute real credentials in scripts, screenshots, or tickets.
  • The default port mapping listens on the host network; restrict sources with a firewall and use Nginx with valid HTTPS for Internet access.
  • Protect /infra/pgadmin/pgpass and /opt/pgadmin/.env; prefer a least-privilege database role.
  • Pin and validate the container image, and back up any pgAdmin state you choose to persist.
  • pgAdmin can execute privileged SQL. Dropping a database, table, or data still requires separate target confirmation and a recent backup.

20 - PGWeb: Browser-based PostgreSQL Client

Run the bundled pgweb Docker application for small, interactive PostgreSQL queries from a browser.

PGWeb Client

PGWeb is a browser-based PostgreSQL client. Pigsty includes a small Docker Compose template under app/pgweb; it publishes container port 8081 on host port 8886.

cd ~/pigsty/app/pgweb
make up                    # docker compose up -d

Open http://cli.pigsty when that portal entry resolves to the Infra node, or browse directly to http://10.10.10.10:8886. The public demo is http://cli.pigsty.cc.

PGWeb asks for a PostgreSQL connection URL. For example:

postgres://dbuser_meta:[email protected]:5432/meta?sslmode=disable
postgres://test:[email protected]:5432/test?sslmode=disable

These strings contain public demonstration defaults and disable TLS. Use a least-privilege account, a non-default secret, and an appropriate sslmode for real deployments; do not expose an unauthenticated PGWeb container or database credentials to untrusted networks.

PGWeb

Shortcuts

The bundled Makefile provides:

make up         # launch with docker compose
make run        # launch with docker run
make view       # print the local access point and example URL
make log        # follow container logs
make info       # inspect the container with jq
make stop       # stop the container
make clean      # stop and remove the container
make pull       # pull the current unpinned sosedoff/pgweb image
make rmi        # remove the local image
make save       # save the image to /tmp/docker/pgweb.tgz
make load       # load it from /tmp/docker/pgweb.tgz

The template currently uses the unpinned sosedoff/pgweb image. Pin an image digest or version in app/pgweb/docker-compose.yml when reproducible production deployment matters.

21 - PostgREST: Auto-Generated API

Deploy PostgREST with Pigsty Compose templates and auto-generate REST APIs from PostgreSQL schema.

PostgREST exposes PostgreSQL schemas directly as REST APIs.

Pigsty provides the app/postgrest template, with default port 8884.

Quick Start

cd ~/pigsty/app/postgrest
vi .env         # check DB_URI / DB_SCHEMA / JWT
make up

Default endpoints:

  • http://<IP>:8884
  • http://api.pigsty (if ingress domain is configured)

Key Settings

Common .env parameters:

  • POSTGREST_DB_URI: database connection string
  • POSTGREST_DB_SCHEMA: exposed schema (default pigsty)
  • POSTGREST_DB_ANON_ROLE: anonymous role
  • POSTGREST_JWT_SECRET: JWT secret

Swagger UI (Optional)

You can run Swagger UI separately to preview APIs:

docker run --rm --name swagger -p 8882:8080 \
  -e API_URL=http://10.10.10.10:8884 \
  swaggerapi/swagger-ui

Then open http://<IP>:8882.

Common Commands

make up
make log
make stop
make clean

References

22 - Electric: PostgreSQL Sync Engine

Self-host Electric with Pigsty to sync PostgreSQL data to frontend apps with partial replication and real-time delivery.

Electric is a PostgreSQL sync engine focused on efficiently delivering database changes to frontend and edge applications.

Pigsty provides the app/electric template (conf/app/electric.yml) to bootstrap database, container, and ingress settings in one flow.

Quick Start

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap
./configure -c app/electric
vi pigsty.yml                 # update domain, passwords, secrets
./deploy.yml
./docker.yml
./app.yml

Default endpoints:

  • http://<IP>:8002
  • http://elec.pigsty (default domain in the template)

Metrics port defaults to 8003 (ELECTRIC_PROMETHEUS_PORT).

Key Settings

conf/app/electric.yml writes apps.electric.conf into /opt/electric/.env. Common parameters:

  • DATABASE_URL: PostgreSQL connection string used by Electric (replication privileges required)
  • ELECTRIC_PORT: Electric HTTP port (default 8002)
  • ELECTRIC_PROMETHEUS_PORT: metrics port (default 8003)
  • ELECTRIC_INSECURE: can be true in dev; disable in prod and use proper secrets

Operations

cd /opt/electric
make up
make logs
make down

References

23 - Jupyter: Notebooks and Data Analysis

Run JupyterLab with Pigsty’s standalone Docker Compose template and access PostgreSQL safely.

JupyterLab is an interactive notebook, terminal, and data-analysis environment. Pigsty v4.5.0 has two distinct deployment paths:

  • The VIBE module, managed by Ansible and systemd, for the complete v4.5.0 development sandbox.
  • The lightweight standalone app/jupyter Docker Compose template documented here.

app/jupyter is not a default apps inventory entry, and data-directory preparation is a separate step. Do not assume that app.yml -e app=jupyter handles its directory ownership.

JupyterLab

Quick Start

cd ~/pigsty/app/jupyter
vi .env                    # change JUPYTER_TOKEN and optionally pin JUPYTER_IMAGE
chmod 600 .env
make dir                   # create /data/jupyter, owned by 1000:100
make up                    # docker compose up -d

Generate a strong token with openssl rand -hex 32. The default port is 8888; open http://<host_ip>:8888.

lab.pigsty works only when that name is configured in infra_portal, Nginx, and DNS. The template default JUPYTER_TOKEN=pigsty is for local demonstrations only and must be replaced in production.

Current Template

The v4.5.0 .env defaults are:

JUPYTER_IMAGE=quay.io/jupyter/minimal-notebook:latest
JUPYTER_PORT=8888
JUPYTER_TOKEN=pigsty

Compose mounts host /data/jupyter at /home/jovyan/work and passes the token into the container. The latest tag changes upstream; production deployments should use a tested, explicit image tag or digest.

For SciPy, R, Julia, TensorFlow, PyTorch, or Spark, select another Jupyter Docker Stacks image listed in .env, while still pinning its version and validating architecture support.

Access PostgreSQL

Install the modern Psycopg driver and optional analysis libraries from a Jupyter terminal:

pip install "psycopg[binary]" pandas sqlalchemy

Do not store real passwords in notebooks. This example obtains the connection string through hidden input and reads system information only:

from getpass import getpass
import psycopg

pgurl = getpass("PostgreSQL URL: ")
with psycopg.connect(pgurl) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT current_database(), current_user, version()")
        print(cur.fetchone())

Use Pandas and SQLAlchemy with a system statistics view:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(pgurl)
df = pd.read_sql(
    "SELECT datname, numbackends, xact_commit, xact_rollback "
    "FROM pg_stat_database ORDER BY datname",
    engine,
)
df

These examples access system views only. Obtain authorization from the data owner before reading application tables, and limit columns, predicates, and result size.

Persistence and Dependencies

Only /home/jovyan/work is mapped to /data/jupyter. The following are not persistent across container recreation by default:

  • Python or Conda packages installed temporarily inside the container
  • notebooks, configuration, and caches outside work
  • the container’s own user state

For production, install dependencies through a pinned image, custom Dockerfile, or reproducible dependency file, and back up /data/jupyter separately. A persistent mount is not a backup.

Management Commands

From ~/pigsty/app/jupyter:

make up      # start JupyterLab
make dir     # create the data directory with 1000:100 ownership
make view    # show access endpoints
make log     # follow logs
make info    # inspect the container
make stop    # stop the container
make pull    # pull the image selected in .env

make clean removes the container but retains /data/jupyter. make purge recursively deletes /data/jupyter; it is an unrecoverable data-deletion operation and requires confirmation of the exact directory and a recent backup.

Security Checklist

  • Use a strong random token, protect .env, and do not disable authentication.
  • The default port mapping listens on the host network. Restrict sources with a firewall and prefer Nginx with valid HTTPS.
  • A notebook can execute arbitrary code and access mounted files and databases. Grant only least-privilege database accounts and host directories.
  • Pin and scan the image, and validate dependency upgrades reproducibly.
  • Back up /data/jupyter regularly and test restoration to a temporary directory.

Related Links

24 - PGLOG: PostgreSQL Log Analysis Application

A sample Applet included with Pigsty for analyzing PostgreSQL CSV log samples

PGLOG is a sample application included with Pigsty that uses the pglog.sample table in MetaDB as its data source. You simply need to load logs into this table, then access the related dashboard.

Pigsty provides convenient commands for pulling CSV logs and loading them into the sample table. On the meta node, the following shortcut commands are available by default:

catlog  [node=localhost]  [date=today]   # Print CSV log to stdout
pglog                                    # Load CSVLOG from stdin
pglog12                                  # Load PG12 format CSVLOG
pglog13                                  # Load PG13 format CSVLOG
pglog14                                  # Load PG14 format CSVLOG (=pglog)

catlog | pglog                       # Analyze current node's log for today
catlog node-1 '2021-07-15' | pglog   # Analyze node-1's csvlog for 2021-07-15

Next, you can access the following links to view the sample log analysis interface.

  • PGLOG Overview: Present the entire CSV log sample details, aggregated by multiple dimensions.

PGLOG overview dashboard

  • PGLOG Session: Present detailed information about a specific connection in the log sample.

PGLOG session dashboard

The catlog command pulls CSV database logs from a specific node for a specific date and writes to stdout

By default, catlog pulls logs from the current node for today. You can specify the node and date through parameters.

Using pglog and catlog together, you can quickly pull database CSV logs for analysis.

catlog | pglog                       # Analyze current node's log for today
catlog node-1 '2021-07-15' | pglog   # Analyze node-1's csvlog for 2021-07-15

25 - NOAA ISD Global Weather Station Historical Data Query

Demonstrate how to import data into a database using the ISD dataset as an example

If you have a database and don’t know what to do with it, why not try this open-source project: Vonng/isd

You can directly reuse the monitoring system Grafana to interactively browse sub-hourly meteorological data from nearly 30,000 surface weather stations over the past 120 years.

This is a fully functional data application that can query meteorological observation records from 30,000 global surface weather stations since 1901.

Project URL: https://github.com/Vonng/isd

Online Demo: https://demo.pigsty.io/d/isd-overview

isd-overview.jpg

Quick Start

Clone this repository

git clone https://github.com/Vonng/isd.git; cd isd;

Prepare a PostgreSQL instance

The PostgreSQL instance should have the PostGIS extension enabled. Use the PGURL environment variable to pass database connection information:

# Pigsty uses dbuser_dba as the default admin account with password DBUser.DBA
export PGURL=postgres://dbuser_dba:[email protected]:5432/meta?sslmode=disable
psql "${PGURL}" -c 'SELECT 1'  # Check if connection is available

Fetch and import ISD weather station metadata

This is a daily-updated weather station metadata file containing station longitude/latitude, elevation, name, country, province, and other information. Use the following command to download and import:

make reload-station   # Equivalent to downloading the latest station data then loading: get-station + load-station

Fetch and import the latest isd.daily data

isd.daily is a daily-updated dataset containing daily observation data summaries from global weather stations. Use the following command to download and import. Note that raw data downloaded directly from the NOAA website needs to be parsed before it can be loaded into the database, so you need to download or build an ISD data parser.

make get-parser       # Download the parser binary from Github, or you can build directly with go using make build
make reload-daily     # Download and import the latest isd.daily data for this year into the database

Load pre-parsed CSV dataset

The ISD Daily dataset has some dirty data and duplicate data. If you don’t want to manually parse and clean it, a stable pre-parsed CSV dataset is also provided here.

This dataset contains isd.daily data up to 2023-06-24. You can download and import it directly into PostgreSQL without needing a parser.

make get-stable       # Get the stable isd.daily historical dataset from Github
make load-stable      # Load the downloaded stable historical dataset into the PostgreSQL database

More Data

Two parts of the ISD dataset are updated daily: weather station metadata and the latest year’s isd.daily (e.g., the 2023 tarball).

You can use the following command to download and refresh these two parts. If the dataset hasn’t been updated, these commands won’t re-download the same data package:

make reload           # Actually: reload-station + reload-daily

You can also use the following commands to download and load isd.daily data for a specific year:

bin/get-daily  2022                   # Get daily weather observation summary for 2022 (1900-2023)
bin/load-daily "${PGURL}" 2022        # Load daily weather observation summary for 2022 (1900-2023)

In addition to the daily summary isd.daily, ISD also provides more detailed sub-hourly raw observation records isd.hourly. The download and load methods are similar:

bin/get-hourly  2022                  # Download hourly observation records for a specific year (e.g., 2022, options 1900-2023)
bin/load-hourly "${PGURL}" 2022       # Load hourly observation records for a specific year

Data

Dataset Overview

ISD provides four datasets: sub-hourly raw observation data, daily statistical summary data, monthly statistical summary, and yearly statistical summary

DatasetNotes
ISD HourlySub-hourly observation records
ISD DailyDaily statistical summary
ISD MonthlyNot used, can be calculated from isd.daily
ISD YearlyNot used, can be calculated from isd.daily

Daily Summary Dataset

  • Compressed package size 2.8GB (as of 2023-06-24)
  • Table size 24GB, index size 6GB, total size approximately 30GB in PostgreSQL
  • If timescaledb compression is enabled, total size can be compressed to 4.5 GB

Sub-hourly Observation Data

  • Total compressed package size 117GB
  • After loading into database: table size 1TB+, index size 600GB+, total size 1.6TB

Database Schema

Weather Station Metadata Table

CREATE TABLE isd.station
(
    station    VARCHAR(12) PRIMARY KEY,
    usaf       VARCHAR(6) GENERATED ALWAYS AS (substring(station, 1, 6)) STORED,
    wban       VARCHAR(5) GENERATED ALWAYS AS (substring(station, 7, 5)) STORED,
    name       VARCHAR(32),
    country    VARCHAR(2),
    province   VARCHAR(2),
    icao       VARCHAR(4),
    location   GEOMETRY(POINT),
    longitude  NUMERIC GENERATED ALWAYS AS (Round(ST_X(location)::NUMERIC, 6)) STORED,
    latitude   NUMERIC GENERATED ALWAYS AS (Round(ST_Y(location)::NUMERIC, 6)) STORED,
    elevation  NUMERIC,
    period     daterange,
    begin_date DATE GENERATED ALWAYS AS (lower(period)) STORED,
    end_date   DATE GENERATED ALWAYS AS (upper(period)) STORED
);

Daily Summary Table

CREATE TABLE IF NOT EXISTS isd.daily
(
    station     VARCHAR(12) NOT NULL, -- station number 6USAF+5WBAN
    ts          DATE        NOT NULL, -- observation date
    -- Temperature & Dew Point
    temp_mean   NUMERIC(3, 1),        -- mean temperature ℃
    temp_min    NUMERIC(3, 1),        -- min temperature ℃
    temp_max    NUMERIC(3, 1),        -- max temperature ℃
    dewp_mean   NUMERIC(3, 1),        -- mean dew point ℃
    -- Air Pressure
    slp_mean    NUMERIC(5, 1),        -- sea level pressure (hPa)
    stp_mean    NUMERIC(5, 1),        -- station pressure (hPa)
    -- Visibility
    vis_mean    NUMERIC(6),           -- visible distance (m)
    -- Wind Speed
    wdsp_mean   NUMERIC(4, 1),        -- average wind speed (m/s)
    wdsp_max    NUMERIC(4, 1),        -- max wind speed (m/s)
    gust        NUMERIC(4, 1),        -- max wind gust (m/s)
    -- Precipitation / Snow Depth
    prcp_mean   NUMERIC(5, 1),        -- precipitation (mm)
    prcp        NUMERIC(5, 1),        -- rectified precipitation (mm)
    sndp        NuMERIC(5, 1),        -- snow depth (mm)
    -- FRSHTT (Fog/Rain/Snow/Hail/Thunder/Tornado)
    is_foggy    BOOLEAN,              -- (F)og
    is_rainy    BOOLEAN,              -- (R)ain or Drizzle
    is_snowy    BOOLEAN,              -- (S)now or pellets
    is_hail     BOOLEAN,              -- (H)ail
    is_thunder  BOOLEAN,              -- (T)hunder
    is_tornado  BOOLEAN,              -- (T)ornado or Funnel Cloud
    -- Record counts used for statistical aggregation
    temp_count  SMALLINT,             -- record count for temp
    dewp_count  SMALLINT,             -- record count for dew point
    slp_count   SMALLINT,             -- record count for sea level pressure
    stp_count   SMALLINT,             -- record count for station pressure
    wdsp_count  SMALLINT,             -- record count for wind speed
    visib_count SMALLINT,             -- record count for visible distance
    -- Temperature flags
    temp_min_f  BOOLEAN,              -- aggregate min temperature
    temp_max_f  BOOLEAN,              -- aggregate max temperature
    prcp_flag   CHAR,                 -- precipitation flag: ABCDEFGHI
    PRIMARY KEY (station, ts)
); -- PARTITION BY RANGE (ts);

Sub-hourly Raw Observation Data Table

ISD Hourly
CREATE TABLE IF NOT EXISTS isd.hourly
(
    station    VARCHAR(12) NOT NULL, -- station id
    ts         TIMESTAMP   NOT NULL, -- timestamp
    -- air
    temp       NUMERIC(3, 1),        -- [-93.2,+61.8]
    dewp       NUMERIC(3, 1),        -- [-98.2,+36.8]
    slp        NUMERIC(5, 1),        -- [8600,10900]
    stp        NUMERIC(5, 1),        -- [4500,10900]
    vis        NUMERIC(6),           -- [0,160000]
    -- wind
    wd_angle   NUMERIC(3),           -- [1,360]
    wd_speed   NUMERIC(4, 1),        -- [0,90]
    wd_gust    NUMERIC(4, 1),        -- [0,110]
    wd_code    VARCHAR(1),           -- code that denotes the character of the WIND-OBSERVATION.
    -- cloud
    cld_height NUMERIC(5),           -- [0,22000]
    cld_code   VARCHAR(2),           -- cloud code
    -- water
    sndp       NUMERIC(5, 1),        -- mm snow
    prcp       NUMERIC(5, 1),        -- mm precipitation
    prcp_hour  NUMERIC(2),           -- precipitation duration in hour
    prcp_code  VARCHAR(1),           -- precipitation type code
    -- sky
    mw_code    VARCHAR(2),           -- manual weather observation code
    aw_code    VARCHAR(2),           -- auto weather observation code
    pw_code    VARCHAR(1),           -- weather code of past period of time
    pw_hour    NUMERIC(2),           -- duration of pw_code period
    -- misc
    -- remark     TEXT,
    -- eqd        TEXT,
    data       JSONB                 -- extra data
) PARTITION BY RANGE (ts);

Parser

The raw data provided by NOAA ISD is in a highly compressed proprietary format that needs to be processed through a parser before it can be converted into database table format.

For the Daily and Hourly datasets, two parsers are provided here: isdd and isdh. Both parsers take annual data compressed packages as input, produce CSV results as output, and work in pipeline mode as shown below:

NAME
        isd -- Integrated Surface Dataset Parser

SYNOPSIS
        isd daily   [-i <input|stdin>] [-o <output|stout>] [-v]
        isd hourly  [-i <input|stdin>] [-o <output|stout>] [-v] [-d raw|ts-first|hour-first]

DESCRIPTION
        The isd program takes noaa isd daily/hourly raw tarball data as input.
        and generate parsed data in csv format as output. Works in pipe mode

        cat data/daily/2023.tar.gz | bin/isd daily -v | psql ${PGURL} -AXtwqc "COPY isd.daily FROM STDIN CSV;"

        isd daily  -v -i data/daily/2023.tar.gz  | psql ${PGURL} -AXtwqc "COPY isd.daily FROM STDIN CSV;"
        isd hourly -v -i data/hourly/2023.tar.gz | psql ${PGURL} -AXtwqc "COPY isd.hourly FROM STDIN CSV;"

OPTIONS
        -i  <input>     input file, stdin by default
        -o  <output>    output file, stdout by default
        -p  <profpath>  pprof file path, enable if specified
        -d              de-duplicate rows for hourly dataset (raw, ts-first, hour-first)
        -v              verbose mode
        -h              print help

User Interface

Several dashboards made with Grafana are provided here for exploring the ISD dataset and querying weather stations and historical meteorological data.

ISD Overview

Global overview with overall metrics and weather station navigation.

isd-overview.jpg

ISD Country

Display all weather stations within a single country/region.

isd-country.jpg

ISD Station

Display detailed information for a single weather station, including metadata and daily/monthly/yearly summary metrics.

ISD Station Dashboard

isd-station.jpg

ISD Detail

Display raw sub-hourly observation metric data for a weather station, requires the isd.hourly dataset.

ISD Station Dashboard

isd-detail.jpg


26 - WHO COVID-19 Pandemic Dashboard

A sample Applet included with Pigsty for visualizing World Health Organization official pandemic data

Covid is a sample Applet included with Pigsty for visualizing the World Health Organization’s official pandemic data dashboard.

You can browse COVID-19 infection and death cases for each country and region, as well as global pandemic trends.

Overview

GitHub Repository: https://github.com/pgsty/pigsty-app/tree/master/covid

Online Demo: https://demo.pigsty.io/d/covid

COVID-19 analytics dashboard

Installation

Enter the application directory on the admin node and execute make to complete the installation.

make            # Complete all configuration

Other sub-tasks:

make reload     # download latest data and pour it again
make ui         # install grafana dashboards
make sql        # install database schemas
make download   # download latest data
make load       # load downloaded data into database
make reload     # download latest data and pour it into database

27 - StackOverflow Global Developer Survey

Analyze database-related data from StackOverflow’s global developer survey over the past seven years

Overview

GitHub Repository: https://github.com/pgsty/pigsty-app/tree/master/db

Online Demo: https://demo.pigsty.io/d/sf-survey

Stack Overflow database survey dashboard

28 - DB-Engines Database Popularity Trend Analysis

Analyze database management systems on DB-Engines and browse their popularity evolution

Overview

GitHub Repository: https://github.com/pgsty/pigsty-app/tree/master/db

Online Demo: https://demo.pigsty.io/d/db-engine

Database engine monitoring dashboard

29 - AWS & Aliyun Server Pricing

Analyze compute and storage pricing on Aliyun / AWS (ECS/ESSD)

Overview

GitHub Repository: https://github.com/pgsty/pigsty-app/tree/master/cloud

Online Demo: https://demo.pigsty.io/d/ecs

Article: Analyzing Computing Costs: Has Aliyun Really Reduced Prices?

Data Source

Aliyun ECS pricing can be obtained as raw CSV data from Price Calculator - Pricing Details - Price Download.

Schema

Download Aliyun pricing details and import for analysis

CREATE EXTENSION file_fdw;
CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw;

DROP FOREIGN TABLE IF EXISTS aliyun_ecs CASCADE;
CREATE FOREIGN TABLE aliyun_ecs
    (
        "region" text,
        "system" text,
        "network" text,
        "isIO" bool,
        "instanceId" text,
        "hourlyPrice" numeric,
        "weeklyPrice" numeric,
        "standard" numeric,
        "monthlyPrice" numeric,
        "yearlyPrice" numeric,
        "2yearPrice" numeric,
        "3yearPrice" numeric,
        "4yearPrice" numeric,
        "5yearPrice" numeric,
        "id" text,
        "instanceLabel" text,
        "familyId" text,
        "serverType" text,
        "cpu" text,
        "localStorage" text,
        "NvmeSupport" text,
        "InstanceFamilyLevel" text,
        "EniTrunkSupported" text,
        "InstancePpsRx" text,
        "GPUSpec" text,
        "CpuTurboFrequency" text,
        "InstancePpsTx" text,
        "InstanceTypeId" text,
        "GPUAmount" text,
        "InstanceTypeFamily" text,
        "SecondaryEniQueueNumber" text,
        "EniQuantity" text,
        "EniPrivateIpAddressQuantity" text,
        "DiskQuantity" text,
        "EniIpv6AddressQuantity" text,
        "InstanceCategory" text,
        "CpuArchitecture" text,
        "EriQuantity" text,
        "MemorySize" numeric,
        "EniTotalQuantity" numeric,
        "PhysicalProcessorModel" text,
        "InstanceBandwidthRx" numeric,
        "CpuCoreCount" numeric,
        "Generation" text,
        "CpuSpeedFrequency" numeric,
        "PrimaryEniQueueNumber" text,
        "LocalStorageCategory" text,
        "InstanceBandwidthTx" text,
        "TotalEniQueueQuantity" text
        ) SERVER fs OPTIONS ( filename '/tmp/aliyun-ecs.csv', format 'csv',header 'true');

Similarly for AWS EC2, you can download the price list from Vantage:


DROP FOREIGN TABLE IF EXISTS aws_ec2 CASCADE;
CREATE FOREIGN TABLE aws_ec2
    (
        "name" TEXT,
        "id" TEXT,
        "Memory" TEXT,
        "vCPUs" TEXT,
        "GPUs" TEXT,
        "ClockSpeed" TEXT,
        "InstanceStorage" TEXT,
        "NetworkPerformance" TEXT,
        "ondemand" TEXT,
        "reserve" TEXT,
        "spot" TEXT
        ) SERVER fs OPTIONS ( filename '/tmp/aws-ec2.csv', format 'csv',header 'true');



DROP VIEW IF EXISTS ecs;
CREATE VIEW ecs AS
SELECT "region"                                       AS region,
       "id"                                           AS id,
       "instanceLabel"                                AS name,
       "familyId"                                     AS family,
       "CpuCoreCount"                                 AS cpu,
       "MemorySize"                                   AS mem,
       round("5yearPrice" / "CpuCoreCount" / 60, 2)   AS ycm5, -- ¥ / (core·month)
       round("4yearPrice" / "CpuCoreCount" / 48, 2)   AS ycm4, -- ¥ / (core·month)
       round("3yearPrice" / "CpuCoreCount" / 36, 2)   AS ycm3, -- ¥ / (core·month)
       round("2yearPrice" / "CpuCoreCount" / 24, 2)   AS ycm2, -- ¥ / (core·month)
       round("yearlyPrice" / "CpuCoreCount" / 12, 2)  AS ycm1, -- ¥ / (core·month)
       round("standard" / "CpuCoreCount", 2)          AS ycmm, -- ¥ / (core·month)
       round("hourlyPrice" / "CpuCoreCount" * 720, 2) AS ycmh, -- ¥ / (core·month)
       "CpuSpeedFrequency"::NUMERIC                   AS freq,
       "CpuTurboFrequency"::NUMERIC                   AS freq_turbo,
       "Generation"                                   AS generation
FROM aliyun_ecs
WHERE system = 'linux';

DROP VIEW IF EXISTS ec2;
CREATE VIEW ec2 AS
SELECT id,
       name,
       split_part(id, '.', 1)                                                               as family,
       split_part(id, '.', 2)                                                               as spec,
       (regexp_match(split_part(id, '.', 1), '^[a-zA-Z]+(\d)[a-z0-9]*'))[1]                 as gen,
       regexp_substr("vCPUs", '^[0-9]+')::int                                               as cpu,
       regexp_substr("Memory", '^[0-9]+')::int                                              as mem,
       CASE spot
           WHEN 'unavailable' THEN NULL
           ELSE round((regexp_substr("spot", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END     AS spot,
       CASE ondemand
           WHEN 'unavailable' THEN NULL
           ELSE round((regexp_substr("ondemand", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END AS ondemand,
       CASE reserve
           WHEN 'unavailable' THEN NULL
           ELSE round((regexp_substr("reserve", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END  AS reserve,
       "ClockSpeed"                                                                         AS freq
FROM aws_ec2;

Visualization

Browse the interactive panels in the online demo: https://demo.pigsty.io/d/ecs