Database Reference
In-Depth Information
CHAPTER 2
Database Administration
This chapter covers what we deem to be the most common activities for basic admin‐
istration of a PostgreSQL server: role and permission management, database creation,
add-on installation, backup, and restore. We assume you've already installed Post‐
greSQL and have administration tools at your disposal.
Configuration Files
The main configuration files that control basic operations of a PostgreSQL server in‐
stance are:
postgresql.conf
Controls general settings, such as memory allocation, default storage location for
new databases, the IP addresses that PostgreSQL listens on, location of logs, and
plenty more. Version 9.4 introduced an additional file called
postgresql.auto.conf
,
which is created or rewritten whenever you use the new
ALTER SYSTEM
SQL com‐
mand. The settings in that file override the
postgresql.conf
file.
pg_hba.conf
Controls security. It manages access to the server, dictating which users can log in
to which databases, which IP addresses or groups of addresses can connect, and
which authentication scheme to expect.
pg_ident.conf
If present, maps an authenticated OS login to a PostgreSQL user. People sometimes
map the OS root account to the
postgres
superuser account. Each authentication
line in
pg_hba.conf
can dictate usage of a different
pg_ident.conf
file.
If you accepted the default installation options, you find these files in the main Post‐
greSQL data folder. You can edit them using any text editor, or using the Admin Pack
in pgAdmin. Download instructions are in
“Editing postgresql.conf and pg_hba.conf
