# Default (Cloudflare CDN)
curl -fsSL https://repo.pigsty.io/pig | bash

# China Mirror
curl -fsSL https://repo.pigsty.cc/pig | bash

pig --version
pig --help

pig repo list                    # List available repos and modules
pig repo info                    # Show repository information
pig repo status                  # Display current repo config
pig repo add [modules...]        # Add repositories
pig repo set                     # Setup all required repos (recommended)
pig repo rm [modules...]         # Remove repositories
pig repo update                  # Update package cache

pig ext list [pattern]           # Search/list extensions
pig ext info <name>              # Show extension details
pig ext avail <name>             # Show availability matrix
pig ext status                   # Show installed extensions
pig ext scan                     # Scan installed extensions
pig ext add <name> [-v version]  # Install extension
pig ext rm <name>                # Remove extension
pig ext update                   # Update extensions

pig install pg17                 # Install PostgreSQL 17
pig install pg_duckdb -v 17      # Install extension for PG 17
pig install vector postgis       # Install multiple extensions

pig pg init                      # Initialize data directory
pig pg start                     # Start PostgreSQL
pig pg stop                      # Stop PostgreSQL
pig pg status                    # Check status
pig pg psql [database]           # Connect to database
pig pg ps                        # Show connections
pig pg vacuum [database]         # Vacuum database
pig pg log tail                  # View logs in real-time

pig pb info                      # Show backup information
pig pb ls                        # List all backups
pig pb backup                    # Create backup
pig pb backup full               # Full backup
pig pb backup incr               # Incremental backup
pig pb restore                   # Restore to latest
pig pb restore -t <time>         # Restore to specific time
pig pb log tail                  # View backup logs

pig pt list                      # List cluster members
pig pt config                    # Show cluster config
pig pt status                    # View service status
pig pt log -f                    # View logs in real-time

pig sty init                     # Download and install Pigsty
pig sty boot                     # Install Ansible dependencies
pig sty conf [-m template]       # Generate configuration
pig sty deploy                   # Run deployment playbook
pig sty list                     # List available versions

pig status                       # Show environment status

# Quick setup for Piglet Run
pig repo set                     # Setup repositories
pig install pg17                 # Install PostgreSQL 17
pig install vector pg_duckdb    # Install extensions
pig sty init && pig sty deploy  # Deploy Pigsty

# Daily operations
pig pg status                    # Check PostgreSQL
pig pb info                      # Check backups
pig ext status                   # Check extensions

# Piglet Run Configuration

# System settings
system:
  hostname: piglet
  timezone: UTC
  locale: en_US.UTF-8

# Database settings
database:
  host: localhost
  port: 5432
  user: dba
  database: postgres
  max_connections: 100
  shared_buffers: 256MB

# Services
services:
  vscode:
    enabled: true
    port: 8080
  jupyter:
    enabled: true
    port: 8888
  grafana:
    enabled: true
    port: 3000

# Storage
storage:
  data_dir: /data
  backup_dir: /data/backup
  temp_dir: /tmp/piglet

# Logging
logging:
  level: info
  file: /var/log/piglet/piglet.log
  max_size: 100M
  max_files: 10

# PostgreSQL Configuration

postgresql:
  version: 17
  data_directory: /data/postgres

  # Connection settings
  listen_addresses: localhost
  port: 5432
  max_connections: 100

  # Memory settings
  shared_buffers: 256MB
  effective_cache_size: 768MB
  work_mem: 4MB
  maintenance_work_mem: 64MB

  # WAL settings
  wal_level: replica
  max_wal_size: 1GB
  min_wal_size: 80MB

  # Logging
  log_destination: csvlog
  logging_collector: on
  log_directory: pg_log

vscode:
  enabled: true
  port: 8080
  auth: password
  extensions:
    - ms-python.python
    - rust-lang.rust-analyzer

jupyter:
  enabled: true
  port: 8888
  notebook_dir: /home/dba/notebooks
  kernels:
    - python3
    - ir

backup:
  enabled: true
  schedule: "0 2 * * *"
  retention: 7

  database:
    type: full
    compress: true

  files:
    enabled: true
    paths:
      - /home/dba
      - /etc/piglet

network:
  # Domain settings
  domain: localhost

  # SSL settings
  ssl:
    enabled: false
    cert: /etc/piglet/ssl/cert.pem
    key: /etc/piglet/ssl/key.pem

  # Proxy settings
  proxy:
    enabled: false
    host: proxy.example.com
    port: 8080

export PIG_DATABASE_PORT=5433
export PIG_SERVICES_VSCODE_ENABLED=false
export PIG_LOGGING_LEVEL=debug

vscode:
  enabled: true
  bind_addr: 127.0.0.1:8080
  auth: password
  password: ${VSCODE_PASSWORD}
  cert: false

  # User data directory
  user_data_dir: /home/dba/.local/share/code-server

  # Extensions directory
  extensions_dir: /home/dba/.local/share/code-server/extensions

code-server --install-extension ms-python.python

{
  "editor.fontSize": 14,
  "editor.tabSize": 2,
  "editor.formatOnSave": true,
  "terminal.integrated.defaultProfile.linux": "bash",
  "python.defaultInterpreterPath": "/usr/bin/python3",
  "files.autoSave": "afterDelay"
}

# Start VS Code server
pig start vscode

# Stop VS Code server
pig stop vscode

# Restart VS Code server
pig restart vscode

# View logs
pig logs vscode

# Check service status
systemctl status code-server

# Check port binding
ss -tlnp | grep 8080

# View logs
journalctl -u code-server -f

# List installed extensions
code-server --list-extensions

# Reinstall extension
code-server --uninstall-extension EXTENSION_ID
code-server --install-extension EXTENSION_ID

jupyter:
  enabled: true
  port: 8888
  token: ${JUPYTER_TOKEN}

  # Notebook directory
  notebook_dir: /home/dba/notebooks

  # Allowed origins
  allow_origin: "*"

  # Kernels
  kernels:
    - python3
    - ir
    - julia

c.ServerApp.ip = '127.0.0.1'
c.ServerApp.port = 8888
c.ServerApp.open_browser = False
c.ServerApp.notebook_dir = '/home/dba/notebooks'
c.ServerApp.token = ''
c.ServerApp.allow_origin = '*'

# R kernel
R -e "IRkernel::installspec()"

# Julia kernel
julia -e 'using Pkg; Pkg.add("IJulia")'

pip install jupyterlab-git
jupyter labextension install @jupyterlab/git

# Start Jupyter
pig start jupyter

# Stop Jupyter
pig stop jupyter

# Restart Jupyter
pig restart jupyter

# View logs
pig logs jupyter

import psycopg2
import pandas as pd

# Connect to database
conn = psycopg2.connect(
    host="localhost",
    database="postgres",
    user="dba"
)

# Query data
df = pd.read_sql("SELECT * FROM my_table", conn)
df.head()

%load_ext sql
%sql postgresql://dba@localhost/postgres

%%sql
SELECT * FROM pg_stat_activity LIMIT 5;

# Check service status
systemctl status jupyter

# Check port binding
ss -tlnp | grep 8888

# View logs
journalctl -u jupyter -f

# List available kernels
jupyter kernelspec list

# Reinstall kernel
python -m ipykernel install --user

psql -U dba -d postgres

# Memory
shared_buffers = 256MB
effective_cache_size = 768MB
work_mem = 4MB
maintenance_work_mem = 64MB
huge_pages = try

# Connections
listen_addresses = 'localhost'
port = 5432
max_connections = 100
superuser_reserved_connections = 3

# WAL
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
wal_buffers = 8MB
checkpoint_completion_target = 0.9

# Logging
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_statement = 'ddl'
log_min_duration_statement = 1000

# Start PostgreSQL
pig start postgres

# Stop PostgreSQL
pig stop postgres

# Restart PostgreSQL
pig restart postgres

# Reload configuration
pig reload postgres

# View logs
pig logs postgres

# Create database
pig db create mydb

# List databases
pig db list

# Drop database
pig db drop mydb

# Connect to database
pig db connect mydb

# Create user
pig user create myuser

# Grant privileges
psql -c "GRANT ALL ON DATABASE mydb TO myuser"

# Change password
pig user passwd myuser

# Full backup
pig backup db --full

# Point-in-time recovery
pig restore db --time "2024-01-15 14:30:00"

# Connection statistics
psql -c "SELECT * FROM pg_stat_activity"

# Database size
psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database"

# Table statistics
psql -c "SELECT * FROM pg_stat_user_tables"

# Piglet Run Nginx Configuration

upstream vscode {
    server 127.0.0.1:8080;
}

upstream jupyter {
    server 127.0.0.1:8888;
}

upstream grafana {
    server 127.0.0.1:3000;
}

server {
    listen 80;
    server_name _;

    # Homepage
    location / {
        root /www/piglet;
        index index.html;
    }

    # VS Code Server
    location /code {
        proxy_pass http://vscode;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }

    # JupyterLab
    location /jupyter {
        proxy_pass http://jupyter;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_set_header Host $host;
    }

    # Grafana
    location /ui {
        proxy_pass http://grafana;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }
}

server {
    listen 443 ssl http2;
    server_name example.com;

    ssl_certificate /etc/piglet/ssl/cert.pem;
    ssl_certificate_key /etc/piglet/ssl/key.pem;
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256;
    ssl_prefer_server_ciphers off;

    # ... location blocks ...
}

# Redirect HTTP to HTTPS
server {
    listen 80;
    server_name example.com;
    return 301 https://$server_name$request_uri;
}

# Start Nginx
pig start nginx

# Stop Nginx
pig stop nginx

# Restart Nginx
pig restart nginx

# Reload configuration
pig reload nginx

# Test configuration
nginx -t

cat > /etc/nginx/conf.d/mysite.conf << 'EOF'
server {
    listen 80;
    server_name mysite.example.com;

    root /www/mysite;
    index index.html;

    location / {
        try_files $uri $uri/ =404;
    }
}
EOF

nginx -t && systemctl reload nginx

location /ws {
    proxy_pass http://backend;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
    proxy_read_timeout 86400;
}

location /app {
    proxy_pass http://127.0.0.1:3001;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
}

log_format main '$remote_addr - $remote_user [$time_local] "$request" '
                '$status $body_bytes_sent "$http_referer" '
                '"$http_user_agent" "$http_x_forwarded_for"';

access_log /var/log/nginx/access.log main;

limit_req_zone $binary_remote_addr zone=api:10m rate=10r/s;

location /api {
    limit_req zone=api burst=20 nodelay;
    proxy_pass http://backend;
}

# Test configuration
nginx -t

# Check error log
tail -f /var/log/nginx/error.log

# Check access log
tail -f /var/log/nginx/access.log

# View connections
ss -tlnp | grep nginx

[server]
http_port = 3000
root_url = %(protocol)s://%(domain)s/ui/
serve_from_sub_path = true

[security]
admin_user = admin
admin_password = ${GRAFANA_PASSWORD}

[auth.anonymous]
enabled = false

[dashboards]
default_home_dashboard_path = /var/lib/grafana/dashboards/home.json

[database]
type = postgres
host = localhost:5432
name = grafana
user = grafana

apiVersion: 1
datasources:
  - name: Prometheus
    type: prometheus
    url: http://localhost:9090
    access: proxy
    isDefault: true

datasources:
  - name: PostgreSQL
    type: postgres
    url: localhost:5432
    database: postgres
    user: grafana
    secureJsonData:
      password: ${GRAFANA_DB_PASSWORD}

datasources:
  - name: Loki
    type: loki
    url: http://localhost:3100
    access: proxy

# Start Grafana
pig start grafana

# Stop Grafana
pig stop grafana

# Restart Grafana
pig restart grafana

# View logs
pig logs grafana

# /etc/grafana/provisioning/dashboards/default.yaml
apiVersion: 1
providers:
  - name: default
    folder: ''
    type: file
    options:
      path: /var/lib/grafana/dashboards

[smtp]
enabled = true
host = smtp.example.com:587
user = [email protected]
password = ${SMTP_PASSWORD}
from_address = [email protected]

pg_stat_activity_count

100 - (avg by (instance) (rate(node_cpu_seconds_total{mode="idle"}[5m])) * 100)

(1 - node_memory_MemAvailable_bytes / node_memory_MemTotal_bytes) * 100

(1 - node_filesystem_avail_bytes / node_filesystem_size_bytes) * 100

curl -X POST -H "Content-Type: application/json" \
  -d '{"name":"mykey", "role": "Admin"}' \
  http://admin:password@localhost:3000/api/auth/keys

# Check service status
systemctl status grafana-server

# View logs
journalctl -u grafana-server -f

# Test database connection
psql -U grafana -d grafana -c "SELECT 1"

juicefs:
  name: piglet
  metadata: postgres://dba@localhost:5432/juicefs
  storage: minio
  bucket: http://localhost:9000/piglet
  access_key: ${MINIO_ACCESS_KEY}
  secret_key: ${MINIO_SECRET_KEY}

  # Mount options
  mount_point: /data/jfs
  cache_dir: /var/cache/juicefs
  cache_size: 10240  # 10GB

[piglet]
meta = postgres://dba@localhost:5432/juicefs
storage = minio
bucket = http://localhost:9000/piglet
access-key = ${MINIO_ACCESS_KEY}
secret-key = ${MINIO_SECRET_KEY}

# Performance options
cache-dir = /var/cache/juicefs
cache-size = 10240
buffer-size = 300
prefetch = 3

# Mount filesystem
pig mount jfs

# Unmount filesystem
pig umount jfs

# Check status
pig status jfs

# View statistics
juicefs stats /data/jfs

juicefs format \
  --storage minio \
  --bucket http://localhost:9000/pig \
  postgres://dba@localhost:5432/juicefs \
  piglet

juicefs mount \
  postgres://dba@localhost:5432/juicefs \
  /data/jfs \
  --cache-dir /var/cache/juicefs \
  --cache-size 10240

juicefs fsck postgres://dba@localhost:5432/juicefs

juicefs snapshot create /data/jfs snap-$(date +%Y%m%d)

juicefs snapshot list /data/jfs

juicefs snapshot restore /data/jfs snap-20240115

juicefs snapshot delete /data/jfs snap-20240115

# Clone directory
juicefs clone /data/jfs/source /data/jfs/dest

# Clone with snapshot
juicefs clone /data/jfs/.snapshots/snap-20240115 /data/jfs/restored

# Increase cache size
juicefs config /data/jfs --cache-size 20480

# Enable writeback
juicefs config /data/jfs --writeback

# Entry cache TTL
juicefs mount ... --entry-cache 3 --dir-entry-cache 3 --attr-cache 3

juicefs stats /data/jfs

juicefs mount ... --metrics localhost:9567

# Check mount status
mount | grep juicefs

# View logs
journalctl -u juicefs -f

# Debug mode
juicefs mount --debug ...

# Repair filesystem
juicefs fsck --repair postgres://dba@localhost:5432/juicefs

-- Create extension
CREATE EXTENSION pgvector;

-- Create extension in specific schema
CREATE EXTENSION postgis SCHEMA public;

-- Update extension
ALTER EXTENSION pgvector UPDATE;

pig ext install pgvector
pig ext install postgis

SELECT * FROM pg_extension;

SELECT * FROM pg_available_extensions ORDER BY name;

SELECT * FROM pg_available_extension_versions
WHERE name = 'pgvector';

-- Enable tracking
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

-- Configure
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.max = 10000;

-- Create extension
CREATE EXTENSION vector;

-- Create vector column
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    embedding vector(384)
);

-- Create index
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Create extension
CREATE EXTENSION postgis;

-- Create geometry column
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom geometry(Point, 4326)
);

-- Spatial query
SELECT name FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(-122.4, 37.8)::geography, 1000);

-- Create extension
CREATE EXTENSION timescaledb;

-- Create hypertable
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER,
    value DOUBLE PRECISION
);

SELECT create_hypertable('metrics', 'time');

# List installed extensions
pig ext list

# Show extension info
pig ext info pgvector

# Remove extension
pig ext remove pgvector

curl -H "Authorization: Bearer YOUR_API_KEY" \
  http://localhost/api/v1/status

pig api key create --name mykey

GET /api/v1/status

{
  "status": "healthy",
  "version": "2.5.0",
  "uptime": 86400,
  "services": {
    "postgres": "running",
    "vscode": "running",
    "jupyter": "running"
  }
}

GET /api/v1/system

{
  "hostname": "piglet",
  "os": "Ubuntu 22.04",
  "cpu": 4,
  "memory": "8GB",
  "disk": "100GB"
}

GET /api/v1/databases

{
  "databases": [
    {
      "name": "postgres",
      "owner": "dba",
      "size": "50MB"
    }
  ]
}

POST /api/v1/databases
Content-Type: application/json

{
  "name": "mydb",
  "owner": "dba"
}

DELETE /api/v1/databases/{name}

GET /api/v1/backups

{
  "backups": [
    {
      "id": "backup-20240115",
      "type": "full",
      "size": "1.2GB",
      "created_at": "2024-01-15T02:00:00Z"
    }
  ]
}

POST /api/v1/backups
Content-Type: application/json

{
  "type": "full",
  "databases": ["postgres"]
}

POST /api/v1/backups/{id}/restore
Content-Type: application/json

{
  "target_time": "2024-01-15T14:30:00Z"
}

GET /api/v1/services

{
  "services": [
    {
      "name": "postgres",
      "status": "running",
      "port": 5432
    },
    {
      "name": "vscode",
      "status": "running",
      "port": 8080
    }
  ]
}

POST /api/v1/services/{name}/{action}

GET /api/v1/snapshots

POST /api/v1/snapshots
Content-Type: application/json

{
  "name": "snap-20240115",
  "description": "Before upgrade"
}

POST /api/v1/snapshots/{name}/restore

GET /api/v1/users

POST /api/v1/users
Content-Type: application/json

{
  "username": "newuser",
  "password": "secure_password",
  "databases": ["mydb"]
}

{
  "error": {
    "code": "NOT_FOUND",
    "message": "Database not found",
    "details": {
      "database": "nonexistent"
    }
  }
}

import requests

api_key = "YOUR_API_KEY"
base_url = "http://localhost/api/v1"

headers = {"Authorization": f"Bearer {api_key}"}

# Get status
response = requests.get(f"{base_url}/status", headers=headers)
print(response.json())

# Create database
response = requests.post(
    f"{base_url}/databases",
    headers=headers,
    json={"name": "mydb", "owner": "dba"}
)

const apiKey = "YOUR_API_KEY";
const baseUrl = "http://localhost/api/v1";

// Get status
fetch(`${baseUrl}/status`, {
  headers: { Authorization: `Bearer ${apiKey}` }
})
  .then(res => res.json())
  .then(data => console.log(data));