Dec 05, 2023
Edward S. & Ignas R.
How to Install PostgreSQL on CentOS 7
Nowadays, most modern applications include a database. However, because there are so many database management systems available, selecting the best one for your needs takes time. PostgreSQL is one of the most popular options.
PostgreSQL is a powerful relational database management system (DBMS) that is free and open source. It is scalable, dependable, community-driven, and equipped with advanced optimization features.
In this tutorial, we’ll show you two methods for installing PostgreSQL on a CentOS 7 VPS and go over the fundamentals of this database management system.
How Does PostgreSQL Work
Unlike NoSQL databases, PostgreSQL stores data in rows, columns, and tables. For relational queries, PostgreSQL uses SQL, while non-relational queries are processed with JSON.
One of the main advantages of PostgreSQL is its large community. Being open-source, it attracts many developers that actively contribute to this DBMS’ growth. Therefore, it’s no surprise that PostgreSQL offers features such as advanced optimization and data types similar to companies like SQL Server and Oracle.
Important! CentOS Linux 8 reached its End of Life (EOL) on Dec. 31, 2021. While CentOS Linux 7 is still supported, it will reach EOL on June 30, 2024. We recommend keeping that in mind when choosing this OS. You can read more about it on the official website.
You can install PostgreSQL on CentOS 7 by using one of the following two methods:
- Install from the PostgreSQL repository
- Install PostgreSQL from existing CentOS repositories
Let’s review each of these methods in more detail.
How to Install PostgreSQL on CentOS 7 Using the PostgreSQL Repository
The first method is to download PostgreSQL from its official repository. This way, users can be sure they are getting the latest version. Keep in mind that choosing an older version from the official PostgreSQL website is also possible.
1. Access Your Server
2. Download PostgreSQL Repository RPM
Then, download and install the repository RPM for PostgreSQL by running the command below:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
3. Install PosgreSQL on CentOS 7
Next is installing the PostgreSQL 15 server on your machine:
sudo yum install -y postgresql15-server
4. Initialize the Database
Then, initialize the database:
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
5. Start the Database
After that, launch PostgreSQL with the following command:
sudo systemctl start postgresql-15
6. (Optional) Enable PostgreSQL Launch on Reboot
If you want PostgreSQL to launch automatically after a system reboot, use this command:
sudo systemctl enable postgresql-15
How to Install PostgreSQL on CentOS 7 Using the CentOS Repositories
The CentOS 7 repository contains PostgreSQL by default. However, note that it may not offer the latest version. Currently, the repository hosts PostgreSQL version 9.2.24.
1. Access Your Server
First, access your virtual private server via SSH.
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:
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:
To validate the new user, log in to PostgreSQL:
From here, you can check all currently active 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.
Delete a Database
You can delete a database using the dropdb command with the following syntax:
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.
If you need a reliable DBMS, consider PostgreSQL. It’s a powerful and scalable database management system that can suit anyone’s needs, as it offers enterprise-grade features for free.
In this tutorial, we have shown you two methods of how to install PostgreSQL on CentOS 7. We have also covered the basic PostgreSQL server setup process, from user creation to database deletion.
We hope that you found this tutorial useful. In case you have any questions, make sure to check out the official PostgreSQL documentation or leave a comment below.