Configuring PostgreSQL for the First Time

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 actually proceed further, let’s briefly discuss about the two most important PostgreSQL configuration file – 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 a number of entries for different hosts and its associated permissions related to database, users 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 rights to connect to the specified database. All of these verifications are being done with the help of the pg_hba.conf file. Any changes in pg_hba.conf file are dynamic and don’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 regards 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';

For 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 a number of 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 login as the PostgreSQL superuser postgres locally. With this, you can create database, roles etc. as usual.

However, if you want 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 error something like 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 making specific entry 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 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 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)

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 the initial settings that people generally change before getting started with the new installation. For example, if you want PostgreSQL to listen to some 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 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 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 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 is 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, found at https://www.pgadmin.org. Once connected, run the below query to see if the changes we have made is getting reflected correctly.

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

N.B: 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 the PostgreSQL server is reachable remotely and the settings are also reflected correctly.

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

There are 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 up for the future posts.

Happy Learning!!

2 thoughts on “Configuring PostgreSQL for the First Time

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s