This article will show you how to find your PostgreSQL configuration file, and change the port and host, maximum connections, temporary file and memory limits for PostgreSQL on Linux.
Once you have your PostgreSQL database server up and running, you’ll probably want to tweak the configuration. The most common things you will want to change are the port and host bindings, and memory and file size limits if you want to tweak the performance of your server.
Finding the PostgreSQL Configuration File
The PostgreSQL configuration file is usually named postgresql.conf and by default is located in the data directory of your PostgreSQL installation. The location of the PostgreSQL data directory will vary depending on the Linux distribution you are using, and how you installed PostgreSQL. Generally, however, you should just be able to issue the following command to get the path to the PostgreSQL configuration file for the active PostgreSQL server:
pg_config --sysconfdir
Ubuntu/Debian
The default location for the PostgreSQL configuration file in Ubuntu and Debian Linux is:
/etc/postgresql/<POSTGRESQL_VERSION>/main/postgresql.conf
Note that the path will differ for each version of PostgreSQL installed.
Red Hat/Fedora
The default location for the PostgreSQL configuration file on Red Hat and Fedora based systems is:
/var/lib/pgsql/<POSTGRESQL_VERSION>/data/postgresql.conf.
Note that the path will differ for each version of PostgreSQL installed.
Arch Linux
The default location for the PostgreSQL configuration file in Arch Linux is:
/var/lib/postgres/data/postgresql.conf
Note that as Arch only installs the latest version of PostgreSQL from its package manager, the version number is not included in the path.
Other Linux Distributions
If you cannot find your PostgreSQL configuration file, or you manually installed the server and placed it at a different path, the following command will find all files named postgresql.conf and list their path:
find / -name "postgresql.conf" 2>/dev/null
Editing the PostgreSQL Configuration File
You’ll need to use a text editor to edit the below values in postgresql.conf. nano is the easiest text editor to use for the Linux command line, and can be used to open or create a text file with the following command:
nano /path/to/postgresql.conf
To edit system configuration files, you also need root privileges using the sudo command:
sudo nano /path/to/postgresql.conf
To find a specific configuration line, you can search in nano by pressing CTRL + W
To save and exit in nano, press the CTRL + X keys.
After you have edited PostgreSQl configuration file, you will need to restart the PostgreSQL database service for the changes to take effect.
If the configuration line that you are editing begins with a hash (#), it is commented – and inactive, so you must remove the hash character from the line before changing the value. For example:
# port = 5432
…is commented, whereas
port = 5432
… is not.
Changing the PostgreSQL Database Server Port
There are several reasons why you may want to change the port PostgreSQL is running on – you may want to run multiple PostgreSQL servers from a single server (each requiring their own port), or you may want to use a non-standard port to pass it through your firewall.
To change the PostgreSQL server port, find the following line in postgresql.conf
port = 5432
5432 is the default port for PostgreSQL. Change this to another valid port number and save.
Changing the PostgreSQL Server Host/Address
In short, when a service ‘listens’ on an address, it responds to network requests that match that address only.
By default, PostgreSQL listens on the hostname localhost, allowing only connections from the local machine:
listen_addresses = 'localhost'
To set PostgreSQl to respond on all available network addresses, a wildcard value can be used:
listen_addresses = *
PostgreSQL can also be set to listen only on a specific interface or IP address. You may want to do this if you want to restrict access to the server only to known IP addresses, or if your server is connected to multiple networks (eg public and private) and want to limit access to only a subset of them:
listen_addresses = 192.168.1.10
To function, the listen_addresses value must be for an address associated with the server.
Multiple listen_addresses can be defined, separated by commas, if your server is connected to multiple networks:
listen_addresses = 192.168.1.10,192.168.2.10
Host Names can also be used, and mixed with IP addresses:
listen_addresses = 192.168.1.10,192.168.2.10,localhost,myServerName
Setting the Maximum Number of Server Connections
PostgreSQL can handle a large number of simultaneous database connections, but you may want to limit this if your server regularly becomes overloaded during periods of high demand. Update the maximum number of connections the server will accept using the below configuration line:
max_connections = 100
Note that once the connection limit is hit, your users will receive errors and not be able to use the database until the number of connections drops, so you may want to implement connection pooling in your code, or reduce the number of queries, to mitigate this.
Setting the PostgreSQL Memory Limit
If you are running your PostgreSQL alongside other services, like a web server, file server, or just running it on your local computer for development and don’t want it using too many resources, you can limit the memory usage using the following configuration line:
shared_buffers = 128MB
Alternatively, you may want to raise the memory limit if your database gets very busy.
Setting PostgreSQL Temporary File Limits
While performing queries, especially complex ones on large amounts of data, PostgreSQL may need to store temporary files on your servers storage. If your server has a limited amount of space, you can set a limit to the size of these temporary files on the following configuration line:
temp_file_limit = -1
Note that a value of -1 means that there is no limit!
