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!
