Installing PostgreSQL on Linux Mint

Welcome to this detailed guide on installing PostgreSQL on Linux Mint. PostgreSQL, often simply called Postgres, is a powerful, open-source object-relational database system that is known for its robustness, scalability, and technical standards compliance. It is a favorite among developers and enterprises for its reliability and feature-rich ecosystem.

Linux Mint, with its user-friendly interface and solid performance, is an excellent platform for running PostgreSQL. Whether you're a seasoned database administrator or a newcomer to the world of databases, this guide will walk you through the process of installing PostgreSQL on your Linux Mint system step by step.

Prerequisites

Before we begin, ensure that you have the following:

• A Linux Mint system with administrative privileges.
• An internet connection to download the necessary packages.
• Basic knowledge of using the terminal.

Step 1: Updating the Package List

The first step is to update your package list to ensure that you have access to the latest versions of the software. Open a terminal and run the following command:

sudo apt update

You should see a list of repositories being updated. This process ensures that you will install the most recent version of PostgreSQL available in the Linux Mint repositories.

Step 2: Installing PostgreSQL

With your package list updated, you can now install PostgreSQL using the apt package management tool. Run the following command:

sudo apt install postgresql postgresql-contrib

The postgresql-contrib package contains additional modules and utilities that can be useful for managing and working with PostgreSQL.

Output::

Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  postgresql-client-common postgresql-client postgresql-common ssl-cert
Suggested packages:
  postgresql-doc postgresql-doc-en | postgresql-do-es postgresql-doc-fr | postgresql-doc-ja postgresql-doc-ru
  postgresql-doc-de postgresql-pgpool2 lsof
The following NEW packages will be installed:
  postgresql postgresql-contrib postgresql-client-common postgresql-client postgresql-common ssl-cert
0 upgraded, 7 newly installed, 0 to remove and 23 not upgraded.
Need to get 7,173 kB of archives.
After this operation, 36.3 MB of additional disk space will be used.
Do you want to continue? [Y/n]

Type Y and press Enter to proceed with the installation.

Step 3: Starting and Enabling PostgreSQL

PostgreSQL should start automatically after installation. However, you can manually start and enable it to ensure that it starts on boot using the following commands:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Note: Enabling the service ensures that PostgreSQL starts automatically upon system boot.

Step 4: Checking PostgreSQL Version

To verify that PostgreSQL has been installed correctly, you can check its version by running:

psql --version

Output::

psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

The version number may vary depending on the version available in the Linux Mint repositories at the time of installation.

Step 5: Configuring PostgreSQL

PostgreSQL is installed with a default user postgres, which has superuser privileges. To start using PostgreSQL, you'll need to switch to this user. You can do this by running:

sudo -i -u postgres

Now, you can access the PostgreSQL command line interface by typing psql.

Output::

postgres=#

The # indicates that you are logged in as a superuser.

Step 6: Creating a New Database User and Database

For most use cases, you'll want to create a new user and database. Here's how to do it:

1. Create a new user (replace your_username with your desired username):

CREATE USER your_username WITH PASSWORD 'your_password';

2. Create a new database owned by the new user:

CREATE DATABASE your_database OWNER your_username;

3. Grant all privileges on the new database to the new user:

GRANT ALL PRIVILEGES ON DATABASE your_database TO your_username;

Note: Replace your_password with a strong password of your choice.

Step 7: Accessing the Database

To access the database you've just created, exit the current postgres user session by typing \q, and then connect to the new database with the following command:

psql -d your_database -U your_username

You will be prompted to enter the password for your_username.

Step 8: Securing PostgreSQL

By default, PostgreSQL is configured to allow connections from the local machine. If you need to allow remote connections, you'll need to modify the pg_hba.conf and postgresql.conf files.

Important: Be cautious when configuring remote access to avoid exposing your database to unauthorized users.

1. Edit the pg_hba.conf file:

sudo nano /etc/postgresql/12/main/pg_hba.conf

2. Add a line to allow connections from a specific IP address or network, for example:

host    all             all             192.168.1.0/24          md5

3. Edit the postgresql.conf file:

sudo nano /etc/postgresql/12/main/postgresql.conf

4. Change the listen_addresses line to listen on the desired network interface:

listen_addresses = 'localhost,192.168.1.1'

5. Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

What we've covered

You have now successfully installed PostgreSQL on your Linux Mint system. You've learned how to create new users and databases, and you've been introduced to the basics of securing your PostgreSQL installation. Regularly back up your databases and keep your system updated to maintain a healthy and secure database environment.