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
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.
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.
pgmemento.audit_table_check(10, 'table_A', 'public');