How to Install and Connect to PostgreSQL on Ubuntu

PostgreSQL or Postgres is an open-source, free relational database management system with great extensibility and SQL compliance. It is also a commonly used Relational Database Management System (RDMS) for handling massive and complicated data, particularly in large companies. 

It is a multi-platform database server that can be installed on Linux, Windows, and macOS, and it has extensive support for programming languages like Python, Java, Perl, Ruby, Go, C, and C#. The main features are:

• Easily expandable
• Supports time-series data types
• Versatile
• Efficient and cost-effective
• Scalable and supports concurrency
• Supports non-relational and relational data types

PostgreSQL has a wide range of features that can be used by administrators to create fault-tolerant systems and maintain data integrity, as well as by developers to create apps and end users to manage their data regardless of the size of the dataset. PostgreSQL is not only open source and free, but it is also very extensible. You may create new functions and specify your own data types.

PostgreSQL makes an effort to follow the SQL standard where doing so does not conflict with established functionality or potentially results in poor architectural choices. Most of the SQL standard's essential capabilities are supported, albeit occasionally with slightly different syntax or functionality. Ongoing progress in this direction can be anticipated.

PostgreSQL's primary goal is to manage a range of tasks, from simple technologies to online services or the data warehouse with multiple concurrent users. It supports both SQL for relational queries and JSON for non-relational queries. Many different businesses across many different industries, including financial services, information technology, government, and media and communications, employ PostgreSQL databases, which offer enterprise-class database solutions.

Here, you can learn how to install and connect to PostgreSQL on Ubuntu 22.04.

Some of the critical PostgreSQL configurations are:

Default PostgreSQL port 5432
The default user will be "postgres"
Important Configuration files are located in: /etc/postgresql/postgresql.conf
/etc/postgresql/pg_hba.conf
Default database    postgres
Default data directory    /var/lib/postgresql/

Requirements

• Ubuntu 22.04 server that has been pre-configured.
• A user with sudo/root access.
• A basic firewall.

Install PostgreSQL on Ubuntu

Step 1: Update Your Linux System

Postgres packages are available in Ubuntu's default repositories; therefore, you can install them by using the APT packaging system. To update the package lists, connect to your Ubuntu 22.04 server and run the following command.

sudo apt update

Step 2: PostgreSQL Installation

You can now install the Postgres package using the following methods.

Method 1: From APT Repository

This is the most common method used and will install the latest version of PostgreSQL.

sudo apt-get -y install postgresql

Method 2: From Local Ubuntu Repository

The -contrib package adds some extra utilities and functionality.

sudo apt install postgresql postgresql-contrib -y

Now check the version of PostgreSQL installed by running the following command.

psql -V

The output below indicates that you have installed PostgreSQL version 14.7.

You can also use the below command to find the version directly.

sudo -u postgres psql -c "SELECT VERSION();"

Step 3: Check Running Status of PostgreSQL

Once installed, you can check the status of the PostgreSQL daemon using the following commands.

sudo systemctl status postgresql

If the PostgreSQL service is up and running, the output would be:

PostgreSQL listens to TCP port 5342 by default, and this can be verified using the following command.

ss -pnltue  | grep postgresql

Connect to PostgreSQL

Method 1: Via SQL Shell

You can now connect and interact with the PostgreSQL shell by creating a test user Postgres. You can log in to the PostgreSQL shell using the test user and access the shell using the following commands.

sudo su - postgres

psql

To print the connection details, run the below command.

\conninfo

PostgreSQL servers come pre-configured with postgres, template0, and template1 databases. You may use the subsequent command to list the databases.

\l

Postgres will act as the default database before creating any new database. The databases template0 and template1 are only skeleton databases. These must not be changed or removed.

Method 2: Via pgAdmin Tool

The pgAdmin tool helps to connect to the PostgreSQL database server via an in-built graphical user interface. Here, you are using pgadmin4 to connect to the database.

Step 1: Launch the Application

You need to install pgadmin4 on your system and open the application. The following window will be opened once you click on the pgAdmin application.

Step 2: Create a New Server

Navigate to CreateServer by right-clicking the server's node. Choose the server name on the next window options and click on the connection tab.

Step 3: Connection 

You can now enter the hostname and password for the Postgres user and then click on the Save button.

Step 4: Server Details

Click on the server node and expand the new server. You can see the default database Postgres.

Step 5: Using the Query Tool

Click on Tools, and then click Query Tool.

Now enter the below command in the Query Editor and click on the Execute button.

Select Version();

The output will show the current version of PostgreSQL being used.

Creating a New PostgreSQL Database and User

You can use the below commands to create a new database and user in PostgreSQL.

CREATE DATABASE testdb;

CREATE USER sapta WITH ENCRYPTED PASSWORD 'Sapta@123';

GRANT ALL PRIVILEGES ON DATABASE testdb TO sapta;

Password Protecting PostgreSQL Admin User

When you install PostgreSQL, a new user, Postgres, is created. This account will be assigned the default Postgres role, which does not require any password for authentication. This can pose security issues. Hence, to secure the user account and prevent unauthorized access, you can assign a password for this user account.

The following command can be used to assign a password for the PostgreSQL user.

ALTER USER postgres PASSWORD ‘postgres@123’;

Once done, exit the shell by using the below command.

\q

Enabling Remote Connections to PostgreSQL Server

PostgreSQL, by default, only accepts connections from localhost or the system on which it was installed.

If you want to connect from remote locations, the remote connection feature needs to be enabled. For this you need to make changes to the PostgreSQL configuration file located at /etc/postgresql/<version>/main/ directory. 

In this case, the file would be at /etc/postgresql/14/main/postgresql.conf.

Open /etc/postgresql/14/main/postgresql.conf.

Under the Connection and Authentication section, edit the listen address field to ‘*’, which will allow remote connections from all IPs.

Save the changes and exit.

Now, to allow IPv4 addresses, you can edit /etc/postgresql/14/main/pg_hba.conf.

Under IPv4 local connections, you can allow global connections or specific IP addresses.

Save the changes and restart the PostgreSQL service for the changes to be effective.

systemctl restart postgresql

If there is a firewall running, enable port 5342.

sudo ufw allow 5342/tcp
sudo ufw reload

Now you can verify the remote connectivity using the following command.

psql -h 192.168.153.134  -U postgres

Here, 192.168.153.134 is the server IP.

How to Uninstall PostgreSQL Database on Ubuntu 22.04

You can un-install PostgreSQL using the following command.

sudo apt remove postgresql postgresql-contrib

Final Thoughts

You have now set up PostgreSQL on Ubuntu 22.04 server and learned how to connect to the database, create new databases, enable remote connections, etc. Consider adopting PostgreSQL if you want an effective and stable database management system. Liquid Web offers a dedicated server platform that you can use to try these installation steps to configure your own database server.