In this article, we want to teach you How To Set up PostgreSQL on Centos 7.
PostgreSQL is an enterprise-class open source database management system.
It supports both SQL and JSON for relational and non-relational queries for extensibility and SQL compliance.
PostgreSQL supports advanced data types and performance optimization features, which are only available in expensive commercial databases, like Oracle and SQL Server.
Also, it is known as Postgres.
Set up PostgreSQL on Centos 7
Before you start to install PostgreSQL on Centos 7, you need to log in to your server as a non-root user with sudo privileges. To do this, you can follow our article the Initial Server Setup with Centos 7.
Now you can follow the steps below to install PostgreSQL on Centos 7.
Install PostgreSQL on Centos 7
Here you will install PostgreSQL from the default Centos repository. First, update your local package index with the command below:
sudo yum update
Then, you can install PostgreSQL and its dependencies on Centos 7 with the command below:
sudo yum install postgresql-server postgresql-contrib
When your installation is completed, you can initialize your database with the following command:
sudo postgresql-setup initdb
Now you can use the following commands to start and enable PostgreSQL on boot:
sudo systemctl start postgresql
sudo systemctl enable postgresql
Verify that your PostgreSQL is active and running on your Centos 7 with the command below:
sudo systemctl status postgresql
In your output you will see:
Output postgresql.service - PostgreSQL database server Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled) Active: active (running) since Tue 2021-12-21 03:26:09 EST; 57s ago Main PID: 3430 (postgres) CGroup: /system.slice/postgresql.service ├─3430 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432 ├─3431 postgres: logger process ├─3433 postgres: checkpointer process ├─3434 postgres: writer process ├─3435 postgres: wal writer process ├─3436 postgres: autovacuum launcher process └─3437 postgres: stats collector process
At this point, you have PostgreSQL active and running on your Centos 7. Let’s see some basic usage of PostgreSQL.
How to Use PostgreSQL on Centos 7
During the PostgreSQL installation, a user named Postgres will be created. You can change the Postgres user password with the command below:
sudo passwd postgres
You will be asked to enter the password and in your output, you will see:
Output passwd: all authentication tokens updated successfully.
Now you can switch to the PostgreSQL shell on Centos 7 with the following command:
su - postgres
Note: If you receive an error, you can set a valid shell on the user with the following command:
su --shell /bin/bash postgres
Then, you can run the same command:
su - postgres
You can change the PostgreSQL Postgres user password with the command below:
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'NewPassword';"
Remember to change the phrase ‘NewPassword’ with your own.
Here you can switch to the PostgreSQL user shell on Centos 7 with the following command:
In the Postgres shell, you can type \h to list the available commands:
postgres=# \h Output Available help: ABORT DECLARE ALTER AGGREGATE DELETE ALTER COLLATION DISCARD ALTER CONVERSION DO ALTER DATABASE DROP AGGREGATE ALTER DEFAULT PRIVILEGES DROP CAST ...
To exit the environment you can type \q.
Also, you can create new databases. With the following command, we create a database named testDB with the Postgres Linux user:
Next, you can create a new role with the Postgres Linux user on Centos 7 by using the following command, here we named it samplerole:
createuser -P samplerole
You will be asked to set a password for your new role.
Now you can connect to the new database that you have created with the following command:
You can use \l or \list commands to show all the databases. To know the current database you’re using, you can use \c. In case you want more information about connections such as the socket, port, etc. then you can use \conninfo.
To delete a database you can use the following command:
For more information, you can visit the PostgreSQL Documentation page.
At this point, you learn to set up PostgreSQL on Centos 7.
Hope you enjoy using it.
Maybe you have interested in these articles: