How to install PostgreSQL in Ubuntu?

Introduction

PostgreSQL is one of the most advanced open-source relational database management systems. It is extensively known for its high functioning, scalability, and capacity to manage complicated inquiries effectively. Businesses and developers rely on it to administer structured as well as unstructured data with ease.

Unlike many other database systems, PostgreSQL is highly extensible. It supports custom functions, procedural languages, and advanced indexing techniques. Features like JSONB storage, full-text search, and concurrency control make it suitable for modern applications.

Ubuntu users find PostgreSQL easy to install and configure. It integrates well with various programming languages, including Python, Java, and Node.js. With this guide, you will acquire a step-by-step procedure for installing PostgreSQL on Ubuntu. You will also learn how to verify the installation, establish connections, and perform basic database operations.

What is PostgreSQL?

PostgreSQL is a resilient, open-source relational database management system (RDBMS). It is devised to administer an extensive array of workloads, from small-scale applications to huge-scale enterprise systems. Known for its stability and scalability, PostgreSQL has become a primary option for developers as well as businesses globally.

Key Features of PostgreSQL

1. SQL and NoSQL Support
   • PostgreSQL primarily follows the SQL standard but also provides NoSQL capabilities.
   • It supports JSON and JSONB data types, permitting developers to keep and query semi-structured data.
   • This makes it a flexible choice for applications that need both structured & unstructured data storage.
2. ACID Compliance
   • PostgreSQL ensures data integrity by following Atomicity, Consistency, Isolation, & Durability (ACID) foundation.
   • Transactions are operated reliably, stopping data corruption or loss even in the event of system crashes.
3. High Extensibility
   • Developers can create custom functions, operators, and data types.
   • It accommodates procedural languages like PL/pgSQL, PL/Python, PL/Perl, and PL/Tcl.
   • Extensions such as PostGIS (for geospatial data) and pg_partman (for partition management) further intensify its capacities.
4. Concurrency and Performance Optimization
   • Uses Multi-Version Concurrency Control (MVCC) to maintain several transactions efficiently without locking tables.
   • Offers advanced indexing techniques, incorporating Hash, GiST, GIN, B-tree, and BRIN indexes.
   • Supports parallel query execution, improving performance for complex operations.
5. Security and Authentication
   • Renders powerful security features, incorporating SSL encryption, role-based accessibility administration (RBAC), and row-level protection.
   • Supports multiple authentication methods such as LDAP, Kerberos, and SCRAM-SHA-256.
6. Scalability and Replication
   • Can handle massive datasets across distributed environments.
   • Supports Streaming Replication, Logical Replication, and Partitioning for high availability and load balancing.
7. Cross-Platform Compatibility
   • Fucntions on several OSs, incorporating Linux, macOS, Windows, and FreeBSD.
   • Integrates seamlessly with multiple programming languages such as Python, Java, PHP, & Ruby.

Why Do Enterprises Choose PostgreSQL?

• Open-Source and Cost-Effective – Unlike proprietary databases, PostgreSQL has no licensing fees.
• Reliable and Community-Driven – Backed by an active open-source community that continuously improves its features.
• Cloud-Ready – Compatible with major cloud providers such as Google Cloud, and Azure.
• Big Data Support – Works with Apache Hadoop, Spark, and Kafka for advanced data processing.

Also Read: How to List All Databases in PostgreSQL?

Steps to install PostgreSQL in Ubuntu

Installing PostgreSQL on Ubuntu is the simplest strategy. It requires updating system packages, installing the database server, & configuring it to begin on its own. Implement these steps to guarantee a smooth installation.

Step 1: Update System Packages

Before installing PostgreSQL, update the system package list. This guarantees you acquire the latest protection patches & software versions. Open the terminal and run:

sudo apt update && sudo apt upgrade -y

• sudo apt update refreshes the package list to get information about newer versions.
• sudo apt upgrade -y installs available updates for existing packages.
• The -y flag automatically confirms the upgrade, saving time.

Step 2: Install PostgreSQL and Additional Packages

Now, install PostgreSQL along with useful extensions:

sudo apt install postgresql postgresql-contrib -y

• The postgresql package installs the core database server.
• The postgresql-contrib package includes extra features such as:
  • pg_stat_statements (tracks query execution statistics).
  • pgcrypto (provides cryptographic functions).
  • hstore (stores key-value pairs in a single column).

Ubuntu’s default repositories contain stable PostgreSQL versions. If you require the contemporary version, you may add the official PostgreSQL repository before installing.

Step 3: Verify Installation Files

After installation, confirm that PostgreSQL was installed correctly:

dpkg -l | grep postgresql

This lists installed PostgreSQL packages along with their versions. If PostgreSQL does not appear in the output, the installation may not have finished aptly.

Step 4: Enable and Start PostgreSQL Service

To ensure PostgreSQL runs automatically after a system reboot, enable it with:

sudo systemctl enable postgresql

Then, start the PostgreSQL service manually:

sudo systemctl start postgresql

To confirm that PostgreSQL is operating, verify its status:

sudo systemctl status postgresql

You must see an output indicating that the service is not inactive (running). If the service is inactive, restart it with:

sudo systemctl restart postgresql

Step 5: Allow PostgreSQL Through Firewall (If Enabled)

If your system has ufw (Uncomplicated Firewall) enabled, allow PostgreSQL connections:

sudo ufw allow 5432/tcp

• Port 5432 is the default port for PostgreSQL.
• This command allows remote clients to connect if configured.

To check firewall rules:

sudo ufw status

If the firewall is inactive, PostgreSQL will function without additional configuration.

Step 6: Verify PostgreSQL Installation

Finally, check the installed PostgreSQL version:

psql --version

If you see output similar to psql (PostgreSQL) 14.1, the installation was successful.

Next Steps

• Configure PostgreSQL for remote access if needed.
• Create and manage user roles for database security.
• Optimize performance by tuning PostgreSQL settings.

Verify the Installation of PostgreSQL

To confirm PostgreSQL is installed, check its version:

psql --version

You should see an output like:

psql (PostgreSQL) 14.1

If you get a version number, PostgreSQL is installed correctly.

Also Read: Postgresql Drop Database with Examples

Connect to PostgreSQL

By default, PostgreSQL constructs a user named postgres. Switch to this user with:

sudo -i -u postgres

Then, access the PostgreSQL command-line interface (psql) by running:

psql

You will now see the PostgreSQL prompt:

postgres=#

To exit, type:

\q

This will make you come back you to the terminal.

How to create a database on PostgreSQL?

To construct a new database, first log in as the postgres user:

sudo -i -u postgres

Then, open psql:

psql

Construct a database named testdb:

CREATE DATABASE testdb;

To list all databases, use:

\l

Your new database should appear in the list.

Check the connection Information

While inside psql, check connection details using:

\conninfo

This displays information about the current database connection, including host, port, and user details.

Also Read: PostgreSQL Version Checking: Tips for Better Management

Conclusion

PostgreSQL comes out as a resilient & flexible database system. Its open-source attribute, integrated with strong community assistance, makes it a desired option for developers and enterprises. Whether you are working on web applications, analytics, or large-scale data processing, PostgreSQL provides the reliability you need.

Setting up PostgreSQL on Ubuntu is a quick and easy procedure. Within minutes, you can install it, verify its status, and start creating databases. Once configured, PostgreSQL offers high-speed data processing, secure authentication, and advanced query optimization.

Now that PostgreSQL is up and running on your Ubuntu system, you can start leveraging its full potential. Explore its indexing options, implement data replication, and fine-tune performance settings. With its extensive features and flexibility, PostgreSQL empowers you to build scalable as well as effective applications.