How to Install PostgreSQL Server on CentOS 8

PostgreSQL is a free and open-source object-relational database management system. PostgreSQL was developed by the PostgreSQL Global Development Group and is available for various platforms, including Linux, Microsoft Windows, and Mac OS X. PostgreSQL is known for its robustness, high availability, data integrity, reliability and ease of installation. PostgreSQL comes with many advanced features that allow you to build complex applications.

PostgreSQL supports many programming languages including Java, Python, Ruby, Perl, PHP and pgSQL. It also offers several library interfaces including JDBC, ODBC, OCI, libpq, C/C+, PHP, .NET, Perl, Qt and many more. If you are looking for enterprise-class database solutions, then PostgreSQL is the best choice for you.

In this tutorial, we will explain how to install the PostgreSQL server on CentOS 8.

Step 1 – Install PostgreSQL 12

By default, the latest version of PostgreSQL is not available in CentOS 8 default repository, so you will need to add the PostgreSQL repository to your system.

You can add it with the following command:

dnf update -y
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y

Next, disable the built-in PostgreSQL module by running the following command:

dnf -qy module disable postgresql

Next, install PostgreSQL 12 with the following command:

dnf install postgresql12 postgresql12-server -y

Once the PostgreSQL server has been installed, you can proceed to the next step.

Step 2 – Manage PostgreSQL Service

First, initialize the PostgreSQL database with the following command:

/usr/pgsql-12/bin/postgresql-12-setup initdb

You should get the following output:

Initializing database ... OK

Next, start the PostgreSQL service and enable it to start after system reboot with the following command:

systemctl start postgresql-12
systemctl enable postgresql-12

You can check the status of the PostgreSQL service with the following command:

systemctl status postgresql-12

You should see the following output:

● postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-03-22 11:33:01 EDT; 26s ago
     Docs: https://www.postgresql.org/docs/12/static/
 Main PID: 27099 (postmaster)
    Tasks: 8 (limit: 12537)
   Memory: 17.4M
   CGroup: /system.slice/postgresql-12.service
           ├─27099 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
           ├─27102 postgres: logger  
           ├─27104 postgres: checkpointer  
           ├─27105 postgres: background writer  
           ├─27106 postgres: walwriter  
           ├─27107 postgres: autovacuum launcher  
           ├─27108 postgres: stats collector  
           └─27109 postgres: logical replication launcher  

Mar 22 11:33:01 centos8 systemd[1]: Starting PostgreSQL 12 database server...

You can also check the PostgreSQL service with the following command:

netstat -antup | grep 5432

You should see the following output:

tcp        0      0 127.0.0.1:5432      0.0.0.0:*               LISTEN      30317/postmaster

Step 3 – Set PostgreSQL Admin Password

During the PostgreSQL installation, a new user called postgres is created without a password. It is recommended to set a PostgreSQL password for security reasons.

To set a password, log in to the postgres user with the following command:

su - postgres

Next, set a new password with the following command:

psql -c "alter user postgres with password 'password'"

Next, exit from the PostgreSQL shell with the following command:

exit

Step 4 – Configure PostgreSQL Server for Remote Access

By default, PostgreSQL is configured to listen on the localhost so that only local applications can connect to the database server, restricting external applications from connecting to the database.

You can configure PostgreSQL server for remote connection by editing postgresql.conf file:

nano /var/lib/pgsql/12/data/postgresql.conf

Find and change the listen_addresses value to * as shown below:

listen_addresses = '*'

Save and close the file. Then, you will also need to configure PostgreSQL to accept remote connections. You can do it by editing pg_hba.conf file:

nano /var/lib/pgsql/12/data/pg_hba.conf

Find the following line:

host all all 127.0.0.1/32 ident

Replace it with the following line:

host all all 0.0.0.0/0 md5

Save and close the file when you are finished. Then, restart the PostgreSQL service to apply the changes:

systemctl restart postgresql-12

Next, you can verify the PostgreSQL listening connection with the following command:

netstat -antup | grep 5432

You should see the following output:

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      21603/postmaster   
tcp6       0      0 :::5432                 :::*                    LISTEN      21603/postmaster

Next, go to the remote machine and run the following command to verify the PostgreSQL connection:

psql -h your-server-ip -p 5432 -U postgres -W

You will be asked to provide your Postgres password:

Password for user postgres:

Provide your password and hit Enter. Once the connection has been established, you should see the following output:

psql (9.3.24, server 12.2)
WARNING: psql major version 9.3, server major version 12.
         Some psql features might not work.
Type "help" for help.
postgres=#

Conclusion

Congratulations! You have successfully installed PostgreSQL 12 on CentOS 8. You can now host any application and use PostgreSQL as a database backend – try it out today on VPS Hosting from Atlantic.Net! For more information, visit the PostgreSQL official documentation at Postgres Doc.