Postgresql is an open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.
It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. PostgreSQL is used as the primary data store or data warehouse for many web, mobile, geospatial, and analytics applications. PostgreSQL can store structured and unstructured data in a single product.
In this guide we are going to install Postgresql 14 in Fedora 34.
Related Content
- Postgres Permissions – Create, Update, Delete Database Users
- How to Install and Configure Postgres 14 on Centos 8
- How to Install and Configure Postgres 13 on Centos 8
- Running Postgresql 14 with Docker and Docker-Compose
- How to Install and Configure Postgres 13 Ubuntu 20.04
- How to Install and Configure Postgres 14 on Debian 11
- How to Install and Configure Postgres 14 Ubuntu 20.04
- How to Install & Configure Postgres 14 on OpenSUSE Leap 15.3
Prerequisites
To follow along, ensure you have the following:
- Updated Fedora 34 server
- Root access to the server or user with root access
- Internet access from the server
- Basic knowledge of Linux terminal
Table of contents
- Ensure the server is up to date
- Installing and starting Postgres Server
- PostgreSQL Roles and Databases Authentication Methods
- Connecting to postgres database
- Configuring postgres 14 instance for remote access
- User management
1. Ensure the server is up to date
Before proceeding, let us ensure that our server has up to date packages. Use this command:
$ sudo dnf -y update
Last metadata expiration check: 2:55:24 ago on Tue 26 Oct 2021 12:05:29 PM UTC.
Dependencies resolved.
Nothing to do.
Complete!2. Installing and starting Postgres Server
The fedora dnf postgres module contains a Postgres 14 installation but it is not the default one. Let us check Postges module on Fedora using this command:
$ sudo dnf module list postgresql
Last metadata expiration check: 2:56:45 ago on Tue 26 Oct 2021 12:05:29 PM UTC.
Fedora Modular 34 - x86_64
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL module
postgresql 10 client, server [d] PostgreSQL module
postgresql 11 client, server [d] PostgreSQL module
postgresql 12 client, server PostgreSQL module
postgresql 13 client, server PostgreSQL module
Fedora Modular 34 - x86_64 - Updates
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL module
postgresql 10 client, server [d] PostgreSQL module
postgresql 11 client, server [d] PostgreSQL module
postgresql 12 client, server PostgreSQL module
postgresql 13 client, server PostgreSQL module
postgresql 14 client, server PostgreSQL module
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalledThat mean to use it you have to enable it. First reset the postgres module so we don’t have a default
sudo dnf module reset postgresqlThen enable postgres 14 with this command:
$ sudo dnf module enable postgresql:14
Last metadata expiration check: 3:03:33 ago on Tue 26 Oct 2021 12:05:29 PM UTC.
Dependencies resolved.
================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
================================================================================================================================================================================================================================================================================
Enabling module streams:
postgresql 14
Transaction Summary
================================================================================================================================================================================================================================================================================
Is this ok [y/N]: y
Complete!Now let us install Postgresql 14 and the Contrib package which provides several additional features for the PostgreSQL database system:
sudo dnf install -y postgresql-server postgresql-contribOnce the installation is complete, initialize the PostgreSQL database with the following command:
$ sudo postgresql-setup initdb
WARNING: using obsoleted argument syntax, try --help
WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.logNow start the service:
sudo systemctl start postgresqlThen enable the service so it starts when the server reboots:
$ sudo systemctl enable postgresql
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.Confirm that Postgres 14 is running:
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2021-10-26 15:18:53 UTC; 46s ago
Process: 17645 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
Main PID: 17647 (postmaster)
Tasks: 8 (limit: 4603)
Memory: 15.7M
CPU: 47ms
CGroup: /system.slice/postgresql.service
├─17647 /usr/bin/postmaster -D /var/lib/pgsql/data
├─17648 postgres: logger
├─17650 postgres: checkpointer
├─17651 postgres: background writer
├─17652 postgres: walwriter
├─17653 postgres: autovacuum launcher
├─17654 postgres: stats collector
└─17655 postgres: logical replication launcher
Oct 26 15:18:52 ip-10-2-40-182.us-west-2.compute.internal systemd[1]: Starting PostgreSQL database server...
Oct 26 15:18:52 ip-10-2-40-182.us-west-2.compute.internal postmaster[17647]: 2021-10-26 15:18:52.998 UTC [17647] LOG: redirecting log output to logging collector process
Oct 26 15:18:52 ip-10-2-40-182.us-west-2.compute.internal postmaster[17647]: 2021-10-26 15:18:52.998 UTC [17647] HINT: Future log output will appear in directory "log".
Oct 26 15:18:53 ip-10-2-40-182.us-west-2.compute.internal systemd[1]: Started PostgreSQL database server.The Active: active (running) shows that the service is up and running.
Next, let us verify that the installation was successful by connecting to the PostgreSQL database server and printing its version:
sudo -u postgres psql -c "SELECT version();"Output:
$ sudo -u postgres psql -c "SELECT version();"
could not change directory to "/home/fedora": Permission denied
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 14.0 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 11.2.1 20210728 (Red Hat 11.2.1-1), 64-bit
(1 row)3. PostgreSQL Roles and Databases Authentication Methods
PostgreSQL uses a concept called roles to handle client authentication and authorization. By default, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.
The installation procedure created a user account called postgres that is associated with the default postgres role. In order to use PostgreSQL, you can log in to that account.
PostgreSQL supports multiple authentication methods . The most commonly-used methods are:
Trust– A role can connect without a password, as long as the conditions defined in thepg_hba.confare met.Password– A role can connect by providing a password. The passwords can be stored asscram-sha-256,md5, andpassword(clear-text).Ident– Only supported on TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.Peer– Same as Ident, but it is supported on local connections only.
4. Connecting to postgres database
- By Switching to
postresuser
Switch over to the postgres account on your server by typing this in the terminal;:
sudo -i -u postgresYou can now access a Postgres prompt immediately by typing:
$ psql
psql (14.0)
Type "help" for help.
postgres=#This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.
- By running the command as
postgresuser
Use this to run the command directly as the postgres user using sudo
sudo -u postgres psqlOutput:
$ sudo -u postgres psql
psql (14.0)
Type "help" for help.
postgres=#5. Configuring postgres 14 instance for remote access
To achieve this, we will modify postgres configuration files. We need to open the files and adjust the configs are required. The main configuration file for Postgresql 14 can be found in this path /var/lib/pgsql/14/data/pg_hba.conf
Let’s change peer identification to trust:
sed -i '/^local/s/peer/trust/' /var/lib/pgsql/data/pg_hba.confChange ident identification to md5 to allow password login.
sed -i '/^host/s/ident/md5/' /var/lib/pgsql/data/pg_hba.confAdd a block to allow access from everywhere:
Add this content to the file /var/lib/pgsql/data/pg_hba.conf
host all all 0.0.0.0/0 md5Ensure PostgreSQL is listening on *
Add this line to the config here /var/lib/pgsql/data/postgresql.conf
listen_addresses='*'Enable and restart postgresql server to reload the configs
sudo systemctl restart postgresql
sudo systemctl enable postgresql6. User management
Creating Superuser
Now that everything is set up, let us create a super user.
Connect to the DB as postres role:
$ sudo -u postgres psql
psql (14.0)
Type "help" for help.
postgres=#Create super user with name root:
CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'passwordhere';Output:
postgres=# CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'passwordhere';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root | Superuser, Create role, Create DB | {}
postgres=#Managing Application Users
Use this to create a database, create a user and grant that user all accesss to that database:
create database app_db_name;
create user app_user with encrypted password 'dbpassword';
grant all privileges on database app_db_name to app_user;Checkout this comprehensive guide on user and permission management in postgres here.
Connecting to the instance from remote host
Use this command to connect to the postgres instance from local machine:
psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'
# like
psql 'postgres://root:passwordhere@192.160.1.20:5432/postgres?sslmode=disable'Conclusion
Up to this point we have managed to install Postgresql 14 on an Fedora 34 server, do some basic configurations then do basic user management.