PostgreSQL is an free-opensource object-relational database management system. The objective of this tutorial is to perform an installation and basic configuration of PostgreSQL server on RHEL 8 / CentOS 8 Linux server.

In this tutorial you will learn:
  • How to install PostgreSQL database server on RHEL 8 / CentOS 8
  • How to start and enable PostgreSQL database server
  • How to access PostgreSQL database from localhost and remote location
  • How to set password for the default postgres user
  • How to enable PostgreSQL to listen on all networks
  • How to secure PostgreSQL remote connection with MD5 password authentication
  • How to open PostgreSQL firewall port
  • How to establish remote connection to PostgreSQL server using psql client
Initializing and accessing PostgreSQL database on Red Hat Enterprise Linux 8
Initializing and accessing PostgreSQL database on Red Hat Enterprise Linux 8

Software Requirements and Conventions Used

Software Requirements and Linux Command Line Conventions
Category Requirements, Conventions or Software Version Used
System RHEL 8 / CentOS 8
Software PostgreSQL Server 10.5-1.el8
Other Privileged access to your Linux system as root or via the sudo command.
Conventions # - requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
$ - requires given linux commands to be executed as a regular non-privileged user

Local PostgreSQL Installation and database access step by step instructions


SUBSCRIBE TO NEWSLETTER
Subscribe to Linux Career NEWSLETTER and receive latest Linux news, jobs, career advice and tutorials.


  1. Install PostreSQL server.

    Execute the below dnf command to perform a PostreSQL server package installation:
    # dnf install postgresql-server
    
  2. Initialize PostgreSQL database:
    # postgresql-setup --initdb --unit postgresql
     * Initializing database in '/var/lib/pgsql/data'
     * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
    


  3. Start PostgreSQL and optionally enable it to start after reboot.
    # systemctl start postgresql
    # systemctl enable postgresql
    
    At this point the PostreSQL server should be up and running and listening on localhost port 5432. Use ss command to confirm that this is the case:
    $ ss -nlt
    State  Recv-Q  Send-Q     Local Address:Port     Peer Address:Port  
    LISTEN 0       128              0.0.0.0:111           0.0.0.0:*     
    LISTEN 0       32         192.168.122.1:53            0.0.0.0:*     
    LISTEN 0       128              0.0.0.0:22            0.0.0.0:*     
    LISTEN 0       128            127.0.0.1:5432          0.0.0.0:*     
    LISTEN 0       128                 [::]:111              [::]:*     
    LISTEN 0       128                 [::]:22               [::]:*     
    LISTEN 0       128                [::1]:5432             [::]:*
    


  4. Access PostreSQL database.

    When you install PostgreSQL database on yourRHEL 8 / CentOS 8 system the installer will also automatically create a new default user postgres.

    The default password for postgres user is not set, hence it is empty. To access the PostgreSQL database first execute su command as root user to switch to postres user. Then, type psql to login to the database.
    NOTE
    Any attempt to access PostgreSQL database as a root user will result in psql: FATAL: role "root" does not exist error message.
    Example:
    # su - postgres
    $ psql
    psql (10.5)
    Type "help" for help.
    
    postgres=#
    
    NOTE
    To exit from PostreSQL database shell type \q or hit CTRL+d key combination.

PostgreSQL database remote access and secure connection

  1. Set password for the postgres user.

    In order to access the PostreSQL server remotely we will first set password for the postres user:
    # su - postgres
    $ psql
    psql (10.5)
    Type "help" for help.
    
    postgres=# \password postgres
    Enter new password: 
    Enter it again: 
    postgres=# exit
    postgres-# \q
    


  2. Enable PostgreSQL server to listen on all available networks.

    Edit the main configuration file /var/lib/pgsql/data/postgresql.conf:
    # nano /var/lib/pgsql/data/postgresql.conf
    
    Once ready add the following line somewhere to the CONNECTIONS AND AUTHENTICATION section:
    listen_addresses = '*'
    
    WARNING
    The above configuration will enable PostreSQL to listen on all available networks. It is recommended to set more strict rules to in order to allow access to PostgreSQL only from selected network(s).
    Use ss command to confirm that PostgreSQL is listening on 0.0.0.0 network:
    $ ss -nlt
    State  Recv-Q  Send-Q     Local Address:Port     Peer Address:Port  
    LISTEN 0       128              0.0.0.0:111           0.0.0.0:*     
    LISTEN 0       32         192.168.122.1:53            0.0.0.0:*     
    LISTEN 0       128              0.0.0.0:22            0.0.0.0:*     
    LISTEN 0       128            0.0.0.0:5432          0.0.0.0:*     
    LISTEN 0       128                 [::]:111              [::]:*     
    LISTEN 0       128                 [::]:22               [::]:*     
    LISTEN 0       128                [::]:5432             [::]:*
    
  3. Enable MD5-encrypted password authentication:
    # echo "host all all 0.0.0.0/0  md5" >> /var/lib/pgsql/data/pg_hba.conf
    
  4. Apply PostgreSQL configuration changes:
    # systemctl restart postgresql
    
  5. Open firewall port 5432 for a remote PostgreSQL incoming traffic:
    # firewall-cmd --zone=public --permanent --add-service=postgresql
    # firewall-cmd --reload
    
  6. Connect to the PostgreSQL database server from a remote location.

    First install the psql PostgreSQL client tool on your remote host:
    RHEL/CENTOS
    # dnf install postgresql
    UBUNTU/DEBIAN
    # apt install postgresql-client
    
    Create a remote connection to host eg. 192.168.1.151 as a postgres user and user password as defined in the aboveStep 1:
    $ psql -h 192.168.1.151 -U postgres
    Password for user postgres: 
    psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 10.5)
    Type "help" for help.
    
    postgres=# 
    
ARE YOU LOOKING FOR A LINUX JOB?
Submit your RESUME, create a JOB ALERT or subscribe to RSS feed on LinuxCareers.com.
LINUX CAREER NEWSLETTER
Subscribe to NEWSLETTER and receive latest news, jobs, career advice and tutorials.
DO YOU NEED ADDITIONAL HELP?
Get extra help by visiting our LINUX FORUM or simply use comments below.