Database

Logflare

source: documentation: author: supabase tags:

  • native
  • official

Logflare

Logflare is a centralized web-based log management solution to easily access Cloudflare, Vercel & Elixir logs.

The Logflare Wrapper allows you to read data from Logflare endpoints within your Postgres database.

Preparation

Before you get started, make sure the wrappers extension is installed on your database:


_10
create extension if not exists wrappers with schema extensions;

and then create the foreign data wrapper:


_10
create foreign data wrapper logflare_wrapper
_10
handler logflare_fdw_handler
_10
validator logflare_fdw_validator;

Secure your credentials (optional)

By default, Postgres stores FDW credentials inide pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.


_10
-- Save your Logflare API key in Vault and retrieve the `key_id`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'logflare',
_10
'YOUR_SECRET'
_10
)
_10
returning key_id;

Connecting to Logflare

We need to provide Postgres with the credentials to connect to Logflare, and any additional options. We can do this using the create server command:


_10
create server logflare_server
_10
foreign data wrapper logflare_wrapper
_10
options (
_10
api_key_id '<key_ID>' -- The Key ID from above.
_10
);

Creating Foreign Tables

The Logflare Wrapper supports data reads from Logflare's endpoints.

IntegrationSelectInsertUpdateDeleteTruncate
Logflare✅❌❌❌❌

For example:


_10
create foreign table my_logflare_table (
_10
id bigint,
_10
name text,
_10
_result text
_10
)
_10
server logflare_server
_10
options (
_10
endpoint '9dd9a6f6-8e9b-4fa4-b682-4f2f5cd99da3'
_10
);

Meta column

You can define a specific meta column _result (data type: text) in the foreign table. It will store the whole result record in JSON string format, so you can extract any fields from it using Postgres JSON queries like _result::json->>'foo'. See more examples below.

Query parameters

Logflare endpoint query parameters can be passed using specific parameter columns like _param_foo and _param_bar. See more examples below.

Foreign table options

The full list of foreign table options are below:

  • endpoint - Logflare endpoint UUID or name, required.

Query Pushdown Support

This FDW doesn't support query pushdown.

Examples

Some examples on how to use Logflare foreign tables.

Basic example

Assume the Logflare endpoint response is like below:


_10
[
_10
{
_10
"id": 123,
_10
"name": "foo"
_10
}
_10
]

Then we can define a foreign table like this:


_11
create foreign table people (
_11
id bigint,
_11
name text,
_11
_result text
_11
)
_11
server logflare_server
_11
options (
_11
endpoint '9dd9a6f6-8e9b-4fa4-b682-4f2f5cd99da3'
_11
);
_11
_11
select * from people;

Query parameters example

Suppose the Logflare endpoint accepts 3 parameters:

  1. org_id
  2. iso_timestamp_start
  3. iso_timestamp_end

And its response is like below:


_10
[
_10
{
_10
"db_size": "large",
_10
"org_id": "123",
_10
"runtime_hours": 21.95,
_10
"runtime_minutes": 1317
_10
}
_10
]

We can define a foreign table and parameter columns like this:


_14
create foreign table runtime_hours (
_14
db_size text,
_14
org_id text,
_14
runtime_hours numeric,
_14
runtime_minutes bigint,
_14
_param_org_id bigint,
_14
_param_iso_timestamp_start text,
_14
_param_iso_timestamp_end text,
_14
_result text
_14
)
_14
server logflare_server
_14
options (
_14
endpoint 'my.custom.endpoint'
_14
);

and query it with parameters like this:


_10
select
_10
db_size,
_10
org_id,
_10
runtime_hours,
_10
runtime_minutes
_10
from
_10
runtime_hours
_10
where _param_org_id = 123
_10
and _param_iso_timestamp_start = '2023-07-01 02:03:04'
_10
and _param_iso_timestamp_end = '2023-07-02';