O'Reilly logo

PostgreSQL: Up and Running, 2nd Edition by Leo S. Hsu, Regina O. Obe

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required