PostgreSQL Setup on Ubuntu 24.04: Easy Installation and Configuration

With this step-by-step guide, you will learn PostgreSQL Setup on Ubuntu 24.04. PostgreSQL is a powerful, open-source relational database management system (RDBMS). It’s known for its scalability, and support for advanced data types and performance optimization features.

Why Choose PostgreSQL on Ubuntu 24.04?

Ubuntu 24.04, being a stable and popular Linux distribution, provides an excellent environment for running PostgreSQL. The combination of Ubuntu’s user-friendliness and PostgreSQL’s powerful features ensures a seamless and efficient database management experience.

Prerequisites

Before you begin the PostgreSQL setup on Ubuntu 24.04, you need the following requirements:

• A system running Ubuntu 24.04
• A user account with sudo privileges
• An active internet connection

By following the steps below, you can easily start your PostgreSQL setup on Ubuntu 24.04 and get it up and running on your server. Let’s begin the installation process.

Step 1 – PostgreSQL Setup on Ubuntu 24.04 – Installation Process

First, you must run the system update and upgrade by using the following command:

# sudo apt update
# sudo apt upgrade -y

Then, you must install the postgresql-common package which includes utilities for managing PostgreSQL. To do this, run the following command:

sudo apt install postgresql-common -y

Next, add the PostgreSQL repository to your Ubuntu 24.04 which includes the latest version of PostgreSQL. To do this, run the command below:

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

This script will add the PostgreSQL Global Development Group (PGDG) APT repository to Ubuntu 24.04.

Again run the system update with the following command:

sudo apt update

Now use the following command to install PostgreSQL on Ubuntu 24.04:

sudo apt install postgresql -y

Step 2 – Start and Enable PostgreSQL Service

After your PostgreSQL setup on Ubuntu 24.04 is completed, you need to start and enable your service with the following commands:

# sudo systemctl start postgresql
# sudo systemctl enable postgresql

Then, verify PostgreSQL service is active and running on Ubuntu 24.04 with the command below:

sudo systemctl status postgresql

In your output, you should see:

Step 3 – Access and Secure PostgreSQL Service on Ubuntu 24.04

PostgreSQL uses the psql command-line interface to interact with the database. By default, PostgreSQL creates a user named postgres. Switch to this user and access the PostgreSQL prompt with the following command:

sudo -i -u postgres psql

In your output, you should see:

Now to secure your PostgreSQL, you can change your postgres user password. To do this, from your PostgreSQL shell, run the following command:

postgres=# \password postgres

You will be asked to enter a password. Once you are done, exit from your PostgreSQL shell with the command below:

postgres=# \q

Step 4 – Configure Remote Access For PostgreSQL Server

By default, PostgreSQL only allows local connections. To enable remote access, you need to modify the PostgreSQL configuration files.

First, you need to open the postgresql.conf file in your preferred text editor like Vi Editor or Nano Editor:

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

Find the line that starts with listen_addresses, uncomment it, and modify it to allow connections from all IP addresses:

listen_addresses = '*'

When you are done, save and close the file.

Next, open the pg_hba.conf file to configure client authentication:

sudo vi /etc/postgresql/16/main/pg_hba.conf

Add the following line at the end of the file to allow remote connections:

host    all             all             0.0.0.0/0               md5

Once you are done, save and close the file.

Restart the PostgreSQL service to apply the changes:

sudo systemctl restart postgresql

You can verify if PostgreSQL is installed and running correctly by logging in to the PostgreSQL shell with the postgres user:

sudo -i -u postgres psql

Step 5 – Creating a New PostgreSQL Database and User

At this step of PostgreSQL setup on Ubuntu 24.04, you can learn to create a new user and database. From your PostgreSQL shell, you can use the following commands to create a new database and a user with privileges to manage it:

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

To see the list of databases, you can run:

postgres=# \l

To exit the PostgreSQL prompt, run the command below:

postgres=# \q

Step 6 – Backup and Restore PostgreSQL Databases

Regular backups are essential to safeguard your data. PostgreSQL provides tools to perform backups and restore databases.

To backup a database, you can use the pg_dump utility:

pg_dump mydatabase > mydatabase_backup.sql

To restore a database from a backup file, you can use the psql command:

psql mydatabase < mydatabase_backup.sql

That’s it, you are done with the PostgreSQL setup on Ubuntu 24.04. For more information, you can visit the official website.

Conclusion

PostgreSQL Setup on Ubuntu 24.04 is straightforward when you follow these steps. With PostgreSQL’s powerful features and Ubuntu’s stable environment, you’ll have a robust database system ready to handle your applications. Hope you enjoy it. Also, you may like to read the following articles:

OpenLiteSpeed Setup on Ubuntu 24.04 (LOMP Stack)

Change Ubuntu 24.04 Desktop to Cinnamon

Install Grafana on Ubuntu 24.04

Enable ionCube Loader on Ubuntu 24.04

FAQs

How do I check the version of PostgreSQL installed?
You can check the installed PostgreSQL version by running psql –version in the terminal.

How can I change the password for a PostgreSQL user?
To change a user’s password, log into the PostgreSQL prompt and run: ALTER USER username WITH PASSWORD ‘newpassword’;

How do I create a backup of all databases?
Use the pg_dumpall utility to create a backup of all databases: pg_dumpall > all_databases_backup.sql

Can I run PostgreSQL on a different port?
Yes, you can change the port by modifying the port parameter in postgresql.conf file and restart the PostgreSQL service.