Tomasz ZIss 10:50 on 2022-02-25  

PostgreSQL 14 – WAL-based replication across minor versions

PostreSQL has multiple options for building replication. Among them two seems to be similar to
Oracle Data Guard offer:

  • WAL Log-shipping – applying entire WAL (similar to Oracle redo logs/ archived redo logs), but not managed – database in read-only mode.
  • Streaming replication – it is similar to Oracle Real-Time Apply when changes from primary server are applied as soon as possible without waiting for WAL/standby redo logs switching. Interesting fact is that WAL files always have 16MB size and it is not dependent of number of changes made to database. It is different behavior comparing Oracle where size vary. Of course we talking about manually switching or frequent switches enforced to minimize recovery point objective for idle time in database. In PostgreSQL this parameter is archive_timeout, in Oracle it is archive_lag_target. If we set it to quite low value we have to remember to secure additional space for WAL files.

Interesting is method of preventing of WAL files deletion on primary database server. In Oracle this is achieved by archived redo log deletion policy specified in RMAN configuration. In Postgres there is no centralized place for all backup/recovery process management. For protection of removing WAL files we are using replication slots.

I’ve checked if we could use WAL based replication. I have primary server in 14.1 version and standby server in 14.2 version. I’ve managed successfully build streaming application among them.
Streaming works correctly:

2022-02-25 09:13:33.135 UTC [1557] LOG: started streaming WAL from primary at 4/70000000 on timeline 1

Unfortunately it is not officially supported in PostgreSQL (but documentation says it should work). In Oracle we could the same behavior (for example when using Standby-first patching approach). Supported for 31 days, but in practice I saw cases where we have replication between different patch levels for more than 60 days.

I’ve spent several hours for troubleshooting why my streaming replication doesn’t work. I’ve discovered PostreSQL uses 2 configuration files: postgres.conf and postgres.auto.conf. First is Oracle pfile and second spfile. I had strange values for primary_conninfo and after correction all started to work.