In this tutorial, we'll explain how to install and manage PostgreSQL on AlmaLinux 9.
PostgreSQL is a powerful, open-source object-relational database system (RDBMS) that supports complex data types, ACID-compliant transactions. The RDBMS is widely used in web applications, data warehousing, and geospatial data management. PostgreSQL supports Structured Query Language (SQL) for storing and retrieving data in tables.
This tutorial will guide you through the steps to install PostgreSQL on AlmaLinux 9, log in to the PostgreSQL shell, create a database, user, and table, and perform basic operations like inserting, editing, and deleting data.
Prerequisites
Before you begin, make sure you have the following:
- A server running AlmaLinux 9 dedicated server or KVM VPS.
- Basic knowledge of the Linux command line.
- A root user access or normal user with sudo rights.
Install and Manage PostgreSQL on AlmaLinux
Step 1: Update System Packages
Before installing PostgreSQL, ensure that your system packages are up to date.
sudo dnf update -y
This command updates your system with the latest package versions.
Step 2: Install PostgreSQL
AlmaLinux includes PostgreSQL in its official repositories, but for the latest version, we’ll use the official PostgreSQL repository.
2.1. Add the PostgreSQL Repository
First, install the dnf package that allows adding external repositories:
sudo dnf install -y dnf-utils
Now, add the PostgreSQL repository to your system:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2.2. Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql
2.3. Install PostgreSQL Server
After adding the repository, install the PostgreSQL server package:
sudo dnf install -y postgresql16-server
Note: You can replace postgresql16 with a different version if required.
2.4. Initialize the PostgreSQL Database
After installing PostgreSQL, initialize the database to set up the initial structure:
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
2.5. Enable and Start PostgreSQL
To ensure PostgreSQL starts automatically on boot, enable and start the PostgreSQL service:
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16
Check the status of the PostgreSQL service to confirm it is running:
sudo systemctl status postgresql-16
Step 3: Configure PostgreSQL
3.1. First we need to set or reset the postgres User Password. Switch to the postgres user on your system using the following command:
sudo -i -u postgres
Access the PostgreSQL shell as the postgres user:
psql
Set a password for the postgres user:
ALTER USER postgres PASSWORD 'your_new_password';
Exit the PostgreSQL shell:
\q
Exit the postgres user environment:
exit
PostgreSQL uses two types of authentication methods: peer and password. By default, it uses peer for local users. You may want to switch to password authentication for better security. When we switch to md5 password authentication it will ask to enter password for postgres user.
3.2. Modify Authentication Method
Open the pg_hba.conf file:
sudo nano /var/lib/pgsql/16/data/pg_hba.conf
Find the line that reads:
local all all peer
Replace peer with md5 for password authentication:
local all all md5
Save and close the file.
3.3. Restart PostgreSQL
After making changes, restart the PostgreSQL service for them to take effect:
sudo systemctl restart postgresql-16
Step 4: Set Up PostgreSQL User and Database
PostgreSQL uses the postgres superuser by default. To log in as the postgres user and start using PostgreSQL, run the following:
sudo -i -u postgres
You should now be in the PostgreSQL environment. Access the PostgreSQL command-line interface (CLI) with:
psql
Enter the passwod you have set on previous step.
4.1. Create a New PostgreSQL User
To create a new user, use the following SQL command. Replace username with your desired username:
CREATE USER username WITH PASSWORD 'password';
4.2. Create a New Database
Create a new database with the following SQL command. Replace dbname with your desired database name:
CREATE DATABASE dbname;
4.3. Grant Privileges to User
Grant privileges to the newly created user so they can access the database:
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
4.4. Exit PostgreSQL
To exit the PostgreSQL prompt, type:
\q
Exit the postgres user environment:
exit
Step 5: Managing PostgreSQL Databases
Now that PostgreSQL is installed and a database has been created, let's explore basic database management tasks such as listing, removing, and accessing databases.
5.1. List Databases
To list all databases in PostgreSQL, log into the psql shell as the postgres user:
sudo -i -u postgres psql
Then, run the following command:
\l
This will show a list of all databases.
5.2. Drop (Delete) a Database
To remove a database, use the following command in the psql prompt:
DROP DATABASE dbname;
Replace dbname with the name of the database you wish to delete.
5.3. Connect to a Database
To connect to a specific database, use the following command:
\c dbname
Replace dbname with the name of the database you want to connect to.
Step 6: Backup and Restore PostgreSQL Databases
6.1. Log in as the postgres user and start using PostgreSQL, run the following:
sudo -i -u postgres
6.2. Backup a PostgreSQL Database
To back up a PostgreSQL database, you can use the pg_dump utility. Run the following command as the postgres user:
pg_dump dbname > dbname_backup.sql
This creates a backup of the dbname database and saves it as an SQL file.
6.3. Restore a PostgreSQL Database
To restore a database from a backup file, use the psql utility:
psql dbname < dbname_backup.sql
Conclusion
We've seen how to install and manage PostgreSQL on on AlmaLinux 9 server, created a new database, added users, and managed databases. PostgreSQL is a powerful tool for handling complex database tasks, and mastering its installation and management is a vital skill for any Linux system administrator.
With this guide, you are now equipped to start using PostgreSQL for your applications on AlmaLinux!
