pgMemento

https://pgxn.org/dist/pgmemento/
Admin
pgMemento is a PostgreSQL extension that provides an audit trail for DML and DDL changes with...
Tokens:18,173
Snippets:199
Trust Score:10
Update:3 months ago
Show doc for...
Tokens:
Raw
### Install pgMemento Extension

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Install-pgMemento.html

After building, navigate to the 'dist' folder, unzip the archive, and run these commands to install the pgMemento extension into your PostgreSQL instance. You may need to run 'make install' again if the initial installation fails.

```bash
make && sudo make install
```

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

### Install pgMemento using SQL Script

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Install-pgMemento.html

Run this command to install pgMemento by executing its SQL scripts. This method creates a 'pgmemento' schema and adds it to your database's search_path.

```bash
psql -h localhost -p 5432 -U my_user -d my_database -f INSTALL_PGMEMENTO.sql
```

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

### Check if pgMemento Extension is Available

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Install-pgMemento.html

Query the 'pg_available_extensions' system view to confirm that pgMemento has been successfully installed and is recognized by PostgreSQL.

```sql
SELECT * FROM pg_available_extensions WHERE name = 'pgmemento';
```

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

### Start Auditing for a Single Table

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Initialize-auditing.html

Manually starts auditing for a specific table, adding a tracer column and creating DML triggers.

```APIDOC
## Start auditing for single tables

### Description
Auditing can also be started manually for single tables using the `create_table_audit` function. This function adds an additional tracer column to the table and creates triggers that are fired during DML changes. The last argument defines if existing data is logged as 'inserted'.

### Function Signature
`pgmemento.create_table_audit(tablename, schemaname, audit_id_column_name, log_old_data, log_new_data, log_state)`

### Parameters
* **tablename** (string) - The name of the table to audit.
* **schemaname** (string) - The name of the schema the table belongs to. Defaults to 'public'.
* **audit_id_column_name** (string) - The name for the audit ID column. Defaults to 'pgmemento_audit_id'.
* **log_old_data** (boolean) - Whether to log old data. Defaults to TRUE.
* **log_new_data** (boolean) - Whether to log new data. Defaults to FALSE.
* **log_state** (boolean) - Whether to log the state. Defaults to FALSE.

### Example
```sql
SELECT pgmemento.create_table_audit(
  tablename := 'table_A',
  schemaname := 'public',
  audit_id_column_name := 'audit_trail_id',
  log_old_data := TRUE,
  log_new_data := TRUE,
  log_state := TRUE
);
```
```

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

### Upgrade pgMemento using psql or ALTER EXTENSION

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Upgrade-from-v0.7-to-v0.7.4.html

Execute the UPGRADE_v07_to_v074.sql script with psql, or use ALTER EXTENSION UPDATE if both versions are installed.

```bash
-- with psql
psql -h localhost -p 5432 -U my_user -d my_database -f UPGRADE_061_to_V07.sql
```

```sql
-- or after installation from within the database
ALTER EXTENSION UPDATE TO '0.7.4';
```

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

### Check pgMemento Version

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Upgrade-from-v0.6.1-to-v0.7.html

Run this SQL query to verify if your current pgMemento installation is on version 0.6.1 by checking the `table_operation` in the `table_event_log`.

```sql
SELECT table_operation FROM pgmemento.table_event_log  WHERE op_id = 21;
```

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

### Generate Restore Query String

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-record.html

This example shows how to use the `pgmemento.restore_query` function to obtain the SQL query string that `pgmemento.restore_record` uses internally. This query can then be executed directly.

```APIDOC
## The restore query

### Description
Generates the SQL query string used internally by `pgmemento.restore_record` to retrieve a specific historical record. This query can be executed directly to get the record in a relational layout without needing a column definition list.

### Function
`pgmemento.restore_query(start_from_tid, end_at_tid, table_name, schema_name, aid)`

### Parameters
- **start_from_tid** (integer) - Required - Transaction ID from where to start the search.
- **end_at_tid** (integer) - Required - Upper transaction ID filter that defines the version of the tuple.
- **table_name** (text) - Required - Name of the table the record belongs to.
- **schema_name** (text) - Required - Name of the schema the table belongs to.
- **aid** (integer) - Required - The `audit_id` that defines which row you are interested in.

### Example
```sql
SELECT pgmemento.restore_query(1, 10, 'table_A', 'public', 555);
```

_**Note:**_ The returned query text can be long. It is recommended to export the result to a SQL file for easier management.
```

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

### Restore Record with Column Definition

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-record.html

This example shows how to restore a specific record by providing a column definition list. The `pgmemento.restore_record_definition` function can be used to automatically generate this list.

```APIDOC
## Restore Record with Column Definition

### Description
Restores a specific record from the audit log by providing the necessary parameters and a column definition list.

### Function
`pgmemento.restore_record(start_from_tid, end_at_tid, table_name, schema_name, aid, [audit_column_name])`

### Parameters
- **start_from_tid** (integer) - Required - Transaction ID from where to start the search.
- **end_at_tid** (integer) - Required - Upper transaction ID filter to define the tuple version.
- **table_name** (text) - Required - Name of the table the record belongs to.
- **schema_name** (text) - Required - Name of the schema the table belongs to.
- **aid** (integer) - Required - The `audit_id` that defines which row is of interest.
- **audit_column_name** (text) - Optional - The name of the audit column if it differs from the default `pgmemento_audit_id`.

### Column Definition List
This is provided as an alias to the `pgmemento.restore_record` function, specifying the structure of the returned record.

### Example
```sql
SELECT
  *
FROM
  pgmemento.restore_record(1, 10, 'table_A', 'public', 555)
  AS (id integer, column_B text, column_C character, audit_trail_id bigint);
```

### Related Function for Column Definition
`pgmemento.restore_record_definition(start_from_tid, table_name, schema_name, audit_column_name)`

### Example for `restore_record_definition`
```sql
SELECT pgmemento.restore_record_definition(10, 'table_A', 'public', 'audit_trail_id');
```
```

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

### Restore Record as JSONB

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-record.html

This example demonstrates how to retrieve historical records as a JSONB object, simplifying the process by reducing the column definition to a single JSONB column.

```APIDOC
## Returning JSONB

### Description
Retrieves historic tuples as a JSONB object. This simplifies the column definition requirement to a single JSONB column.

### Function
`pgmemento.restore_record(start_from_tid, end_at_tid, table_name, schema_name, aid, jsonb_output)`

### Parameters
- **start_from_tid** (integer) - Required - Transaction ID from where to start the search.
- **end_at_tid** (integer) - Required - Upper transaction ID filter that defines the version of the tuple.
- **table_name** (text) - Required - Name of the table the record belongs to.
- **schema_name** (text) - Required - Name of the schema the table belongs to.
- **aid** (integer) - Required - The `audit_id` that defines which row you are interested in.
- **jsonb_output** (boolean) - Required - Set to `TRUE` to return the log as JSONB.

### Example
```sql
SELECT
  *
FROM
  pgmemento.restore_record(1, 10, 'table_A', 'public', 555, TRUE)
  AS (log JSONB);
```
```

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

### Get Column Definition List for Restore

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-record.html

Use `pgmemento.restore_record_definition` to get the column definition list required for restoring a record in relational format. Specify the table name, schema name, and optionally the audit ID column name.

```sql
SELECT pgmemento.restore_record_definition(10, ‘table_A’, ‘public’, ‘audit_trail_id’);
```

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

### Check pgMemento Version

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Install-pgMemento.html

Execute this SQL query to retrieve the installed pgMemento version and its build identifier. The build ID can help track specific changes or pull requests included in the build.

```sql
SELECT full_version, build_id FROM pgmemento.version();
```

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

### Uninstall pgMemento as an Extension

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Uninstall-pgMemento.html

Use this command to remove pgMemento if it was installed as a PostgreSQL extension. This will drop the 'pgmemento' schema and associated objects.

```sql
DROP EXTENSION pgmemento CASCADE;

```

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

### Create log triggers for a single table

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Pause-and-restart-logging.html

If you need to recreate log triggers for a single table without using the `start` endpoint and excluding other tables, you can use the `pgmemento.create_table_log_trigger` function with specific parameters.

```APIDOC
## Create log triggers for a single table
If you want to recreate the log triggers for a single table and don’t want to use the `start` endpoint by excluding every other table, call the following function:
```sql
SELECT pgmemento.create_table_log_trigger(
  table_name := 'table_A',
  schema_name := 'public',
  audit_id_column_name := 'pgmemento_audit_id',
  log_old_data := TRUE,
  log_new_data := FALSE
);
```
```

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

### Get Restore Query String

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-record.html

Obtain the raw SQL query string used internally by `pgmemento.restore_record` to retrieve a historical record. This query can be executed directly or saved to a file.

```sql
SELECT pgmemento.restore_query(1, 10, 'table_A', 'public', 555);
```

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

### Start Auditing for a Single Table

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Initialize-auditing.html

Manually enable auditing for a specific table using `pgmemento.create_table_audit`. This function adds a tracer column and creates DML triggers. It also allows configuring whether to log old and new data, and whether to log the existing data as 'inserted'.

```sql
SELECT pgmemento.create_table_audit(
  tablename := 'table_A',
  schemaname := 'public',
  audit_id_column_name := 'audit_trail_id',
  log_old_data := TRUE,
  log_new_data := TRUE,
  log_state := TRUE
);

```

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

### Get Record Definition for Multiple Versions

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-multiple-versions-at-once.html

Use `pgmemento.restore_record_definition` to retrieve the schema definition for multiple versions of a record within a transaction ID range. The output includes event_id and transaction_id.

```sql
SELECT pgmemento.restore_record_definition(1, 10, 1);
```

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

### Get Column List by Transaction ID

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Audit_column_log.html

Retrieves column names, data types, and ordinal positions for a specific transaction ID. Use this when the table name might have changed.

```sql
SELECT
  column_name,
  data_type,
  ordinal_position
FROM
  pgmemento.get_column_list_by_txid(10, 'table_A', 'public');

```

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

### Aggregate JSONB Changes per Audit ID

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Merge-JSONB-logs.html

Use pgmemento.jsonb_merge as an aggregate function to get all changes for a given audit_id as a single JSONB row. Ordering by row_log ID allows viewing first or last changes per field.

```sql
SELECT
  r.audit_id,
  pgmemento.jsonb_merge(r.old_data ORDER BY r.id) AS first_changes,
  pgmemento.jsonb_merge(r.old_data ORDER BY r.id DESC) AS last_changes
FROM
  pgmemento.row_log r
JOIN
  pgmemento.table_event_log e 
  ON e.event_key = r.event_key
WHERE
  e.transaction_id = 1000000
GROUP BY
  r.audit_id;
```

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

### Persist a Restored Table State

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Persist-a-restored-recordset.html

Use `pgmemento.restore_table_state` to persist a restored recordset. You can specify the start and end transaction IDs, original table and schema, target schema, target table type (VIEW, MATERIALIZED VIEW, or TABLE), and whether to update the state.

```sql
SELECT
  pgmemento.restore_table_state(
    start_from_tid := 1,
    end_at_tid := 10,
    original_table_name := 'table_A',
    original_schema_name := 'public',
    target_schema_name := 'target_schema',
    target_table_type := 'VIEW',
    update_state := TRUE
  );

```

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

### Get Column List by Transaction ID Range

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Audit_column_log.html

Retrieves historical column information including name, count, data type, ordinal position, and transaction ID range. Use the table's log_id for querying by a range of transaction IDs. The result set includes all versions of columns that have changed.

```sql
SELECT
  column_name,
  column_count,
  data_type,
  ordinal_position,
  txid_range
FROM
  pgmemento.get_column_list_by_txid_range(1, 10, 1);

```

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

### Get Table Log ID using audit_table_check

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Audit_table_log.html

Use the pgmemento.audit_table_check function to retrieve the log_id for a historic table. This function returns various parameters useful for restore operations, including historical and current table/schema names and their corresponding log IDs.

```sql
SELECT
  table_log_id,
  log_tab_name,
  log_tab_schema,
  log_tab_id,
  recent_tab_name,
  recent_tab_schema,
  recent_tab_id
FROM
  pgmemento.audit_table_check(10, 'table_A', 'public');

```

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

### Build pgMemento Extension

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Install-pgMemento.html

Execute this script in the 'extension/pgxn' directory to build the pgMemento extension. This process compiles the SQL, prepares the file structure, and creates a zip archive in the 'dist' directory.

```bash
chmod +x build.sh && ./build.sh
```

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

### Execute pgMemento Upgrade Script

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Upgrade-from-v0.6.1-to-v0.7.html

Use the `psql` command-line tool to execute the upgrade script. Ensure you are in a shell environment and provide the necessary connection details.

```bash
psql -h localhost -p 5432 -U my_user -d my_database -f UPGRADE_061_to_V07.sql
```

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

### Create pgMemento Extension in Database

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Install-pgMemento.html

Once the extension is available, run this command within your target database to enable pgMemento functionality.

```sql
CREATE EXTENSION pgmemento;
```

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

### Initialize Auditing for a Schema

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Initialize-auditing.html

Use the `pgmemento.init` function to activate auditing for an entire database schema. You can specify parameters like the schema name, audit ID column name, and whether to log old/new data or existing states. Exclude specific tables from auditing using the `except_tables` parameter.

```sql
SELECT pgmemento.init(
  schemaname := 'data',
  audit_id_column_name := 'audit_trail_id',
  log_old_data := TRUE,
  log_new_data := TRUE,
  log_state := TRUE,
  trigger_create_table := TRUE
  except_tables  := ARRAY['table_xyz']
);

```

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

### Run UPGRADE_v060_to_v061.sql script

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Upgrade-from-v0.6-to-v0.6.1.html

Execute the SQL upgrade script using psql. This script handles function replacements and updates audit log entries.

```bash
psql -h localhost -p 5432 -U my_user -d my_database -f UPGRADE_060_to_V061.sql
```

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

### Row Log Structure (Old and New Data)

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/DML-logging.html

Demonstrates how both old and new data are logged for UPDATE operations. 'new_data' is blank for DELETEs and contains the full row for INSERTs.

```sql
old_data  |  new_data   
---|---
{“column_B”:“old_value”}  |  {“column_B”:“new_value”} 
```

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

### Initialize Auditing for a Schema

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Initialize-auditing.html

Activates auditing for an entire database schema with customizable options.

```APIDOC
## Initialize Auditing for a Schema

### Description
To activate auditing for an entire database schema simply run the `init` function.

### Function Signature
`pgmemento.init(schemaname, audit_id_column_name, log_old_data, log_new_data, log_state, trigger_create_table, except_tables)`

### Parameters
* **schemaname** (string) - The name of the schema to audit. Defaults to 'public'.
* **audit_id_column_name** (string) - The name for the audit ID column. Defaults to 'pgmemento_audit_id'.
* **log_old_data** (boolean) - Whether to log old data. Defaults to TRUE.
* **log_new_data** (boolean) - Whether to log new data. Defaults to FALSE.
* **log_state** (boolean) - Whether to log the state. Defaults to FALSE.
* **trigger_create_table** (boolean) - Whether to automatically create triggers for newly created tables. Defaults to FALSE.
* **except_tables** (array of strings) - An array of table names to exclude from auditing. Defaults to an empty array.

### Example
```sql
SELECT pgmemento.init(
  schemaname := 'data',
  audit_id_column_name := 'audit_trail_id',
  log_old_data := TRUE,
  log_new_data := TRUE,
  log_state := TRUE,
  trigger_create_table := TRUE,
  except_tables  := ARRAY['table_xyz']
);
```
```

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

### Get Next Transaction ID After Timestamp

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Thinking-in-transactions.html

Use this SQL query to find the minimum transaction ID that occurred after a specified timestamp. This is useful for users who want to query historical data based on time.

```sql
SELECT
  min(id)
FROM
  pgmemento.transaction_log
WHERE
  stmt_date >= '2018-10-26 16:00:00'
LIMIT 1;

```

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

### Restart auditing

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Pause-and-restart-logging.html

To resume auditing after it has been paused, you can use the `pgmemento.start` function or the `START_AUDITING` script. This function requires that pgMemento is already initialized and registered for the schema.

```APIDOC
## Restart auditing
To start auditing again you can call `pgmemento.start` or run the `START_AUDITING` script. This will only work if pgMemento is already initialized in the schema and registered in the `audit_schema_log`. The `start` endpoint offers the same options than init except that you cannot log the existing content of tables. If you choose a different logging behavior or default name for the `audit_id` column, the `reinit` endpoint is called, hence a new entry is written to the `audit_schema_log`.
```

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

### Uninstall pgMemento using SQL Script

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Uninstall-pgMemento.html

Execute this command in a shell environment to remove pgMemento by running its uninstall script. Ensure you are in the pgMemento directory.

```bash
psql -h localhost -p 5432 -U my_user -d my_database -f UNINSTALL_PGMEMENTO.sql

```

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

### Set User-Defined Session Info

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/User-defined-session-infos.html

Set the `pgmemento.session_info` configuration parameter for the current session. The value must be valid JSON. This allows tracking client-specific details like user and message.

```sql
SELECT
  set_config(
    'pgmemento.session_info',
    '{"client_user":"fxku", "message":"Added new category to table"}'::text,
    FALSE
  );

```

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

### Iterate Through Record Versions with JSONB Output

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-multiple-versions-at-once.html

Use `pgmemento.restore_record` with a LATERAL join to iterate over all event versions of a record, outputting each version as a JSONB object. This is useful for analyzing the evolution of a tuple over time. The version in the JSONB log column represents the state BEFORE the event.

```sql
SELECT
  i.filter_tid,
  i.table_operation,
  j.log
FROM (
  SELECT
    first_value(e.transaction_id) OVER () AS first_tid,
    e.transaction_id AS filter_tid,
    --e.transaction_id AS first_tid,
    --COALESCE(lead(e.transaction_id) OVER (), e.transaction_id + 1) AS filter_tid,
    e.table_operation
  FROM
    pgmemento.table_event_log e
  LEFT JOIN
    pgmemento.row_log r
    ON r.event_key = e.event_key
  WHERE
    e.table_name = 'table_A'
    AND e.schema_name = 'public'
    AND (r.audit_id = 10 OR e.op_id IN (1,11,12,2,21,22))
    AND e.transaction_id > (
      SELECT
        min(lower(txid_range))
      FROM
        pgmemento.audit_table_log
      WHERE
        log_id = 1 // public.table_A
      )
  ORDER BY
    e.transaction_id,
    e.id
) i,
LATERAL (
  SELECT
    *
  FROM
    pgmemento.restore_record(i.first_tid, i.filter_tid, 'table_A', 'public', 10, TRUE)
    AS (log JSONB)
) j;
```

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

### Create Schema Event Triggers

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Initialize-auditing.html

Create event triggers for DDL operations like `CREATE TABLE` by calling `pgmemento.create_schema_event_trigger`. Passing `TRUE` enables an additional trigger for these events, using settings from `audit_schema_log` for new tables.

```sql
SELECT pgmemento.create_schema_event_trigger(TRUE);

```

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

### Row Log Structure (Old Data)

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/DML-logging.html

Shows the row log table, detailing the old data for modified rows. For INSERTs, 'old_data' is blank.

```sql
ROW_LOG
ID  |  audit_id  |  event_key  |  old_data   
---|---|---|---
1  |  555  |  1581097980;1581097980;1000000;4;table_A;public  |  {“column_B”:“old_value”}   
2  |  556  |  1581097980;1581097980;1000000;4;table_A;public  |  {“column_B”:“old_value”}   
3  |  557  |  1581097980;1581097980;1000000;4;table_A;public  |  {“column_B”:“old_value”}   
```

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

### Table Event Log Structure

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/DML-logging.html

Illustrates the structure of the table event log, recording metadata for each table operation within a transaction.

```sql
TABLE_EVENT_LOG
ID  |  transaction_id  |  stmt_time  |  op_id  |  table_operation  |  table_name  |  public_name  |  event_key   
---|---|---|---|---|---|---|---
10  |  10  |  2020-07-02 18:53:00.100  |  4  |  UPDATE  |  table_A  |  public  |  1581097980;1581097980;1000000;4;table_A;public   
```

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

### Restore Multiple Record Versions

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-multiple-versions-at-once.html

Retrieve all versions of a record within a specified transaction ID range using `pgmemento.restore_records`. The column definition list must be extended to include `audit_trail_id`, `event_id`, and `transaction_id`.

```sql
SELECT
  *
FROM
  pgmemento.restore_records(1, 10, 'table_A', 'public', 555)
  AS (id integer, column_B text, column_C character, audit_trail_id bigint, event_id integer, transaction_id integer);
```

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

### Restore Record as JSONB

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-record.html

Retrieve a historical record as a JSONB object by setting the `jsonb_output` flag to TRUE. This simplifies the process as a specific column definition list is not required, only a single JSONB column.

```sql
SELECT
  *
FROM
  pgmemento.restore_record(1, 10, 'table_A', 'public', 555, TRUE)
  AS (log JSONB);
```

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

### Restore Record in Relational Format

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-record.html

Restore a specific record in its relational format by providing the necessary temporal filters, table and schema names, audit ID, and a column definition list. The column definition list must match the table's structure.

```sql
SELECT   * FROM   pgmemento.restore_record(1, 10, 'table_A', 'public', 555)   AS (id integer, column_B text, column_C character, audit_trail_id bigint);
```

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

### Create Schema Event Triggers

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Initialize-auditing.html

Creates event triggers for DDL events like CREATE TABLE to automatically enable auditing for new tables.

```APIDOC
## Create pgMemento’s event triggers

### Description
If the `init` function or script has not been used, event triggers can be created by calling the `create_schema_event_trigger` procedure. With `TRUE` an additional event trigger for `CREATE TABLE`, `CREATE TABLE AS` and `SELECT INTO` events is created. The logging behavior and the name for the `audit_id` column for newly created tables is picked from the `audit_schema_log`.

### Function Signature
`pgmemento.create_schema_event_trigger(create_ddl_trigger)`

### Parameters
* **create_ddl_trigger** (boolean) - If TRUE, creates an event trigger for DDL events like CREATE TABLE.

### Example
```sql
SELECT pgmemento.create_schema_event_trigger(TRUE);
```
```

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

### Transaction Log Structure

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/DML-logging.html

Displays the structure of the transaction log table, which records details for each audited transaction.

```sql
TRANSACTION_LOG (columns process_id, client_port not displayed)
ID  |  txid  |  txid_time  |  user_name  |  client_name  |  application_name  |  session_info   
---|---|---|---|---|---|---
10  |  1000000  |  2020-07-02 18:53:00.100  |  felix  |  ::1/128  |  psql  |  {“client_user”:“fxku”}   
```

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

### Restore Recordset using pgmemento.restore_recordset

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-recordset.html

Use this function to restore a set of records from a specified table. Omit the `aid` (audit_id) parameter when restoring multiple records.

```sql
SELECT
  *
FROM
  pgmemento.restore_recordset(1, 10, 'table_A', 'public', 'audit_trail_id')
  AS (id integer, column_B text, column_C character, audit_trail_id bigint);
```

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

### Create Log Triggers for a Single Table

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Pause-and-restart-logging.html

Use `pgmemento.create_table_log_trigger` to recreate log triggers for a specific table. This function allows customization of the audit ID column name and logging behavior for old and new data.

```sql
SELECT pgmemento.create_table_log_trigger(
  table_name := 'table_A',
  schema_name := 'public',
  audit_id_column_name := 'pgmemento_audit_id',
  log_old_data := TRUE,
  log_new_data := FALSE
);
```

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

### Reinitialize Auditing Behavior

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Initialize-auditing.html

Alters the logging behavior for an already initialized schema without logging data, but updating the audit schema log.

```APIDOC
## Altering the logging behavior

### Description
If you’ve already initialized auditing but want to enable logging new data or change the tracer column, you can call the `pgmemento.reinit` function. It has the same arguments as `init` and will drop and create auditing for a given schema without logging data, but updating the `audit_schema_log`.

### Function Signature
`pgmemento.reinit(schemaname, audit_id_column_name, log_old_data, log_new_data, log_state, trigger_create_table, except_tables)`

### Parameters
* **schemaname** (string) - The name of the schema to reinitialize auditing for.
* **audit_id_column_name** (string) - The desired name for the audit ID column.
* **log_old_data** (boolean) - Whether to log old data.
* **log_new_data** (boolean) - Whether to log new data.
* **log_state** (boolean) - Whether to log the state.
* **trigger_create_table** (boolean) - Whether to automatically create triggers for newly created tables.
* **except_tables** (array of strings) - An array of table names to exclude from auditing.
```

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

### Restore Value at Specific Transaction

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-value.html

Use `pgmemento.restore_change` to retrieve a historic value that was inserted exactly at the given transaction ID. This is faster than `restore_value` as it avoids scanning the entire history before the transaction.

```sql
SELECT pgmemento.restore_change(10, 555, 'column_B', NULL::text);
```

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

### Persist a Restored Schema State

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Persist-a-restored-recordset.html

Use `pgmemento.restore_schema_state` to restore all logged tables of a given schema. This function checks the `audit_table_log` to determine which tables need to be recreated for the specified transaction ID range. It supports specifying a target schema and table type.

```sql
SELECT
  pgmemento.restore_table_state(
    start_from_tid := 1,
    end_at_tid := 10,
    original_table_name := 'public',
    target_schema_name := 'target_schema',
    target_table_type := 'VIEW',
    update_state := TRUE
  );

```

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

### Find historic column name and data type

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-value.html

Queries the `audit_column_log` and `audit_table_log` tables to find the column name and data type before a given transaction ID.

```APIDOC
## Find historic column name and data type

### Description
If the column name and data type have changed over time, this query helps find the column name and data type before a specific transaction ID by inspecting the audit logs.

### Query
```sql
SELECT
  c.column_name,
  c.data_type
FROM
  pgmemento.audit_column_log c,
  pgmemento.audit_table_log t
WHERE
  c.audit_table_id = t.id
  AND c.txid_range @> 10::numeric
  AND t.table_name = 'table_A'
ORDER BY
  c.ordinal_position;
```

### Parameters
- **10** (numeric) - The transaction ID to check against.
- **'table_A'** (text) - The name of the table to query.
```

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

### Pause logging for a single table

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Pause-and-restart-logging.html

Use the `pgmemento.drop_table_log_trigger` function to pause or deactivate logging for a specific table within a given schema.

```APIDOC
## Pause logging for single tables
If you only want to pause/deactivate logging for a single table use the `pgmemento.drop_table_log_trigger` function:
```sql
SELECT pgmemento.drop_table_log_trigger('table_A', 'public');
```
```

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

### Search Transactions by Column Effect using '?' Operator

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Search-by-key.html

Use the '?' operator to find all distinct transaction IDs that had an effect on a specific column ('column_B') within a given row (identified by 'audit_id'). This query joins the table event log with the row log to filter events based on changes in 'old_data' or 'new_data'.

```sql
SELECT DISTINCT
  e.transaction_id
FROM
  pgmemento.table_event_log e
JOIN
  pgmemento.row_log r
  ON r.event_key = e.event_key
WHERE 
  r.audit_id = 4 
  AND (r.old_data ? 'column_B'
   OR r.new_data ? 'column_B');

```

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

### Revert Transactions by Range

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Reverting-multiple-transactions.html

Reverts a specified range of transactions. This method is suitable for general transaction range reversion.

```APIDOC
## revert_transactions

### Description
Reverts a range of transactions specified by `lower_txid` and `upper_txid`.

### Method
SQL Function Call

### Parameters
#### Path Parameters
- **lower_txid** (txid) - Required - The lower bound of the transaction ID range to revert.
- **upper_txid** (txid) - Required - The upper bound of the transaction ID range to revert.

### Request Example
```sql
SELECT pgmemento.revert_transactions(12345, 67890);
```

### Response
#### Success Response (void)
Returns void upon successful execution.

```

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

### Update a Key in row_log

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Data-corrections.html

Use `pgmemento.update_key` to modify a key's value within the row_log. Provide the audit_id, the key's path as an array, the old value, and the new value. This function updates both `old_data` and `new_data` columns regardless of logging status.

```sql
SELECT pgmemento.update_key(555, '{column_B}', 'old_value'::text, 'corrected value'::text);
```

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

### Find Historic Column Name and Data Type

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Restore-a-value.html

If the column name or data type has changed over time, query the `audit_column_log` and `audit_table_log` tables to find the correct historical identifiers for a given transaction ID and table name.

```sql
SELECT
  c.column_name,
  c.data_type
FROM
  pgmemento.audit_column_log c,
  pgmemento.audit_table_log t
WHERE
  c.audit_table_id = t.id
  AND c.txid_range @> 10::numeric
  AND t.table_name = 'table_A'
ORDER BY
  c.ordinal_position;
```

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

### Find Rows by Old Value using @> Operator

Source: https://pgxn.org/dist/pgmemento/doc/pgmemento-docs-0.7.4/Search-by-key-and-value.html

Use the `@>` operator with a JSONB literal to filter rows where the 'old_data' column contains a specific key-value pair. This is useful for reconstructing past states of data.

```sql
SELECT DISTINCT
  audit_id 
FROM
  pgmemento.row_log
WHERE 
  old_data @> '{"column_B": "old_value"}'::jsonb;
```
Tokens:
Raw