You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
A complimentary guide was made for the Postgres logs
Navigating the API logs:
The Database API is powered by a PostgREST web-server, recording every request to the API Edge Network logs. To precisely navigate them, use the Log Explorer. These logs are managed through Logflare and can be queried with a subset of BigQuery SQL syntax.
The log table that contains API requests is edge_logs.
Notably, it contains:
field
description
event_message
the log's message
timestamp
time event was recorded
request metadata
metadata about the REST request
response metadata
metadata about the REST response
The request and response columns are arrays in the metadata field and must be unnested. This is done with a cross join.
Unnesting example
select-- the event message does not require unnesting
event_message,
-- unnested status_code column from metadata.response field
status_code
from
edge_logs
-- Unpack data stored in the 'metadata' fieldcross join unnest(metadata) AS metadata
-- After unpacking the 'metadata' field, extract the 'response' field from itcross join unnest(response) AS response;
The most useful fields for debugging are:
NOTE: not every field is included below. For a full list, check the API Edge field reference in the Log Explorer
select
city
from
edge_logs
-- Unpack 'metadata' fieldcross join unnest(metadata) AS metadata
-- unpack 'request' from 'metadata'cross join unnest(request) AS request;
-- unpack 'cf' from 'request'cross join unnest(cf) AS cf;
IP and browser/environment data:
Suggested use cases:
Detecting request behavior from IP
Detecting abuse by IP
Detecting errors by user_agent
Column
Description
Sample value
request.headers.cf_connecting_ip
Requester's IP
80.81.18.138
request.headers.user_agent
Requester's browser or app environment
Mozilla/5.0 (Linux; Android 11; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Mobile Safari/537.36
Unnesting example:
select
cf_connecting_ip
from
edge_logs
-- Unpack 'metadata' fieldcross join unnest(metadata) AS metadata
-- unpack 'request' from 'metadata'cross join unnest(request) AS request;
-- unpack 'headers' from 'request'cross join unnest(headers) AS headers;
query type and formatting data:
Suggested use cases:
identify problematic queries
identify unusual behavior by authenticated users
Column
Description
Sample value
request.method
Request Method (PATCH, GET, PUT...)
GET
request.url
Request URL, which contains the PostgREST formatted query
You can use regex (Advanced Regex Guide) to find the objects related to your query. Try isolating by:
function names
column names
table names
query methods (select, insert, ...)
Example:
select
cast(timestampas datetime) astimestamp,
status_code,
url,
event_message
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(response) AS request;
cross join unnest(response) AS response;
where-- find all errors
status_code >=400and-- find queries featuring the a specific <table_name> and <column_name>
(
regexp_contains(url, '<table_name>')
and
regexp_contains(event_message, '<column_name1>|<column_name2>')
)
However, some errors that are reported through the Database API occur at the PostgreSQL level. If it is not clear which error occurred you should reference the timestamp of the error and try to see if you can find it in the Postgres logs.
select
cast(postgres_logs.timestampas datetime) astimestamp,
error_severity,
user_name,
query,
detail,
sql_state_code,
event_message
from postgres_logs
cross join unnest(metadata) as metadata
cross join unnest(metadata.parsed) as parsed
where-- filter only for error events
regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')
and-- All DB API requests are registered as the authenticator roleparsed.user_name='authenticator'and-- find failed queries featuring the function <function_name>
regexp_contains(parsed.query, '<function_name>')
and-- limit the time of the search to be around the time of the failed API requestpostgres_logs.timestamp between '2024-04-15 10:50:00'AND'2024-04-15 10:50:27'order bytimestampdesclimit100;
Like PostgREST, PostgreSQL has a reference table for interpreting error codes.
PostgREST server and Cloudflare errors
In some cases, errors may emerge because of Cloudflare or PostgREST server errors. For 500 and above errors, you may want to check your PostgREST logs and the Cloudflare docs.)
Practical examples:
Find All Errors:
select
cast(timestampas datetime) astimestamp,
status_code,
event_message,
pathfrom edge_logs
cross join unnest(metadata) as metadata
cross join unnest(response) AS response
cross join unnest(request) AS request
where-- find all errors
status_code >=400and-- only look at DB API
regexp_contains(path, '^/rest/v1/');
Group errors by path and code:
select
status_code,
path,
count(path) as reoccurrence_per_path,
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(response) AS response
cross join unnest(request) AS request
where-- find all errors
status_code >=400and-- only look at DB API
regexp_contains(path, '^/rest/v1/')
group bypath, status_code
order by reoccurrence_per_path;
Find requests by region:
selectpath,
region,
count(region) as region_count
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(request) as request
cross join unnest(cf) as cf
where-- only look at DB API
regexp_contains(path, '^/rest/v1/')
group by region, pathorder by requester_region_count;
Find total requests by IP:
select
cf_connecting_ip as ip,
count(cf_connecting_ip) as ip_count
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(request) as request
cross join unnest(cf) as cf
where-- only look at DB API
regexp_contains(path, '^/rest/v1/')
group by ip
order by ip_count;
Search frequented query paths by authenticated user:
select-- only available for front-end clients
auth_users,
path,
count(auth_users) as ip_count
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(request) as request
cross join unnest(sb) as sb
where-- only look at DB API
regexp_contains(path, '^/rest/v1/')
group by auth_users, path;
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Navigating the API logs:
The Database API is powered by a PostgREST web-server, recording every request to the API Edge Network logs. To precisely navigate them, use the Log Explorer. These logs are managed through Logflare and can be queried with a subset of BigQuery SQL syntax.
The log table that contains API requests is
edge_logs
.Notably, it contains:
The request and response columns are arrays in the metadata field and must be unnested. This is done with a
cross join
.Unnesting example
The most useful fields for debugging are:
Request object
Cloudflare geographic data:
Suggested use cases:
Unnesting example:
IP and browser/environment data:
Suggested use cases:
Unnesting example:
query type and formatting data:
Suggested use cases:
Unnesting example:
Response object
Status code:
Suggested use cases:
Unnesting example:
Finding errors
API Level errors
The
metadata.request.url
contains PostgREST formatted queries.For example, the following call to the JS client:
translates to calling the following endpoint:
You can use regex (Advanced Regex Guide) to find the objects related to your query. Try isolating by:
Example:
PostgREST has an error reference table that you can use to interpret status codes.
Database-level errors
However, some errors that are reported through the Database API occur at the PostgreSQL level. If it is not clear which error occurred you should reference the timestamp of the error and try to see if you can find it in the Postgres logs.
Like PostgREST, PostgreSQL has a reference table for interpreting error codes.
PostgREST server and Cloudflare errors
In some cases, errors may emerge because of Cloudflare or PostgREST server errors. For 500 and above errors, you may want to check your PostgREST logs and the Cloudflare docs.)
Practical examples:
Find All Errors:
Group errors by path and code:
Find requests by region:
Find total requests by IP:
Search frequented query paths by authenticated user:
Beta Was this translation helpful? Give feedback.
All reactions