Welcome to this step-by-step guide on how to install PostgreSQL on Ubuntu. PostgreSQL, often referred to as Postgres, is a powerful, open-source object-relational database system. It is known for its robustness, scalability, and support for advanced data types. This guide will walk you through the entire installation process, from updating your system to configuring PostgreSQL.

Prerequisites

Before we begin, make sure you have the following:

• A machine running Ubuntu 20.04 or later.
• Sudo privileges to execute commands as a superuser.
• A stable internet connection to download necessary packages.

Updating the System

First, it’s essential to ensure your system is up to date. Open your terminal and run the following commands:

1
2

sudo apt update
sudo apt upgrade -y

This will update the list of available packages and their versions and install any newer versions of the packages you have.

Installing PostgreSQL

To install PostgreSQL, execute the following command in your terminal:

1

sudo apt install postgresql postgresql-contrib -y

The postgresql-contrib package provides additional utilities and extensions for PostgreSQL. Once the installation is complete, PostgreSQL is installed on your system.

Starting PostgreSQL Service

After installation, you need to start the PostgreSQL service. Use the following command:

1

sudo systemctl start postgresql

To ensure that PostgreSQL starts automatically on boot, enable the service with:

1

sudo systemctl enable postgresql

Verifying the Installation

To verify that PostgreSQL is installed correctly, you can check its status:

1

sudo systemctl status postgresql

You should see a message indicating that PostgreSQL is active and running.

Basic PostgreSQL Commands

Here are some basic commands to interact with PostgreSQL:

• Access the PostgreSQL prompt:

  1 2	sudo -i -u postgres psql

• Exit the PostgreSQL prompt:

  1

  \q

• Check PostgreSQL version:

  1	postgres=# SELECT version();

Configuring PostgreSQL

PostgreSQL configuration files are located in the /etc/postgresql/ directory. The main configuration file is postgresql.conf, and you can edit it to change settings like the listening address, ports, and more.

To edit the configuration file:

1

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

After making changes, restart PostgreSQL to apply them:

1

sudo systemctl restart postgresql

Creating a New PostgreSQL User

To create a new user in PostgreSQL, follow these steps:

1. Switch to the PostgreSQL user:

   1	sudo -i -u postgres

2. Open the PostgreSQL prompt:

   1

   psql

3. Create a new user:

   1	CREATE USER myuser WITH PASSWORD 'mypassword';

4. Grant privileges to the user:

   1	ALTER USER myuser WITH SUPERUSER;

5. Exit the PostgreSQL prompt:

   1

   \q

Read Also : How to connect PostgreSQL with Node.js and Sequelize

Creating a New Database

To create a new database, use the following steps:

1. Switch to the PostgreSQL user:

   1	sudo -i -u postgres

2. Open the PostgreSQL prompt:

   1

   psql

3. Create a new database:

   1	CREATE DATABASE mydatabase;

4. Assign the new database to a user:

   1	GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

5. Exit the PostgreSQL prompt:

   1

   \q

Connecting to the Database

To connect to the newly created database, use the following command:

1

psql -U myuser -d mydatabase -h 127.0.0.1 -W

• U specifies the username.
• d specifies the database name.
• h specifies the host.
• W prompts for the password.

Conclusion

In this comprehensive guide, we covered the entire process of installing PostgreSQL on Ubuntu, from updating your system to creating and connecting to a database. PostgreSQL is a versatile database system, and with the knowledge from this guide, you should be able to manage your databases effectively.

FAQs

Q1: How do I uninstall PostgreSQL from Ubuntu?

To uninstall PostgreSQL, use the following command:

1

sudo apt-get --purge remove postgresql postgresql-* -y

Q2: How do I reset a PostgreSQL user password?

To reset a user password, first switch to the PostgreSQL user and open the prompt:

1
2

sudo -i -u postgres
psql

Then, execute the following command:

1

ALTER USER myuser WITH PASSWORD 'newpassword';

Q3: Can I install multiple versions of PostgreSQL on the same machine?

Yes, you can install multiple versions of PostgreSQL on the same machine. Each version will have its own directory and port. Make sure to configure them correctly in their respective configuration files.

Q4: How do I backup and restore a PostgreSQL database?

To backup a database, use:

1

pg_dump mydatabase > mydatabase_backup.sql

To restore a database, use:

1

psql mydatabase < mydatabase_backup.sql

Q5: What is the default port for PostgreSQL?

The default port for PostgreSQL is 5432. You can change this in the postgresql.conf file if needed.

By following this guide, you should have a robust PostgreSQL installation on your Ubuntu system, ready to handle your data management needs. Happy databasing