Learn How To Install PostgreSQL 11.1 On Arch Linux

November 29, 2019

Table of Contents

Prerequisites

https://www.itweb.services/tutorials/linux-guides/installing-2019-arch-linux-on-a-itweb.services-server”>this article.)

  • Sudo access.
    • Commands required to be ran as root are prefixed by #, and ones that can be ran as a regular user by $. The recommended way to run commands as root is to, as a regular user, prefix each of them with sudo.
  • Install PostgreSQL 11.1 Database

    Install PostgreSQL:

    # pacman -S postgresql
    

    If you run the Btrfs filesystem, you should consider disabling copy-on-write for the database directory:

    # chattr +C /var/lib/postgres/data/
    

    This package creates the user postgres on your system.

    Initialize the database cluster. This must be done by the new postgres user, and this command will do this as long as your user account is setup with sudo privileges:

    $ sudo -iu postgres initdb -D /var/lib/postgres/data
    

    Start PostgreSQL, and make it start after every boot:

    # systemctl enable --now postgresql
    

    Connect to PostgreSQL, as database user postgres which acts as the database root user:

    # psql -U postgres
    

    Then set a password:

    postgres-# password postgres
    

    Now quit:

    postgres-# q
    

    Consider a Firewall

    You may want to consider configuring a firewall. By default, PostgreSQL will listen on port 5432 not only from localhost, but also from anywhere on your public IP address. PostgreSQL will also only approve incoming connections from localhost, but external attempts will still reach PostgreSQL and get the following error: no pg_hba.conf entry for host.

    Although PostgreSQL is considered quite secure, it’s more secure to have a firewall not even give external packets to the PostgreSQL server, unless absolutely necessary. Even if direct remote access is desired, using a firewall to block the traffic and using a VPN would be more secure.

    Prepare for Upgrades

    By default, pacman will upgrade PostgreSQL when new versions are released to the official Arch repositories, when you upgrade your entire Arch system by running the following command:

    # pacman -Syu
    

    It is strongly recommended to configure pacman to not automatically install upgrades to PostgreSQL. When an upgrade is released and you upgrade your entire Arch system, pacman will let you know a new version is available. Edit /etc/pacman.conf, and add the following:

    IgnorePkg   = postgresql*
    

    Handle Minor Version Upgrades

    When pacman shows you there is a minor version upgrade, such as 11.0 to 11.1, PostgreSQL’s versioning policy is that you can safely perform the upgrade. That said, it is a good idea to backup your database first.

    To perform a minor upgrade, stop PostgreSQL and confirm it is stopped:

    # systemctl stop postgresql
    # systemctl status postgresql
    

    Then, force upgrading the packages:

    # pacman -S postgresql postgresql-libs
    

    Start PostgreSQL:

    # systemctl start postgresql
    

    Handle Major Version Upgrades

    When pacman shows there is a major version upgrade, such as 11.x to 12.x, it’s recommended to check PostgreSQL’s website, as well as Arch’s website, to see if there are steps you must perform to properly upgrade. In the past, when PostgreSQL has given such steps, skipping them prevented databases from working. It’ is highly recommended that you backup your database first, as these upgrades are more risky.

    Need help?

    Do you need help setting up this on your own service?
    Please contact us and we’ll provide you the best possible quote!