### Configure pg_profile Owner and Dependencies

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Sets up the 'profile_usr' role as the pg_profile owner, creates a dedicated schema for it, and installs the dblink and pg_profile extensions. This example demonstrates a more complex setup where each extension resides in its own schema.

```sql
\c postgres postgres
CREATE SCHEMA dblink;
CREATE EXTENSION dblink SCHEMA dblink;
CREATE USER profile_usr with password 'profile_pwd';
GRANT USAGE ON SCHEMA dblink TO profile_usr;
CREATE SCHEMA profile AUTHORIZATION profile_usr;
\c postgres profile_usr
CREATE EXTENSION pg_profile SCHEMA profile;
```

--------------------------------

### Install pg_profile Extension and Dependencies (SQL)

Source: https://context7.com/zubkov-andrei/pg_profile/llms.txt

This SQL script installs the necessary pg_profile extension along with its required dependencies, dblink and pg_stat_statements. It also shows how to install optional extensions like pg_stat_kcache and pg_wait_sampling for enhanced metrics. Finally, it demonstrates how to verify the installation and take the first statistics sample.

```sql
-- Required: Install dependencies
CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Install pg_profile
CREATE EXTENSION pg_profile;

-- Optional: Additional statistics extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;      -- OS-level resource usage
CREATE EXTENSION IF NOT EXISTS pg_wait_sampling;    -- Wait event sampling

-- Verify installation
SELECT * FROM show_servers();

-- The 'local' server is created automatically
-- Take first sample to start collecting statistics
SELECT take_sample();
```

--------------------------------

### Build and Install pg_profile from Source

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/README.md

Compiles and installs the pg_profile extension using PGXS. This process requires PostgreSQL development packages. The `installcheck` step verifies the installation.

```bash
sudo make USE_PGXS=y install && make USE_PGXS=y installcheck
```

--------------------------------

### Generate Report using psql (Sample IDs)

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Example of generating a report using the psql command-line tool with sample identifiers. The '-Aqtc' flags ensure clean output, and '-o' redirects the output to an HTML file.

```bash
$ psql -Aqtc "SELECT profile.get_report(480,482)" -o report_480_482.html
```

```bash
$ psql -Aqtc "SELECT profile.get_report('omega',12,14)" -o report_omega_12_14.html
```

--------------------------------

### Install pg_profile Extension

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Installs the pg_profile extension and its dependencies (dblink, pg_stat_statements) into the default 'public' schema. This is the simplest installation method.

```sql
CREATE EXTENSION dblink;
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_profile;
```

--------------------------------

### Install pg_profile in a Custom Schema

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Installs pg_profile and its dependencies into a dedicated 'profile' schema. This is the recommended approach for better organization of extension objects.

```sql
CREATE EXTENSION dblink;
CREATE EXTENSION pg_stat_statements;
CREATE SCHEMA profile;
CREATE EXTENSION pg_profile SCHEMA profile;
```

--------------------------------

### Schedule Subsamples with psql and watch

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

This example demonstrates how to schedule frequent subsample calls using the psql command-line tool with the `watch` command. It pipes the SQL command to psql and redirects output to /dev/null. The `watch 15` command executes the `take_subsample()` function every 15 seconds.

```bash
echo "select take_subsample(); \watch 15" | psql &> /dev/null
```

--------------------------------

### Taking Database Samples

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Initiates the process of taking samples from enabled servers. It's recommended to take samples periodically, for example, every hour or half-hour. The `take_sample()` function returns 'OK' for successful samples and error messages for failures.

```APIDOC
## POST /take_sample

### Description
Initiates the process of taking samples from enabled database servers. This function is crucial for gathering performance metrics.

### Method
POST

### Endpoint
/take_sample

### Parameters
#### Query Parameters
- **server_id** (integer) - Optional - The ID of the specific server to take a sample from. If not provided, samples are taken from all enabled servers.

### Request Body
This endpoint does not require a request body.

### Request Example
```json
{
  "message": "Initiating sample collection for all enabled servers."
}
```

### Response
#### Success Response (200)
- **server** (string) - The name or identifier of the server.
- **result** (string) - The status of the sample collection ('OK' or an error message).
- **elapsed** (string) - The time taken to collect the sample.

#### Response Example
```json
[
  {
    "server": "ok_node",
    "result": "OK",
    "elapsed": "00:00:00.48"
  },
  {
    "server": "fail_node",
    "result": "could not establish connection\nSQL statement \"SELECT dblink_connect('server_connection',server_connstr)\"  +
PL/pgSQL function take_sample(integer) line 69 at PERFORM                  +
PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment+SQL function \"take_sample\" statement 1                  +
FATAL:  database \"nodb\" does not exist",
    "elapsed": "00:00:00"
  }
]
```
```

--------------------------------

### Install pg_profile Extension Files

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/README.md

Extracts the pg_profile extension files to the PostgreSQL extensions directory. Ensure you are using the correct pg_config path for your PostgreSQL installation.

```bash
# tar xzf pg_profile-<version>.tar.gz --directory $(pg_config --sharedir)/extension
```

--------------------------------

### Generate Regular Reports in pg_profile

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Provides examples of generating regular reports using the get_report() function with different parameters like sample identifiers, time ranges, and baselines. Reports are generated in HTML markup.

```sql
SELECT * FROM get_report(start_id => 1, end_id => 10);
SELECT * FROM get_report(time_range => '[2023-01-01 10:00:00+00, 2023-01-01 12:00:00+00]');
SELECT * FROM get_report(baseline => 'my_baseline');
```

--------------------------------

### Generate SQL Script for Manual Installation

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/README.md

Generates a SQL script (`pg_profile--{version}.sql`) for manual creation of pg_profile objects. This is useful for environments like RDS where direct file system access is restricted.

```bash
make USE_PGXS=y sqlfile
```

--------------------------------

### Configure Collecting Role and Statistics Extensions

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Sets up schemas and installs statistics extensions (pg_stat_statements, pg_stat_kcache, pg_wait_sampling). It then creates the 'profile_collector' role, grants necessary privileges for statistics collection and resetting, and configures the connection string for pg_profile to use this role.

```sql
\c postgres postgres
CREATE SCHEMA pgss;
CREATE SCHEMA pgsk;
CREATE SCHEMA pgws;
CREATE EXTENSION pg_stat_statements SCHEMA pgss;
CREATE EXTENSION pg_stat_kcache SCHEMA pgsk;
CREATE EXTENSION pg_wait_sampling SCHEMA pgws;
CREATE USER profile_collector with password 'collector_pwd';
GRANT pg_read_all_stats TO profile_collector;
GRANT USAGE ON SCHEMA pgss TO profile_collector;
GRANT USAGE ON SCHEMA pgsk TO profile_collector;
GRANT USAGE ON SCHEMA pgws TO profile_collector;
GRANT EXECUTE ON FUNCTION pgsk.pg_stat_kcache_reset TO profile_collector;
GRANT EXECUTE ON FUNCTION pgss.pg_stat_statements_reset TO profile_collector;
GRANT EXECUTE ON FUNCTION pgws.pg_wait_sampling_reset_profile TO profile_collector;

\c postgres profile_usr
SELECT profile.set_server_connstr('local','dbname=postgres port=5432 host=localhost user=profile_collector password=collector_pwd');
```

--------------------------------

### Generate Report using psql (Time Ranges)

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Examples of generating reports using psql with time ranges. This includes specifying a fixed time range and generating a report for the last 24 hours using the 'now()' function.

```bash
psql -Aqtc "select profile.get_report(tstzrange('2020-05-13 11:51:35+03','2020-05-13 11:52:18+03'))" -o report_range.html
```

```bash
psql -Aqtc "select profile.get_report(tstzrange(now() - interval '1 day',now()))" -o last24h_report.html
```

--------------------------------

### Display Server Relation Size Sampling Policies (SQL)

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Retrieves and displays the currently configured relation size collection policies for all servers managed by pg_profile. The output shows the server name, window start and end times, window duration, and the sample interval.

```sql
SELECT * FROM show_servers_size_sampling();
```

--------------------------------

### Systemd Service for Scheduling Subsamples

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

This systemd service unit file configures a background service to run the pg_profile subsampling function periodically. It uses a shell command to execute `take_subsample()` via psql every 15 seconds, similar to the psql `watch` example, but managed by systemd for reliability. The service is set to run as the 'postgres' user and group.

```systemd
[Unit]
Description=pg_profile subsampling unit

[Service]
Type=simple
ExecStart=/bin/sh -c 'echo "select take_subsample(); \watch 15" | /path/to/psql -qo /dev/null'
User=postgres
Group=postgres

[Install]
WantedBy=multi-user.target
```

--------------------------------

### Take Sample (All Enabled Servers)

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Collects a sample for all enabled servers sequentially. Returns a table with server name, result, and elapsed time.

```APIDOC
## POST /api/take_sample

### Description
Collects a sample for all *enabled* servers sequentially. Server samples are taken one by one.

### Method
POST

### Endpoint
/api/take_sample

### Parameters
#### Query Parameters
None

#### Request Body
None

### Request Example
```json
{
  "message": "Initiate sample collection for all enabled servers."
}
```

### Response
#### Success Response (200)
- **server** (string) - The name of the server.
- **result** (string) - 'OK' if the sample was taken successfully, or error text if an exception occurred.
- **elapsed** (interval) - The time elapsed while taking the sample for the server.

#### Response Example
```json
[
  {
    "server": "server1",
    "result": "OK",
    "elapsed": "00:00:05"
  },
  {
    "server": "server2",
    "result": "Error: Connection refused",
    "elapsed": "00:00:01"
  }
]
```
```

--------------------------------

### Execute take_sample() and View Results

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

This code snippet shows how to execute the `take_sample()` function and display its results, including server status and elapsed time. It illustrates the expected output format, differentiating between successful ('OK') and failed sample collections, with detailed error messages for failures.

```sql
select * from take_sample();
```

--------------------------------

### Create PostgreSQL Server with pg_profile

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Creates a new server description within pg_profile. Requires a unique server name and connection string. Optional parameters include enabling the server, setting sample age, and adding a description. The server name must be unique.

```sql
SELECT profile.create_server('omega','host=name_or_ip dbname=postgres port=5432');
```

--------------------------------

### Update pg_profile Extension

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Updates the pg_profile extension to the latest version. This command should be run after installing new extension files.

```sql
ALTER EXTENSION pg_profile UPDATE;
```

--------------------------------

### Update pg_profile Extension

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/README.md

Updates an existing pg_profile extension to a newer version. This command should be run after installing the new extension files (Step 1) and preserves historic data.

```sql
postgres=# ALTER EXTENSION pg_profile UPDATE;
```

--------------------------------

### Show Available Samples

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

This function, `show_samples()`, is used to retrieve a list of all existing samples within the pg_profile repository. The output includes information about detected statistics reset times for each sample.

```sql
show_samples()
```

--------------------------------

### Take a Sample with pg_profile

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Executes the take_sample() function to collect performance statistics. This function requires the 'profile_usr' role to be configured correctly and have access to the connection string set by set_server_connstr().

```sql
\c postgres profile_usr
SELECT * FROM take_sample();
```

--------------------------------

### Schedule Sample Taking with Cron

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

This snippet demonstrates how to schedule the `take_sample()` function using a cron-like tool for periodic sample collection. It highlights the basic cron syntax for a 30-minute interval and points out the lack of error checking in this basic implementation.

```shell
*/30 * * * *   psql -c 'SELECT profile.take_sample()' > /dev/null 2>&1
```

--------------------------------

### Show Samples

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Retrieves a table of existing samples for a specified server or the local server, for a given number of past days or all existing samples.

```APIDOC
## GET /api/show_samples

### Description
Returns a table containing existing samples for a specified server (defaults to the local server) for the last 'days' days, or all existing samples if 'days' is omitted.

### Method
GET

### Endpoint
/api/show_samples

### Parameters
#### Query Parameters
- **server_name** (string) - Optional - The name of the server. Defaults to 'local'.
- **days** (integer) - Optional - The number of past days to retrieve samples for. If omitted, all existing samples are returned.

### Request Example
```json
{
  "message": "Retrieve samples for server 'db01' from the last 7 days."
}
```

### Response
#### Success Response (200)
- **sample** (integer) - The sample identifier.
- **sample_time** (timestamp with time zone) - The time when the sample was taken.
- **sizes_collected** (boolean) - True if all relation sizes were collected in this sample.
- **dbstats_reset** (timestamp with time zone) - Reset timestamp for pg_stat_database statistics, or null.
- **clustats_reset** (timestamp with time zone) - Reset timestamp for pg_stat_bgwriter statistics, or null.
- **archstats_reset** (timestamp with time zone) - Reset timestamp for pg_stat_archiver statistics, or null.

#### Response Example
```json
[
  {
    "sample": 101,
    "sample_time": "2023-10-27T10:00:00Z",
    "sizes_collected": true,
    "dbstats_reset": null,
    "clustats_reset": "2023-10-27T09:00:00Z",
    "archstats_reset": null
  },
  {
    "sample": 102,
    "sample_time": "2023-10-27T11:00:00Z",
    "sizes_collected": false,
    "dbstats_reset": "2023-10-27T10:30:00Z",
    "clustats_reset": null,
    "archstats_reset": null
  }
]
```
```

--------------------------------

### Configure Dataset Filtering (Equal Rule) in JSON

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/json_schema.md

This JSON example demonstrates dataset filtering using the 'equal' rule. It filters data to include only rows where the 'flt_state_code' field is equal to the value 1.

```json
{
  "header": [
    {
      "type": "row_table",
      "limit": "topn",
      "filter": {
        "type": "equal",
        "field": "flt_state_code",
        "value": 1
      },
      ...
    },
    ...
  ]
}
```

--------------------------------

### Show PostgreSQL Server Settings and List Servers with pg_profile

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Retrieves the current configuration settings for a specific PostgreSQL server or lists all configured servers within the pg_profile extension. The `show_server_settings` function returns detailed settings for a given server, while `show_servers` provides a summary of all managed servers.

```sql
SELECT profile.show_server_settings('my_server');
SELECT profile.show_servers();
```

--------------------------------

### Configure pg_stat_statements for Statement Statistics

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/README.md

To include statement statistics in pg_profile reports, the `pg_stat_statements` extension must be installed and configured. Key parameters include `pg_stat_statements.max_` to prevent data loss and `pg_stat_statements.track` to control statement tracking.

```sql
# _pg_stat_statements.max_ - low setting for this parameter may cause some statements statistics to be wiped out before sample is taken. Report will warn you if your _pg_stat_statements.max_ is seems to be undersized.
# _pg_stat_statements.track = 'top'_ - _all_ value will affect accuracy of _%Total_ fields for statements-related sections of report.
```

--------------------------------

### Generate Differential Report by Sample IDs

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Generates a differential report by comparing two intervals defined by start and end sample identifiers. Optional parameters include a description, a flag to include growth data, and a list of databases to exclude.

```sql
get_diffreport([server name,] start1_id integer, end1_id integer, start2_id integer, end2_id integer [, description text [, with_growth boolean [, db_exclude name[] ]]])
```

--------------------------------

### Take Sample (Specific Server)

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Collects a sample for a specified server. Optionally allows overriding the server relation size collection policy.

```APIDOC
## POST /api/take_sample/{server_name}

### Description
Collects a sample for a specified server. This is useful for setting different sampling frequencies or taking explicit samples.

### Method
POST

### Endpoint
/api/take_sample/{server_name}

### Parameters
#### Path Parameters
- **server_name** (string) - Required - The name of the server to take a sample from.

#### Query Parameters
- **skip_sizes** (boolean) - Optional - If true, skips relation size collection. If omitted or null, the server's policy applies.

#### Request Body
None

### Request Example
```json
{
  "message": "Initiate sample collection for server 'db01' skipping size collection."
}
```

### Response
#### Success Response (200)
- **server** (string) - The name of the server.
- **result** (string) - 'OK' if the sample was taken successfully, or error text if an exception occurred.
- **elapsed** (interval) - The time elapsed while taking the sample for the server.

#### Response Example
```json
{
  "server": "db01",
  "result": "OK",
  "elapsed": "00:00:07"
}
```
```

--------------------------------

### JSON Report Datasets Structure

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/json_schema.md

The 'datasets' attribute in a JSON report stores all statistics data for the reporting period. Data is organized into datasets, each identified by a unique key. An example shows the 'dbstats' dataset containing database statistics.

```json
{
  "type": 2,
  "datasets": {
    "dbstats": [
      {
        "datid": "17519",
        "dbname": "bench",
        "ord_db": 1,
        "datsize1": "143 MB",
        "datsize2": "143 MB",
        "blks_hit1": 7811421,
        "blks_hit2": 49030,
        ...
      },
      {
        "datid": "24020",
        "dbname": "demo",
        "ord_db": 2,
        "datsize1": "7564 kB",
        "datsize2": "7564 kB",
        "blks_hit1": 72951,
        "blks_hit2": 70063,
        ...
      }
    ]
  },
  "sections": [...],
  "properties": {...}
}
```

--------------------------------

### Display Server Samples with Metadata (SQL)

Source: https://context7.com/zubkov-andrei/pg_profile/llms.txt

The `show_samples` function displays available samples for a specified server, including metadata on statistics collection status and any detected resets. It can filter samples by a number of days.

```sql
-- Show all samples for the local server
SELECT * FROM show_samples('local');

-- Show samples from the last 7 days
SELECT * FROM show_samples('production', 7);

-- Example output:
--  sample | sample_time                  | sizes_collected | dbstats_reset | bgwrstats_reset
-- --------+------------------------------+-----------------+---------------+-----------------
--       1 | 2024-01-15 10:00:00+00       | t               |               |
--       2 | 2024-01-15 10:30:00+00       | f               |               |
--       3 | 2024-01-15 11:00:00+00       | t               |               |

```

--------------------------------

### Handle Multi-Value IDs and Titles in JSON

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/json_schema.md

This JSON example illustrates how 'id' and 'title' properties can accept a two-element array of strings. This is used for tables built by pairs of rows, typically for differential reports, where each row in the pair has its own 'id' and 'title'.

```json
{
  "id": [
    "mean_exec_time1",
    "mean_exec_time2"
  ]
}
```

```json
{
  "title": [
    "properties.timePeriod1",
    "properties.timePeriod2"
  ]
}
```

--------------------------------

### Create Baseline with Sample IDs

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Creates a named baseline using a range of sample IDs. The retention period can be specified in days, or set to infinite by omitting the parameter.

```SQL
CREATE_BASELINE([server name,] baseline_name VARCHAR(25), start_id INTEGER, end_id INTEGER [, days INTEGER])
```

--------------------------------

### Server Management Functions

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

This section details the functions available for managing server configurations, including creation, deletion, enabling/disabling, renaming, and updating various server settings.

```APIDOC
## Server Management API

### Description
This API provides functions to manage server configurations for the PG Profile extension. You can create, delete, enable, disable, rename, and update various settings for servers.

### Functions

#### `create_server(server_name TEXT, server_connstr TEXT, server_enabled BOOLEAN = TRUE, max_sample_age INTEGER = NULL, description TEXT = NULL)`

*   **Description**: Creates a new server description.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The unique name of the server.
    *   `server_connstr` (TEXT) - Required - The connection string for the server.
    *   `server_enabled` (BOOLEAN) - Optional (default: TRUE) - Flag to enable the server for common `take_sample()` calls.
    *   `max_sample_age` (INTEGER) - Optional (default: NULL) - Overrides the global `pg_profile.max_sample_age` setting for this server (in days).
    *   `description` (TEXT) - Optional (default: NULL) - A textual description of the server.

#### `drop_server(server_name TEXT)`

*   **Description**: Drops a server and all its associated samples.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server to drop.

#### `enable_server(server_name TEXT)`

*   **Description**: Enables a server, including it in common `take_sample()` calls.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server to enable.

#### `disable_server(server_name TEXT)`

*   **Description**: Disables a server, excluding it from common `take_sample()` calls.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server to disable.

#### `rename_server(server_name TEXT, new_name NAME)`

*   **Description**: Renames an existing server.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The current name of the server.
    *   `new_name` (NAME) - Required - The new name for the server.

#### `set_server_max_sample_age(server_name TEXT, max_sample_age INTEGER)`

*   **Description**: Sets a new retention period (in days) for server samples. Set to `NULL` to reset.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server.
    *   `max_sample_age` (INTEGER) - Required - The new maximum sample age in days.

#### `set_server_db_exclude(server_name TEXT, exclude_db NAME[])`

*   **Description**: Sets a list of databases to exclude for a given server. Useful for environments like Amazon RDS.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server.
    *   `exclude_db` (NAME[]) - Required - An array of database names to exclude.

#### `set_server_connstr(server_name TEXT, new_connstr TEXT)`

*   **Description**: Sets a new connection string for a server.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server.
    *   `new_connstr` (TEXT) - Required - The new connection string.

#### `set_server_description(server_name TEXT, description TEXT)`

*   **Description**: Sets a new description for a server.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server.
    *   `description` (TEXT) - Required - The new description text.

#### `set_server_subsampling(server_name TEXT, subsample_enabled BOOLEAN, min_query_duration INTERVAL, min_xact_duration INTERVAL, min_xact_age INTEGER, min_idle_xact_dur INTERVAL HOUR TO SECOND)`

*   **Description**: Configures subsampling settings for a server.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server.
    *   `subsample_enabled` (BOOLEAN) - Required - Enables or disables subsampling.
    *   `min_query_duration` (INTERVAL) - Required - Minimum query duration threshold for subsampling.
    *   `min_xact_duration` (INTERVAL) - Required - Minimum transaction duration threshold for subsampling.
    *   `min_xact_age` (INTEGER) - Required - Minimum transaction age threshold for subsampling.
    *   `min_idle_xact_dur` (INTERVAL HOUR TO SECOND) - Required - Minimum idle transaction duration threshold for subsampling.

#### `set_server_setting(server_name TEXT, setting TEXT, value JSONB)`

*   **Description**: Fine-tunes server settings, particularly for collection of statistics. Available settings include `collect_pg_stat_statements`, `collect_pg_wait_sampling`, `collect_objects`, `collect_relations`, and `collect_functions`. Boolean values are accepted for collection settings.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server.
    *   `setting` (TEXT) - Required - The name of the setting to modify (e.g., `collect_pg_stat_statements`).
    *   `value` (JSONB) - Required - The value for the setting (e.g., `true`, `false`).

#### `show_server_settings(server_name TEXT)`

*   **Description**: Retrieves the current settings for a specific server.
*   **Method**: N/A (SQL Function)
*   **Parameters**:
    *   `server_name` (TEXT) - Required - The name of the server.

#### `show_servers()`

*   **Description**: Displays a list of all existing servers and their configurations.
*   **Method**: N/A (SQL Function)

### Request Example (Creating a Server)

```sql
SELECT profile.create_server('omega','host=name_or_ip dbname=postgres port=5432');
```

### Response Example (show_servers() output - conceptual)

```json
[
  {
    "server_name": "omega",
    "server_connstr": "host=name_or_ip dbname=postgres port=5432",
    "server_enabled": true,
    "max_sample_age": null,
    "description": "Primary production database"
  }
]
```
```

--------------------------------

### Take Sample Subset (Parallel Collection)

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Collects samples for a subset of enabled servers, suitable for parallel collection. Specify the total number of sets and the current set to process.

```APIDOC
## POST /api/take_sample_subset

### Description
Collects samples for a subset of enabled servers, designed for parallel collection. Allows specifying the total number of subsets and the current subset to process.

### Method
POST

### Endpoint
/api/take_sample_subset

### Parameters
#### Query Parameters
- **sets_cnt** (integer) - Required - The total number of subsets.
- **current_set** (integer) - Required - The subset to process (0 to sets_cnt - 1).

#### Request Body
None

### Request Example
```json
{
  "message": "Initiate sample collection for subset 2 of 5."
}
```

### Response
#### Success Response (200)
- **server** (string) - The name of the server.
- **result** (string) - 'OK' if the sample was taken successfully, or error text if an exception occurred.
- **elapsed** (interval) - The time elapsed while taking the sample for the server.

#### Response Example
```json
[
  {
    "server": "server3",
    "result": "OK",
    "elapsed": "00:00:03"
  },
  {
    "server": "server4",
    "result": "OK",
    "elapsed": "00:00:04"
  }
]
```
```

--------------------------------

### Display Configured Servers in pg_profile

Source: https://context7.com/zubkov-andrei/pg_profile/llms.txt

Retrieves and displays a list of all servers configured within the pg_profile repository. The output includes server ID, name, connection string, enabled status, and the maximum sample age setting.

```sql
-- List all servers
SELECT * FROM show_servers();

-- Example output:
--  server_id | server_name | connstr                              | enabled | max_sample_age
-- -----------+-------------+--------------------------------------+---------+----------------
--          1 | local       | dbname=postgres                      | t       | 7
--          2 | production  | host=prod.example.com dbname=myapp   | t       | 30
```

--------------------------------

### Create pg_profile Extension in Database

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/README.md

Demonstrates how to create the pg_profile extension within a PostgreSQL database. It shows the recommended method of creating the extension in a dedicated schema to keep its objects organized.

```sql
postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE EXTENSION pg_profile;
```

```sql
postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE SCHEMA profile;
postgres=# CREATE EXTENSION pg_profile SCHEMA profile;
```

--------------------------------

### Show Baselines

Source: https://context7.com/zubkov-andrei/pg_profile/llms.txt

Lists all baselines for a specified server, including their sample ranges and retention dates. This function helps in understanding the available performance snapshots.

```sql
-- Show all baselines for a server
SELECT * FROM show_baselines('production');

-- Example output:
--  bl_name           | start_id | end_id | keep_until
-- -------------------+----------+--------+------------------------
--  peak_load_2024    |      100 |    150 | 
--  release_v2.0      |      200 |    250 | 2025-01-01 00:00:00+00
--  black_friday_2024 |      300 |    350 | 

```

--------------------------------

### Listing Available Samples

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Retrieves a list of all existing samples stored in the repository. This function also displays statistics reset times for detected periods.

```APIDOC
## GET /show_samples

### Description
Retrieves a list of all existing samples in the repository, including detected statistics reset times.

### Method
GET

### Endpoint
/show_samples

### Parameters
This endpoint does not accept any parameters.

### Request Example
```
GET /show_samples
```

### Response
#### Success Response (200)
- **sample_id** (integer) - Unique identifier for the sample.
- **timestamp** (timestamp) - The time when the sample was taken.
- **statistics_reset_time** (timestamp) - The time when statistics were last reset for this sample.

#### Response Example
```json
[
  {
    "sample_id": 1,
    "timestamp": "2023-10-27T10:00:00Z",
    "statistics_reset_time": "2023-10-27T09:00:00Z"
  },
  {
    "sample_id": 2,
    "timestamp": "2023-10-27T10:30:00Z",
    "statistics_reset_time": "2023-10-27T10:00:00Z"
  }
]
```
```

--------------------------------

### Import Data into pg_profile

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Demonstrates importing data from a local CSV file into a PostgreSQL table and then using the import_data() function to load it into pg_profile. Assumes data has been previously exported.

```sql
CREATE TABLE import (section_id bigint, row_data json);
\copy import from 'export.csv'
SELECT * FROM import_data('import');
```

--------------------------------

### Show Baselines

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Displays information about existing baselines, including their names, sample intervals, and retention times. This function can be called for a specific server or all servers.

```SQL
SHOW_BASELINES([server name])
```

```SQL
SELECT * FROM profile.baseline_show('local');
```

--------------------------------

### Take Subsample on All Enabled Servers

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

The take_subsample() function performs a subsample on all enabled servers with subsampling turned on. It processes servers serially and returns a table with server name, subsample result ('OK' or error), and elapsed time. This function can be used to control sample creation via SQL queries. Note that attempting to take a subsample while another is in progress will result in an error.

```sql
SELECT server, result, elapsed FROM take_subsample();
```

--------------------------------

### Take Subsample for a Subset of Servers

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

The take_subsample_subset() function allows for parallel subsampling by processing a subset of enabled servers. It takes two integer arguments: sets_cnt (total number of subsets) and current_set (the current subset to process, from 0 to sets_cnt - 1). It returns a table similar to take_subsample(), indicating the server, result, and elapsed time for each subsample.

```sql
SELECT server, result, elapsed FROM take_subsample_subset(sets_cnt => 4, current_set => 1);
```

--------------------------------

### Generate Latest Report in pg_profile

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Shows how to generate a report for the two latest samples using the get_report_latest() function. This is useful for quick comparisons of recent performance data.

```sql
SELECT * FROM get_report_latest();
```

--------------------------------

### Create Baseline with Time Range

Source: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md

Creates a named baseline based on a time range. Samples overlapping this interval will be included. Retention can be set in days or infinite.

```SQL
CREATE_BASELINE([server name,] baseline_name VARCHAR(25), time_range TSTZRANGE [, days INTEGER])
```