PostgreSQL: Up and Running

Chapter 2. Database Administration

This chapter will cover what we feel are the most common activities for basic administration of a PostgreSQL server; namely: role management, database creation, add-on installation, backup, and restore. We’ll assume you’ve already installed PostgreSQL and have one of the administration tools at your disposal.

Configuration Files

Three main configuration files control basic operations of a PostgreSQL server instance. These files are all located in the default PostgreSQL data folder. You can edit them using your text editor of choice, or using the admin pack that comes with pgAdmin (Editing postgresql.conf and pg_hba.conf from pgAdmin).

• postgresql.conf controls general settings, such as how much memory to allocate, default storage location for new databases, which IPs PostgreSQL listens on, where logs are stored, and so forth.

• pg_hba.conf controls security. It manages access to the server, dictating which users can login into which databases, which IPs or groups of IPs are permitted to connect and the authentication scheme expected.

• pg_ident.conf is the mapping file that maps an authenticated OS login to a PostgreSQL user. This file is used less often, but allows you to map a server account to a PostgreSQL account. For example, people sometimes map the OS root account to the postgre’s super user account. Each authentication line in pg_hba.conf can use a different pg_ident.conf file.

If you are ever unsure where these files are located, run the Example 2-1 ...