Configuring PostgreSQL for the First Time

Arindam GhoshArindam Ghosh
11 min read

We have discussed the PostgreSQL installation steps here.

Next comes the initial configuration part. We will separate the initial configuration into two steps:

  1. Enable Remote Connections

  2. Set Server-Level Options

Before we proceed further, let’s briefly discuss the two most important PostgreSQL configuration files – pg_hba.conf and postgresql.conf, which we are going to modify soon as part of the initial configuration.

pg_hba.conf file:

The client authentication methodology that PostgreSQL follows is called host based authentication and is generally controlled by the pg_hba.conf file. The file consists of several entries for different hosts and their associated permissions related to the database, user authentication methods etc.

Whenever PostgreSQL receives a connection request, it verifies the machine(host) from which the application is requesting a connection and if it has the right to connect to the specified database. All of these verifications are being done with the help of the pg_hba.conf file. Any change in pg_hba.conf file is dynamic and doesn’t require a re-start of PostgreSQL.

You can find the PostgreSQL documentation about pg_hba.conf file here.

postgresql.conf file:

Most global configuration settings for PostgreSQL are stored in postgresql.conf file, which is created automatically when you install PostgreSQL.

Configuration parameters in the postgresql.conf file specifies server behavior with regard to authentication, encryption, auditing, memory management, query performance and other behaviors.

Changing some of the parameters in postgresql.conf file may require a reload or restart of the server for the new parameter value to take effect. You will generally notice a comment beside the parameter stating that changing that parameter requires a reload or restart.

You can also find out the list of the parameters that require a server restart by executing the below query.

SELECT name FROM pg_settings WHERE context = 'postmaster';

Those, who are looking for the PostgreSQL documentation about these parameter settings, can take a look at the PostgreSQL documentation here.

Next comes the below question-

How to locate these configuration files?

Traditionally the configuration files are stored inside the database cluster’s data directory. The below command will tell you the location of the data directory.

show data_directory;

postgres=# show data_directory;
     data_directory     
------------------------
 /var/lib/pgsql/12/data
(1 row)

postgres=#

Apart from this, there are several ways to find out the location of the configuration files directly.

You can simply run the below query on psql prompt which will give you the file location as output.

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

postgres=# SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
       name        |                setting                 
-------------------+----------------------------------------
 config_file       | /var/lib/pgsql/12/data/postgresql.conf
 data_directory    | /var/lib/pgsql/12/data
 external_pid_file | 
 hba_file          | /var/lib/pgsql/12/data/pg_hba.conf
 ident_file        | /var/lib/pgsql/12/data/pg_ident.conf
(5 rows)

postgres=#

You may also use the below command as well to find out the location:

SHOW config_file;

postgres=# SHOW config_file;
              config_file               
----------------------------------------
 /var/lib/pgsql/12/data/postgresql.conf
(1 row)

postgres=#

SHOW hba_file;

postgres=# SHOW config_file;
              config_file               
----------------------------------------
 /var/lib/pgsql/12/data/postgresql.conf
(1 row)

postgres=# SHOW hba_file;
              hba_file              
------------------------------------
 /var/lib/pgsql/12/data/pg_hba.conf
(1 row)

postgres=#
  1. Enable Remote Connections:

PostgreSQL is generally configured to allow the root user to log in as the PostgreSQL superuser postgres locally. With this, you can create databases, roles etc. as usual.

However, if you want to do this using some GUI tool over the network from some remote server, it will simply not work. By default, your server will not accept remote connections and you will get an error something like the below:

psql: FATAL:  Peer authentication failed for user "username"

You have to explicitly allow remote connections to PostgreSQL by editing the pg_hba.conf and postgresql.conf file and make specific entries to the file.

Let’s jump into modifying the pg_hba.conf and postgresql.conf files.

NOTE: It is always recommended to keep a backup copy of the configuration files before actually modifying them. Just in case, if the need arises, it will help you to return to the old \ default settings.

You can simply execute the below command which will create a backup file named postgresql.conf.bak inside the same directory where the actual file is located.

cp postgresql.conf postgresql.conf.bak
  • Edit postgresql.conf to listen on any address:
sudo vi /var/lib/pgsql/12/data/pg_hba.conf

The file looks something like the below:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)
  • Search for listen_addresses field inside the file, uncomment it and set it to *. Then save and quit out of the postgresql.conf file.
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

Note: Please don’t forget to uncomment the line by removing the pound sign ‘ # ‘ preceded by the parameter that you’ve just changed. People generally change the parameter values but tend to forget to uncomment the lines and the change doesn’t come into effect.

  • Edit pg_hba.conf file:
sudo vi /var/lib/pgsql/12/data/pg_hba.conf

Initially the pg_hba.conf file looks like below:

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
  • Append the below-mentioned line to the bottom of the file, save and exit. The 0.0.0.0 IP address acts as a “wildcard” for all IPv4 addresses. You may replace this with the specific IP address if PostgreSQL is running on a server with a static IP address.
host    all             all             0.0.0.0/0               md5

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    all             all             0.0.0.0/0               md5
  • Restart the PostgreSQL service:
sudo systemctl restart postgresql-12

You can connect to the server remotely at this point, without making the below-recommended changes. However, there are a few initial settings that people generally change before getting started with the new installation. For example, if you want PostgreSQL to listen to a different port other than the default 5432, you have to change the server-level settings accordingly.

  1. Set Server-Level Options:
  • Edit the postgresql.conf file:
sudo vi /var/lib/pgsql/12/data/postgresql.conf

The file will look something like the below:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)
  • If you want PostgreSQL to listen to some different port, then find the port setting under the CONNECTIONS AND AUTHENTICATION section, uncomment it and change the value you want to set accordingly. For this demonstration, let’s change it to 1433.
port = 1433
  • Next look at the max_connection settings. You’ll find the max_connection settings set to a value, typically 100. It is recommended not to set this parameter too high as each connection uses a small amount of shared memory and limited shared memory will not be able to allow a very high number of connections. Just for this demonstration, let’s change max_connections to 500.
max_connections = 500

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                 # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 1433                            # (change requires restart)
max_connections = 500                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)
  • Similarly, you may want to change the value of the shared_buffers. This parameter defines the size of the PostgreSQL shared buffer pool which governs the amount of memory to be used by PostgreSQL for caching data. The default value is 128 MB which can be increased as per your requirement, based on the available server resource and expected load. Find the shared_buffers setting under Memory and change the value to 256MB for this demonstration purpose and Save and exit the file.
shared_buffers = 256MB

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 256MB                  # min 128kB
                                        # (change requires restart)
#huge_pages = try                       # on, off, or try
                                        # (change requires restart)
#temp_buffers = 8MB                     # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                        # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB                         # min 64kB
#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB                  # min 100kB
#shared_memory_type = mmap              # the default is the first option
                                        # supported by the operating system:
                                        #   mmap
                                        #   sysv
                                        #   windows
                                        # (change requires restart)
dynamic_shared_memory_type = posix      # the default is the first option
                                        # supported by the operating system:
                                        #   posix
                                        #   sysv
                                        #   windows
                                        #   mmap
  • Perform a restart of the PostgreSQL service:
sudo systemctl restart postgresql-12

Test the changes:

Let’s test the change that we have made to verify if everything is working as expected.

But before that, let’s create a test database ‘dbaguides’ and a test user ‘dbaguidesapp’ which we will use while connecting to the PostgreSQL server remotely for testing.

[user@server ~]$ sudo su - postgres
[sudo] password for user:
-bash-4.2$ psql
psql (12.3)
Type "help" for help.

postgres=# CREATE DATABASE dbaguides;
CREATE DATABASE
postgres=# \c dbaguides;
You are now connected to database "dbaguides" as user "postgres".
dbaguides=# CREATE ROLE dbaguidesapp WITH LOGIN;
CREATE ROLE
dbaguides=# \password dbaguidesapp
Enter new password:
Enter it again:
dbaguides=# \q
-bash-4.2$ exit
logout
[user@server ~]$

Let’s connect to the PostgreSQL server using an external client of your choice. You can download and use pgAdmin tool as well which can be found at https://www.pgadmin.org. Once connected, run the below query to see if the changes we have made are getting reflected correctly.

SELECT name, setting FROM pg_settings WHERE name IN ('port', 'max_connections', 'shared_buffers');

Note: Please use the public IP address of your server or server name in place of the below <Server IP> field.

[user@server ~]$ psql -h <Server IP> -U dbaguidesapp -d dbaguides -p 1433
Password for user dbaguidesapp:
psql (12.3)
Type "help" for help.

dbaguides=> SELECT name, setting FROM pg_settings
dbaguides-> WHERE name IN ('port', 'max_connections', 'shared_buffers');
      name       | setting
-----------------+---------
 max_connections | 500
 port            | 1433
 shared_buffers  | 32768
(3 rows)

dbaguides=> \q
[user@server ~]$

We can now see that the PostgreSQL server is reachable remotely and the settings are also reflected correctly.

These are the bare minimum changes that are required for you to get going with the server post-installation.

There is a way lot more optimization possible by tuning these configuration parameters according to the business requirement and server load and it requires broader discussion to cover all those points. I’ll save them for future posts.

Happy Learning 🙏

0
Subscribe to my newsletter

Read articles from Arindam Ghosh directly inside your inbox. Subscribe to the newsletter, and don't miss out.

configurationPostgreSQLpostgresdbaguides

Written by

Arindam Ghosh
Arindam Ghosh

Database reliability engineer 👨‍💻 with expertise in designing, implementing, and maintaining highly available and scalable database systems 💻 Enjoy automating various database tasks ⚙️ and implementing database DevOps 🚀 🙏