mirror of
https://github.com/supabase/supabase.git
synced 2026-09-22 13:37:53 +08:00
TheOtherBrian1
b0de9dd7a6
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES ## What kind of change does this PR introduce? docs update ## What is the current behavior? No docs on how to interpret and configure PG logs ## What is the new behavior? Adds docs on how to interpret and manage PG logs ## Additional context Related Linear issue: - https://linear.app/supabase/issue/DEBUG-131/create-docs-outlining-all-log-settings <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Documentation** * Added a new guide for customizing Supabase-hosted Postgres logging. * Documented available log settings with default values, plus SQL examples to inspect effective settings and role-specific overrides. * Covered configuration options (CLI, Management API, SQL), including precedence rules, role-level override/reset examples, and restart guidance for scheduled logging. * Updated the docs navigation with a new “Postgres log configuration” entry. * **Chores** * Updated the MDX spelling allow list to include “subfield”. <!-- end of auto-generated comment: release notes by coderabbit.ai --> --------- Co-authored-by: github-actions[bot] <41898282+github-actions[bot]@users.noreply.github.com> Co-authored-by: coderabbitai[bot] <136622811+coderabbitai[bot]@users.noreply.github.com> Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com> Co-authored-by: Ali Waseem <waseema393@gmail.com>
936 lines
30 KiB
Plaintext
936 lines
30 KiB
Plaintext
---
|
|
id: 'postgres-log-configuration'
|
|
title: 'Postgres log configurations'
|
|
slug: 'postgres-log-configuration'
|
|
description: 'Customizing Postgres log configurations'
|
|
subtitle: 'Configure database log settings to better suit your observability and compliance requirements'
|
|
---
|
|
|
|
## Available log settings:
|
|
|
|
The table lists _configurable_ log settings. See each setting's section for details.
|
|
|
|
| Setting | Category | Default | Set By |
|
|
| :------------------------------------------------------------- | :------------------ | :-------: | :-------------------- |
|
|
| [`log_autovacuum_min_duration`](#logautovacuumminduration) | Background Activity | `10min` | `API` + `CLI` |
|
|
| [`log_checkpoints`](#logcheckpoints) | Background Activity | `true` | `API` + `CLI` |
|
|
| [`log_lock_waits`](#loglockwaits) | Background Activity | `true` | `API` + `CLI` + `SQL` |
|
|
| [`log_recovery_conflict_waits`](#logrecoveryconflictwaits) | Background Activity | `false` | `API` + `CLI` |
|
|
| [`log_startup_progress_interval`](#logstartupprogressinterval) | Background Activity | `10000ms` | `API` + `CLI` |
|
|
| [`log_temp_files`](#logtempfiles) | Background Activity | `-1` | `API` + `CLI` + `SQL` |
|
|
| [`log_connections`](#logconnections) | Network Monitoring | `false` | `API` + `CLI` |
|
|
| [`log_disconnections`](#logdisconnections) | Network Monitoring | `false` | `API` + `CLI` |
|
|
| [`cron.log_statement`](#cronlogstatement) | Query Activity | `true` | `API` + `CLI` |
|
|
| [`auto_explain.*`](#autoexplain) | Query Activity | `10000ms` | `SQL` |
|
|
| [`log_duration`](#logduration) | Query Activity | `false` | `SQL` |
|
|
| [`log_min_duration_statement`](#logmindurationstatement) | Query Activity | `-1` | `SQL` |
|
|
| [`log_min_error_statement`](#logminerrorstatement) | Query Activity | `error` | `SQL` |
|
|
| [`log_min_messages`](#logminmessages) | Query Activity | `warning` | `SQL` |
|
|
| [`log_statement`](#logstatement) | Query Activity | `ddl` | `SQL` |
|
|
| [`pgaudit.*`](#pgaudit) | Query Activity | `N/A` | `SQL` |
|
|
|
|
To view log settings for your project, you can run:
|
|
|
|
```sql
|
|
select
|
|
name,
|
|
setting,
|
|
unit,
|
|
short_desc,
|
|
extra_desc,
|
|
context,
|
|
enumvals,
|
|
reset_val,
|
|
case
|
|
when sourcefile = '/etc/postgresql-custom/custom-overrides.conf' then 'set by CLI/API'
|
|
else 'platform default'
|
|
end as configuration_source
|
|
from "pg_settings"
|
|
where
|
|
category in ('Reporting and Logging / When to Log', 'Reporting and Logging / What to Log')
|
|
or (name like 'auto_explain.%' or name like 'pgaudit.%' or name = 'cron.log_statement');
|
|
```
|
|
|
|
To view settings targeting specific database roles, you can run:
|
|
|
|
```sql
|
|
select
|
|
rolname,
|
|
rolconfig
|
|
from pg_roles
|
|
where
|
|
rolname in (
|
|
'anon',
|
|
'authenticated',
|
|
'postgres',
|
|
'service_role'
|
|
-- ,<ANY CUSTOM ROLES>
|
|
);
|
|
```
|
|
|
|
## Configuring log settings
|
|
|
|
There are three potential ways to change log settings:
|
|
|
|
- [Supabase CLI](/docs/guides/local-development/cli/getting-started)
|
|
- [Supabase Management API](/docs/reference/api/v1-update-postgres-config)
|
|
- **SQL commands**
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configure with the CLI"
|
|
id="item-1"
|
|
>
|
|
|
|
Install the [Supabase CLI](/docs/guides/local-development/cli/getting-started) then update the relevant setting:
|
|
|
|
```sh
|
|
supabase --experimental \
|
|
postgres-config update --config log_lock_waits=true \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
To remove overrides, you can run:
|
|
|
|
```sh
|
|
supabase --experimental \
|
|
postgres-config delete --config log_lock_waits,log_disconnections,... \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
</AccordionItem>
|
|
|
|
<AccordionItem
|
|
header="Configure with the management API"
|
|
id="item-2"
|
|
>
|
|
|
|
Before using the API, generate an [access token](/dashboard/account/tokens), then update the desired setting:
|
|
|
|
```sh
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"LOG_SETTING": VALUE
|
|
}'
|
|
```
|
|
|
|
</AccordionItem>
|
|
|
|
<AccordionItem
|
|
header="Configure with SQL"
|
|
id="item-3"
|
|
>
|
|
|
|
<Admonition type="caution">
|
|
|
|
Log settings configured directly with SQL take precedence over values set by the Management API and CLI.
|
|
|
|
</Admonition>
|
|
|
|
Supabase projects include the [supautils extension](/blog/roles-postgres-hooks), which grants the postgres role authority over superuser-only log settings.
|
|
|
|
As a result, you can configure _certain_ log settings directly with SQL at the `role` and `connection` levels:
|
|
|
|
```sql
|
|
-- impacts the role
|
|
alter role postgres set log_statement = 'none';
|
|
|
|
-- impacts just the live connection
|
|
set log_statement = 'none';
|
|
```
|
|
|
|
To remove a role level override, you can reset the value with the `default` keyword:
|
|
|
|
```sql
|
|
alter role postgres set log_statement = default;
|
|
```
|
|
|
|
<Admonition type="note">
|
|
|
|
When updating log settings for Data API roles (anon, authenticator, or service_role), reload PostgREST to apply the changes:
|
|
|
|
```sql
|
|
NOTIFY pgrst, 'reload config';
|
|
```
|
|
|
|
</Admonition>
|
|
|
|
</AccordionItem>
|
|
|
|
</Accordion>
|
|
|
|
## Background activity
|
|
|
|
Logs the activity of Postgres background processes and utilities. Helps diagnose and detect performance and operational issues.
|
|
|
|
### `log_autovacuum_min_duration`
|
|
|
|
`update` and `delete` commands leave behind obsolete row versions to support rollbacks and concurrent queries. A background process called the [Autovacuum](https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM) permanently removes the rows in batch jobs at a later point. The setting logs Autovacuum when they run longer than the limit.
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring vacuum activity
|
|
- Identifying resource strain, such as [IO usage](/docs/guides/platform/manage-your-usage/disk-iops), caused by vacuums
|
|
|
|
**Example logs:**
|
|
|
|
```sh
|
|
# records tables vacuumed
|
|
automatic vacuum of table "postgres.public.vac_test": index scans: 0
|
|
automatic analyze of table "postgres.public.vac_test"
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"log_autovacuum_min_duration": "0ms"
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config log_autovacuum_min_duration=10ms \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
</AccordionItem>
|
|
|
|
</Accordion>
|
|
|
|
### `log_checkpoints`
|
|
|
|
Checkpoints are background operations that write modified data from memory to disk. It enables Postgres to discard [WAL files](/docs/guides/database/replication#write-ahead-log-wal) that otherwise must be retained for data recovery and replication.
|
|
|
|
The setting records automatic checkpoint events.
|
|
|
|
**Useful for:**
|
|
|
|
- Measuring checkpoint write volume
|
|
- Identifying excessive checkpoint-related disk activity
|
|
- Detecting read-replica issues
|
|
- Deciding whether checkpoint settings should be adjusted
|
|
|
|
**Example logs:**
|
|
|
|
```sh
|
|
# Monitoring checkpointer activity
|
|
checkpoint starting: time
|
|
checkpoint complete: wrote 405563 buffers (6.4%); 0 WAL file(s) added, 0 removed, 465 recycled; write=269.656 s, sync=3.570 s, total=274.133 s; sync files=2393, longest=0.375 s, average=0.002 s; distance=6635965 kB, estimate=7953512 kB
|
|
```
|
|
|
|
```sh
|
|
# Monitoring replay activity from read replicas
|
|
restartpoint starting: time
|
|
recovery restart point at 0/B837D6F0
|
|
restartpoint complete: wrote 266 buffers (0.4%); 0 WAL file(s) added, 1 removed, 0 recycled; write=25.760 s, sync=0.004 s, total=25.773 s; sync files=20, longest=0.003 s, average=0.001 s; distance=19779 kB, estimate=565114 kB; lsn=0/B837D748, redo lsn=0/B837D6F0
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"log_checkpoints": false
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config log_checkpoints=true \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
</AccordionItem>
|
|
|
|
</Accordion>
|
|
|
|
### `log_lock_waits`
|
|
|
|
Logs when operations are blocked by database locks for more than `1s`. For more information on lock management, reference [postgreslocksexplained.com](https://postgreslocksexplained.com/locks/concept).
|
|
|
|
**Useful for:**
|
|
|
|
- Identifying queries that are blocked by locks
|
|
- Identifying which queries are blocking
|
|
- Measuring how long queries remain blocked
|
|
|
|
**Example logs:**
|
|
|
|
```sh
|
|
# records when a process is waiting on a lock for 1+s
|
|
process 1017208 still waiting for "lock_type" on relation 75874 of database 5 after 1001.872 ms
|
|
```
|
|
|
|
```sh
|
|
# records when a process is finally able to claim its lock
|
|
process 1007982 acquired "lock_type" on transaction 445264 after 2000.880 ms
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"log_lock_waits": false
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config log_lock_waits=true \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
```sql name=SQL
|
|
alter role "postgres" set log_lock_waits to true;
|
|
```
|
|
|
|
</AccordionItem>
|
|
|
|
</Accordion>
|
|
|
|
### `log_recovery_conflict_waits`
|
|
|
|
If a read-replica is acting on data that is being modified/discarded by the primary, then it may wait to determine if the data should be available or not before responding. The setting `log_recovery_conflict_waits` determines if the replica should report waits that last more than `1s`.
|
|
|
|
**Useful for:**
|
|
|
|
- Detecting operations that interfere with replica queries
|
|
- Detecting operations that may cause replication lag
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
recovery still waiting after 1000.156 ms: recovery conflict on lock
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"log_recovery_conflict_waits": true
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config log_recovery_conflict_waits=true \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `log_startup_progress_interval`
|
|
|
|
When a server is recovering from a crash, it has to go through several checks before it becomes operational again. To provide more clarity about a recovery's progress, the setting causes Postgres to log its current startup task if it takes longer than the interval.
|
|
|
|
**Useful for:**
|
|
|
|
- Determining if a server is responsive during startup/recovery
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
syncing data directory (pre-fsync), elapsed time: 0.00 s, current path: ./base/4/13456
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"log_startup_progress_interval": "1s"
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config log_startup_progress_interval=1s \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `log_temp_files`
|
|
|
|
Some queries require sorting, hashing, or other memory-intensive operations. When these operations exceed the memory limits primarily managed by the [work_mem](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) and [hash_mem_multiplier](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-HASH-MEM-MULTIPLIER) settings, Postgres uses temporary files on disk to complete them.
|
|
|
|
When temp files larger than the `log_temp_files` limit are created, Postgres logs the event, helping identify queries that can benefit from memory tuning.
|
|
|
|
**Useful for:**
|
|
|
|
- Determining when the memory constraint settings should be adjusted
|
|
- Identifying disk strain caused by temp files
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
# records the creation of a temp file that is 8.33MB in size
|
|
temporary file: path "base/pgsql_tmp/pgsql_tmp306918.0", size 8331264
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"log_temp_files": "10kB"
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config log_temp_files=10MB \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
```sql name=SQL
|
|
alter role "postgres" set "log_temp_files" to '10kB';
|
|
```
|
|
|
|
</AccordionItem>
|
|
|
|
</Accordion>
|
|
|
|
## Network monitoring
|
|
|
|
Logs information about clients connecting/disconnecting from the database. Some insightful values that can be captured include:
|
|
|
|
- When a client first authenticated
|
|
- How long they were connected for
|
|
- Their IP address
|
|
|
|
### `log_connections`
|
|
|
|
Logs when a client establishes a new database connection, including connection receipt, authentication, and authorization.
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring successful authentication attempts
|
|
- Auditing database access
|
|
|
|
**Example logs:**
|
|
|
|
```sh
|
|
connection received: host=127.0.0.1
|
|
connection authorized: user=postgres database=postgres application_name=Supavisor auth_query
|
|
connection authenticated: identity="pgbouncer" method=scram-sha-256
|
|
```
|
|
|
|
<Admonition type="note">
|
|
|
|
The logged IP address is from the device directly communicating with Postgres. If you connect through [Supavisor](/docs/guides/database/connecting-to-postgres#poolers), the [dedicated pooler](/docs/guides/database/connecting-to-postgres#poolers), or the [Data API](/docs/guides/database/connecting-to-postgres#data-apis-and-client-libraries), those service IPs will appear instead of the original client.
|
|
|
|
</Admonition>
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"log_connections": true
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config log_connections=true \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `log_disconnections`
|
|
|
|
Logs when a database connection gracefully closes.
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring how long connections persist
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
disconnection: session time: 0:00:01.492 user=postgres database=postgres host=127.0.0.1
|
|
```
|
|
|
|
<Admonition type="note">
|
|
|
|
The logged IP address is from the device directly communicating with Postgres. If you connect through [Supavisor](/docs/guides/database/connecting-to-postgres#poolers), the [dedicated pooler](/docs/guides/database/connecting-to-postgres#poolers), or the [Data API](/docs/guides/database/connecting-to-postgres#data-apis-and-client-libraries), those service IPs will appear instead of the original client.
|
|
|
|
</Admonition>
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"log_disconnections": true
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config log_disconnections=true \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
## Query activity
|
|
|
|
<Admonition type="caution">
|
|
|
|
Logging a large amount of query activity can impact query performance and increase logging costs. Configure them with caution for debugging or mandatory compliance.
|
|
|
|
</Admonition>
|
|
|
|
Records queries or metadata about queries.
|
|
|
|
### `cron.log_statement`
|
|
|
|
Logs when the [pg_cron extension](/docs/guides/cron/install) starts a cron job.
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring successful cron job executions
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
cron job 1 starting: select 1
|
|
```
|
|
|
|
<Admonition type="note">
|
|
|
|
Beyond logs, [pg_cron](/docs/guides/cron/install) also records all cron executions in the [`cron.job_run_details`](https://github.com/citusdata/pg_cron#monitoring-jobs) table. Consider disabling `cron.log_statement` to instead monitor cron activity only in `cron.job_run_details` instead.
|
|
|
|
</Admonition>
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
<Admonition type="caution">
|
|
|
|
`cron.log_statement` requires a server restart to take effect, which results in a few seconds of downtime.
|
|
|
|
By default, the API and CLI automatically trigger a restart when updating this setting. The examples below use the `--no-restart` flag to defer the change until the server is restarted at a later time.
|
|
|
|
</Admonition>
|
|
|
|
```sh name=API
|
|
curl https://api.supabase.com/v1/projects/PROJECT_REF/config/database/postgres \
|
|
--request PUT \
|
|
--header 'Content-Type: application/json' \
|
|
--header 'Authorization: Bearer ACCESS_TOKEN' \
|
|
--data '{
|
|
"cron.log_statement": true,
|
|
"restart_database": false
|
|
}'
|
|
```
|
|
|
|
```sh name=CLI
|
|
supabase --experimental \
|
|
postgres-config update --config cron.log_statement=false --no-restart \
|
|
--project-ref <project-ref>
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `auto_explain.*`
|
|
|
|
[auto_explain](https://www.postgresql.org/docs/current/auto-explain.html) is a Postgres module that is installed on all Supabase projects. It logs the statements and [explain plans](https://www.postgresql.org/docs/current/sql-explain.html) of queries that took more than the setting's limit.
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring and optimizing slow queries
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
duration: 1661.934 ms plan: Query Text: SELECT * FROM example;
|
|
Seq Scan on example (cost=0.00..1443.00 rows=100000 width=36)
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
<Admonition type="note">
|
|
|
|
`auto_explain` is a family of configurations. The primary one is `auto_explain.log_min_duration`. However, there are other configs of note, such as `auto_explain.log_analyze` and `auto_explain.log_buffers` that control the details of the query plan recorded. Reference the module's [official docs](https://www.postgresql.org/docs/current/auto-explain.html) for more information.
|
|
|
|
</Admonition>
|
|
|
|
```sql name=SQL
|
|
alter role "postgres" set "auto_explain.log_min_duration" to '2s';
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `log_duration`
|
|
|
|
It logs the duration of all queries, but not the queries themselves.
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring query duration
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
duration: 0.599 ms
|
|
```
|
|
|
|
Note, even though the query will not be logged, the primary command associated with the query will be recorded in the `command` subfield:
|
|
|
|
```sh
|
|
...other subfields
|
|
command_tag: "SELECT"
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sql name=SQL
|
|
alter role "postgres" set "log_duration" to true;
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `log_min_duration_statement`
|
|
|
|
It is similar to `auto_explain.log_min_duration`, but it lighter weight. It only logs query statements that run longer than the setting's limit.
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring and optimizing slow queries
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
duration: 1.097 ms statement: select * from example_table limit 100;
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sql name=SQL
|
|
alter role "postgres" set "log_min_duration_statement" to '2s';
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `log_min_error_statement`
|
|
|
|
When an event is logged, beyond the primary message, multiple subfields are also captured, such as the [status code](https://www.postgresql.org/docs/current/errcodes-appendix.html). The `log_min_error_statement` field determines if the query responsible for the log should be recorded, too, under the `query` subfield.
|
|
|
|
If the event is equally or more severe than `log_min_error_statement`, the query will be captured. To view the varying severity levels, reference [log_min_messages](#logminmessages).
|
|
|
|
**Useful for:**
|
|
|
|
- Detecting what queries induced specific logs
|
|
- Detecting what queries induced a specific error
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
duplicate key value violates unique constraint "example_pkey"
|
|
...
|
|
# affiliated subfield
|
|
query: "insert into example (id) values (1), (1);
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sql name=SQL
|
|
alter role "postgres" set "log_min_error_statement" to 'error';
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `log_min_messages`
|
|
|
|
Determines what _query generated_ logs (not background or networking logs) are recorded based on severity level, as described in the table below.
|
|
|
|
As an example of how `log_min_messages` works, if the setting were changed to `error`, Postgres would stop recording logs with the severity levels `warning`, `notice`, `info`, and `debug1 ... debug5` events. However, it would continue recording all `error`, `log`, `fatal`, and `panic` occurrences.
|
|
|
|
| Severity | Description | Example log |
|
|
| :-------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
|
|
| **debug1 ... debug5** | Successively detailed debugging and server info, predominantly used by Postgres developers and extension maintainers. | `DEBUG1: rehashing catalog cache id 6...` |
|
|
| **info** | Information explicitly requested by the user during an operation. | `INFO: analyzing "public.example..."` <br/><br/> Example returned by the [`analyze verbose`](https://www.postgresql.org/docs/current/sql-analyze.html) command |
|
|
| **notice** | Helpful, non-essential information about automatic background actions. | `NOTICE: table "old_logs" does not exist, skipping` <br/><br/> Example returned by the [`drop table if exists`](https://www.postgresql.org/docs/current/sql-droptable.html) commands |
|
|
| **warning** | A query completed, but skipped requested actions. | `WARNING: no privileges were granted for "some_user"` <br/><br/> Example returned by the [`grant`](https://www.postgresql.org/docs/current/sql-grant.html) command |
|
|
| **error** | A specific query failed, but the overall database connection remains alive. | `ERROR: duplicate key value violates unique constraint "example_pkey"` |
|
|
| **log** | Operational events. Usually generated by [background activity log settings](/docs/guides/database/postgres/postgres-log-config#background-activity) or by [database functions](/docs/guides/database/functions?queryGroups=language&language=js#debugging-functions) | `LOG: connection received...` |
|
|
| **fatal** | An error that causes a database connection to abruptly terminate. | `FATAL: terminating connection due to administrator command` |
|
|
| **panic** | A critical, system-wide failure that forces the database to shut down and crash-recover. | `PANIC: could not locate a valid checkpoint record at 0/61013608` |
|
|
|
|
<Admonition type="note">
|
|
|
|
Note: `log` is considered more severe than `warning` and `error`.
|
|
|
|
</Admonition>
|
|
|
|
**Useful for:**
|
|
|
|
- Controlling what _query generated logs_ are recorded overall
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem
|
|
header="Configuration examples"
|
|
id="item-1"
|
|
>
|
|
|
|
```sql name=SQL
|
|
alter role "postgres" set "log_min_messages" to 'log';
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `log_statement`
|
|
|
|
<Admonition type="caution">
|
|
|
|
To avoid excessive logging that can impact performance, be mindful of the potential impact when configuring log_statement to `mod` or `all`. Consider using [`pgaudit`](#pgaudit) over `log_statement` for more granular control over query logging.
|
|
|
|
</Admonition>
|
|
|
|
Logs queries that match the configured action type:
|
|
|
|
- `ddl`: Log all `alter`, `drop`, and `create` commands
|
|
- `all`: Log all queries
|
|
- `mod`: Log `update`, `insert`, `delete`, and `merge` commands
|
|
- `none`: Log nothing (disables the setting)
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring queries on your platform
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
# Logging a select query
|
|
statement: select * from testing WHERE id = 5 limit 100;
|
|
```
|
|
|
|
<Accordion
|
|
type="default"
|
|
chevronAlign="right"
|
|
justified
|
|
size="medium"
|
|
className="text-foreground-light mt-8 mb-6"
|
|
>
|
|
<AccordionItem header="Configuration examples" id="item-1">
|
|
|
|
```sql name=SQL
|
|
alter role "postgres" set "log_statement" to 'ddl';
|
|
```
|
|
|
|
</AccordionItem>
|
|
</Accordion>
|
|
|
|
### `pgaudit.*`
|
|
|
|
A suite of log settings enabled by the [pgAudit extension](/docs/guides/database/extensions/pgaudit). Unlike `log_statement`, it allows you to monitor queries against specific tables, with a higher degree of granularity.
|
|
|
|
**Useful for:**
|
|
|
|
- Monitoring queries on your platform
|
|
|
|
**Example log:**
|
|
|
|
```sh
|
|
# Logging a DDL query
|
|
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(
|
|
id int,
|
|
name text,
|
|
description text
|
|
); <not logged>
|
|
```
|
|
|
|
**Configuration methods:**
|
|
|
|
Review the [pgAudit docs](/docs/guides/database/extensions/pgaudit) for more configuration details.
|
|
|
|
## Resources
|
|
|
|
- [Advanced Log Filtering](/docs/guides/observability/advanced-log-filtering)
|
|
- [Database Function Logging](/docs/guides/database/functions#general-logging)
|
|
- [Supabase Logging](/docs/guides/observability/logs)
|