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.
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.
root or with the
sudo prefix. For more information on privileges, see our Users and Groups guide.
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:
|Defines where values of the database will be stored
|Connections and Authentications
|Allows you to define the settings for connections, security, and authentication
|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.
|Control the way replications and replication data is handled by the server.
|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.
|Modifies the tracking of runtime data.
|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
|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.
|Allows you to set version-specific compatibility options
|Defines the behavior upon an error.
|Config File Includes
|Lists the config files that will be included when Postgres looks for configuration files
|Allows you to add settings that may not fit in a particular section, or to keep your settings organized within this section.
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:
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 1/4 of the RAM on your Linode.
|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
archive_mode = on
|Turning archive mode on is a viable strategy to increase the redundancy of your backups.
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
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:
- 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.
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
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
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.
This page was originally published on