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 the 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.
1 2 | sudoyuminstallepel-release sudoyumupdate |
Step 2. Install PostgreSQL.
PostgreSQL is included in the default repositories of CentOS 8, and can be installed using the following dnf command:
1 | dnfinstall@postgresql |
Once the installation is done, you can initialize the database using the below command:
1 | /usr/bin/postgresql-setup--initdb |
To start and enable PostgreSQL you need to use the systemctl command:
1 2 3 4 | sudosystemctlstartpostgresql ## <-- start the server ## sudosystemctlstoppostgresql ## <-- stop the server ## sudosystemctlrestartpostgresql ## <-- resstart the server ## sudosystemctlstatuspostgresql ## <-- 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:1<spanclass="line">sudosu-postgres-c"createuser intanramona"</span>
- Create a new PostgreSQL DatabaseCreate a new database named ramona using the createdb command:1<spanclass="line">sudosu-postgres-c"createdb intanramona"</span>
- Grant privilegesTo grant permissions to the ramona user on the database we created in the previous step, connect to the PostgreSQL shell:1<spanclass="line">sudo-upostgrespsql</span>
and run the following query:
1<spanclass="k">grant</span><spanclass="k">all</span><spanclass="k">privileges</span><spanclass="k">on</span><spanclass="k">database</span>intanramonadb<spanclass="k">to</span><spanclass="p">intanramona;</span>
Step 5. Enable remote access to PostgreSQL server.
Use the following steps to enable remote access to the PostgreSQL server:
1 | sudonano/etc/postgresql/10/main/postgresql.conf |
Update listen_addresses like given below:
1 2 3 4 5 6 7 | #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses='*' |
Now restart the PostgreSQL service with systemctl command:
1 | sudosystemctlrestartpostgresql |
Verify the changes with the ss utility:
1 | ss-nlt|grep5432 |
1 2 | 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 below examples:
1 2 3 4 5 6 7 8 9 10 | # 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 question, please leave a comment below.
