How to Install PostgreSQL on Ubuntu?

PostgreSQL is a powerful, open-source relational database management system (RDBMS) widely used by developers, data scientists, and system administrators. Whether you’re building a web application, working on data analytics, or just learning database management, PostgreSQL is a solid choice due to its robustness, scalability, and advanced features.

If you’re using Ubuntu, a popular Linux distribution, you’ll be happy to know that installing PostgreSQL is straightforward. In this guide, we’ll walk you through the process of install PostgreSQL on Ubuntu, from installation to configuration, ensuring you can get your database up and running in no time. Whether you’re a beginner or someone who needs a refresher, we’ve got you covered with clear instructions and best practices.

By the end of this tutorial, you’ll not only have PostgreSQL installed on your Ubuntu system but also be familiar with basic configuration and management tasks to get the most out of your database.

What is PostgreSQL?

PostgreSQL is a free, open-source, object-relational database system. It is known for its reliability, flexibility, and support for a wide range of advanced features such as ACID compliance, foreign keys, views, triggers, and stored procedures. PostgreSQL is often used by developers, data analysts, and organizations to handle mission-critical applications.

As a powerful SQL-compliant database, PostgreSQL is designed to handle large amounts of data while maintaining data integrity and security. It supports both SQL (relational) and JSON (non-relational) queries, making it a versatile solution for modern applications.

Why Choose PostgreSQL?

There are several reasons to choose PostgreSQL over other database management systems, especially for Ubuntu users:

• Open Source: PostgreSQL is free to use, and it has a strong community that contributes to its continuous development.
• ACID Compliant: PostgreSQL ensures that all transactions are processed reliably and follow the principles of ACID (Atomicity, Consistency, Isolation, Durability).
• Extensibility: PostgreSQL supports a variety of extensions, including PostGIS for geographical data, and it allows users to define custom functions.
• Cross-platform Compatibility: PostgreSQL works on multiple platforms, including Windows, Linux, and macOS.
• Scalability: It can handle large-scale applications, with the ability to manage billions of rows of data and run in high-performance environments.
• Community Support: With a large community of developers, you can easily find solutions to common issues and get help with your PostgreSQL setup.

System Requirements

Before install PostgreSQL on Ubuntu system, ensure your system meets the following requirements:

• Operating System: Ubuntu 20.04 LTS or later (the instructions in this guide apply to Ubuntu 20.04, 22.04, and higher).
• Disk Space: At least 1GB of free space for the PostgreSQL database files (more may be needed depending on your database size).
• RAM: Minimum 2GB of RAM, but 4GB is recommended for better performance.
• Internet Access: Required for downloading packages and updates from the repositories.

How to Install PostgreSQL on Ubuntu?

Step 1: Update Your System

Before installing any software on your system, it’s always a good idea to update your package lists and installed packages. Open a terminal and run the following commands:

sudo apt update
sudo apt upgrade

This ensures that your system has the latest security patches and updates.

Step 2: Install PostgreSQL

Ubuntu provides an official package for PostgreSQL, which makes installation a breeze. To install PostgreSQL, use the following command:

sudo apt install postgresql postgresql-contrib

• postgresql: This package includes the core PostgreSQL database system.
• postgresql-contrib: This package includes additional utilities and extensions that enhance PostgreSQL’s functionality.

Once the installation is complete, PostgreSQL will be automatically started, and the necessary services will be set up.

Step 3: Verify the Installation

To verify that PostgreSQL has been successfully installed, you can check the version of PostgreSQL with the following command:

psql --version

You should see the version number of PostgreSQL, confirming that it’s installed.

Basic PostgreSQL Configuration

Once PostgreSQL is installed, it’s time to configure it and start using it.

Switch to the PostgreSQL User

By default, PostgreSQL creates a user called postgres on your system. You need to switch to this user to perform administrative tasks within PostgreSQL. To do so, run the following command:

sudo -i -u postgres

This will log you into the PostgreSQL shell as the postgres user. You’ll notice the prompt changes to something like this:

postgres@yourhostname:~$

Create a Database

Now that you’re logged in as the postgres user, you can create a new PostgreSQL database. To create a new database, use the following command:

createdb mydatabase

This command creates a new database called mydatabase. You can replace mydatabase with any name you prefer.

Connect to the PostgreSQL Database

To connect to the database, use the following command:

psql mydatabase

This opens an interactive session with your PostgreSQL database. You’ll be able to execute SQL commands, query data, and manage the database.

To exit the PostgreSQL shell, type \q.

PostgreSQL Administration and Management

Manage PostgreSQL Service

PostgreSQL is managed as a service on Ubuntu. You can control the PostgreSQL service using systemctl commands.

• Start PostgreSQL: sudo systemctl start postgresql
• Stop PostgreSQL: sudo systemctl stop postgresql
• Restart PostgreSQL: sudo systemctl restart postgresql
• Enable PostgreSQL to Start on Boot: sudo systemctl enable postgresql
• Check PostgreSQL Status: sudo systemctl status postgresql

Configure Remote Access

By default, PostgreSQL only listens for connections from the local machine. If you want to enable remote connections to your PostgreSQL server, follow these steps:

1. Edit PostgreSQL Configuration File: Open the postgresql.conf file. sudo nano /etc/postgresql/13/main/postgresql.conf Find the line that starts with #listen_addresses = 'localhost' and change it to: listen_addresses = '*'
2. Configure Client Authentication: Edit the pg_hba.conf file to allow remote connections. sudo nano /etc/postgresql/13/main/pg_hba.conf Add the following line to the file (replace your_ip_address with your client’s IP): host all all your_ip_address/32 md5
3. Restart PostgreSQL: sudo systemctl restart postgresql

After these changes, PostgreSQL will accept remote connections from the specified IP address.

Troubleshooting Common Issues

1. PostgreSQL is Not Starting:
   • Check the PostgreSQL logs located in /var/log/postgresql/ to see if there are any error messages.
   • Ensure that the configuration files are correctly set.
2. Cannot Connect to PostgreSQL Remotely:
   • Verify that the listen_addresses and pg_hba.conf files are correctly configured.
   • Ensure that there are no firewall rules blocking access to PostgreSQL’s default port (5432).
3. Permission Denied:
   • If you encounter permission errors when trying to create a database or user, ensure you’re logged in as the postgres user or another superuser in PostgreSQL.

Conclusion

Install PostgreSQL on Ubuntu is a straightforward process, and with the steps outlined in this guide, you should be able to get PostgreSQL up and running on your system in no time. PostgreSQL is a powerful, scalable, and flexible database system that’s perfect for everything from small projects to enterprise-level applications.

Now that you have PostgreSQL installed, you can start exploring its powerful features, such as writing complex queries, building tables, and working with data. You’re also well-equipped to manage and maintain your PostgreSQL database on Ubuntu, including configuring remote access and optimizing its performance.

Feel free to explore more advanced install topics or check out additional resources to further your knowledge.

If you found this guide helpful, please leave a comment below, share it with others, or explore our related articles for further learning!

FAQs

Q1: Is PostgreSQL free to use?

Yes, PostgreSQL is completely free and open-source software. You can use it for personal, educational, or commercial purposes without any licensing fees.

Q2: How do I update PostgreSQL on Ubuntu?

To update PostgreSQL on Ubuntu, run the following commands:

sudo apt update
sudo apt upgrade postgresql

This will update PostgreSQL to the latest version available in the official Ubuntu repositories.

Q3: Can I use PostgreSQL for web development?

Absolutely! PostgreSQL is commonly used in web development, particularly with popular frameworks like Django, Flask, and Ruby on Rails. It’s well-suited for applications requiring robust data integrity and scalability.

Q4: What’s the default port for PostgreSQL?

PostgreSQL uses port 5432 by default. If you need to change this, you can modify the postgresql.conf file.

Read Also : How to Install PHP on Ubuntu?