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:
sudo apt install wget ca-certificates
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:
sudo apt update
sudo apt upgrade
INSTALL POSTGRESQL SERVER
Once you have the latest version, you can proceed to install PostgreSQL on Ubuntu. Use the following command:
sudo apt install postgresql
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:
sudo service postgresql status
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:
sudo -u postgres psql
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:
CREATE DATABASE mydatabase;
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.
GRANT ALL ON DATABASE Fetch TO user
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
sudo apt 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:
psql -h localhost -d DB-name -U DB-User
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:
sudo passwd postgres
Log into the Postgres system account.
su - postgres
Change the admin database password using this command:
psql -c "ALTER USER postgres WITH PASSWORD 'your-password'
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:
sudo nano /etc/postgresql/{version}/main/pg_hba.conf
Replace "[version]" with your PostgreSQL version, e.g., "13".
RESTART POSTGRESQL
After making changes to the configuration, restart the PostgreSQL service:
sudo service postgresql restart
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:
sudo apt remove postgresql*
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:
sudo apt remove postgresql-13*
On the other hand, if you just need to remove the client one, run this:
sudo apt remove postgresql-client
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