How to Install PostgreSQL in Linux VPS

PostgreSQL, often referred to as Postgres, is a powerful open-source relational database management system (RDBMS) known for its reliability, robustness, and extensibility. It’s widely used by developers and organizations for managing data and building scalable applications. In this guide, we will walk you through the process of installing PostgreSQL on both Ubuntu and Red Hat Enterprise Linux (RHEL) based distributions, such as CentOS, Rocky Linux, and Alma Linux. We’ll cover the setup, configuration, and basic usage to get you started with PostgreSQL on your Linux VPS.

What is PostgreSQL?

PostgreSQL is an advanced, enterprise-class RDBMS that supports a wide range of features, including:

• ACID Compliance: PostgreSQL ensures data integrity by supporting ACID (Atomicity, Consistency, Isolation, Durability) properties, making it suitable for critical applications.
• Extensibility: It offers support for various extensions and custom data types, allowing users to tailor the database to their specific requirements.
• Robustness and Reliability: With its proven track record and active development community, PostgreSQL is known for its stability and reliability.
• Scalability: PostgreSQL can handle large volumes of data and high transaction loads, making it suitable for both small-scale projects and enterprise-level applications.
• Open Source: Being open source, PostgreSQL is freely available and supported by a vibrant community of developers and users.

Advantages of PostgreSQL:

PostgreSQL, often hailed as the world’s most advanced open-source relational database, boasts a myriad of advantages that cater to diverse application requirements. From its robustness and extensibility to its comprehensive feature set, PostgreSQL stands out as a top contender in the realm of relational database management systems (RDBMS).

• Feature-rich Environment: PostgreSQL provides a rich set of features, including advanced SQL support, JSON support, full-text search capabilities, and support for geospatial data. These features enable developers to implement complex database functionalities efficiently.
• Highly Extensible: PostgreSQL’s architecture allows for seamless extension through the use of custom data types, procedural languages (such as PL/pgSQL, PL/Python, PL/Perl), and numerous built-in and third-party extensions. This extensibility empowers users to tailor the database to their specific needs and integrate additional functionality seamlessly.
• Strong Community Support: PostgreSQL boasts a vibrant and active community of developers, contributors, and users who collaborate to enhance the database’s capabilities, fix bugs, and provide support. The community-driven nature of PostgreSQL ensures rapid development, timely updates, and extensive documentation, making it easier for users to resolve issues and stay updated with the latest features.
• Security: PostgreSQL provides robust security features, including SSL support, role-based access control (RBAC), and data encryption capabilities.
• Open Source and Cross-platform Compatibility: As an open-source project, PostgreSQL is freely available and can be deployed on various operating systems, including Linux, Windows, macOS, and BSD. Its cross-platform compatibility allows users to deploy PostgreSQL in diverse environments and seamlessly migrate between different platforms as needed.

Installing PostgreSQL on Ubuntu:

Step 1: Update Package Repository:

Before installing PostgreSQL, it’s essential to ensure that your package repository is up to date.

sudo apt update

Step 2: Install PostgreSQL:

Use the following command to install PostgreSQL and its additional contrib package, which provides additional features and functions.

sudo apt install postgresql postgresql-contrib

Step 3: Verify Installation:

Once the installation is complete, you can check the status of the PostgreSQL service to ensure it’s running correctly.

sudo systemctl status postgresql

Step 4: Configure Firewall (if needed):

If your firewall is enabled and you need to access the PostgreSQL database remotely, you’ll need to allow traffic on port 5432, which is the default port used by PostgreSQL.

sudo ufw allow 5432/tcp

Installing PostgreSQL on RHEL based Distributions (CentOS, Rocky, Alma Linux):

Step 1: Enable PostgreSQL Repository:

Before installing PostgreSQL on RHEL-based distributions, you need to enable the PostgreSQL repository.

RHEL 8.x:

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

RHEL 7.x:

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Disable the built-in PostgreSQL module using the command below:

Sudo dnf -qy module disable postgresql

Step 2: Install PostgreSQL:

Install the PostgreSQL server and contrib package using the following command:

sudo yum install postgresql16-server postgresql16-contrib

Step 3: Initialize PostgreSQL:

Initialize the PostgreSQL database cluster using the `postgresql-12-setup` command.

sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

Step 4: Start PostgreSQL Service:

Start the PostgreSQL service and enable it to start automatically on boot.

sudo systemctl enable postgresql-16 sudo systemctl start postgresql-16

Step 5: Configure Firewall (if needed):

If the firewall is enabled, allow traffic on port 5432 to access PostgreSQL remotely.

sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent sudo firewall-cmd --reload

Access PostgreSQL Shell:

Access the PostgreSQL interactive terminal by switching to the `postgres` user and entering the PostgreSQL prompt.

sudo -u postgres psql

CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword'; 
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

\q

psql -V

Securing PostgreSQL

After PostgreSQL installation, a default user account named ‘postgres’ is automatically created with full superadmin privileges. Therefore, it’s imperative to set a strong password for the corresponding PostgreSQL database user account to enhance security.

To set a password for the ‘postgres’ user in PostgreSQL, you can use the following command within the PostgreSQL shell (psql):

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'new_password'"

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Issue the following command to install the pgAdmin.

sudo apt install pgadmin4-web

sudo /usr/pgadmin4/bin/setup-web.sh

Follow the prompts to configure pgAdmin, including setting up an initial email address and password for the administrator account.

sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm 

sudo yum install pgadmin4-web

sudo /usr/pgadmin4/bin/setup-web.sh

Follow the prompts to configure pgAdmin, including setting up an initial email address and password for the administrator account.

Access the pgAdmin From Web

Once configured, you can access pgAdmin via a web browser by navigating to http://serverip/pgadmin4. Log in with the email address and password you provided during the setup.

After logging in, you can add your PostgreSQL server to pgAdmin by clicking on the “Add New Server” button and entering the connection details (host, port, username, password) for your PostgreSQL server. Once added, you can manage your PostgreSQL databases remotely using pgAdmin’s graphical interface.