【转】PostgreSQL recommendations

https://help.deepsecurity.trendmicro.com/10_2/aws/postgresql-tuning.html

PostgreSQL recommendations

General database setup recommendations are located in Prepare a database for Deep Security Manager on AWS. In addition to those general recommendations, this article provides recommendations specific to using PostgreSQL as your Deep Security database. There are general requirements that apply to all deployments (see General requirements) and other tuning recommendations that you should consider depending on your organization's needs (see Tuning PostgreSQL settings).

General requirements

• Deep Security has been tested with these PostgreSQL 9.6 distributions: PostgreSQL Core Distribution and Amazon RDS for PostgreSQL. The Deep SecurityManager installer will check that you have a supported database installed. However, there is no ongoing monitoring of the database version, so if you decide to upgrade your database after installing Deep Security Manager, you must ensure that you upgrade to a version that is supported by the manager.
• PostgreSQL is supported for new installations of Deep Security 10.1 or greater. There is no supported migration path for moving from a pre-10.1 version of Deep Security with another database to Deep Security 10.1 with a PostgreSQL database.
• For information on how to install and configure a PostgreSQL database, refer to the PostgreSQL documentation. If you need additional help with setting up PostgreSQL, there are options available for professional support.
• To prepare a PostgreSQL database for use with Deep Security Manager:

  CREATE DATABASE "<database>";

  CREATE ROLE "<username>" WITH PASSWORD '<password>';

  GRANT ALL ON DATABASE "<database>" TO "<username>";

  GRANT CONNECT ON DATABASE "<database>" TO "<username>";

• Based on your security requirements, consider using TLS to secure traffic between the Deep Security Manager and PostgreSQL. To turn on TLS after Deep Security Manager has been installed, see Encrypt communication between the Deep Security Manager and the database.
• If using multi-tenancy, users also need the right to create new databases and roles:

  ALTER ROLE <username> CREATEDB CREATEROLE;

Tuning PostgreSQL settings

To change the settings described in this section:

1. Open the postgresql.conf file. On Windows, the file is in C:\PostgreSQL\data\pg96. On Linux, it's in /var/lib/pgsql/9.6/data.
2. Edit the parameters that you want to change.
3. Save and close the file.
4. Restart the PostgreSQL service.

Logging settings

By default, PostgreSQL log files are not rotated, which can lead to the log files using a large amount of disk space. When using PostgreSQL with Deep Security, we recommend that you use these four parameters in the postgresql.conf file to configure log rotation:

• log_filename
• log_rotation_age
• log_rotation_size
• log_truncate_on_rotation

log_rotation_age and log_rotation_size control when a new log file is created. For example, setting log_rotation_age to 1440 will create a new log file every 1440 minutes (1 day), and setting log_rotation_size to 10000 will create a new log file when the previous one reaches 10 000 KB.

log_filename controls the name given to every log file. You can use time and date format conversion in the name. For a complete list, see http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html.

When log_truncate_on_rotation is set to "on", it will overwrite any log file that has the same name as a newly created log file.

There are several combinations of parameters that you can use to achieve a log rotation to suit your requirements. Here is one example:

• log_filename = 'postgresql-%a.log' (every log file has the first 3 letters of the weekday in its name)
• log_rotation_age = 1440 (a new log file is created daily)
• log_rotation_size = 0. (setting is disabled to prevent the overwriting of the daily log file every time this limit is exceeded)
• log_truncate_on_rotation = on (enable log file overwrite)

Lock management

By default, the deadlock_timeout setting in the postgresql.conf file is configured to 1 second. This means every time a query waits on a lock for more than 1 second, PostgreSQL will launch a check for deadlock condition and will log an error if the logging setting has been configured that way (by default, it is). This can lead to performance degradation on bigger systems, where it can be normal for queries to wait for more than 1 second during load times. On large systems, consider increasing the deadlock_timeout setting. The PostgreSQL documentation contains this recommendation: "Ideally the setting should exceed your typical transaction time [...]".

Maximum connections

The max_connections setting in the postgresql.conf file specifies the maximum number of open connections to the database. The default value is 100. We recommend increasing this value to 500.

Shared buffers

The shared_buffers setting in the postgresql.conf file specifies how much memory PostgreSQL can use to cache data. A system with 1 GB of RAM must have one quarter of its memory value for shared buffer, which means the shared buffer should be set to 256 MB (the default is 32 MB).

Work memory and maintenance work memory

The work_mem setting in the postgresql.conf file specifies the amount of memory that can be used by internal sort operations and hash tables before writing to temporary disk files. The default value is 1 MB, but it should be increased when running complex queries. The maintenance_work_mem setting determines the maximum amount of memory used for maintenance operations such as ALTER TABLE.

Effective cache size

The effective_cache_size setting in the postgresql.conf file is used to estimate cache effects by a query. This setting only affects cost estimates during query planning and does not result in higher memory consumption. Consider increasing this setting.

Checkpoints

Checkpoints are usually the main source of writes to data files. To get the smoothest performance, most checkpoints should be "timed" (triggered by checkpoint_timeout) and not "requested" (triggered by filling all the available WAL segments or by an explicit CHECKPOINT command). We strongly recommend that you make checkpoints less frequent.

Setting	Default value	Recommended value
checkpoint_timeout	5min	15min
checkpoint_completion_target	0.5	0.9
max_wal_size	1GB	16GB

Write-ahead log (WAL)

The default value for the wal_level setting is 'minimal', which is appropriate when replication is not in place. Consider changing it if you plan to set up replicas.

Autovacuum settings

PostgreSQL requires periodic maintenance called "vacuuming". Auto vacuuming is recommended in many cases, so there is no need to change the default setting: autovacuum_max_workers = 3.

High availability

High availability (HA) is not set by default and was not enabled in our test environment, but it is highly recommended to ensure business continuity in the case of a database malfunction or server inaccessibility. Refer to your PostgreSQL documentation for information on how to enable and configure HA.

Backup and recovery

Backup and recovery is not set by default, but it's absolutely essential in a production environment.

Basic tools like pg_dump or pg_basebackup are not suitable for backups in an enterprise environment. Consider using other tools like Barman (http://www.pgbarman.org/) for backup and recovery.

Linux recommendations

Transparent Huge Pages (Linux)

Transparent Huge Pages (THP) is a Linux memory management system that reduces the overhead of Translation Lookaside Buffer (TLB) lookups on machines with large amounts of memory by using larger memory pages. THP is enabled by default on Linux, but it is not recommended for computer running a database and should be disable if PostgreSQL is installed on a Linux computer. Refer to your OS vendor's documentation for details.

Strengthen host-based authentication (Linux)

By default, Linux does not have restricted host-based authentication (HBA) for databases. Strengthening the HBA settings on a database appliance helps to prevent unauthorized access from external hosts. The HBA settings restrict access to an IP address range so that only hosts within that range have access. HBA settings were not used on our test environment and we do not recommend them.