Closed Michael2008S closed 5 months ago
Vonng
commented
7 months ago Seems like a problem with pgsodium
unrecognized configuration parameter "pgsodium.enable_event_trigger"
Which pigsty version, config template is used? and can you config cluster with pgsodium.enable_event_trigger = 'off';
Michael2008S
commented
7 months ago Pigsty v2.5.1
根据默认配置生成，再把supa的 yaml 合并过来的配置：
---
#==============================================================#
# File : el.yml
# Desc : Pigsty auto generated config for el8, el9
# Ctime : 2020-05-22
# Mtime : 2023-03-31
# Docs : https://doc.pigsty.cc/#/CONFIG
# Author : Ruohang Feng (rh@vonng.com)
# License : AGPLv3
#==============================================================#
# this is a simple singleton meta config template, check full details with
# https://github.com/Vonng/pigsty/blob/master/files/pigsty/full.yml
all:
children:
# infra cluster for proxy, monitor, alert, etc..
infra: { hosts: { 192.168.18.172: { infra_seq: 1 } } }
# minio cluster, optional backup repo for pgbackrest
minio: { hosts: { 192.168.18.172: { minio_seq: 1 } }, vars: { minio_cluster: minio } }
# etcd cluster for ha postgres
etcd: { hosts: { 192.168.18.172: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
# postgres cluster 'pg-meta' with single primary instance
pg-meta:
hosts: { 192.168.18.172: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-meta
pg_libs: 'pg_net, pg_stat_statements, auto_explain' # add extra extensions to shared_preload_libraries
pg_extensions: # required extensions
- pg_repack_${pg_version}* wal2json_${pg_version}* pgvector_${pg_version}* pg_cron_${pg_version}* pgsodium_${pg_version}*
- vault_${pg_version}* pg_graphql_${pg_version}* pgjwt_${pg_version}* pg_net_${pg_version}* pgsql-http_${pg_version}*
node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am
pg_users:
- { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] ,comment: pigsty admin user }
- { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [ dbrole_readonly ] ,comment: read-only viewer for meta database }
# supabase roles: anon, authenticated, dashboard_user
- { name: anon ,login: false }
- { name: authenticated ,login: false }
- { name: dashboard_user ,login: false ,replication: true ,createdb: true ,createrole: true }
- { name: service_role ,login: false ,bypassrls: true }
# supabase users: please use the same password
- { name: supabase_admin ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: true ,superuser: true ,replication: true ,createdb: true ,createrole: true ,bypassrls: true }
- { name: authenticator ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false ,roles: [ authenticated ,anon ,service_role ] }
- { name: supabase_auth_admin ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false ,createrole: true }
- { name: supabase_storage_admin ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false ,createrole: true ,roles: [ authenticated ,anon ,service_role ] }
- { name: supabase_functions_admin ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false ,createrole: true }
- { name: supabase_replication_admin ,password: 'DBUser.Supa' ,replication: true }
- { name: supabase_read_only_user ,password: 'DBUser.Supa' ,bypassrls: true ,roles: [ pg_read_all_data ] }
pg_databases:
- { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [ pigsty ] ,extensions: [{name: postgis, schema: public}] }
- name: supa
baseline: supa.sql # the init-scripts: https://github.com/supabase/postgres/tree/develop/migrations/db/init-scripts
owner: supabase_admin
comment: supabase postgres database
schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
extensions:
- { name: pgcrypto ,schema: extensions } # 1.3 : cryptographic functions
- { name: pg_net ,schema: extensions } # 0.7.1 : Async HTTP
- { name: pgjwt ,schema: extensions } # 0.2.0 : JSON Web Token API for Postgresql
- { name: uuid-ossp ,schema: extensions } # 1.1 : generate universally unique identifiers (UUIDs)
- { name: pgsodium } # 3.1.8 : pgsodium is a modern cryptography library for Postgres.
- { name: supabase_vault } # 0.2.8 : Supabase Vault Extension
- { name: pg_graphql } # 1.3.0 : pg_graphql: GraphQL support
pg_hba_rules:
- { user: all ,db: supa ,addr: intra ,auth: pwd ,title: 'allow supa database access from intranet'}
- { user: all ,db: supa ,addr: 172.0.0.0/8 ,auth: pwd ,title: 'allow supa database access from docker network'}
vars: # global parameters
version: v2.5.1 # pigsty version string
admin_ip: 192.168.18.172 # admin node ip address
region: default # upstream mirror region: default,china,europe
node_tune: tiny # use tiny template for NODE in demo environment
pg_conf: tiny.yml # use tiny template for PGSQL in demo environment
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
minio : { domain: sss.pigsty ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
postgrest : { domain: api.pigsty ,endpoint: "${admin_ip}:8884" }
pgadmin : { domain: adm.pigsty ,endpoint: "${admin_ip}:8885" }
pgweb : { domain: cli.pigsty ,endpoint: "${admin_ip}:8886" }
bytebase : { domain: ddl.pigsty ,endpoint: "${admin_ip}:8887" }
supa : { domain: supa.pigsty ,endpoint: "${admin_ip}:8000", websocket: true }
nginx_navbar: # application nav links on home page
- { name: PgAdmin4 , url : 'http://adm.pigsty' , comment: 'PgAdmin4 for PostgreSQL' }
- { name: PGWeb , url : 'http://cli.pigsty' , comment: 'PGWEB Browser Client' }
- { name: ByteBase , url : 'http://ddl.pigsty' , comment: 'ByteBase Schema Migrator' }
- { name: PostgREST , url : 'http://api.pigsty' , comment: 'Kong API Gateway' }
- { name: Gitea , url : 'http://git.pigsty' , comment: 'Gitea Git Service' }
- { name: Minio , url : 'http://sss.pigsty' , comment: 'Minio Object Storage' }
- { name: Wiki , url : 'http://wiki.pigsty' , comment: 'Local Wikipedia' }
- { name: Explain , url : '/pigsty/pev.html' , comment: 'pgsql explain visualizer' }
- { name: Package , url : '/pigsty' , comment: 'local yum repo packages' }
- { name: PG Logs , url : '/logs' , comment: 'postgres raw csv logs' }
- { name: Schemas , url : '/schema' , comment: 'schemaspy summary report' }
- { name: Reports , url : '/report' , comment: 'pgbadger summary report' }
node_timezone: Asia/Hong_Kong # use Asia/Hong_Kong Timezone
node_ntp_servers: # NTP servers in /etc/chrony.conf
- pool cn.pool.ntp.org iburst
- pool ${admin_ip} iburst # assume non-admin nodes does not have internet access
pgbackrest_method: minio # pgbackrest repo method: local,minio,[user-defined...]
# if you want to use minio as backup repo instead of local fs, uncomment minio related lines
# don't forget to configure pgbackrest_repo and change credentials there!
#pgbackrest_method: minio
# if disabled, original /etc/yum.repos.d will be kept
repo_remove: true # remove existing repo on admin node during repo bootstrap
node_repo_remove: true # remove existing node repo for node managed by pigsty
# WARNING: CHANGE THESE PASSWORDS
#grafana_admin_username: admin
grafana_admin_password: pigsty
#pg_admin_username: dbuser_dba
pg_admin_password: DBUser.DBA
#pg_monitor_username: dbuser_monitor
pg_monitor_password: DBUser.Monitor
#pg_replication_username: replicator
pg_replication_password: DBUser.Replicator
#patroni_username: postgres
patroni_password: Patroni.API
#haproxy_admin_username: admin
haproxy_admin_password: pigsty
# this config template assume you are using pre-packed offline packages
# If you wish to download upstream yum packages directly from internet,
# consider using ad hoc `el7.yml`, `el8.yml`, `el9.yml` config instead.
...not sure how to config the pgsodium.enable_event_trigger = 'off';
Michael2008S
commented
7 months ago 使用下面方法disable enable_event_trigger 可以运行：
To set up pgsodium.enable_event_trigger = 'off', you need to modify the configuration of your PostgreSQL database where the pgsodium extension is installed. pgsodium is an extension for PostgreSQL that provides cryptographic functions. Here's how you can set the enable_event_trigger setting to 'off':
Access the PostgreSQL Command Line: You need to have administrative access to the PostgreSQL server. Connect to your PostgreSQL database using a command-line tool like psql or a graphical interface such as pgAdmin.
Check if pgsodium is Installed: Before changing any settings, ensure that the pgsodium extension is installed in your database. You can check this by running the following SQL query:
SELECT * FROM pg_extension WHERE extname = 'pgsodium';Modify the Configuration: You can change the pgsodium.enable_event_trigger setting by executing the following SQL command:
ALTER DATABASE supa SET pgsodium.enable_event_trigger TO 'off';Replace your_database_name with the name of your database.
Reload the Configuration: After changing the setting, you may need to reload the database configuration to apply the changes. This can be done with the following SQL command:
SELECT pg_reload_conf();Verify the Setting: After reloading the configuration, you can verify that the setting has been applied by querying the current setting:
SHOW pgsodium.enable_event_trigger;Restart the PostgreSQL Service (if necessary): In some cases, you might need to restart the PostgreSQL service for the changes to take effect. This depends on your PostgreSQL setup and environment.
Remember that altering database configurations can have other effects, so make sure you understand the implications of this change, especially if you're working in a production environment.
Vonng
commented
5 months ago Add notice to the supabase setup doc: https://github.com/Vonng/pigsty/tree/master/app/supabase#database
docoker compose 执行与报错信息：
在pgadmin4 上查看到的扩展安装情况：
操作系统信息：