How to Install and Use PostgreSQL on Fedora

PostgreSQL is a powerful, free, open-source relational database management software. It is very popular due to its reliability, stability, and support for open technical standards. It is versatile and expandable so that you can use it in various specialized use cases with a powerful extension ecosystem. If you want to create highly scalable computing environments in your organization, then PostgreSQL is the best option.

This guide will show you how to install and use the PostgreSQL server on Fedora.

Step 1 – Enable PostgreSQL Repo

By default, Fedora provides a PostgreSQL version 13 via its default repo, so you must enable the PostgreSQL 14 repo in your server.

First, reset the default PostgreSQL repo with the following command.

dnf update -y
dnf module reset postgresql -y

Next, enable the PostgreSQL 14 repo with the following command.

dnf module enable postgresql:14

You will see the following output.

Last metadata expiration check: 0:02:34 ago on Sunday 07 May 2023 04:12:57 AM.
Dependencies resolved.
========================================================================================================================================================================
 Package                                 Architecture                           Version                                   Repository                               Size
========================================================================================================================================================================
Enabling module streams:
 postgresql                                                                     14                                                                                     

Transaction Summary
========================================================================================================================================================================

Is this ok [y/N]: y
Complete!

Step 2 – Install PostgreSQL 14

Now, run the following command to install PostgreSQL 14 on your server.

dnf install postgresql-server postgresql

After the installation, initialize the PostgreSQL database with the following command.

postgresql-setup --initdb

You will get the following output.

 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Step 3 – Start PostgreSQL Service

Now, start the PostgreSQL service and enable it to start at system reboot with the following command.

systemctl enable --now postgresql

You can now check the status of PostgreSQL with the following command.

systemctl status postgresql

You will get the following output.

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: active (running) since Sun 2023-05-07 04:16:16 EDT; 8s ago
    Process: 17972 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
   Main PID: 17975 (postmaster)
      Tasks: 8 (limit: 4666)
     Memory: 15.8M
        CPU: 67ms
     CGroup: /system.slice/postgresql.service
             ├─17975 /usr/bin/postmaster -D /var/lib/pgsql/data
             ├─18002 postgres: logger
             ├─18004 postgres: checkpointer
             ├─18005 postgres: background writer
             ├─18006 postgres: walwriter
             ├─18007 postgres: autovacuum launcher
             ├─18008 postgres: stats collector
             └─18009 postgres: logical replication launcher

May 07 04:16:16 fedora systemd[1]: Starting PostgreSQL database server...

You can check the PostgreSQL version with the following command.

postgres --version

Output:

postgres (PostgreSQL) 14.1

By default, PostgreSQL runs on port 5432. You can check it with the following command.

ss -antpl | grep -i postmaster

Output.

LISTEN 0      244        127.0.0.1:5432      0.0.0.0:*    users:(("postmaster",pid=17975,fd=7))    
LISTEN 0      244            [::1]:5432         [::]:*    users:(("postmaster",pid=17975,fd=6))

Step 4 – Configure PostgreSQL for Remote Access

By default, PostgreSQL is accessible only from the local system. You can allow remote access by editing the PostgreSQL configuration file.

nano /var/lib/pgsql/data/postgresql.conf

Change the following line:

listen_addresses = '*'

Save and close the file, then edit another configuration file.

nano /var/lib/pgsql/data/pg_hba.conf

Find the following lines:

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

Replace them with the next line:

# Accept from anywhere
host all all 0.0.0.0/0 md5

Save and close the file, then restart the PostgreSQL service to reload the changes.

systemctl restart postgresql

Step 5 – Set PostgreSQL Password

By default, no password is assigned to the Postgres user. For security reasons, setting a strong password for the Postgres user is a good idea.

First, log in to PostgreSQL with the following command.

su - postgres

Once logged in, set a password for the Postgres user.

psql -c "alter user postgres with password 'securepassword'"

Finally, exit from the Postgres Shell with the following command.

exit

Conclusion

In this post, we explained how to install PostgreSQL 14 on Fedora. We also showed you how to enable PostgreSQL remote access and set a Postgres password. You can now try PostgreSQL on VPS hosting from Atlantic.Net!