Is it possible to do WAL physical replication to a db without making that db read-only?

Question

(Novice question. Please let me know how I can improve the question, or if I need to delete)

I want to create a separate database so I can run more expensive queries on it. This database should be a copy of the original database. I am considering physical replication, but I believe this requires the new database to be read-only. However, I have one table that I need to write to - we have a pl/psql function that reads from the database and writes ID's that have been updated to that table.

Is it not possible to create this writable table within that replica that is read-only? (dumb question) I assume not, but is there a workaround?

Alternatively, I could create 2 separate DB's, and one would be the read only physical replica, and one would just have the table of changes.

However, that seems like a lot of work just for one writable table. Also, I'm not sure how to make our pl/psql function work across two databases or if that's even a good idea.

The last alternative I have is to create a logical replica, but I am not familiar with how to set this up. However, I believe I will have the added complexity of setting this up (which appears to be more complex than WAL/physical replication setup), and I would need to turn off our triggers on the logical replica. Whenever we have database migrations that create new tables, and apply these migrations to the replica (as to the primary), we would need to make sure we drop those triggers on the replica as the primary already has the same triggers and the updates from those triggers are being replicated to the logical replica. If I create a logical replica, would I be correct in that I have to deal with this added complexity (over physical replica).

I'm not entirely sure if this makes sense. Please correct me if I'm wrong somewhere.

I want to keep things simple, if possible, but need help understanding any tradeoffs if they exist.

Answer 1

Streaming replication keeps the standby cluster physically identical to the primary cluster (with small exceptions like hint bits). This excludes that you can modify a table on the standby.

Your idea with an additional database (in an additional cluster) could work, but you would have to use a foreign data wrapper to access the other database. And you are right, it is a complicated solution.

Logical replication seems like the best solution to me, particularly if you only need to replicate some tables. You shouldn't have a problem with triggers - you can configure if they should fire during replication or not. The biggest problem will be DDL changes, which will not be replicated.

Answer 2

If it is OK to have slightly stale data for the queries and these are really one-off or so, you could use disk snapshots from the replica using LVM thin volumes. I've made such a setup wherein you stop the hot standby instance, take a read-write LVM snapshot, start hot standby instance, then mount the LVM snapshot, start a new instance on it and promote to master. The downtime is just a couple seconds on the hot standby.