Configuring Standby Database Performance

For reliability, the SAS Web Infrastructure Platform Data Server supports several types of database replication. The simplest form of replication is a standby database, and it has the least impact on database performance. A standby database is an exact copy of the Data Server database and of the supported types of replication. To support standby, the Data Server database functions by writing to the transaction log. Sending the logs might consume resources on the same host as the database, but normal database operations should not be slowed if hardware resources are sufficient.
You can have either a warm standby instance or a hot standby instance of the SAS Web Infrastructure Platform Data Server. A warm standby instance can quickly be enabled as a production database. A hot standby instance allows read-only queries even while in standby mode. It is simple to set up log shipping in a warm standby. A hot standby can use log shipping, but is kept more up-to-date with streaming replication. In either case, you should set the size of the wal_keep_segments parameter sufficiently high so that data is not lost before changes can be sent to the standby.
The following values are recommended for the wal_keep_segments parameter, which is defined in the postgresql.conf file:
  • medium database: 32
  • large database: 128
For more information about warm standby and hot standby with streaming replication, see http://www.postgresql.org/docs/.