PostgreSQL, often simply Postgres, is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. With over 30 years of active development, it has earned a strong reputation for its proven architecture and data integrity. This guide aims to provide a detailed walkthrough for installing PostgreSQL on Ubuntu 23.04, catering to newcomers with clear instructions and real command-line examples.

Step 1: Update Your System

First, update your package list to ensure all your system packages are up-to-date. This helps avoid potential conflicts and ensures the PostgreSQL installation goes smoothly.

sudo apt update && sudo apt upgrade -y

Step 2: Install PostgreSQL

Ubuntu’s default repositories contain PostgreSQL packages, which makes the installation process straightforward.

• Install PostgreSQL: Execute the following command to install PostgreSQL along with the postgresql-contrib package, which adds some additional utilities and functionality:

sudo apt install postgresql postgresql-contrib -y

Step 3: Verify Installation

After the installation process completes, PostgreSQL should start automatically. You can verify it’s running by checking its status.

sudo systemctl status postgresql

If PostgreSQL is running correctly, you will see an output indicating that the service is active.

Step 4: Accessing the PostgreSQL Database

By default, PostgreSQL creates a user named postgres with role-based authentication. To log in to the PostgreSQL server, switch to the postgres user and then access the PostgreSQL prompt using the psql command.

1. Switch to the postgres user:

sudo -i -u postgres

2. Access the PostgreSQL prompt:

psql

You are now logged into the PostgreSQL database prompt, indicated by the postgres=# prompt.

Step 5: Creating a New Role and Database

As the postgres user, you can create a new role and assign a database to it. Here’s how:

1. Create a new role: From the PostgreSQL prompt (postgres=#), execute:

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';

Replace myuser with your desired username and mypassword with a strong password.

2. Create a new database: Still at the PostgreSQL prompt, execute:

CREATE DATABASE mydatabase;

Replace mydatabase with your desired database name.

3. Grant privileges: Grant all privileges of the newly created database to your new user:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Step 6: Enabling Remote Access (Optional)

To enable remote access to your PostgreSQL server, edit the postgresql.conf and pg_hba.conf files.

1. Edit postgresql.conf: Open the file in your editor:

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

Find the line #listen_addresses = 'localhost' and change it to listen_addresses = '*' to allow connections from any IP address.

2. Edit pg_hba.conf: Open the file:

sudo nano /etc/postgresql/12/main/pg_hba.conf

Add the following line to allow connections from your IP address:

host    all             all             your_ip_address/32        md5

Replace your_ip_address with your actual IP address.

After successfully installing PostgreSQL on Ubuntu 23.04, consider utilizing Shape.host Linux SSD VPS services for your database hosting needs. Shape.host offers robust, scalable Cloud VPS solutions, providing the perfect environment for running your PostgreSQL databases. With Shape.host, you benefit from high-performance SSD storage, strong security features, and excellent network connectivity, ensuring your database operations are smooth and efficient. Whether you’re managing large datasets or require high availability for your applications, Shape.host’s Cloud VPS services offer the flexibility and performance needed to support your PostgreSQL requirements.