PostgreSQL recovery file syntax, ver 8 vs ver 9


In postgreSQL version 8.4 it seems that you can place the db server into warm standby mode with the following setting in recovery.conf

restore_command = 'pg_standby archiveDir %f %p %r'

In postgreSQL version 9.5 I see the same option, but I also see what appears to be an alternative syntax in recovery.conf:

standby_mode = (boolean)
trigger_file = (string)

So is this new syntax an alternative to the old syntax (both are accepted by PGSQL)? Any benefit to the new syntax?

Does the new syntax mean that I can use the restore_command setting as well (to use my own copy command)?

Best Answer

One benefit of the new syntax is that your restore_command doesn't need to implement its own polling for newly arrived log files, and doesn't have to check for failover triggering, because the core code now does that for you. That means your restore_command only needs to focus on finding and copying the file, which makes it much simpler and easier to get right. For example, if you need to fetch your archive from AWS S3 storage, for example, you don't want to also need to implement your own polling and failover.

Another benefit is that it integrates cleanly with streaming replication. So it can use the WAL archive to get caught up, but then switch to streaming so that it stays caught up in near-real-time. And then goes back to using the WAL archive automatically if it ever falls too far behind.