How to Install PostgreSQL on Ubuntu 22.04 Server

How to install PostgreSQL server on Ubuntu 22.04 server.

First of all, PostgreSQL is one of the most popular Relational Database Management System ( RDMS) that is  used especially in big enterprises to handle large and complex data. Particularly used to optimize efficiency in microservices based applications such as Dockerized apps.

Also Read

Redis vs PostgreSQL – What’s the Difference ? (Pros and Cons)

What is PostgreSQL?

PostgreSQL is a powerful, opensource and object relational database that uses Structured Query Language (SQL) for storing and accessing data from tables which are contained inside databases. Being an object relational database, PostgreSQL augments SQL language by offering complex data types and advanced features such as object inheritance and support for JSON querying. These features are ideal for supporting data intensive and enterprise workloads.

Moreover, PostgreSQL is a cross platform database server, that can be installed on Linux, Windows and macOS. Provides support for Java, Python, Perl, Ruby, Go, C, C# and many more.

With more than 20 years of active development it is primarily used to store data for web, mobile, analytical and geospatial applications. Currently developed and maintained by the PostgreSQL Global Development Group.

In this guide, we demonstrate how to install PostgreSQL on Ubuntu 22.04 server

Also Read

SQLite vs PostgreSQL – What’s the Difference? (Pros and Cons)

Key Features

1. Support for JSON Data.
2. Object inheritance.
3. Full text search.
4. Windows Functions.
5. ACID transactions.
6. Support for non atomic columns.
7. Asynchronous replication.
8.  Multi version concurrency control (MVCC).
9. PostgreSQL views.
10. Foreign keys, views, triggers and subqueries.
11. User defined types and functions.
12. Programming languages including Perl, PHP, Python, Java, C, C++ and Node.JS.

Also Read 

How to Setup/Install PostgreSQL Server on Azure/AWS/GCP

PostgreSQL Use Cases

As a component in LAMP Stack

A perfect alternative to MySQL and MariaDB in LAMP stack. LAPP stands for Linux Apache PostgreSQL PHP. Ideal option for supporting real time and dynamic websites.

General purpose OLTP database

Used as a primary data source for internet scale applications and solutions. OLTTP ( Online Transactional Processing ) is a database system that involves processing queries that involves inserting, updating and deleting data. It allows real time execution of huge amounts of database transactions typically over the internet.

Federated Hub database

PostgreSQL’s JSON support and Foreign Data Wrappers allow it to seamlessly link and work with other data stores, including NoSQL types. This allows it to act as a federated hub for polyglot database systems.

Geospatial database

When used alongside the PostGIS extension, PostgreSQL is an excellent choice for supporting geographic objects. This makes it a suitable geospatial data store for location based services and geographic information systems (GIS).

Now, let us shift gears and install PostgreSQL server  on Ubuntu 22.04.

Also Read

How to Install PostgreSQL on Debian 11 Server Tutorial (Step by Step)

Step 1: Update the system

Please, log into your Ubuntu 22.04 server and update the package lists as follows:

				
					$ sudo apt update
				
			

Once the update of the local package index is complete, head over to the next step.

Also Read

How to Install PostgreSQL on Ubuntu 20.04 Server Tutorial (Step by Step)

Step 2: Install PostgreSQL on Ubuntu 22.04

PostgreSQL database server package is provided  by Ubuntu’s repositories. As such, proceed and install PostgreSQL database server, using the APT package manager as follows.

				
					$ sudo apt install postgresql postgresql-contrib -y
				
			

To check the version of PostgreSQL installed, run the following command:

				
					$ psql -V
				
			

From the output below, you can see that we have installed PostgreSQL version 14.5.

Alternatively, you can run the following query straight from command line:

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

Output

				
					version                                                             
---------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)
				
			

Also Read

How to Create PostgreSQL Docker Image Container (Docker-Compose)

Step 3: Check the status of PostgreSQL

When installed, the PostgreSQL starts automatically without any user intervention.  To verify this, check the running status of the PostgreSQL daemon as follows.

				
					$ sudo systemctl status postgresql
				
			

The output clearly indicates that the PostgreSQL database service is up and running.

By default, PostgreSQL listens on TCP port 5342. To confirm this, run the following ss command.

				
					$ ss -pnltue  | grep postgresql
				
			

You should get the following output:

Great! We have successfully installed and verified that the PostgreSQL database server is running. 

Also Read

MySQL vs PostgreSQL – What’s the Difference (Pros and Cons)

Step 4: Interact With PostgreSQL shell

Up until this point, we have successfully installed PostgreSQL server.  We will now demonstrate how to log into the shell. By default, a user called Postgres is created with the installation of Postgres user.  To login to PostgreSQL using ‘postgres‘ user, run the command.

				
					$ sudo su - postgres 
				
			

Next, access the shell as follows:

				
					$ psql
				
			

You can print the connection information by running the command:

				
					\conninfo
				
			

By default, PostgreSQL servers ship with three databases: postgres, template0, and template1. To list the databases, run the command:

				
					\l
				
			

Postgres is the default database that you connect to before creating other databases. Meanwhile  template0 and template1 databases are simply skeleton databases. These should not be removed or altered.

Also Read

PostgreSQL vs SQL Server – Which is Better? (Pros and Cons)

Step 5: Create PostgreSQL Database and User

To create a database in PostgreSQL, access the psql shell and run the following command. In this example, testdb is the database name.

				
					CREATE DATABASE testdb;
				
			

To create a user straight from the psql shell, run the following command. For example, to create a user called  ‘robert’ with a password, execute the command:

				
					CREATE USER robert WITH ENCRYPTED PASSWORD 'StrongPassword';
				
			

To grant all the privileges to the user on the database created, run the command:

				
					GRANT ALL PRIVILEGES ON DATABASE testdb TO robert;
				
			

NOTE

You can also create a database by simply switching to the postgres account and running the: createdb command as shown.

				
					createdb mydatabase
				
			

You can then switch to the psql shell and confirm that the database was created as shown.

				
					\l
				
			

Additionally, you can create a user using the createuser command. In this example, we are creating a user called jack.

				
					createuser jack
				
			

Next, access the psql shell and create the user’s password as shown:

				
					ALTER USER jack WITH ENCRYPTED PASSWORD 'StrongPassword'
				
			

Also Read

How to Use PostgreSQL Coalesce Function with Examples

Step 6: Secure PostgreSQL Admin User

As previously mentioned, once PostgreSQL is installed, a new user account called Postgres is created. This account is associated with the default Postgres role. As you have already noticed and does not require any password for authentication. 

To prevent unauthorized access to the database and underlying data structures, it’s recommended that you configure a password for the postgres user which also acts as an administrative user for the PostgreSQl database server.

Therefore, to secure the Postgres user account with a password, run the following command:

				
					# ALTER USER postgres PASSWORD 'SecurePassword';
				
			

Of course, ‘SecurePassword‘ is just a placeholder. Be sure to replace it with a strong password to ensure better protection.

Exit PostgreSQL shell by running the command:

				
					# \q
				
			

Also Read

When To Use MongoDB Instead Of PostgreSQL For Web Projects

Step 7: Configure Remote Connections to PostgreSQL Server

PostgreSQL, by default, only accepts connections from localhost or the system on which it was installed. You might want to allow remote connections in order to allow other users to access the database server or even yourself if you’ll be accessing it remotely.

PostgreSQL derives its configuration from the postgresql.conf file which is located in the /etc/postgresql/<version>/main/ directory. The version indicates the version of PostgreSQL installed.

For example, in our case, the full path to the configuration file is /etc/postgresql/14/main/postgresql.conf. So access the configuration file using your preferred text editor. In this example we are using vim.

				
					sudo vim /etc/postgresql/14/main/postgresql.conf
				
			

Under the CONNECTIONS AND AUTHENTICATION section locate and uncomment the ‘listen_addresses’ directive. Replace ‘localhost‘ with the asterisk sign ‘*‘. This allows PostgreSQL to listen to all remote IP addresses.

Save the changes and exit the file.

Next, edit the pg_hba.conf in order to allow IPv4 connections from remote clients.

				
					
$ sudo vim /etc/postgresql/14/main/pg_hba.conf
				
			

Under ‘IPv4 local connections‘, modify the fourth column as follows to allow global remote connections.

				
					0.0.0.0/0
				
			

Save the changes and exit the configuration file.

To effect the changes made, restart PostgreSQL service.

				
					$ sudo systemctl restart postgresql
				
			

If you have a firewall running, allow port 5342 as shown.

				
					$ sudo ufw allow 5342/tcp
				
			

				
					$ sudo ufw reload
				
			

To connect to the PostgreSQL server from a remote client, execute the command. Here, 192.168.2.108 is the server’s IP address.

				
					$ psql -h 192.168.2.108 -U postgres
				
			

Thank you for reading How to install PostgreSQL server on Ubuntu 22.04 server. We shall conclude this article blog. 

Also Read

Choosing the Right Database – MongoDB vs PostgreSQL vs MySQL for Web Applications

How to install PostgreSQL server on Ubuntu 22.04 server. Conclusion

In this article, we have walked you through the installation of PostgreSQL  on Ubuntu 22.04. We have further demonstrated how to interact with the PostgreSQL shell and perform various tasks such as creating a database and user and configuring remote connection to the server.

For more information about PostgreSQL, check out OUR blog by navigating here.