How To Install PostgreSQL In Linux & Master Database Management

In this tutorial I’ll teach you how to install PostgreSQL in Linux in straightforward steps.

Before that, understanding PostgreSQL is essential! PostgreSQL, or just Postgres for short, is a relational database management system that operates on different operating systems like Windows, Linux, macOS, FreeBSD, and more.

Besides, runs on Structured Query Language, commonly referred to as SQL. It’s a go-to choice for a ton of popular projects, whether they’re big or small. It follows industry standards and comes packed with cool features like dependable transactions and the ability to handle multiple operations at once without locking reads.

What Are The Prerequisites for Installing PostgreSQL in Linux

Prerequisites are key to ensuring users have everything they need before they learn how to install PostgreSQL in Linux. They prevent errors like:

• Command not found- if they don’t have the right package manager.
• Permission Denied- if they miss the sudo/root access.
• Network error- try to install without an internet connection.

Logging in as Root in Linux

• To log in as root, you need the server’s public IP address and the password or private key for the root account.
• If not connected, use the command: ssh root@your_server_ip to log in as the root user.
• The root user has extensive privileges and should not be used regularly to avoid harmful changes.
• For daily tasks, create a new user account with limited privileges.

Creating a New User

• After logging in as root, you can create a new user account.
• You can choose any username for the new account.
• A password for the account is necessary, and you can also add optional details.

Granting Administrative Privileges

• To allow a normal user to run commands with administrative rights, grant them superuser or root privileges.
• Use the command: usermod -aG sudo sammy to add the user to the sudo group.
• This enables the user to execute commands with superuser rights while logged in as a regular user.

Ubuntu 20.04 Server Firewall Setup

• The UFW firewall can be used on Ubuntu 20.04 servers to limit connections to certain services.
• Applications can register their profiles with UFW, allowing it to manage them by name.
• The firewall permits SSH connections for easy access.
• To activate the firewall, type ufw enable and hit ENTER.
• You can check allowed SSH connections by typing ufw status.
• Update firewall settings as you install and configure more services.

Enabling External Access for Regular User

• After creating a regular user, you can SSH directly into that account.
• It’s best to remain logged in as root for troubleshooting and necessary adjustments.
• The method depends on whether the root account uses a password or SSH keys for access.
• If using password authentication, you can SSH into the new user account.
• To disable password authentication, set up SSH keys instead.
• If SSH keys are in use, password authentication can be disabled.

How to Install PostgreSQL in Linux- Debian/Ubuntu & From Source Code

Install PostgreSQL on Ubuntu

This guide will help you install PostgreSQL 16 on Ubuntu 22.04.

Step 1. Add PostgreSQL Repository

Start by updating the package list and installing required packages:

sudo apt update<br>sudo apt install gnupg2 wget

Next, add the PostgreSQL repository:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Then, import the repository key:

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

Finally, update the package list again:

sudo apt update

Step 2. Install PostgreSQL 16

First, install PostgreSQL and its additional modules:

sudo apt install postgresql-16 postgresql-contrib-16

Next, start the PostgreSQL service:

sudo systemctl start PostgreSQL

Then, enable the PostgreSQL service to start on boot:

sudo systemctl enable postgresql

Step 3. Configure PostgreSQL server

PostgreSQL configuration is in the postgresql.conf file. You can edit it with a text editor like nano or vim:

sudo nano /etc/postgresql/16/main/postgresql.conf

Change the listen_addresses to * for remote access:

listen_addresses = ‘*’
Set PostgreSQL to use md5 password authentication in the pg_hba.conf file for remote connections:

sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/16/main/pg_hba.conf<br>sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/16/main/pg_hba.conf<br>echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf<br>Restart PostgreSQL to apply the changes:

sudo systemctl restart PostgreSQL

Allow access to PostgreSQL through the firewall:

Enhance Your CyerPanel Experience Today!

Discover a world of enhanced features and show your support for our ongoing development with CyberPanel add-ons. Elevate your experience today!

Learn More

sudo ufw allow 5432/tcp

Connect to the PostgreSQL database server

First, connect to the PostgreSQL server as the postgres user:

sudo -u postgres psql

Next, set a password for the postgres user:

ALTER USER postgres PASSWORD '';

Make sure to replace with your desired password.

Finally, exit psql:

\q

How To Install PostgreSQL In Linux from Source Code

If you are an advanced user wanting the latest version or a tailored setup, you can compile PostgreSQL from the source.

Install required packages

<br>sudo apt install build-essential libreadline-dev zlib1g-dev flex bison -y

Get the latest PostgreSQL source code

<br>wget https://ftp.postgresql.org/pub/source/v15.3/postgresql-15.3.tar.gz

Unpack the files and navigate to the directory

<br>tar -xvzf postgresql-15.3.tar.gz<br>cd postgresql-15.3

Build and install PostgreSQL

<br>./configure --prefix=/usr/local/pgsql<br>make && sudo make install

How to Start, Stop & Restart PostgreSQL in Linux?

Step 1: Review the Postgres Status

Open the terminal and execute the following “sudo” command to review the current status of the Postgres server:

sudo systemctl status postgresql

The above snippet shows that the Postgres server is currently deactivated.

Step 2: Start the Postgres Server

Run the command “/etc/init.d/postgresql” with the start option to launch the Postgres server:

/etc/init.d/postgresql start<br>img

The output indicates that the Postgres server has started successfully.

Step 3: Check the Postgres Status

Run the command “/etc/init.d/postgresql” with the status option to check the Postgres status:

/etc/init.d/postgresql status<br>img

The output shows that the Postgres server is running successfully.

Step 4: Stop the Postgres Server

Enter the command “/etc/init.d/postgresql” followed by the stop option to stop the Postgres server:

/etc/init.d/postgresql stop<br>img

The output confirms that the Postgres server has been stopped successfully.

Step 5: Verify Postgres Status

Enter the command “/etc/init.d/postgresql” with the status option to verify the Postgres status:

/etc/init.d/postgresql status<br>img

The output confirms that the Postgres server has been stopped successfully.

How to Run PostgreSQL in Linux & Access the Database

How to Start PostgreSQL on Linux and Use the Database, Once you know how to start PostgreSQL in Linux, you can begin using it.

Change to the PostgreSQL User

PostgreSQL automatically creates a user called postgres. Switch to this user:

<br>sudo -i -u postgres

Access the PostgreSQL Database
Run:

<br>psql

Now you are in the PostgreSQL interactive shell! 🎉

Create a New Database

<br>CREATE DATABASE mydatabase;

View All Databases

\l

Exit the PostgreSQL Shell

\q

How to Upgrade PostgreSQL Version in Linux

Upgrading to a new PostgreSQL version improves security and adds new features.

Check Your Current PostgreSQL Version

psql --version

Upgrade PostgreSQL on Ubuntu/Debian

Install the latest version:

sudo apt update && sudo apt install postgresql -/

Start the upgrade process:

sudo pg_upgradecluster main

Upgrade PostgreSQL on CentOS/Fedora

Install the new version:

sudo dnf install postgresql16-server -y

Transfer your data:

sudo pg_upgrade -b /usr/pgsql-15/bin -B /usr/pgsql-16/bin -d /var/lib/pgsql/15/data -D /var/lib/pgsql/16/date

Conclusion

At this point, you should have learned how to install it in Linux and working on your Linux machine. No matter if you’re a beginner or an experienced programmer, knowing PostgreSQL can greatly enhance your skills in managing databases and improving performance.

FAQs

1. How to install PostgreSQL in Linux?
Use sudo apt install postgresql for Ubuntu or sudo dnf install postgresql for Fedora.

2. How to start PostgreSQL in Linux?
Execute sudo systemctl start postgresql.

3. How can I find the PostgreSQL version on Linux?
Type psql –version.

4. How to upgrade PostgreSQL version on Linux?
To upgrade PostgreSQL version in Linux, you can use sudo do-release-upgrade or pg_upgrade.

5. Is it possible to install several PostgreSQL versions on Linux?
Yes, but they need to operate on different ports.