Configure PostgreSQL

What is PostgreSQL?

PostgreSQL is a popular, open source relational database system. Our PostgreSQL section has detailed instructions on how to install PostgreSQL on different distributions. These basic installations will be sufficient for many use cases; however, PostgreSQL provides many advanced configuration options that can help optimize your databases’s performance in a production environment.

PostgreSQL can be configured and tuned through a series of configuration files. In this guide you will learn about the configuration files and how to fine-tune your database to fit your specific needs.

Before You Begin

You should have a working installation of PostgreSQL on your system before beginning this guide. Go through our How to Install PostgreSQL on Ubuntu guide to install PostgreSQL and create a sample database.

Note

The steps in this guide require root privileges. Be sure to run the steps below as root or with the sudo prefix. For more information on privileges, see our Users and Groups guide.

PostgreSQL Configuration Files

Understanding postgresql.conf

Most global configuration settings are stored in postgresql.conf, which is created automatically when you install PostgreSQL. Open this file in your preferred text editor:

File: /etc/postgresql/9.5/main/postgresql.conf

1 2 3 4 5 6 7 8 9 10 11

----------------------------- # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form: # # name = value # # (The "=" is optional.) Whitespace may be used. Comments are introduced with # "#" anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation.

The contents of the configuration file are broken up into different sections:

Note

Some of the directives in this configuration file are extremely use-case specific. Please consider all effects carefully before changing directives.

Common Postgres Configuration Options

Configuring a PostgreSQL database can be a complex process. Below are some basic configuration settings recommended when using PostgreSQL on a Linode. All of these options are explained in further detail in the PostgreSQL Tuning Guide:

Tune Authentication Options through pg_hba.conf

The pg_hba.conf file handles the default authentication options for client connections to the database. Entries in this file take the form:

  TYPE  DATABASE        USER            ADDRESS                 METHOD

The following entries are included by default:

File: /etc/postgresql/9.5/main/pg_hba.conf

TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer

local   all             all                                     peer

host    all             all             127.0.0.1/32            md5

host    all             all             ::1/128                 md5

Each entry specifies how matching requests are authenticated. By default, if you type psql at the command line on the host where PostgreSQL is running, the peer authentication method will be used. It will attempt to log you in as the database user whose name matches the currently logged in Linux user. To require password authentication by default, set the METHOD field for the local entry to password.

To allow a user on a remote system to log in to the example database using a non-hashed password, add a new line to this file, replacing 192.0.2.0 with the remote computer’s public IP address:

File: /etc/postgresql/9.5/main/pg_hba_conf

host    example         exampleuser      192.0.2.0             password

The entries in this table are read in order for each incoming connection attempt. The first entry that matches will be applied to the connection. As a result, more general configurations (matching all users, all databases, or all IP addresses) should come at the end of the file, and should generally have tighter restrictions. More specific matches with less stringent authentication methods (such as the example above) should be placed at the beginning of the list.

Note

See the official pg_hba documentation for details about each of the configuration options.

Match System Users to Database Users with pg_ident.conf

Sometimes, especially when connecting from remote hosts, a user’s Linux username may not match their PostgreSQL database username. In these cases, you can specify a mapping in /etc/postgresql/9.5/main/pg_ident.conf to match each system user with the correct database user. Entries in this file take the form:

  MAPNAME     SYSTEM-USERNAME     PG-USERNAME

• MAPNAME can be arbitrary.
• SYSTEM-USERNAME is the user’s Linux username.
• PG-USERNAME is the matching database user.

In the following example, exampleuser can log in to postgres as the database user db_user:

  examplemap       exampleuser     db_user

If you specify a mapping in this file, you must add map=map-name after the authentication method in the appropriate entry in pg_hba.conf. To allow the example user from the earlier pg_hba.conf example to log in as db_user, the complete entry would look like this:

File: /etc/postgresql/9.5/main/pg_hba.conf

host    example         exampleuser      192.0.2.0             password map=examplemap

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

• PostgreSQL Documentation