Managing PostgreSQL Extensions

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_extensions: [ postgis, timescaledb, pgvector ]           # <--- Install extension packages
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'    # <--- Configure preload extensions
    pg_databases:
      - name: meta
        extensions: [ postgis, timescaledb, vector ]            # <--- Enable in database

#all.children.pg-meta.vars:
pg_extensions: [ postgis, timescaledb, pgvector ]

pig install postgis timescaledb       # Install multiple extensions
pig install pgvector -v 17            # Install for specific PG major version

ansible pg-test -b -a 'pig install pg_duckdb'   # Batch install on cluster with Ansible

# EL systems (RHEL, Rocky, Alma, Oracle Linux)
sudo yum install -y pgvector_17*

# Debian / Ubuntu
sudo apt install -y postgresql-17-pgvector

repo_extra_packages: [ pgvector, postgis, timescaledb ]

./node.yml -t node_repo -e node_repo_modules=node,pgsql   # Add PGDG and Pigsty upstream repos

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'   # Preload extensions
    pg_extensions: [ timescaledb, postgis, pgvector ]          # Install packages

pg edit-config pg-meta --force -p shared_preload_libraries='timescaledb, pg_stat_statements, auto_explain'
pg restart pg-meta   # Modify pg-meta params and restart to apply

pg_databases:
  - name: meta
    extensions:
      - vector                             # Simple form
      - { name: postgis, schema: public }  # Specify schema

-- View upgradeable extensions
SELECT name, installed_version, default_version FROM pg_available_extensions
WHERE installed_version IS NOT NULL AND installed_version <> default_version;

-- Update extension to latest version
ALTER EXTENSION vector UPDATE;

-- Update to specific version
ALTER EXTENSION vector UPDATE TO '0.8.1';

DROP EXTENSION vector;              -- Drop extension
DROP EXTENSION vector CASCADE;      -- Cascade drop (drops dependent objects)

pg edit-config pg-meta --force -p shared_preload_libraries='pg_stat_statements, auto_explain'
pg restart pg-meta   # Restart to apply config

SELECT extname, extversion, nspname AS schema
FROM pg_extension e JOIN pg_namespace n ON e.extnamespace = n.oid
ORDER BY extname;

SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL   -- Only show installed
ORDER BY name;

SELECT * FROM pg_available_extensions WHERE name = 'vector';

SELECT e.extname, d.refobjid::regclass AS depends_on
FROM pg_extension e
JOIN pg_depend d ON d.objid = e.oid
WHERE d.deptype = 'e' AND e.extname = 'postgis_topology';

SELECT classid::regclass, objid, deptype
FROM pg_depend
WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'vector');

\dx                    # List enabled extensions
\dx+ vector            # Show extension details

./node.yml -t node_repo -e node_repo_modules=node,pgsql        # Add PGDG and Pigsty repos
./node.yml -t node_repo -e node_repo_modules=node,pgsql,local  # Including local repo

# Pigsty repo
curl -fsSL https://repo.pigsty.io/key | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty >/dev/null
curl -fsSL https://repo.pigsty.io/yum/repo | sudo tee /etc/yum.repos.d/pigsty.repo >/dev/null

# China mainland mirror
curl -fsSL https://repo.pigsty.cc/key | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty >/dev/null
curl -fsSL https://repo.pigsty.cc/yum/repo | sudo tee /etc/yum.repos.d/pigsty.repo >/dev/null

curl -fsSL https://repo.pigsty.io/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg
sudo tee /etc/apt/sources.list.d/pigsty.list > /dev/null <<EOF
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/infra generic main
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/pgsql $(lsb_release -cs) main
EOF
sudo apt update

# China mainland mirror: replace repo.pigsty.io with repo.pigsty.cc

CREATE EXTENSION postgis;                    -- Create base extension first
CREATE EXTENSION postgis_topology;           -- Then create dependent extension
-- Or
CREATE EXTENSION postgis_topology CASCADE;   -- Auto-create dependencies

SELECT * FROM pg_available_extension_versions WHERE name = 'vector';