Chapter 2. Database Administration
This chapter covers what we deem to be the most common activities for basic administration of a PostgreSQL server: role and permission management, database creation, add-on installation, backup, and restore. We assume you’ve already installed PostgreSQL and have administration tools at your disposal.
The main configuration files that control basic operations of a PostgreSQL server instance are:
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 SYSTEMSQL command. The settings in that file override the postgresql.conf file.
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.
If present, maps an authenticated OS login to a PostgreSQL user. People sometimes map the OS root account to the
postgressuperuser 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 PostgreSQL data folder. You can edit them using any text editor, or using the Admin Pack in pgAdmin. Download ...