1. Guides & Tutorials
  2. Database Management Systems
  3. PostgreSQL
  4. Configure PostgreSQL

Configure PostgreSQL

Updated by Linode Written by Angel

Contribute on GitHub

Report an Issue | View File | Edit File

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:

/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:

Directive Use
File Locations Defines where values of the database will be stored
Connections and Authentications Allows you to define the settings for connections, security, and authentication
Resource Usage Defines the parameters (memory, space) usable by PostgreSQL.
Write Ahead Log Configures Write-Ahead logging, which if properly configured, can result in a lower amount of disk writes.
Replication Control the way replications and replication data is handled by the server.
Query Tuning This set of directives can help you optimize the process of querying to the database.
Error Reporting and Logging Defines how and where the database logging will take place.
Runtime Statistics Modifies the tracking of runtime data.
Autovacuum Parameters A maintenance feature that runs a daemon and periodically reuses previously occupied disk space.
Client Connection Defaults This is one of the directives that controls a wide range of features within PostgreSQL
Lock Management Sets a timer that functions as a fail-safe. If the database is queried and locks-down, the timer will check for a dead-lock condition, and will restore the database if it is found.
Version/Platform Compatibility Allows you to set version-specific compatibility options
Error Handling Defines the behavior upon an error.
Config File Includes Lists the config files that will be included when Postgres looks for configuration files
Customized Options Allows you to add settings that may not fit in a particular section, or to keep your settings organized within this section.
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:

Directive Objective
listen_addresses = ‘localhost’ By default, Postgres only listens on localhost. However, by editing this section and replacing localhost with an IP, you can force Postgres to listen on another IP. Use ‘*’ to listen on all IP addresses.
max_connections = 50 Sets the exact maximum number of client connections allowed. The higher the setting the more resources Postgres will require. Adjust this value based on the size of your Linode and the traffic you expect your DB to receive.
shared_buffers = 128MB As detailed in the official documentation, this directive is initially set to a low value. On the Linode platform, this can be ¼ of the RAM on your Linode.
wal_level It is important to consider Write-Ahead Logging (WAL) when configuring your Postgres instance. WAL, can save your database in an emergency, by writing and logging at the same time. So your changes are written even if your machine loses power. Before configuring, read DSHL’s guide to understanding WAL, and the official chapter on WAL Reliability.
synchronous_commit = off When using a Linode, it is okay to turn this Directive to off.
archive_mode = on Turning archive mode on is a viable strategy to increase the redundancy of your backups.

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:

/etc/postgresql/9.5/main/pg_hba.conf 
1
2
3
4
5
6
7
8

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:

/etc/postgresql/9.5/main/pg_hba_conf 
1

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:

/etc/postgresql/9.5/main/pg_hba.conf 
1

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.

Join our Community

Find answers, ask questions, and help others.

This guide is published under a CC BY-ND 4.0 license.