How to check the PostgreSQL logs?

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

In the data/postgresql.conf settings file of your Postgres server, set 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:

link

link

Logs, in postgres, do not record changes in database data, but only events related to the state of the server , such as startup, readiness for connections, and especially 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 bank changes on the server you would have to create audit strategies within each bank, an interesting suggestion is presented in postgres wiki , in case you have to create a trigger for each table that you want to audit.

Audit trigger

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

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

It would be possible to store in a table the data of the tables that are being created by the legacy system and then consult the data.

Create a table to store the 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 pg_event_trigger_ddl_commands() function, 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 the trigger will be triggered when the command is terminated and is a CREATE TABLE command. The WHEN parameter is not required, so it would be possible to audit other DDL command types, the options are indicated in the documentation.

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

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

Below you'll find links to the subject in the postgres documentation: link link link link link link