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.
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 531 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.
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.
Currently, Supabase is the most popular open-source project in the PostgreSQL ecosystem, with over 90,000 GitHub stars.
Supabase also offers a “generous” free tier for small startups — free 500 MB storage, more than enough for storing user tables and analytics data.
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?”: when your data/compute scale exceeds the cloud computing sweet spot (Supabase: 4C/8G/500MB free storage), costs can explode.
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 PostgreSQL’s core feature, only 64 extensions are available on Supabase cloud.
Self-hosted Supabase with Pigsty provides up to 531 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 a series of PostgreSQL extensions they develop and maintain, and plans to replace the native PostgreSQL kernel with OrioleDB (which they acquired). These kernels and extensions are not available in 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 all 10 missing Supabase extensions into ready-to-use RPM/DEB packages, ensuring they work on all major Linux distributions:
Filter 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 availablePostgreSQL cluster, highly available MinIO 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 gain the freedom to use different PostgreSQL kernels (PostgreSQL 14-18, default 18), install 531 extensions, scale Supabase/Postgres/MinIO, freedom from database operations, and freedom from vendor lock-in — running locally forever. Compared to cloud service costs, you only need to prepare servers and run a few commands.
Single-Node Quick Start
Let’s start with single-node Supabase deployment. We’ll cover multi-node high availability later.
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.
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/MinIO 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 MinIO for object storage to hold PostgreSQL backups and Supabase Storage.
This deployment provides a minimum safety net RTO (hour-level recovery time) / RPO (MB-level data loss) disaster recovery in single-node conditions.
For serious production deployments, Pigsty recommends at least 3-4 nodes, ensuring both MinIO 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:
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.
If using different PostgreSQL business user passwords, modify POSTGRES_PASSWORD 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 -t app_config,app_launch # Using playbookcd /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:
all:vars:certbot_sign:true# Use certbot to sign real certificatesinfra_portal:home:i.pigsty.io # Replace with your domain!supa:domain:supa.pigsty.io # Replace with your domain!endpoint:"10.10.10.10:8000"websocket:truecertbot:supa.pigsty.io # Certificate name, usually same as domainchildren:supabase:vars:apps:supabase:# Supabase app definitionconf:# Override /opt/supabase/.envSITE_URL:https://supa.pigsty.io # <------- Change to your external domain nameAPI_EXTERNAL_URL:https://supa.pigsty.io/auth/v1# <--- Auth external URL; keep /auth/v1SUPABASE_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.supa.vars.apps.[supabase].conf to point to Alibaba Cloud OSS:
# if using s3/minio as file storageS3_BUCKET:data # Legacy template compatibility, keep aligned with GLOBAL_S3_BUCKETGLOBAL_S3_BUCKET:data # Bucket actually used by Supabase StorageS3_ENDPOINT:https://sss.pigsty:9000 # Replace with S3-compatible service infoS3_ACCESS_KEY:s3user_data # Replace with S3-compatible service infoS3_SECRET_KEY:S3User.Data # Replace with S3-compatible service infoS3_FORCE_PATH_STYLE:true# Replace with S3-compatible service infoS3_PROTOCOL:https # Replace with S3-compatible service infoS3_REGION:stub # Legacy template compatibilityREGION:stub # Region actually used by Supabase StorageSTORAGE_TENANT_ID:stub # Supabase Storage tenant idS3_PROTOCOL_ACCESS_KEY_ID:s3user_data # S3 protocol access keyS3_PROTOCOL_ACCESS_KEY_SECRET:S3User.Data# S3 protocol access key
Reload Supabase configuration:
./app.yml -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-repositoryaliyun:# Define new backup repo 'aliyun'type:s3 # Alibaba Cloud OSS is S3-compatibles3_endpoint:oss-cn-beijing-internal.aliyuncs.coms3_region:oss-cn-beijings3_bucket:pigsty-osss3_key:xxxxxxxxxxxxxxs3_key_secret:xxxxxxxxs3_uri_style:hostpath:/pgbackrestbundle:y# bundle small files into a single filebundle_limit:20MiB # Limit for file bundles, 20MiB for object storagebundle_size:128MiB # Target size for file bundles, 128MiB for object storagecipher_type:aes-256-cbc # enable AES encryption for remote backup repocipher_pass:pgBackRest.MyPass # Set encryption password for pgBackRest backup reporetention_full_type:time # retention full backup by time on minio reporetention_full:14# keep full backup for the last 14 days
Then specify aliyun backup repository in all.vars.pgbackrest_method and reset pgBackrest:
./pgsql.yml -t pgbackrest
Pigsty will switch the backup repository to external object storage. For more backup configuration, see PostgreSQL Backup.
Advanced: Using SMTP
You can use SMTP for sending emails. Modify the supabase app configuration with SMTP information:
all:children:supabase:# supa groupvars:# supa group varsapps:# supa group app listsupabase:# the supabase appconf:# the supabase app conf entriesSMTP_HOST:smtpdm.aliyun.com:80SMTP_PORT:80SMTP_USER:[email protected]SMTP_PASS:your_email_user_passwordSMTP_SENDER_NAME:MySupabaseSMTP_ADMIN_EMAIL:[email protected]ENABLE_ANONYMOUS_USERS:false
Don’t forget to reload configuration with app.yml.
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 531 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 minimum safety net RTO (hour-level recovery) / RPO (MB-level data loss) disaster recovery.
For RTO < 30s with zero data loss on failover, use multi-node high availability deployment:
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 MinIO 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
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 applicationsodoo:# App name, should have corresponding ~/pigsty/app/odoo folderfile:# 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 filePG_HOST:10.10.10.10# PostgreSQL hostPG_PORT:5432# PostgreSQL portPG_USERNAME:odoo # PostgreSQL userPG_PASSWORD:DBUser.Odoo # PostgreSQL passwordODOO_PORT:8069# Odoo app portODOO_DATA:/data/odoo/webdata # Odoo webdataODOO_ADDONS:/data/odoo/addons # Odoo pluginsODOO_DBNAME:odoo # Odoo database nameODOO_VERSION:19.0# Odoo image version# Odoo databasepg-odoo:hosts:{10.10.10.10:{pg_seq: 1, pg_role:primary } }vars:pg_cluster:pg-odoopg_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'}node_crontab:['00 01 * * * postgres /pg/bin/pg-backup full']# Full backup daily at 1aminfra:{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 variablesadmin_ip:10.10.10.10# Admin node IP addressregion: default # Upstream mirror region:default|china|europenode_tune: oltp # Node tuning specs:oltp,olap,tiny,critpg_conf: oltp.yml # PGSQL tuning specs:{oltp,olap,tiny,crit}.ymldocker_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 imagesno_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:12345infra_portal:# Domain names and upstream servershome :{domain:i.pigsty }minio :{domain: m.pigsty ,endpoint:"${admin_ip}:9001",scheme: https ,websocket:true}odoo:# Nginx server config for odoodomain:odoo.pigsty # REPLACE WITH YOUR OWN DOMAIN!endpoint:"10.10.10.10:8069"# Odoo service endpoint: IP:PORTwebsocket:true# Add websocket supportcertbot:odoo.pigsty # Certbot cert name, apply with `make cert`repo_enabled:falsenode_repo_modules:node,infra,pgsqlpg_version:18#----------------------------------## Credentials: MUST CHANGE THESE!#----------------------------------#grafana_admin_password:pigstygrafana_view_password:DBUser.Viewerpg_admin_password:DBUser.DBApg_monitor_password:DBUser.Monitorpg_replication_password:DBUser.Replicatorpatroni_password:Patroni.APIhaproxy_admin_password:pigstyminio_secret_key:S3User.MinIOetcd_root_password:Etcd.Root
Basics
Check the configurable environment variables in the .env file:
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.
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 elegantly solves these issues for Dify, deploying all components with a single command based on configuration files and using mirrors to address China region access challenges. This makes Dify deployment and delivery very smooth. It handles PostgreSQL primary database, PGVector vector database, MinIO object storage, Redis, VictoriaMetrics monitoring, Grafana visualization, Nginx reverse proxy, and free HTTPS certificates all at once.
Pigsty ensures all Dify state is stored in externally managed services, including metadata in PostgreSQL and other data in the file system. Dify instances launched via Docker Compose become stateless applications that can be destroyed and rebuilt at any time, greatly simplifying operations.
Installation
Let’s start with single-node Dify deployment. We’ll cover production high-availability deployment methods later.
curl -fsSL https://repo.pigsty.io/get | bash;cd ~/pigsty
./bootstrap # Prepare Pigsty dependencies./configure -c app/dify # Use Dify application templatevi 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 # Install Docker and Docker Compose./app.yml # Deploy Dify stateless 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 use the ./configure -c app/dify command for configuration, Pigsty automatically generates a configuration file based on the conf/app/dify.yml template and your current environment. Here’s a detailed explanation of the default configuration:
---#==============================================================## 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.ymlall:children:# the dify applicationdify:hosts:{10.10.10.10:{}}vars:app:dify # specify app name to be installed (in the apps)apps:# define all applicationsdify:# app name, should have corresponding ~/pigsty/app/dify folderfile:# data directory to be created- {path: /data/dify ,state: directory ,mode:0755}conf:# override /opt/dify/.env config file# change domain, mirror, proxy, secret keyNGINX_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 defaultDIFY_PORT:5001# where to store dify files? the default is ./volume, we'll use another volume created aboveDIFY_DATA:/data/dify# enable the upstream websocket sidecar, while keeping PostgreSQL/pgvector externalCOMPOSE_PROFILES:collaborationNEXT_PUBLIC_SOCKET_URL:ws://dify.pigstyTRIGGER_URL:http://dify.pigstyENDPOINT_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 credentialsDB_TYPE:postgresqlDB_USERNAME:difyDB_PASSWORD:difyai123456DB_HOST:10.10.10.10DB_PORT:5432DB_DATABASE:difyDB_SSL_MODE:disableVECTOR_STORE:pgvectorPGVECTOR_HOST:10.10.10.10PGVECTOR_PORT:5432PGVECTOR_USER:difyPGVECTOR_PASSWORD:difyai123456PGVECTOR_DATABASE:difyPGVECTOR_MIN_CONNECTION:2PGVECTOR_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: pathpg-meta:hosts:{10.10.10.10:{pg_seq: 1, pg_role:primary } }vars:pg_cluster:pg-metapg_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 1aminfra:{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 variablesversion:v4.4.0 # pigsty version stringadmin_ip:10.10.10.10# admin node ip addressregion: default # upstream mirror region:default|china|europenode_tune: oltp # node tuning specs:oltp,olap,tiny,critpg_conf: oltp.yml # pgsql tuning specs:{oltp,olap,tiny,crit}.ymldocker_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 imagesno_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:12345infra_portal:# domain names and upstream servershome :{domain:i.pigsty }#minio : { domain: m.pigsty ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }dify:# nginx server config for difydomain:dify.pigsty # REPLACE WITH YOUR OWN DOMAIN!endpoint:"10.10.10.10:5001"# dify service endpoint: IP:PORTwebsocket:true# add websocket supportcertbot:dify.pigsty # certbot cert name, apply with `make cert`repo_enabled:falsenode_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:pigstygrafana_view_password:DBUser.Viewerpg_admin_password:DBUser.DBApg_monitor_password:DBUser.Monitorpg_replication_password:DBUser.Replicatorpatroni_password:Patroni.APIhaproxy_admin_password:pigstyminio_secret_key:S3User.MinIOetcd_root_password:Etcd.Root...
Checklist
Here’s a checklist of configuration items you need to pay attention to:
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 definitionsdify:# Dify groupvars:# Dify group variablesapps:# Application configurationdify:# Dify application definitionconf:# Dify application configurationNGINX_SERVER_NAME:dify.pigstyvars:# Global parameters#certbot_sign: true # Use Certbot for free HTTPS certificatecertbot_email:[email protected]# Email for certificate requests, for expiration notifications, optionalinfra_portal:# Configure Nginx serversdify:# Dify server definitiondomain: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 enabledcertbot:dify.pigsty # Specify Certbot certificate name
Use the following commands to request Nginx certificates:
# Request certificate, can also manually run /etc/nginx/sign-cert scriptmake cert
# The above Makefile shortcut actually runs the following playbook task:./infra.yml -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
File Backup
You can use restic to backup Dify’s file storage (default location /data/dify):
Another more reliable method is using JuiceFS to mount MinIO object storage to the /data/dify directory, allowing you to use MinIO/S3 for file state storage.
If you want to store all data in PostgreSQL, consider “storing file system data in PostgreSQL using JuiceFS”.
For example, you can create another dify_fs database and use it as JuiceFS metadata storage:
METAURL=postgres://dify:difyai123456@:5432/dify_fs
OPTIONS=( --storage postgres
--bucket :5432/dify_fs
--access-key dify
--secret-key difyai123456
${METAURL} jfs
)juicefs format "${OPTIONS[@]}"# Create PG file systemjuicefs mount ${METAURL} /data/dify -d # Mount to /data/dify directory in backgroundjuicefs bench /data/dify # Test performancejuicefs umount /data/dify # Unmount
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.
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:
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:
Role
Login
Purpose
dbuser_insforge
Yes
Application user that connects InsForge to PostgreSQL; granted superuser in the template
anon
No
PostgREST anonymous role
authenticated
No
PostgREST authenticated-user role
project_admin
No
Administrative 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:
NOTIFYpgrst,'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:
Service
Container
Host Port
Description
InsForge App
insforge
7130
Dashboard and main API
PostgREST
insforge-postgrest
5430
Automatically generated REST API
Deno Runtime
insforge-deno
7133
Edge function runtime
The default Docker volumes are:
Volume
Mount Point
Description
storage-data
/insforge-storage
Local file storage
insforge-logs
/insforge-logs
Application logs
deno_cache
/deno-dir
Deno 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:
InsForge is installed in /opt/insforge. Common commands include:
cd /opt/insforge
make up # Start servicesmake view # Print access URLsmake info # Show container statusmake log # Follow logsmake restart # Restart containersmake down # Stop containersmake clean # Remove stopped containersmake 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.
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.
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.
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
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).
First-time access requires creating an administrator account
Management Commands
Pigsty provides convenient Makefile commands to manage NocoDB:
make up # Start NocoDB servicemake run # Start with Docker (connect to external PostgreSQL)make view # Display NocoDB access URLmake log # View container logsmake info # View service detailsmake stop # Stop the servicemake clean # Stop and remove containersmake pull # Pull the latest imagemake rmi # Remove NocoDB imagemake save # Save image to /tmp/nocodb.tgzmake 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:
# add to nginx_upstream- {name: wiki , domain: wiki.pigsty.cc , endpoint:"127.0.0.1:9002"}
./infra.yml -t nginx_config
ansible all -b -a 'nginx -s reload'
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.
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:
Parameter
Default
Description
MAYBE_IMAGE
ghcr.io/maybe-finance/maybe
Maybe image repository
MAYBE_VERSION
stable
Image tag; keep stable for production
MAYBE_PORT
5002
Host port exposed by Maybe
MAYBE_DATA
/data/maybe
Persistent directory on the host
APP_DOMAIN
maybe.pigsty
Placeholder for the default Maybe ingress domain
SECRET_KEY_BASE
Sample random string
Rails encryption and signing secret; must be replaced in production
DB_HOST / DB_PORT
10.10.10.10 / 5432
Pigsty PostgreSQL endpoint
POSTGRES_USER
maybe
Application user that connects Maybe to PostgreSQL
POSTGRES_PASSWORD
MaybeFinance2026
Application user password; must be replaced in production
POSTGRES_DB
maybe_production
Maybe production database
REDIS_VERSION
7-alpine
Local 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:
Maybe is installed in /opt/maybe. Common commands include:
cd /opt/maybe
make up # Start Maybemake run # Start in the foreground and show logsmake restart # Restart containersmake down # Stop containersmake status # Show container statusmake log # Follow logsmake health # Check the Rails /up health endpointmake migrate # Run Rails db:prepare manuallymake console # Enter the Rails consolemake 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.
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.
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.
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.
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 Immichmake logs # Follow logsmake info # Show container statusmake pull # Pull imagesmake restart # Restart containersmake 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:
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
Pigsty provides convenient Makefile commands to manage Metabase:
make up # Start Metabase servicemake run # Start with Docker (connect to external PostgreSQL)make view # Display Metabase access URLmake log # View container logsmake info # View service detailsmake stop # Stop the servicemake clean # Stop and remove containersmake pull # Pull the latest imagemake rmi # Remove Metabase imagemake save # Save image to filemake 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:
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
Change Default Credentials: Modify metadata database username and password
Enable HTTPS: Configure SSL certificates for production
Configure Authentication: Enable SSO or LDAP authentication
Restrict Access: Limit access through firewall
Regular Backups: Back up the Metabase metadata database
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.
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:
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.
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:
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.
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=2CELERY_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 JumpServermake down # Stop JumpServermake restart # Restart containersmake status # Show container statusmake log # Follow logsmake health # Check HTTP / DB / Redis healthmake migrate # Run ./jms upgrade_dbmake 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 migrationscd /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.
importpsycopg2conn=psycopg2.connect('postgres://dbuser_dba:[email protected]:5432/meta')cursor=conn.cursor()cursor.execute('SELECT * FROM pg_stat_activity')foriincursor.fetchall():print(i)
Alias
make up # pull up jupyter with docker composemake dir # create required /data/jupyter and set ownermake run # launch jupyter with dockermake view # print jupyter access pointmake log # tail -f jupyter logsmake info # introspect jupyter with jqmake stop # stop jupyter containermake clean # remove jupyter containermake pull # pull latest jupyter imagemake rmi # remove jupyter imagemake save # save jupyter image to /tmp/docker/jupyter.tgzmake load # load jupyter image from /tmp/docker/jupyter.tgz
25 - Data Applications
PostgreSQL-based data visualization applications
26 - 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 stdoutpglog # Load CSVLOG from stdinpglog12 # Load PG12 format CSVLOGpglog13 # Load PG13 format CSVLOGpglog14 # Load PG14 format CSVLOG (=pglog)catlog | pglog # Analyze current node's log for todaycatlog 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 Session: Present detailed information about a specific connection in the log sample.
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 todaycatlog node-1 '2021-07-15'| pglog # Analyze node-1's csvlog for 2021-07-15
27 - 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.
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.DBAexportPGURL=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 buildmake 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 Githubmake 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
Dataset
Notes
ISD Hourly
Sub-hourly observation records
ISD Daily
Daily statistical summary
ISD Monthly
Not used, can be calculated from isd.daily
ISD Yearly
Not 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
CREATETABLEIFNOTEXISTSisd.daily(stationVARCHAR(12)NOTNULL,-- station number 6USAF+5WBAN
tsDATENOTNULL,-- observation date
-- Temperature & Dew Point
temp_meanNUMERIC(3,1),-- mean temperature ℃
temp_minNUMERIC(3,1),-- min temperature ℃
temp_maxNUMERIC(3,1),-- max temperature ℃
dewp_meanNUMERIC(3,1),-- mean dew point ℃
-- Air Pressure
slp_meanNUMERIC(5,1),-- sea level pressure (hPa)
stp_meanNUMERIC(5,1),-- station pressure (hPa)
-- Visibility
vis_meanNUMERIC(6),-- visible distance (m)
-- Wind Speed
wdsp_meanNUMERIC(4,1),-- average wind speed (m/s)
wdsp_maxNUMERIC(4,1),-- max wind speed (m/s)
gustNUMERIC(4,1),-- max wind gust (m/s)
-- Precipitation / Snow Depth
prcp_meanNUMERIC(5,1),-- precipitation (mm)
prcpNUMERIC(5,1),-- rectified precipitation (mm)
sndpNuMERIC(5,1),-- snow depth (mm)
-- FRSHTT (Fog/Rain/Snow/Hail/Thunder/Tornado)
is_foggyBOOLEAN,-- (F)og
is_rainyBOOLEAN,-- (R)ain or Drizzle
is_snowyBOOLEAN,-- (S)now or pellets
is_hailBOOLEAN,-- (H)ail
is_thunderBOOLEAN,-- (T)hunder
is_tornadoBOOLEAN,-- (T)ornado or Funnel Cloud
-- Record counts used for statistical aggregation
temp_countSMALLINT,-- record count for temp
dewp_countSMALLINT,-- record count for dew point
slp_countSMALLINT,-- record count for sea level pressure
stp_countSMALLINT,-- record count for station pressure
wdsp_countSMALLINT,-- record count for wind speed
visib_countSMALLINT,-- record count for visible distance
-- Temperature flags
temp_min_fBOOLEAN,-- aggregate min temperature
temp_max_fBOOLEAN,-- aggregate max temperature
prcp_flagCHAR,-- precipitation flag: ABCDEFGHI
PRIMARYKEY(station,ts));-- PARTITION BY RANGE (ts);
Sub-hourly Raw Observation Data Table
ISD Hourly
CREATETABLEIFNOTEXISTSisd.hourly(stationVARCHAR(12)NOTNULL,-- station id
tsTIMESTAMPNOTNULL,-- timestamp
-- air
tempNUMERIC(3,1),-- [-93.2,+61.8]
dewpNUMERIC(3,1),-- [-98.2,+36.8]
slpNUMERIC(5,1),-- [8600,10900]
stpNUMERIC(5,1),-- [4500,10900]
visNUMERIC(6),-- [0,160000]
-- wind
wd_angleNUMERIC(3),-- [1,360]
wd_speedNUMERIC(4,1),-- [0,90]
wd_gustNUMERIC(4,1),-- [0,110]
wd_codeVARCHAR(1),-- code that denotes the character of the WIND-OBSERVATION.
-- cloud
cld_heightNUMERIC(5),-- [0,22000]
cld_codeVARCHAR(2),-- cloud code
-- water
sndpNUMERIC(5,1),-- mm snow
prcpNUMERIC(5,1),-- mm precipitation
prcp_hourNUMERIC(2),-- precipitation duration in hour
prcp_codeVARCHAR(1),-- precipitation type code
-- sky
mw_codeVARCHAR(2),-- manual weather observation code
aw_codeVARCHAR(2),-- auto weather observation code
pw_codeVARCHAR(1),-- weather code of past period of time
pw_hourNUMERIC(2),-- duration of pw_code period
-- misc
-- remark TEXT,
-- eqd TEXT,
dataJSONB-- extra data
)PARTITIONBYRANGE(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, enableif 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 Country
Display all weather stations within a single country/region.
ISD Station
Display detailed information for a single weather station, including metadata and daily/monthly/yearly summary metrics.
ISD Station Dashboard
ISD Detail
Display raw sub-hourly observation metric data for a weather station, requires the isd.hourly dataset.
ISD Station Dashboard
28 - 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.
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 againmake ui # install grafana dashboardsmake sql # install database schemasmake download # download latest datamake load # load downloaded data into databasemake reload # download latest data and pour it into database
29 - StackOverflow Global Developer Survey
Analyze database-related data from StackOverflow’s global developer survey over the past seven years
