PostgreSQL is an open-source Object-Relational Database Management System. This guide explains how to install and upgrade PostgreSQL server on Arch Linux.
A Vultr server running up to date Arch Linux.
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
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
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
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*
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
It's recommended to check PostgreSQL's official website for procedures you must perform for a major version upgrade.
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
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.