Configure and Install PostgreSQL on CentOS 7/8
Configure and Install PostgreSQL on CentOS 7 and 8
PostgreSQL is also known as Postgres, and it is a free, powerful, and open-source relational database management system. PostgreSQL provides an implementation of the SQL querying language, and it is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. PostgreSQL answers the structured query language similar to MySQL.
In this tutorial, you learn how to install PostgreSQL on CentOS from the CentOS repository and PostgreSQL repository. You should log in as a non-root user and have sudo privileges as the prerequisites.
Install PostgreSQL on CentOS from CentOS repository
You can install PostgreSQL on the CentOS repository by running the following command with -contrib package. The -contrib package adds additional functionality and features to the installed PostgreSQL.
$ sudo yum install postgresql postgresql-contrib
After the installation, you need to initialize the PostgreSQL database by running the following command.
$ sudo postgresql-setup initdb
After initializing the database, you need to start the PostgreSQL service and then enable it to make sure that the service starts up during the system boot-up. Run the following commands to start and enable PostgreSQL service.
$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql
To check the PostgresSQL version and confirm installation, run the following command.
$ sudo -u postgres psql -c “SELECT version();”
The psql utility in a command helps to interact with the PostgreSQL database easily.
Install PostgreSQL 12 version on CentOS from PostgreSQL repository
To install the PostgreSQL 12 on CentOS, run the following commands.
First, you need to enable the PostgreSQL repository on CentOS by running the following command.
On CentOS 7:
$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
On CentOS 8:
$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
After installation, you can check the information about the installed package by running the following command.
$ rpm -qi pgdg-redhat-repo
After enabling the PostgreSQL repository, run the following command to install PostgreSQL 12 on CentOS 7 and 8. You can add the -contrib package along with the installation command to add additional functionalities and features to your PostgreSQL.
$ sudo yum -y install epel-release yum-utils
$ sudo yum-config-manager –enable pgdg12
$ sudo yum install postgresql12-server postgresql12-contrib
The configuration file of PostgreSQL database gets stored in the ‘/var/lib/pgsql/12/data/postgresql.conf’.
After installation, run the following command to initialize the database.
$ sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
After initializing the database, you need to start the PostgreSQL service and then enable it to make sure that the service starts up during the system boot-up. Run the following commands to start and enable PostgreSQL service.
$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql
To check the status of PostgreSQL service, run the following command.
$ sudo systemctl status postgresql-12
To check the PostgresSQL version and confirm installation, run the following command.
$ sudo -u postgres /usr/pgsql-12/bin/psql -c “SELECT version();”
The psql utility in a command helps to interact with the PostgreSQL database easily. If you have a firewall and remote clients on your database server, then you need to allow PostgreSQL service by running the following command.
$ sudo firewall-cmd –add-service=postgresql –permanent
$ sudo firewall-cmd –reload
Set PostgreSQL Admin User’s Password
During installation, a postgres user gets created by default. Log in to PostgreSQL as an admin user and set the password for the admin user by running the following command.
$ sudo su – postgres
~]$ psql -c “alter user postgres with password ‘StrongPassword’”
ALTER ROLE
PostgreSQL Databases and Roles
There are different methods to authenticate PostgreSQL, such as Ident, Trust, Password, and Peer. The Ident method is used on TCP/IP connection, while the Trust method is used to connect to the server without a password using the given credential in the pg_hba.conf (PostgreSQL configuration) file.
To log in to PostgreSQL, run the following command.
$ sudo su -postgres
psql
And to exit from PostgreSQL, run the following.
\q
PostgreSQL Database and Role Creation
The createdb and createuser methods are used to create database and user in PostgreSQL. You can create a new database in PostgreSQL called ‘test_database’ by running the following command.
$ sudo su – postgres -c “createdb test_database”
After creating the database, if you want to create a new role called ‘test_usr’, run the following command.
$ sudo su – postgres -c “createuser test_usr”
Next, you should grant permission to the newly created user ‘test_usr’ for the newly created database ‘test_database’. First, log in to PostgreSQL and run the following query in PostgreSQL shell.
$ sudo -u postgres psql
grant all privileges on database test_database to test_usr;
Open PostgreSQL Prompt with New Role
Make sure that you have the same Linux user available as PostgreSQl user and database to open the PostgreSQL prompt with the new role. To create a new user named ‘test_usr’ if the user is not available on CentOS, run the following command.
$ sudo adduser test_usr
After creating the user, you can connect to the database by running the following command.
$ sudo -u test_usr psql
Enable Remote Access to PostgreSQL Server
If you want to enable remote access to the PostgreSQL server, follow the below steps.
First, edit the ‘/etc/postgresql/12/main/posgresql.conf’ file and replace the ‘listen_address’ value to your server IP or * to allow all the interfaces in the CONNECTION AND AUTHENTICATION section.
$ sudo nano /etc/postgresql/12/main/posgresql.conf
listen_addresses = ‘<Your_Server_IP or *’
After making the changes, restart the PostgreSQL service to reflect the changes. Run the following systemctl command to restart the PostgreSQL service.
$ sudo systemctl restart postgresql
To verify the changes made, run the following command.
$ ss -nlt | grep 5432
Finally, you should make the following changes in the pg_hba.conf file to enable the remote connection to the server.
# TYPE DATABASE USER ADDRESS METHOD
# The user test_usr can access all the databases from all location using an md5 (Message-Digest algorithm) password
host all test_usr 0.0.0.0/0 md5
# The user test_usr can access only the test_database from all locations using an md5 password
host test_database test_usr 0.0.0.0/0 md5
# The user test_usr can access all databases from a trusted location (IP) without a password
host all test_usr <IP_address> trust
If you need any further help, please do reach our support department.
