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:


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, which is created or rewritten whenever you use the new ALTER SYSTEM SQL 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 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 ...

Get PostgreSQL: Up and Running, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.