PostgreSQL or Postgres is an open-source general-purpose object-relational database management system with many advanced features that allows you to build fault-tolerant environments or complex applications.
In this guide, we will discuss how to install the PostgreSQL database server on CentOS 8. Before choosing which version to install, make sure that your applications support it.
We will also explore the basics of PostgreSQL database administration.
To be able to install packages, you need to be logged in as root or user with sudo privileges.
Installing PostgreSQL on CentOS 8
At the time of writing this article, there are two versions of PostgreSQL server available for installation from the standard CentOS repositories: version 9.6 and 10.0.
To list the available PostgreSQL module streams, type:
dnf module list postgresql
The output shows that the postgresql module is available with two streams. Each stream has two profiles: server and client. Stream 10 with the profile server is the default one:
CentOS-8 - AppStream Name Stream Profiles Summary postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 9.6 client, server [d] PostgreSQL server and client module
- To install the default stream, PostgreSQL server version 10.0 type:
sudo dnf install @postgresql:10
- To install the PostgreSQL server version 9.6 type:
sudo dnf install @postgresql:9.6
You may also want to install the contrib package which provides several additional features for the PostgreSQL database:
sudo dnf install postgresql-contrib
Once the installation is complete, initialize the PostgreSQL database with the following command:
sudo postgresql-setup initdb
Initializing database ... OK
Start the PostgreSQL service and enable it to start on boot:
sudo systemctl enable --now postgresql
psql tool to verify the installation by connecting to the PostgreSQL database server and print its version:
sudo -u postgres psql -c "SELECT version();"
PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
PostgreSQL Roles and Authentication Methods
PostgreSQL handles the database access permissions using the concept of roles. A role can represent a database user or a group of database users.
PostgreSQL supports multiple authentication methods. The most commonly-used methods are:
- Trust – A role can connect without a password, as long as the conditions defined in the
- Password – A role can connect by providing a password. The passwords can be stored as
- Ident – Only supported on TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
- Peer – Same as Ident, but it is supported on local connections only.
PostgreSQL client authentication is defined in the configuration file named
pg_hba.conf. By default, for local connections, PostgreSQL is set to use the peer authentication method.
postgres user is automatically created when you install the PostgreSQL server. This user is the superuser for the PostgreSQL instance. It is equivalent to the MySQL root user.
To log in to the PostgreSQL server as the
postgres user, first switch to the user and then access the PostgreSQL prompt using the
sudo su - postgres
From here, you can interact with the PostgreSQL instance. To exit out of the PostgreSQL shell, type:
You can also access the PostgreSQL prompt without switching users with the
sudo -u postgres psql
postgres user is used only from the localhost.
Creating PostgreSQL Role and Database
Only superusers and roles with
CREATEROLE privilege can create new roles.
In the following example, we will create a new role named
john, a database named
johndb, and grant privileges on the database.
- First, connect to the PostgreSQL shell:
sudo -u postgres psql
- Create a new PostgreSQL role using thr following command:
- Create a new database:
- Grant privileges to the user on the database by running the following query:
grant all privileges on database johndb to john;
Enable remote access to PostgreSQL server
By default, the PostgreSQL server listens only on the local interface
To enable remote access to your PostgreSQL server, open the configuration file:
sudo nano /var/lib/pgsql/data/postgresql.conf
Scroll down to the
CONNECTIONS AND AUTHENTICATION section and add/edit the following line:
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service with:
sudo systemctl restart postgresql
Verify the changes with the
ss -nlt | grep 5432
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
The output above shows that the PostgreSQL server is listening on the default port on all interfaces (0.0.0.0).
The last step is to configure the server to accept remote connections by editing the
Below are some examples showing different use cases:
# TYPE DATABASE USER ADDRESS METHOD # The user jane can access all databases from all locations using an md5 password host all jane 0.0.0.0/0 md5 # The user jane can access only the janedb database from all locations using an md5 password host janedb jane 0.0.0.0/0 md5 # The user jane can access all databases from a trusted location (192.168.1.134) without a password host all jane 192.168.1.134 trust
CentOS 8 provides two PostgreSQL versions: 9.6 and 10.0.
For more information on this topic visit the PostgreSQL Documentation
If you hit a problem or have feedback, leave a comment below.