To upgrade from v0.6 to v0.7, first, please make sure that you have upgraded to v0.6.1 already. To check that you can run the following query:
SELECT table_operation FROM pgmemento.table_event_log WHERE op_id = 21;
If the result is not
ADD AUDIT_ID, you know you're on v0.6. If there is no such event in the
table_event_log you can safely run the update to v0.7. Simply run the
UPGRADE_v061_to_v07.sql script with psql from inside a shell environment. It will replace all functions and alter log tables. The script should finish with the message "pgMemento upgrade completed!".
psql -h localhost -p 5432 -U my_user -d my_database -f UPGRADE_061_to_V07.sql
Note, log triggers will be switched off for a short amount of time during the upgrade process. This is when some of the log logs tables are dropped and their copies are renamed to take their place. This might only take some seconds. If you care for keeping all changes in the logs during the upgrade, you should disable writes for the time of the upgrade. pgMemento's event triggers are disabled for the entire upgrade process, so avoid schema migrations.
JSONB field in
As v0.7 provides the feature to also log new values in a separate JSONB column -
new_data - the
changes column has been renamed to
Foreign key between
The former foreign key column
row_log referencing the
table_event_log is gone. Both tables now store an
event_key consisting of transaction and statement timestamps (in epoch format), internal txid, operation id, table and schema name. It can be used for joins. However, no foreign key constraint is enforced. The
event_key allows for easier partitioning of these tables.
Updated timestamp columns
stmt_date column in
transaction_log is renamed to
txid_time to easier distinguish between the new timestamp column -
stmt_time - in
table_event_log which stores the
Table OID columns in logs tables
v0.7 stops relying on OID columns in the log tables. Instead the table and schema names are used, e.g. as new columns in
audit_table_log introduces a new column
log_id to trace tables when they get renamed. Due to these changes, there are breaking changes in some function signatures that also required an OID argument.
audit_table_checknow returns the
audit_table_logas first column instead of table OID
delete_audit_table_logworks with the new
delete_table_event_lognow requires table and schema name instead of one OID
table_oidargument is replaced by arguments
DROP AUDIT_ID event behavior changed
drop_table_audit function was updated to now separately choose if the audit trail shall be removed or not and if everything should be logged for the last time or not. Before, you could only choose between removal or logging. With this change the
DROP AUDIT_ID could no longer be treated like a TRUNCATE event and got it's own
op_id value of
81. If a user decides to log the whole content of a table where auditing shall be stopped, pgMemento will now log an extra TRUNCATE event before the DROP AUDIT_ID event.
Log triggers and event triggers were renamed to be potentially less invasive. The general
log_ prefix is replaced with
pgememento_ so that it's immediately visible where the triggers are coming from.
Changed function signatures
Because of the new features of v0.7 like a configurable audit_id column many functions have changed their signatures. Have a look at the list in the UPGRADE script, as all changed functions are dropped explicitly.