PostgreSQL: Up and Running, 2nd Edition

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.

Configuration Files

The main configuration files that control basic operations of a PostgreSQL server instance 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 command. 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 PostgreSQL data folder. You can edit them using any text editor, or using the Admin Pack in pgAdmin. Download ...