applied at 12:30 IST on 6th Nov'17 in CFME 5823
-
change settings in
/var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.confas per https://bugzilla.redhat.com/show_bug.cgi?id=1489277#c0 -
restart db:
systemctl status rh-postgresql95-postgresql
- save new settings to file and check change in values:
psql -d vmdb_production -qAtc 'select * from pg_settings' > current_pg_settings
- or get singular settings:
psql -d vmdb_production
vmdb_production=# select * from pg_settings where pg_settings.name = 'shared_buffers';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
----------------+---------+------+-------------------------+--------------------------------------------------------------+------------+------------+---------+--------------------+---------+------------+----------+----------+-----------+------------------------------------------------------------+------------+-----------------
shared_buffers | 262144 | 8kB | Resource Usage / Memory | Sets the number of shared memory buffers used by the server. | | postmaster | integer | configuration file | 16 | 1073741823 | | 1024 | 262144 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 118 | f
(1 row)
vmdb_production=# select * from pg_settings where pg_settings.name in ('max_connections', 'shared_buffers', 'effective_cache_size', 'work_mem', 'maintenance_work_mem', 'min_wal_size', 'max_wal_size', 'checkpoint_completion_target', 'wal_buffers', 'default_statistics_target');
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
------------------------------+---------+------+------------------------------------------------------+------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------+---------+------------+----------+----------+-----------+------------------------------------------------------------+------------+-----------------
checkpoint_completion_target | 0.7 | | Write-Ahead Log / Checkpoints | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval. | | sighup | real | configuration file | 0 | 1 | | 0.5 | 0.7 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 188 | f
default_statistics_target | 100 | | Query Tuning / Other Planner Options | Sets the default statistics target. | This applies to table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS. | user | integer | default | 1 | 10000 | | 100 | 100 | | | f
effective_cache_size | 786432 | 8kB | Query Tuning / Planner Cost Constants | Sets the planner's assumption about the size of the disk cache. | That is, the portion of the kernel's disk cache that will be used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each. | user | integer | configuration file | 1 | 2147483647 | | 524288 | 786432 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 263 | f
maintenance_work_mem | 524288 | kB | Resource Usage / Memory | Sets the maximum memory to be used for maintenance operations. | This includes operations such as VACUUM and CREATE INDEX. | user | integer | configuration file | 1024 | 2147483647 | | 65536 | 524288 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 129 | f
max_connections | 1000 | | Connections and Authentication / Connection Settings | Sets the maximum number of concurrent connections. | | postmaster | integer | configuration file | 1 | 8388607 | | 100 | 1000 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 65 | f
max_wal_size | 64 | 16MB | Write-Ahead Log / Checkpoints | Sets the WAL size that triggers a checkpoint. | | sighup | integer | configuration file | 2 | 2147483647 | | 64 | 64 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 185 | f
min_wal_size | 64 | 16MB | Write-Ahead Log / Checkpoints | Sets the minimum size to shrink the WAL to. | | sighup | integer | configuration file | 2 | 2147483647 | | 5 | 64 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 186 | f
shared_buffers | 262144 | 8kB | Resource Usage / Memory | Sets the number of shared memory buffers used by the server. | | postmaster | integer | configuration file | 16 | 1073741823 | | 1024 | 262144 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 118 | f
wal_buffers | 2048 | 8kB | Write-Ahead Log / Settings | Sets the number of disk-page buffers in shared memory for WAL. | | postmaster | integer | configuration file | -1 | 262143 | | -1 | 2048 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 175 | f
work_mem | 2097 | kB | Resource Usage / Memory | Sets the maximum memory to be used for query workspaces. | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files. | user | integer | configuration file | 64 | 2147483647 | | 4096 | 2097 | /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf | 128 | f
(10 rows)To check if values have been set after restart..
# convert_to_GB ( setting of shared_buffers * unit size )
(262144*8)/1024/1024
2.00000000000000000000
https://www.postgresql.org/docs/9.1/static/runtime-config-resource.html