O'Reilly logo

PostgreSQL Administration Cookbook, 9.5/9.6 Edition by Gabriele Bartolini, Gianni Ciolli, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required