Database Administration - PostgreSQL: Up and Running - page 18

from pgAdmin” on page 61 . If you are ever unsure where these files are, run the
Example 2-1 query as a superuser while connected to any of your databases.
Example 2-1. Location of configuration files
SELECT name , setting FROM pg_settings WHERE category = 'File Locations' ;
name | setting
-------------------+------------------------------------------
config_file | /etc/postgresql/9.3/main/postgresql.conf
data_directory | /var/lib/postgresql/9.3/main
external_pid_file | /var/run/postgresql/9.3-main.pid
hba_file | /etc/postgresql/9.3/main/pg_hba.conf
ident_file | /etc/postgresql/9.3/main/pg_ident.conf
postgresql.conf
postgresql.conf controls the life-sustaining settings of the PostgreSQL server instance as
well as default settings for new databases. You can override many settings at the database,
user, session, and even function levels. You'll find many details on how to fine-tune your
server by tweaking settings in the article Tuning Your PostgreSQL Server .
An easy way to check the current settings is to query the pg_settings view, as we
demonstrate in Example 2-2 . We provide a synopsis of key setting and description of
the key columns, but to delve deeper, we suggest you check the official documentation,
pg_settings .
Example 2-2. Key settings
SELECT name , context , unit ,
setting , boot_val , reset_val
FROM pg_settings
WHERE name IN ( 'listen_addresses' , 'max_connections' , 'shared_buffers' , 'effec
tive_cache_size' , 'work_mem' , 'maintenance_work_mem'
)
ORDER BY context , name ;
name | context | unit | setting | boot_val | reset_val
----------------------+------------+------+---------+-----------+-----------
listen_addresses | postmaster | | * | localhost | *
max_connections | postmaster | | 100 | 100 | 100
shared_buffers | postmaster | 8kB | 131584 | 1024 | 131584
effective_cache_size | user | 8kB | 16384 | 16384 | 16384
maintenance_work_mem | user | kB | 16384 | 16384 | 16384
work_mem | user | kB | 5120 | 1024 | 5120
If context is set to postmaster , changing this parameter requires a restart of
the PostgreSQL service. If it's set to user , changes just require a reload to take
effect globally. Restarting terminates active connections, whereas reloading does
not.