Collecting changes using triggers and saving them in another database

For security-critical systems, keeping the audit logs on the same machine as the rest of the data may not be enough. In that case, you may need to implement remote-change logging functionality.

If you are running PostgreSQL 9.3 or newer, you can use postgres_fdw, the PostgreSQL foreign data wrapper, to log in to a foreign table-that is, a table reached via a connection to another server.

The previous example can be easily extended to an example of remote logging. This recipe works on PostgreSQL 9.5 or newer:

  1. Connect to the remote auditing database and load the audit trigger code there as well:
        psql -f audit.sql auditdb
  1. All the steps after the first one are carried ...

Get PostgreSQL Administration Cookbook, 9.5/9.6 Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.