How to check Postgresql logs?

Question

I have a legacy database that when doing certain action in the system that has access to it, several tables are modified, I was able to identify at least 04 tables being changed. My question is there is a file of log of Postgresql so I can be sure which tables have been changed?

Answer 1

Logs, no postgres, does not record changes to database data, but only events related to the state of server, such as, startup, readiness for connections and, mainly, errors.

See an example of logging a server on a linux system:

$ tail /var/log/postgresql/postgresql-9.5-main.log

2017-07-10 23:50:49 BRT [1165-2] LOG: database system was not properly Shut down; Automatic Recovery in Progress

2017-07-10 23:50:50 BRT [1165-3] LOG: invalid record length at 0/5EBE9C8

2017-07-10 23:50:50 BRT [1165-4] LOG: redo is not required

2017-07-10 23:50:50 BRT [1165-5] LOG: Multixact Member Wraparound protections are now enabled

2017-07-10 23:50:50 BRT [1091-1] LOG: database system is ready to Accept Connections

...

To log changes in the databases within the Voce server would have to create audit strategies within each database, an interesting suggestion is presented in postgres wiki, in case Voce has to create a Trigger for each table you want to audit.

Audit Trigger

Answer 2

It is possible to have the server register on LOG all commands (DDL, DML, DCL and TCL) executed on the Databases that are hosted on it.

In the settings file data/postgresql.conf from your Postgres server, adjust the following parameters:

log_statement = 'all'
log_destination = 'stderr'
logging_collector = on
log_min_duration_statement = 0
log_filename = 'postgresql-%F.log'

Your server needs to be restarted for the changes to take effect.

References:

https://www.postgresql.org/docs/9.3/static/runtime-config-logging.html

https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries

Answer 3

No, the Postgresql log will only record exceptions and warnings. If you are referring to changes in the table structure, it will be more difficult unless you create a log in your own application. If you are referring to changes in table records, you must create "Triggers" to be triggered at update events and save the changed fields and values in a "Logs" table that must be created.

Answer 4

To identify modifications to the database structure executed by commands DDL, use the resource of EVENT TRIGGER of Postgresql. It is a feature that is available from version 9.3.

With it it would be possible to store in a table the data of the tables that are being created by the legacy system and later consult this data.

Create a table to store data as events occur.

create table auditoria (
    id serial,
    data_ocorrencia timestamp,
    dados jsonb
);

Create a function that returns event_trigger. Note the use of the function pg_event_trigger_ddl_commands(), it will return the data of the object being modified.

CREATE or replace FUNCTION registra_evento_create()
    RETURNS event_trigger
    LANGUAGE plpgsql
AS $$
declare
    r record;
begin
    for r in 
        select jsonb_agg(evento) as eventos
        from (
            select
                pg_identify_object(classid,objid,objsubid) as obj
            from pg_event_trigger_ddl_commands()
        ) as evento
    loop
        execute 'insert into auditoria (data_ocorrencia, dados) values (current_timestamp,$1);'
            using (r.eventos);
    end loop;
end
$$;

Create a EVENT TRIGGER. In this case it will be fired at Rigger when the command is closed and is a command CREATE TABLE. The parameter WHEN is not required, so it would be possible to audit other types of commands DDL, in the documentation are indicated the options.

CREATE EVENT TRIGGER tr_evento_create
ON ddl_command_end WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE registra_evento_create();

After that each command CREATE TABLE will trigger the function registra_evento_create(), and with queries in the table auditoria it would be possible to identify the tables being created by the legacy system.

Below links referring to the subject in the postgres documentation: https://www.postgresql.org/docs/current/static/sql-createeventtrigger.html https://www.postgresql.org/docs/current/static/event-trigger-table-rewrite-example.html https://www.postgresql.org/docs/current/static/event-trigger-matrix.html https://www.postgresql.org/docs/current/static/event-trigger-definition.html https://www.postgresql.org/docs/current/static/functions-event-triggers.html http://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-event-triggers/