Can I setup database triggers to run on a defined secondary set of servers only? This might sound a bit off, but here's what I have been thinking for a while now:
Use Case
You want to build an activity log for each user action on your application using database (postgreSQL) triggers on every relevant table, outputting to an activity_log table.
The triggers should do the trick, but how do we eliminate the burden of every user action triggering an action on the production servers, delaying the whole application?
Purposed Architecture
What I have in mind is a complex structure where one or more secondary postgres nodes would take the entire activity_log trigger activity.
The triggers would be disabled on all primary nodes (the ones the application reads and writes to) and enabled on some/all secondary nodes (let's call them &quot;workers&quot;).
Data would be written to a primary server (no trigger runs) and replication would forward it to all other nodes. When a &quot;worker&quot; node get's the data, the triggers process it and update the activity_log.
activity_log table should be replicated across ALL servers which means that a &quot;worker&quot; node should be able to read, write and send selected data upstream.
Is there anything even close to this?
Is this even possible to achieve without having to rewrite a replication model from scratch?
This currently isn't practical. Writes may not be done on a downstream replica, whether it replays WAL via streaming or archives.

However, the logical decoding feature in PostgreSQL 9.4 sets the foundations for making this sort of thing possible. You could have a logical decoding plugin that reads the change-stream from the master server, then writes audit information about it to a separate PostgreSQL master. If you want to query the audit server from the main production server you could use a foreign data wrapper like postgres_fdw.

The team I'm working with is building an asynchronous multi-master system for PostgreSQL named Bi-Directional Replication for PostgreSQL (BDR). At the moment it can't do what you want, but it's built on top of logical decoding, and the sort of thing you describe is something we're looking at adding in a later release of BDR, or its one-way single-master replication variant UDR.

Alternately, you may wish to look at adopting a system like Londiste. Combined with PostgreSQL's ENABLE REPLICA triggers (which only fire on replication writes via session_replication_role = 'replica', not for local writes) you might be able to rig up something like what you want. Note that Londiste doubles the write load on the master, though, as it has to write a replication queue entry as well as the original write. This might be almost as expensive as updating your activity log directly.

Logical decoding avoids this write amplification, which is why I think it'll be the long term solution for what you want to do. Right now using it would involve some non-trivial software development, though.