In this article, we will have explained the necessary steps to install and configure PostgreSQL on CentOS 8. Before continuing with this tutorial, make sure you are logged in as a user with sudo
privileges. All the commands in this tutorial should be run as a non-root user.
PostgreSQL is a powerful, open-source object-relational database management system that uses and expands the SQL language combined with numerous features that safely keep and scale the most complex data workloads.
PostgreSQL ships with a number of features intended to help programmers to develop applications, administrators to safeguard data integrity and create fault-tolerant environments, and assist you to manage your data no matter how large or small the dataset
Install PostgreSQL on CentOS
Step 1. The first command will update the package lists to ensure you get the latest version and dependencies.
sudo yum install epel-release sudo yum update
Step 2. Install PostgreSQL.
PostgreSQL is included in the default repositories of CentOS 8 and can be installed using the following dnf
command:
dnf install @postgresql
Once the installation is done, you can initialize the database using the below command:
/usr/bin/postgresql-setup --initdb
To start and enable PostgreSQL you need to use the systemctl
command:
sudo systemctl start postgresql ## <-- start the server ## sudo systemctl stop postgresql ## <-- stop the server ## sudo systemctl restart postgresql ## <-- resstart the server ## sudo systemctl status postgresql ## <-- get status of the server ##
Step 4. Create PostgreSQL Database and Role.
- Create a new PostgreSQL Role The following command will create a new role named ramona:
sudo su - postgres -c "createuser intanramona"
- Create a new PostgreSQL DatabaseCreate a new database named ramona using the createdb command:
sudo su - postgres -c "createdb intanramona"
- Grant privileges to grant permissions to the ramona user on the database we created in the previous step, connect to the PostgreSQL shell:
sudo -u postgres psql
and run the following query:
grant all privileges on database intanramona db to intanramona;
Step 5. Enable remote access to the PostgreSQL server.
Use the following steps to enable remote access to the PostgreSQL server:
sudo nano /etc/postgresql/10/main/postgresql.conf
Update listen_addresses like given below:
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*'
Now restart the PostgreSQL service with systemctl
command:
sudo systemctl restart postgresql
Verify the changes with the ss utility:
ss -nlt | grep 5432
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
Finally, update pg_hba.conf file to configure server remote connections using the below examples:
# TYPE DATABASE USER ADDRESS METHOD # The user test_user will be able access all databases from all locations using a md5 password host all intanramona 0.0.0.0/0 md5 # The user test_user will be able access only the test_db from all locations using a md5 password host test_db intanramona 0.0.0.0/0 md5 # The user test_user will be able access all databases from a trusted location (192.168.43.108) without a password host all intanramona 192.168.1.146 trust
Congratulation, you have learned how to install and configure PostgreSQL on CentOS 8. If you have any questions, please leave a comment below.