How to Install, Configure, and Upgrade PostgreSQL on Arch Linux

Introduction

PostgreSQL is an open-source Object-Relational Database Management System. This guide explains how to install and upgrade PostgreSQL server on Arch Linux.

Prerequisites

• A Vultr server running up to date Arch Linux.
• A sudo user

Install PostgreSQL

Install the PostgreSQL package. The installation creates a system user called postgres.

$ sudo pacman -S postgresql

Switch to the postgres user and initialize the database cluster.

$ sudo -u postgres –i  initdb -D '/var/lib/postgres/data'

Exit the postgres user account.

$ exit

PostgreSQL database server runs as a service under the name postgresql.

Enable the PostgreSQL service to start automatically at every boot.

$ sudo systemctl enable postgresql

Start the PostgreSQL service.

$ sudo systemctl start postgresql

Configure PostgreSQL Server

When you first install PostgreSQL, a password for the super-user is not set by default. Use the default PostgreSQL command-line psql to log into the database server.

$ sudo -u postgres psql

Create a password for the super-user.

postgres=# \password postgres

Enter a strong password and confirm.

    Enter new password: EXAMPLE_PASSWORD

    Enter it again: EXAMPLE_PASSWORD

Exit the command-line client.

    postgres=# \q

Example PostgreSQL Commands

Here are a few basic PostgreSQL commands.

• Create a database and log in:

    $ createdb TestDB
    $ psql TestDB

• Check the current user:

    select current_user;

• Create table:

    Create table

• Check the list of user and permissions:

    \du

• Get summary information of all tables in the current database:

    \dt

• Exit the psql command-line:

    \q

How to Upgrade PostgreSQL

Upgrading PostgreSQL requires extra attention. Make sure you follow the official PostgreSQL Upgrade documentation. Upgrading, if not done properly, can cause data loss. It's highly recommended that you backup your databases first.

Minor version upgrades are easy and safe to do. However, if you upgrade to a different major version, you might not be able to access your data. Always check the PostgreSQL Official documentation for more details and to be certain of the steps required to upgrade.

It's highly recommended to configure pacman to skip automatic upgrades for PostgreSQL. When an upgrade is released, and you upgrade your entire Arch system, pacman will indicate a new version is available.

Edit /etc/pacman.conf, and add the following:

IgnorePkg   = postgresql*

Minor Version Upgrades

To perform a minor upgrade:

Stop PostgreSQL.

# systemctl stop postgresql

Confirm it stopped.

    # systemctl status postgresql

Update the packages.

    # pacman -S postgresql postgresql-libs postgresql-old-upgrade

Start PostgreSQL.

    # systemctl start postgresql

Major Version Upgrades

It's recommended to check PostgreSQL's official website for procedures you must perform for a major version upgrade.

More Information

Use the following commands to manage the PostgreSQL server:

• Stop server:

    $ sudo systemctl stop postgresql

• Start server:

    $ sudo systemctl start postgresql

• Restart after configuration changes:

    $ sudo systemctl restart postgresql

• Reload server:

    $ sudo systemctl reload postgresql

• Check status:

    $ sudo systemctl status postgresql

Conclusion

In this guide, you've learned how to install, configure and update PostgreSQL on Arch Linux. You've also used the basic SQL syntax to create a PostgreSQL database.