How to Install and Use PostgreSQL on Arch Linux

PostgreSQL is a free, open-source, object-relational database management system used to build fault-tolerant and complex applications. It is very popular among developers due to its reliability, robustness of its features, and performance. PostgreSQL is one of the most popular database systems and is available for most operating systems, including Arch Linux.

In this post, we will show you how to install and use PostgreSQL on Arch Linux.

Step 1 – Configure Repository

By default, the default repository is outdated in Arch Linux, so you will need to modify the default mirror list. You can do it by editing the mirrorlist configuration file:

nano  /etc/pacman.d/mirrorlist

Remove all lines and add the following lines:

## Score: 0.7, United States
Server = http://mirror.us.leaseweb.net/archlinux/$repo/os/$arch
## Score: 0.8, United States
Server = http://lug.mtu.edu/archlinux/$repo/os/$arch
Server = http://mirror.nl.leaseweb.net/archlinux/$repo/os/$arch
## Score: 0.9, United Kingdom
Server = http://mirror.bytemark.co.uk/archlinux/$repo/os/$arch
## Score: 1.5, United Kingdom
Server = http://mirrors.manchester.m247.com/arch-linux/$repo/os/$arch
Server = http://archlinux.dcc.fc.up.pt/$repo/os/$arch
## Score: 6.6, United States
Server = http://mirror.cs.pitt.edu/archlinux/$repo/os/$arch
## Score: 6.7, United States
Server = http://mirrors.acm.wpi.edu/archlinux/$repo/os/$arch
## Score: 6.8, United States
Server = http://ftp.osuosl.org/pub/archlinux/$repo/os/$arch
## Score: 7.1, India
Server = http://mirror.cse.iitk.ac.in/archlinux/$repo/os/$arch
## Score: 10.1, United States
Server = http://mirrors.xmission.com/archlinux/$repo/os/$arch

Save and close the file, then update all the package indexes with the following command:

pacman -Syu

Step 2 – Install PostgreSQL on Arch Linux

By default, the PostgreSQL package is included in the Arch Linux main repository. You can install it using the following command:

pacman -S postgresql

Once the PostgreSQL is installed, you can verify the PostgreSQL version using the following command:

postgres --version

You will get the following output:

postgres (PostgreSQL) 14.5

Next, initialize the PostgreSQL database with the following command:

sudo -u postgres initdb --locale en_US.UTF-8 -D /var/lib/postgres/data

Step 3 – Manage PostgreSQL Service

By default, the PostgreSQL service is managed by systemd. You can start the PostgreSQL service with the following command:

systemctl start postgresql

To enable the PostgreSQL service to start after the system reboots, run the following command:

systemctl enable postgresql

To check the status of the PostgreSQL service, run the following command:

systemctl status postgresql

You should see the following output:

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; preset: disabled)
     Active: active (running) since Fri 2022-10-28 07:35:27 UTC; 5s ago
    Process: 83878 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=0/SUCCESS)
   Main PID: 83880 (postgres)
      Tasks: 7 (limit: 2362)
     Memory: 15.7M
     CGroup: /system.slice/postgresql.service
             ├─83880 /usr/bin/postgres -D /var/lib/postgres/data
             ├─83883 "postgres: checkpointer "
             ├─83884 "postgres: background writer "
             ├─83885 "postgres: walwriter "
             ├─83886 "postgres: autovacuum launcher "
             ├─83887 "postgres: stats collector "
             └─83888 "postgres: logical replication launcher "

Step 4 – How to Use PostgreSQL

By default, there is not any password for the Postgres user.

To set it, log in with the Postgres user and set the password using the following command:

su - postgres
psql -c "alter user postgres with password 'password'"

To create a new database, run the following command: psql

CREATE DATABASE mydb;

To list all databases, run the following command:

\l

You should see the following list:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

To create a new user and set a password, run the following command:

CREATE USER user1 WITH ENCRYPTED PASSWORD 'password';

Next, grant all the privileges to the mydb database using the following command:

GRANT ALL PRIVILEGES ON DATABASE mydb to user1;

To switch the database to mydb, run the following command:

\c mydb

To exit from the PostgreSQL shell, run the following command:

\q
exit

Step 5 – Configure PostgreSQL for Remote Access

By default, PostgreSQL is configured for local access only. If your application server and database servers are hosted on a different server, then you will need to configure PostgreSQL for remote access.

First, edit the PostgreSQL main configuration file:

nano /var/lib/postgres/data/postgresql.conf

Change the following line with your server IP address:

listen_addresses = 'your-server-ip'

Save and close the file, then edit the pg_hba.conf file:

nano /var/lib/postgres/data/pg_hba.conf

Find the following line:

host    all             all             127.0.0.1/32            trust

And replace it with the following line:

host    all             all             all            trust

Save and close the file, then restart the PostgreSQL service to apply the changes:

systemctl restart postgresql

Next, try to connect your PostgreSQL database using the IP address:

psql -U user1 -h server-ip -p 5432 mydb

If everything is configured properly, you will get into the following shell:

psql (14.5)
Type "help" for help.

mydb=>

Conclusion

In this post, we explained how to install and use PostgreSQL on Arch Linux. You can now install PostgreSQL in your development environment and use it as a database backend. You can try to install and use PostgreSQL on dedicated server hosting from Atlantic.Net!