In modern data management, there are several relational databases that you can use to store and retrieve structured data. One of them is PostgreSQL. If you're an Ubuntu user looking to harness the capabilities of PostgreSQL for your projects, you're in the right place. In this guide, we’ll take you through the process of installing PostgreSQL on an Ubuntu system and how to install PostgreSQL Client and connect to a server. 

What is PostgreSQL?

PostgreSQL is an open-source, powerful, and highly extensible relational database management system (RDBMS). It's known for its reliability, performance, and adherence to SQL standards. And just like MySQL, it’s mostly hosted on Linux. 

With two decades of open-source development, it supports both SQL (relational) and JSON (non-relational) querying. One of its most distinguishing features is its extensibility, which allows users to create custom data types, operators, and functions. This makes it suitable for various applications, from simple web applications to complex enterprise solutions.

PostgreSQL uses a client-server model, where clients connect to the PostgreSQL server to interact with databases. In addition, it also supports multiple programming languages making it a dependable choice for data storage and management.

Prerequisites

Prior to installing PostgreSQL installation journey, ensure the following prerequisites are in place:

• A functional Ubuntu system
• PostgreSQL Server and Client software components
• Administrative access to your Linux system as root or via the sudo command.

Steps to install PostgreSQL on Ubuntu

Installing PostgreSQL on your Ubuntu system involves a series of straightforward steps. The steps can also be used for other Ubuntu-based systems. Let’s dive in:

Add The Official Repository

To ensure a seamless installation from an official repository, it is advisable to utilize the official PostgreSQL repository. This is because PostgreSQL is updated more frequently than the Ubuntu package. 

To setup PostgreSQL on Ubuntu, you need to install prerequisite software packages and certificates for a secure SSL connection. Execute this command: 

Update Your System

Before proceeding to software installations, it's recommended to update the package list. This ensures that you're getting the latest versions. To upgrade, execute the following commands in a terminal window:

Install PostgreSQL Server

Once you have the latest version, you can proceed to install PostgreSQL on Ubuntu. Use the following command:

This command installs both the PostgreSQL database server and additional contributed modules that enhance PostgreSQL's functionality. Should you desire a specific older version, include this specification within the command.

Verify Installation Status

Once the installation is complete, PostgreSQL should be operational as a system service. To check its status, run the following command:

This action helps you identify any potential errors. By default, this database listens for incoming connections on port 5432.

Here are some of the important commands you can also use:

To restart this service:

To stop:

To disable:

To enable:

Access PostgreSQL

Once you install PostgreSQL, it creates a default admin user named “postgres”. To interact with PostgreSQL, you'll often use this user. It’s standard practice to use it to log in to your PostgreSQL database for the first time.

To start an interactive session with your local database, run:

This will log you in as the "postgres" user and give you access to the PostgreSQL shell.

After launching psql, you can check your connection details by keying in \conninfo. The default “postgres” user has no password; therefore, you should set one.

New Database Creation

Now that you are connected to the database server, you can create a new database or even roles. To craft a new database, use this command:

Replace "mydatabase" with the desired database name. Once the database is active, you can populate tables with information about your company or clients. 

You can also grant privileges to the database users. For instance, if your database is called “Fetch,” you can run this command to activate this.

Installing PostgreSQL Client

In cases where you don’t want the PostgreSQL Database server and just want to connect the client to a remote Database server, you can do it easily. Here’s how to work with Ubuntu to install postgresql client

Once you are done with the installation, use the command-line tool of the client to connect with the remote server:

You can also access the database on localhost using this command syntax:

Replace DB-name and DB -User with the one you want to connect.

Secure the PostgreSQL default Database

You also need to ensure that your PostgreSQL is secured. Set up a strong password for the user. Here’s how to change the default database admin user account 

Change the user password by running this command:

Log into the Postgres system account.

Change the admin database password using this command:

Configure Remote Access

By default, PostgreSQL only allows connections from the local host. Therefore, if you intend to connect to the PostgreSQL server remotely, you must make some configuration file changes. Open the configuration file using your preferred text editor:

Replace "[version]" with your PostgreSQL version, e.g., "13".

Restart PostgreSQL

After making changes to the configuration, restart the PostgreSQL service:

Uninstall or remove PostgreSQL 

After you install Postegre Ubuntu, you might want to remove the database server or client on your system. However, it's advisable to have a backup because this process will remove all databases. Run this command: 

If you have multiple versions of PostgreSQL and only want to remove one, you have to mention the specific version. For instance, if you need to remove 13, run this command: 

On the other hand, if you just need to remove the client one, run this:

Conclusion

PostgreSQL is a robust and feature-rich RDBMS that can be valuable to developers and businesses. Installing PostgreSQL on an Ubuntu system is a straightforward process that grants you access to a powerful data management tool. By following this step-by-step guide, you can leverage PostgreSQL's capabilities for your projects.