The pgmemento.audit_table_log table stores information about audited tables, which is important when restoring a whole schema or database. It contains the following columns: * id SERIAL: Primary Key * log_id INTEGER: ID to trace a changing table * relid OID: The table's OID to trace a table when changed [deprected] * schema_name TEXT NOT NULL: The schema the table belongs to * table_name TEXT NOT NULL: The name of the table * audit_id_column TEXT NOT NULL: The name for the audit_id column added to the audited table * log_old_data BOOLEAN NOT NULL: Flag that shows if old values are logged for audited table * log_new_data BOOLEAN NOT NULL: Flag that shows if new values are logged for audited table * txid_range numrange: Stores the transaction IDs when the table has been created and dropped

The txid_range column is probably the most interesting here. The numbers within the ranges refer to the ID values in the pgmemento.transaction_id table. The lower boundary is exclusive ((), the upper boundary inclusive (]). Think about it like this: When the first transaction which created the table occurred, the table did not exist so far. When the table is dropped it exists during the final transaction. If the table still exists the upper boundary is not set (it's NULL).

PostgreSQL offers some cool operators for range types which are useful for restoring previous database states. To query all tables that existed during a given transaction ID use the containment operator @>. Tables that got created during this ID will not be returned as the lower boundary is exclusive. If you use a transaction ID range to filter tables use the intersection operator &&. When filtering by the txid_range column, the ID values need to be of type NUMERIC.

Rename events

When a table is renamed it has a similar effect to the content of the audit_table_log like dropping the table. The transaction ID of the RENAME TABLE event is saved as the upper boundary and a new row is inserted using the same ID as the lower boundary. Through the log_id column you can still tell that it's the same table. Some functions in pgMemento that allow for filtering by a transaction ID range have to deal with renamed tables. Therefore, these functions require the log_id value from audit_table_log instead of explicit table and schema names.

Get a table's log_id

To get the log_id of a historic table use the pgmemento.audit_table_check function. It returns many different parameters which can be helpful for restore workflows, incl. the historic table and schema name as well as the ID value from the audit_table_log. If the table still exists it also returns values for the current table name, schema name and audit_table_log ID. If the table has not been renamed they are the same like the first parameters.

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');