Introduction
PostgreSQL is a relational database management system that is designed to store and organize access to interrelated elements of information.
In this guide, we will install PostgreSQL 17 version on AlmaLinux 9 and Rocky Linux 9.
1. Update Your System
Before installing PostgreSQL 17, update your system to ensure all packages are current:
dnf update -y
2. Check Available PostgreSQL Modules
List the available PostgreSQL modules to verify the default version:
dnf module list postgresql
3. Add the PostgreSQL Repository
AlmaLinux does not include the latest PostgreSQL versions by default. Add the official PostgreSQL repository:
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
4. Disable the Default PostgreSQL Module
To prevent conflicts with older versions, disable the default PostgreSQL module:
dnf -qy module disable postgresql
5. Install PostgreSQL 17
Now install PostgreSQL 17 using:
dnf install -y postgresql17-server
6. Initialize the Database Cluster
Run the following command to initialize the database:
/usr/pgsql-17/bin/postgresql-17-setup initdb
7. Start and Enable PostgreSQL Service
Ensure PostgreSQL starts on boot and is currently running:
systemctl enable postgresql-17
systemctl start postgresql-17
Check the status to confirm it's running:
systemctl status postgresql-17
8. Set a Password for the PostgreSQL User
By default, PostgreSQL creates a postgres superuser. Set a secure password:
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'MyPassword';"
Replace 'MyPassword' with a strong password.
Using PostgreSQL 17
Accessing PostgreSQL
To access the PostgreSQL command-line interface (psql):
sudo -u postgres psql
psql
Creating a New Database
To create a new database, run this command:
CREATE DATABASE newdatabase;
Replace "newdatabase" with your desired database name.
Creating a New User
To create a new PostgreSQL user:
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
Replace myuser and mypassword with your credentials.
Granting Privileges
To grant full access to a user on a database:
GRANT ALL PRIVILEGES ON DATABASE newdatabase TO myuser;
Connecting to a Database
To connect to a specific database:
psql -U myuser -d newdatabase
Listing Databases and Users
To list all databases:
\l
To list all users:
\du
Exiting PostgreSQL
To exit the PostgreSQL shell:
\q