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.
Table of Contents
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:
Output
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 system.
$ sudo dnf install postgresql-contrib
Once the installation is complete, initialize the PostgreSQL database with the following command:
$ sudo postgresql-setup initdb
Output Initializing database ... OK
Start the PostgreSQL service and enable it to start on boot:
$ sudo systemctl enable --now postgresql
Use the psql tool to verify the installation by connecting to the PostgreSQL database server and print its version :
$ sudo -u postgres psql -c "SELECT version();"
Output 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 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:
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.
The 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 psql utility:
$ sudo su - postgres psql
From here, you can interact with the PostgreSQL instance. To exit out of the PostgreSQL shell, type:
\q
You can also access the PostgreSQL prompt without switching users with the sudo command:
$ sudo -u postgres psql
Typically the postgres user is used only from the localhost.
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.
$ sudo -u postgres psql
2. Create a new PostgreSQL role using thr following command:
create role john
3. Create a new database:
create database johndb;
4. Grant privileges to the user on the database by running the following query:
grant all privileges on database johndb to john;
By default, the PostgreSQL server listens only on the local interface 127.0.0.1.
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:
/var/lib/pgsql/data/postgresql.conf #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
$ ss -nlt | grep 5432
Output 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 pg_hba.conf file.
Below are some examples showing different use cases
/var/lib/pgsql/data/pg_hba.conf # 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
NGINX Server Nginx, a popular open-source web server, excels at handling high traffic websites efficiently.… Read More
In the realm of web hosting, choosing the right web server is paramount. It acts… Read More
Are indispensable for ensuring smooth, precise linear motion in many industrial applications. Whether in robotics,… Read More
Cyber attacks are becoming more frequent, complex, and damaging. They can disrupt critical operations and… Read More
With the rise of new threats and the increasing complexity of IT environments, organizations need… Read More
1. Introduction In software design, managing complex systems can be challenging. The Facade Design Pattern… Read More