We have discussed the PostgreSQL installation steps here.
Next comes the initial configuration part. We will separate the initial configuration into two steps:
Enable Remote Connections
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=#
- 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 thepostgresql.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.
- 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 theCONNECTIONS 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 theshared_buffers
setting underMemory
and change the value to256MB
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 🙏