PostgreSQL is a free, open-source, object-relational database system designed to scale the most complicated data workloads. Generally, it is used as the primary data store for many web, mobile, geospatial, and analytics applications. PostgreSQL can store structured and unstructured data in a single product. PostgreSQL 14 comes with a variety of features that help users to deploy their data-backed applications.
In this post, we will show you how to install PostgreSQL 14 in Rocky Linux 8.
Step 1 – Add PostgreSQL 14 Repository
By default, the latest version of PostgreSQL is not included in the Rocky Linux 8 default repo. You can confirm this by running the following command:
dnf update -y dnf module list postgresql
You should see the all available PostgreSQL versions in the following output:
Last metadata expiration check: 0:19:52 ago on Wednesday 16 February 2022 04:26:10 PM UTC. Rocky Linux 8 - AppStream Name Stream Profiles Summary postgresql 9.6 client, server [d] PostgreSQL server and client module postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 12 client, server [d] PostgreSQL server and client module postgresql 13 client, server [d] PostgreSQL server and client module
Now, install the PostgreSQL 14 repository using the following command:
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Once the PostgreSQL 14 repository is added, you can move to the next step.
Also Read
How To Secure PostgreSQL Server
Step 2 – Install PostgreSQL 14 on Rocky Linux 8
First, you will need to disable the default PostgreSQL repository:
dnf -qy module disable postgresql
Next, run the following command to install the PostgreSQL 14:
dnf install postgresql14 postgresql14-server -y
Once the installation is finished, initialized the PostgreSQL database with the following command:
/usr/pgsql-14/bin/postgresql-14-setup initdb
Next, start and enable the PostgreSQL service using the following command:
systemctl start postgresql-14 systemctl enable postgresql-14
To check the status of PostgreSQL, run the following command:
systemctl status postgresql-14
You will get the following output:
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2022-02-16 16:47:32 UTC; 10s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 8423 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 8428 (postmaster)
Tasks: 8 (limit: 11412)
Memory: 17.4M
CGroup: /system.slice/postgresql-14.service
├─8428 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─8430 postgres: logger
├─8432 postgres: checkpointer
├─8433 postgres: background writer
├─8434 postgres: walwriter
├─8435 postgres: autovacuum launcher
├─8436 postgres: stats collector
└─8437 postgres: logical replication launcher
Feb 16 16:47:32 linux systemd[1]: Starting PostgreSQL 14 database server...
Feb 16 16:47:32 linux postmaster[8428]: 2022-02-16 16:47:32.115 UTC [8428] LOG: redirecting log output to logging collector process
Feb 16 16:47:32 linux postmaster[8428]: 2022-02-16 16:47:32.115 UTC [8428] HINT: Future log output will appear in directory "log".
Feb 16 16:47:32 linux systemd[1]: Started PostgreSQL 14 database server.
By default, PostgreSQL listens on port 5432. You can check it with the following command:
ss -antpl | grep 5432
You will get the following output:
LISTEN 0 128 127.0.0.1:5432 0.0.0.0:* users:(("postmaster",pid=8428,fd=7))
LISTEN 0 128 [::1]:5432 [::]:* users:(("postmaster",pid=8428,fd=6))
Step 3 – Connect to PostgreSQL
There are two ways to connect to the local PostgreSQL instance:
Use the following command to connect to the local PostgreSQL instance directly:
sudo -u postgres psql
You will get the PostgreSQL shell in the following output:
psql (14.2) Type "help" for help. postgres=#
You can also use the following method to connect to the local PostgreSQL instance:
First, switch the user to the PostgreSQL user:
su - postgres
Next, run the psql command to connect to the PostgreSQL shell:
psql
You will get the following output:
psql (14.2) Type "help" for help. postgres=#
Now, run the exit command two times to exit from the PostgreSQL.
Also Read
How to Install and Use pgAdmin on Ubuntu 18.04
Step 4 – Working with PostgreSQL
By default, the Postgres user is configured without any password. For security reasons, it is recommended to set a strong password for the Postgres user.
Run the following command to set a password for the Postgres user:
psql -c "alter user postgres with password 'password'"
To create a database named wpdb, run the following command:
psql CREATE DATABASE wpdb;
To list all PostgreSQL databases, run the following command:
\l
You will get a list of all PostgreSQL databases in the following output:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
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
wpdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
Step 5 – Configure PostgreSQL for Remote Access
By default, PostgreSQL is configured to listen on localhost, so only local users can connect to the PostgreSQL database. If your application is hosted on a different server then you will need to configure PostgreSQL for remote access.
You can configure it by editing the following file:
nano /var/lib/pgsql/14/data/postgresql.conf
Change the following line with your server IP address:
listen_addresses = 'your-server-ip'
Save and close the file, then edit another file:
nano /var/lib/pgsql/14/data/pg_hba.conf
Add the following line:
# Accept from anywhere host all all 0.0.0.0/0 md5
Save and close the file, then restart the PostgreSQL service to apply the changes:
systemctl restart postgresql-14
You can now connect to the PostgreSQL server from the remote machine using the following command:
psql -U username -h your-server-ip -p 5432 dbname
Conclusion
In this post, we learned how to install PostgreSQL 14 on Rocky Linux 8. We also learned how to connect and interact with PostgreSQL. Try it on dedicated hosting from Atlantic.Net!
