In this article, we will have explained the necessary steps to install and configure PostgreSQL on CentOS 7. 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 PostgreSQL is a powerful and open source relational database platform. It’s scalable across multiple platforms and is a widely used and well-loved tool.
Install PostgreSQL on CentOS 7
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.
Now run following command to install PostgreSQL with -contrib package which adds additional features and functionalities to PostgreSQL:
sudo yum install postgresql-server postgresql-contrib
Once the installation is done, you can initialize the database using the below command:
sudo postgresql-setup initdb
To start and enable PostgreSQL service after boot run following commands:
sudo systemctl start postgresql sudo systemctl enable postgresql
Step 4. Create PostgreSQL Database and Role.
- Create a new PostgreSQL RoleThe following command will create a new role named ramona:
sudo su - postgres -c "createuser meilanamaria"
- Create a new PostgreSQL DatabaseCreate a new database named ramona using the createdb command:
sudo su - postgres -c "createdb meilanamariadb"
- Grant privilegesTo 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
meilanamaria
db tomeilanamaria
;
Step 5. Enable remote access to 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 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 meilanamaria 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 meilanamaria 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 meilanamaria 192.168.1.146 trust
Congratulation, you have learned how to install and configure PostgreSQL on CentOS 7. If you have any question, please leave a comment below.