Parameters derived at initdb command execution
Some of the parameters derive the value from the environment variable or setting status of host at the time of initdb command execution, and set it in the postgresql.conf file.
| Parameters set at the initdb command execution Parameter | Setting | Default Value |
| max_connections | 100 | 100 |
| shared_buffers | 128MB | 128MB |
| dynamic_shared_memory_type | posix | posix |
| log_timezone | Derived from the environment variable | GMT |
| datestyle | Derived from the environment variable | ISO,MDY |
| timezone | Derived from the environment variable | GMT |
| lc_messages | Derived from the environment variable | – |
| lc_monetary | Derived from the environment variable | C |
| lc_numeric | Derived from the environment variable | C |
| lc_time | Derived from the environment variable | C |
| default_text_search_config | Derived from the environment variable | ‘pg_catalog.simple’ |
Recommended Setting
In PostgreSQL, many parameters and attributes are defined, and changed it if necessary. It is recommended to use the following values at first as the initial state.
Recommended parameter values
Recommended parameter settings in a typical system is as follows.
| Recommended parameters to be set in the postgresql.conf file Parameter name | Recommended value | |
| archive_command | ‘test ! -f {ARCHIVEDIR}/%f && cp %p {ARCHIVEDIR}/%f’ | |
| archive_mode | on | |
| autovacuum_max_workers | Greater than or equal to the number of database | |
| max_wal_size | 2GB | |
| checkpoint_timeout | 30min | |
| checkpoint_warning | 30min | |
| client_encoding | utf8 | |
| effective_cache_size | Amount of installed memory | |
| log_autovacuum_min_duration | 60 | |
| log_checkpoints | on | |
| log_line_prefix | ‘%t %u %d %r ‘ | |
| log_min_duration_statement | 30s | |
| log_temp_files | on | |
| logging_collector | on | |
| maintenance_work_mem | 32MB | |
| max_connections | Expected number of connections or more | |
| max_wal_senders | Slave instance number +1 or more | |
| server_encoding | utf8 | |
| shared_buffers | 1/3 of the amount of installed memory | |
| tcp_keepalives_idle | 60 | |
| tcp_keepalives_interval | 5 | |
| tcp_keepalives_count | 5 | |
| temp_buffers | 8MB | |
| timezone | Default | |
| wal_buffers | 16MB | |
| work_mem | 8MB | |
| wal_level | replica | |
| max_replication_slots | Slave instance number +1 or more | |
pg_hba.conf
Client authentication is controlled by a configuration file, which traditionally is named pg_hba.conf and is stored in the database cluster’s data directory. (HBA stands for host-based authentication.) A default pg_hba.conf file is installed when the data directory is initialized by initdb. It is possible to place the authentication configuration file elsewhere, however; see the hba_file configuration parameter.
A record can have one of the seven formats
More details: https://www.postgresql.org/docs/9.3/auth-pg-hba-conf.html
Enabling archivelog mode
Edit the following parameters in postgresql.conf file and restart the cluster.