Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
tutorial:adm:server_preparation_tmp [2020/07/24 08:11] fiserp [Database server installation - CentOS8] |
tutorial:adm:server_preparation_tmp [2020/07/24 08:35] fiserp [DB server configuration] |
||
---|---|---|---|
Line 47: | Line 47: | ||
===== PostgreSQL | ===== PostgreSQL | ||
<note tip>If you are installing CzechIdM on Microsoft SQL Server, please follow [[tutorial: | <note tip>If you are installing CzechIdM on Microsoft SQL Server, please follow [[tutorial: | ||
- | We install PostgreSQL 12 database binaries and set database data directory | + | We install PostgreSQL 12 database binaries and change |
==== Database server installation - CentOS8 ==== | ==== Database server installation - CentOS8 ==== | ||
* Software installation on CentOS8(versions can vary): | * Software installation on CentOS8(versions can vary): | ||
Line 65: | Line 65: | ||
</ | </ | ||
- | * Copy of the configuration file for systemd, in which we will make change of directory for data: | + | * Copy the PostgreSQL' |
<code bash> | <code bash> | ||
Line 77: | Line 77: | ||
</ | </ | ||
- | * In the file '' | + | * In the file '' |
< | < | ||
Line 94: | Line 94: | ||
<code bash> | <code bash> | ||
- | /usr/bin/postgresql-setup --initdb --unit postgresql | + | postgresql-setup --initdb --unit postgresql |
</ | </ | ||
Line 144: | Line 144: | ||
==== DB server configuration ==== | ==== DB server configuration ==== | ||
- | First of all, enable | + | * Enable |
In the file ''/ | In the file ''/ | ||
Line 151: | Line 151: | ||
host all | host all | ||
</ | </ | ||
- | + | and change the value at the end of each line to '' | |
- | and change the value at the end of each line into md5 like this: | + | |
< | < | ||
host all | host all | ||
Line 158: | Line 157: | ||
</ | </ | ||
- | Now we can do DB sizing. | + | * Adjust |
- | In a file ''/ | + | * In following snippet, we presume the system has 3GB of memory |
+ | * We also log queries running longer than 200ms. | ||
+ | In a file ''/ | ||
< | < | ||
- | max_connections = 100 # (change requires restart) | + | # This is an EXAMPLE. Use the calculator to adjust for your deployment! |
- | shared_buffers = 768MB # min 128kB | + | # DB Version: 12 |
+ | # OS Type: linux | ||
+ | # DB Type: web | ||
+ | # Total Memory (RAM): 3 GB | ||
+ | # Connections num: 100 | ||
+ | # Data Storage: ssd | ||
+ | max_connections = 100 | ||
+ | shared_buffers = 768MB | ||
effective_cache_size = 2304MB | effective_cache_size = 2304MB | ||
- | work_mem = 7864kB | ||
maintenance_work_mem = 192MB | maintenance_work_mem = 192MB | ||
- | |||
- | min_wal_size = 1GB | ||
- | max_wal_size = 2GB | ||
checkpoint_completion_target = 0.7 | checkpoint_completion_target = 0.7 | ||
wal_buffers = 16MB | wal_buffers = 16MB | ||
- | |||
default_statistics_target = 100 | default_statistics_target = 100 | ||
+ | random_page_cost = 1.1 | ||
+ | effective_io_concurrency = 200 | ||
+ | work_mem = 3932kB | ||
+ | min_wal_size = 1GB | ||
+ | max_wal_size = 4GB | ||
log_min_duration_statement = 200 | log_min_duration_statement = 200 |