Postgres database best practices

For best results, configure and maintain your Postgres database according to the vendor's requirements and use the Jive best practices.

• Configure the PostgreSQL ph_hba.conf to allow md5 communication between the application servers and the databases. This setting should be applied to all instances.
• Create users for each of the databases. Typically, the user names are identified as the DB names.
• Create the databases using the OWNER option using the applicable role created previously.
• For performance and troubleshooting, set the logging_collector to on, and ensure that Postgres logs are time-stamped. Use caution in busy environments with log settings, because excessive logging can generate critical load on the server.
• Set the values that may impact performance. The PostgreSQL postgresql.conf file is, by default, set to run using extremely limited resources. Settings where default values can impact performance include but are not limited to, the following:
  • shared_buffers
  • checkpoint_segments
  • checkpoint_completion_target
  • work_mem
• Configure monitoring on the system to assess the following metrics, at a minimum:
  • DB Size
  • Available Memory
  • Connections
  • Read/Write Activity
  • Transaction rates