In this guide, we will walk through installing PostgreSQL 14 and then configure I to use an md5 connection whenever you are login into Postgres.
PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. It was designed to handle a range of workloads from single machines to data warehousing.
Every database must conform to ACID properties i.e Atomicity, Consistency, Isolation, and Durability. This is true for PostgreSQL as well, it must conform to those features for transactions to pass the tests.
1. Update system repositories
Before you do any installation, begin by updating your repositories in order to make them up to date.
sudo dnf update -y
We can check the Apstream for available PostgreSQL first.
sudo dnf module list postgresql
This will list all available PostgreSQL in your Fedora Apstream. The output should look like this.
Fedora Modular 36 - x86_64 Name Stream Profiles Summary 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 Fedora Modular 36 - x86_64 - Updates Name Stream Profiles Summary 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]nstalled
2. Install PostgreSQL 14
To install PostgreSQL use the following command.
sudo dnf install postgresql-server postgresql-contrib
After installation is complete, we need to enable Postgressql, it is disabled by default in our system.
sudo systemctl enable postgresql
Now we need to populate the PostgreSQL database with initial data. This will create
$ sudo postgresql-setup --initdb --unit postgresql * Initializing database in '/var/lib/pgsql/data' * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
Then we can start Postgresql using the following code.
sudo systemctl start postgresql
Check if your PostgreSQL server is running.
● postgresql.service - PostgreSQL database server Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendo> Active: active (running) since Thu 2022-09-01 05:30:34 UTC; 32s ago Process: 13028 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql> Main PID: 13030 (postmaster) Tasks: 8 (limit: 4659) Memory: 16.4M CPU: 60ms CGroup: /system.slice/postgresql.service ├─ 13030 /usr/bin/postmaster -D /var/lib/pgsql/data ├─ 13031 "postgres: logger " ├─ 13033 "postgres: checkpointer " ├─ 13034 "postgres: background writer " ├─ 13035 "postgres: walwriter " ├─ 13036 "postgres: autovacuum launcher " ├─ 13037 "postgres: stats collector " └─ 13038 "postgres: logical replication launcher "
So to check the version of the installed server use the following command.
postgres -V postgres (PostgreSQL) 14.3
3. Setting up password for Postgres role
Local connections on Postgresql use peer-to-peer connections to check the authenticated users. That is Postgresql instead of asking for the user password, it only checks on the logged-in user to ascertain if the user has credentials to get access to the resources.
Let’s change the way we log in and use a more strong password instead. Let’s set the password for the Postgres user first.
Open psql as the Postgres user with this command
sudo -u postgres psql
$ sudo -u postgres psql could not change directory to "/root": Permission denied psql (14.3) Type "help" for help.
The reason we are getting
could not change directory to "/root": Permission denied is because of the Postgres authentication, we need to set the password first.
To change the Postgres password use the following command inside of the Postgres interface.
ALTER USER POSTGRES WITH ENCRYPTED PASSWORD 'mypasswrd'; ALTER ROLE
After we have set the password, we now need to tell Postgres to use this password. To do that we need to change
pg_hba.conf file. I am using vim as my text editor here, you are free to use your preferred editor as well.
Look for this line of code and change peer to md5
sudo vi /var/lib/pgsql/data/pg_hba.conf
Restart PostgreSQL for the changes to take effect.
sudo systemctl restart postgresql
To ascertain that changes did take place login again to your Postgres server, if it asks for the password, then you are good t go.
$ psql -U postgres psql (14.3) Type "help" for help.
You can use the following code whenever you want to upgrade PostgreSQL to a higher version.