Installing PostgreSQL on Manjaro Linux

Welcome to todays tutorial on installing PostgreSQL, one of the most advanced and robust open-source relational database systems, on Manjaro Linux. Manjaro, known for its user-friendliness and rolling release model, is an excellent platform for developers and database administrators who want to leverage the power of PostgreSQL.

PostgreSQL, often simply called Postgres, is renowned for its reliability, feature robustness, and compliance with SQL standards. It supports both SQL (relational) and JSON (non-relational) querying, and it is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.

In this tutorial, we will walk through the process of installing PostgreSQL on Manjaro Linux step by step. We will cover everything from the initial installation to basic configuration and usage. By the end of this guide, you will have a running PostgreSQL instance that you can use for your projects.

Prerequisites

Before we begin, ensure that you have the following:

• A Manjaro Linux system up and running.
• Access to the terminal (you can press Ctrl + Alt + T to open it).
• A user with sudo privileges to execute administrative commands.

Step 1: Updating the System

It's always a good practice to update your system's package list and upgrade all the installed packages to their latest versions before installing new software. To do this, run the following commands in your terminal:

sudo pacman -Syu

You may be prompted to confirm the upgrade process. Type y and press Enter to proceed.

Step 2: Installing PostgreSQL

PostgreSQL can be easily installed from the Manjaro repositories using the pacman package manager. To install PostgreSQL, execute the following command:

sudo pacman -S postgresql

This command will install PostgreSQL along with its default dependencies.

Step 3: Initializing the Database Cluster

After installation, you need to initialize the database cluster before you can use PostgreSQL. By default, PostgreSQL expects a postgres user to exist on the system. If it doesn't, you can create it using the following command:

sudo useradd -m -U -d /var/lib/postgresql -s /bin/bash postgres

Now, initialize the database cluster with the following command:

sudo -iu postgres
initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data/'

This will create a new PostgreSQL database cluster and set the default encoding to UTF-8.

Step 4: Starting and Enabling PostgreSQL

To start the PostgreSQL service, use the following command:

sudo systemctl start postgresql.service

To enable PostgreSQL to start automatically on boot, run:

sudo systemctl enable postgresql.service

You can check the status of the PostgreSQL service with:

sudo systemctl status postgresql.service

You should see an output similar to this, indicating that PostgreSQL is active and running:

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: active (running) since Wed 2023-04-05 15:45:34 UTC; 1min ago
       Docs: man:postgres(1)
   Main PID: 1234 (postgres)
     CGroup: /system.slice/postgresql.service
             ├─1234 /usr/bin/postgres -D /var/lib/postgres/data/
             ├─1235 postgres: checkpointer process
             ├─1236 postgres: writer process
             ├─1237 postgres: wal writer process
             └─1238 postgres: autovacuum launcher process

Step 5: Accessing PostgreSQL

PostgreSQL can be accessed in multiple ways, but the most common method is through the psql command-line interface. To access the PostgreSQL prompt as the postgres user, run:

psql -U postgres

You will be prompted for the password for the postgres user. Initially, no password is set, so you can simply press Enter.

Once logged in, you will see the PostgreSQL prompt:

postgres=#

Step 6: Creating a New Database User and Database

By default, PostgreSQL creates a database user named postgres. For security reasons, it's recommended to create a new user and database for your applications. Here's how to do it:

First, create a new user (replace newuser with your desired username):

CREATE USER newuser WITH PASSWORD 'your_password';

Next, create a new database (replace newdb with your desired database name):

CREATE DATABASE newdb OWNER newuser;

Now, grant all privileges on the new database to the new user:

GRANT ALL PRIVILEGES ON DATABASE newdb TO newuser;

Step 7: Securing PostgreSQL

To ensure that your PostgreSQL installation is secure, you should:

• Set a password for the postgres user.
• Configure pg_hba.conf to use md5 or scram-sha-256 password authentication.
• Optionally, adjust the postgresql.conf file for additional security settings, such as disabling remote connections if not needed.

Edit the pg_hba.conf file located in /var/lib/postgres/data/ and change the authentication method from peer to md5 or scram-sha-256 for local connections:

# "local" is for Unix domain socket connections only
local   all             all                                     md5

After making changes, reload the PostgreSQL configuration with:

sudo systemctl reload postgresql.service

Step 8: Testing the Installation

To test that everything is working correctly, try connecting to the newly created database with the new user:

psql -U newuser -d newdb

Enter the password when prompted, and you should be greeted with the PostgreSQL prompt for the newdb database:

newdb=>

What to Watch Out For

• Permissions: Ensure that you do not give more permissions than necessary to database users.
• Security: Always secure your PostgreSQL installation by setting strong passwords and configuring proper authentication methods.
• Backups: Regularly back up your databases to prevent data loss.
• Updates: Keep your PostgreSQL installation up to date with the latest security patches and features by regularly updating your system.

What we've learned

You have now successfully installed and configured PostgreSQL on your Manjaro Linux system. You've learned how to initialize the database cluster, start and enable the PostgreSQL service, create new users and databases, and secure your installation. With these skills, you're well on your way to leveraging the full power of PostgreSQL for your applications.