Installation

2. Install PostgreSQL on CentOS 7
Then, install PostgreSQL with the following command:

sudo yum install postgresql-server
Note that installation might take some time to finish.

3. Initialize the Database
Once the process is done, initialize the database by using the Linux command below:

sudo postgresql-setup initdb
4. Start the Database
Now, it’s time to activate the PostgreSQL server by running the following command:

sudo systemctl start postgresql.service
5. (Optional) Enable PostgreSQL
With the installation complete and initialized, you can configure PostgreSQL to start on every system reboot automatically. To do that, use this command:

sudo systemctl enable postgresql.service
PostgreSQL Basic Setup
In this section, we will go over the basic configuration for PostgreSQL.

Connect to PostgreSQL

In order to start using PostgreSQL, you will need to connect to its prompt. Start by switching to the postgres user:

sudo su postgres
Then, start PostgreSQL:

psql
Here, it’s possible to check the list of all available commands by typing \h. Alternatively, you can use \h followed by a command for which you need more information. To exit the whole environment, use \q.

Change User Password

By default, Linux will create a new postgres user whenever you install PostgreSQL. If you wish to modify the postgres user password, use the following command:

sudo passwd postgres
A prompt to enter the new password twice will appear. Next, switch to the PostgreSQL prompt and finish changing the password for the PostgreSQL postgres user:

su - postgres
If you receive an error, set a correct shell for the user with the command below:

su --shell /bin/bash postgres
Then, perform the same command:

su - postgres
Alternatively, it’s also possible to change the user password from the PostgreSQL prompt. To do this, you’ll need to first connect to the prompt:

sudo -u postgres psql
Then, to change the password, use the below command. Make sure to input your new password to replace NewPassw0rd:

ALTER USER postgres PASSWORD 'NewPassw0rd';
Lastly, restart the PostgreSQL service to enable these changes:

sudo service postgresql restart
Create New User

The easiest way to create a new user is via interactive mode. First, switch to the postgres user:

su - postgres
Now, create a new user by entering the command below and choosing a name and user role:

createuser --interactive
PostgreSQL createuser interactive command used to create a new role/user
To validate the new user, log in to PostgreSQL:

psql
From here, you can check all currently active users:

\du
The output of \du command on PostgreSQL prompt. It displays all currently created users
Create a Database

Since you now have a user, all there is left to do is create a database with the createdb command.

Keep in mind that PostgreSQL assumes that the role name should be the same as the database name. Therefore, let’s create a database with the same name as our user:

sudo -u postgres createdb newrole
To log in to the newly created database, you need to make a Linux user account under the same name:

sudo adduser newrole
Then, run the following command to connect:

sudo -u newrole psql
You can use \l or \list commands to show all databases. To identify your current database, type \c. In case you want more information about connections, such as the socket or port, use \conninfo.

The output of \conninfo command. It shows comprehensive information about connection to database
Delete a Database

You can delete a database using the dropdb command with the following syntax:

dropdb yourdatabasename
Important! Remember to verify which database you are deleting, as this action is non-reversible.

Apart from the functions used in this article, PostgreSQL is very similar to other database management systems and has many familiar features:

Table creation
Table deletion
Table updates
Column addition
Drop column
Query table
Alter commands
Grant privileges
The syntax for these is similar to most other database management commands. For example, you can list all tables by using the \dt command. To list all roles, run the \du command.

To learn more, we encourage you to read PostgreSQL’s official documentation.