Data is defined as facts related to any object in consideration. This can be a name, age, weight, gender, picture, file, pdf, e.t.c. All this information can be stored in a database. A database can be defined as a systematic data collection. It is vital in any organization as it is used to store, organize and easily manage data.
Databases are broadly classified as:
- Relational databases (RDBMS or SQL databases): data is stored in tables and rows, the tables are then linked using keys. The popular relational databases are MySQL, MariaDB, PostgreSQL, SQLite e.t.c
- Non-relational databases(NoSQL databases): data is stored using a storage model optimized for specific requirements. The most popular NoSQL databases are MongoDB, Couchbase, Apache Cassandra, Redis, Apache HBase e.t.c
PostgreSQL also known as Postgres, is an open-source object-oriented relational database that holds a high reputation in reliability, data integrity, robust feature set, and extensibility. It not only implements the Structured Query Language and also supports non-relational (JSON) queries. PostgreSQL was first developed in 1986 as part of the Postgres project at the University of California at Berkeley. This means that this database system has been in active development for over 30 years resulting in high resilience and performance with notable levels of integrity. PostgreSQL is used in innumerable areas where high performance is required. The main use areas are in storing data for web, analytic and mobile applications.
The latest release, PostgreSQL 15 was made on October 13, 2022. This version builds on the performance improvements of the recent release versions with visible enhancements for managing workloads in both local and distributed deployments, including improved sorting.
The notable features associated with PostgreSQL 15 are:
- Logging and Configuration Enhancements: It introduces a new logging format known as jsonlog. This outputs the log data using a defined JSON structure, thus allowing the logs to be processed in structured logging systems.
- Expressive Developer Features: It includes the SQL standard
MERGEcommand that lets users write conditional SQL statements that can includeINSERT,UPDATE, andDELETEactions within a single statement. It alos allows users to create views that query data using the permissions of the caller, not the view creator. - Improved Sort Performance and Compression: There has been an improvement in in-memory and on-disk sorting algorithms as viewed form benchmarks.
- More Options with Logical Replication: It is more flexible when managing logical replication. New row filtering and column lists for publishers have been introduced, allowing users to replicate a subset of data from a table.
- New built-in extension known as
pg_walinspect: that lets users inspect the contents of write-ahead log files directly from a SQL interface. - PostgreSQL 15 removes both the long-deprecated “exclusive backup” mode and support for Python 2 from PL/Python.
- It introduces ICU collation the default collation for a cluster or an individual database.
Today, we will learn how to install PostgreSQL 15 on Rocky Linux 8 / AlmaLinux 8. We will also cover how to configure and use the database server.
#1. Add PostgreSQL 15 Repository
The default Rocky Linux 8 / AlmaLinux 8 repository provides PostgreSQL 10, to verify this, use the command:
$ dnf provides postgresql-server
Rocky Linux 8 - AppStream 14 MB/s | 8.6 MB 00:00
Rocky Linux 8 - BaseOS 7.1 MB/s | 2.7 MB 00:00
Rocky Linux 8 - Extras 35 kB/s | 11 kB 00:00
postgresql-server-10.21-2.module+el8.6.0+977+ab6e685c.x86_64 : The programs
...: needed to create and run a PostgreSQL server
Repo : appstream
Matched from:
Provide : postgresql-server = 10.21-2.module+el8.6.0+977+ab6e685cFor us to install PostgreSQL 15, we need to add the repository to Rocky Linux 8 / AlmaLinux 8. This can be done using the command:
##For x86_64
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
##For ppc64le
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-ppc64le/pgdg-redhat-repo-latest.noarch.rpm
##For aarch64
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-aarch64/pgdg-redhat-repo-latest.noarch.rpm
Next, disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql#2. Install PostgreSQL 15 on Rocky Linux 8 / AlmaLinux 8
Once the repository has been added using the commands above, we are set to install PostgreSQL 15 on Rocky Linux 8 / AlmaLinux 8.
The command for this is:
sudo dnf install postgresql15-server -yDependency Tree:
....
Transaction Summary
================================================================================
Install 3 Packages
Total download size: 7.8 M
Installed size: 33 M
Is this ok [y/N]: y
Once the installation is complete, Initialize PostgreSQL 15:
$ sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OKNow start and enable the PostgreSQL 15 service:
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15Check if the service is running:
$ systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2022-11-25 05:06:20 EST; 6s ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 3527 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 3532 (postmaster)
Tasks: 7 (limit: 23198)
Memory: 17.5M
CGroup: /system.slice/postgresql-15.service
├─3532 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
├─3534 postgres: logger
├─3535 postgres: checkpointer
├─3536 postgres: background writer
├─3538 postgres: walwriter
├─3539 postgres: autovacuum launcher
└─3540 postgres: logical replication launcher
#3. Getting Started with PostgreSQL 15
PostgreSQL has a few concepts that you need to be aware of before you can comfortably use the database system. These are:
- Roles: This is used to handle client authentication and authorization. These are more similar to the Unix user accounts but since PostgreSQL doesn’t distinguish between users and groups, it prefers to call them roles.
- Ident authentication: This associates the Postgres roles with a matching Unix/Linux system account. In case a role exists within PostgreSQL, the Linux username with a similar identity is able to sign in as that role.
The default role created after the PostgreSQL installation is known as postgres. There are two methods how to login to PostgreSQL. These are:
- Method 1
Direct connection using the sudo command as shown:
$ sudo -u postgres psql
psql (15.1)
Type "help" for help.
postgres=#
To leave the shell, use the command below:
postgres=# \q- Method 2
Switching to the Postgres user first:
sudo -i -u postgresThen access the shell with the command:
[postgres@neveropen ~]$ psql
psql (15.1)
Type "help" for help.
postgres=# To leave the shell, use the command:
postgres=# \qThen exit the user account:
[postgres@neveropen ~]$ exita. Adding a password to the postgres Role
By default, the Postgres role is not secured by a password. To set the password begin by setting a password for the UNIX user:
$ sudo passwd postgres
Changing password for user postgres.
New password: <Enter-Password>
Retype new password: <Re-Enter-Password>
passwd: all authentication tokens updated successfullyNow if you switch to the user’s shell, you should provide the set password:
$ sudo su - postgres
Password:
Last login: Fri Nov 25 13:37:38 CEST 2022 on pts/0While here, if you run the client, you will not be asked for a password:
$ psql
psql (15.0)
Type "help" for help.
postgres=#This is still not secure enough, we need to set a password for the role as well. So while in the shell, use the command below to set a password:
postgres=# \password
Enter new password for user "postgres": <Enter-Your-Password>
Enter it again: <Re-Enter-Your-Password>That is it, you now have the instance secured with a password but it will not work until you update the pg_hba.conf as shown in step 4.
b. Creating a New Role in PostgreSQL 15
Aside from the default role, you can create any other preferred role for PostgreSQL. To achieve that, connect to the database using the preferred method. For example:
sudo -u postgres psqlNow create the Role with the command:
CREATE ROLE testuser WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';Remember to replace testuser and Passw0rd correctly. Verify your creation with the command:
postgres=# \du
List of roles
Role name | Attributes | Member
of
-----------+------------------------------------------------------------+-------
----
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
testuser | Superuser, Create role, Create DB | {}c. Creating a New Database in PostgreSQL 15
Once a role is created, we need to create a database for the user. Remember the database name has to be with the same name as the user since the role will attempt to connect to a database.
postgres=# CREATE DATABASE testuser;
CREATE DATABASE
postgres=# \qd. Opening a Postgres Prompt with the New Role
Now we can log in to the PostgreSQL shell using the created role. But first, we need to add the role as a UNIX user.
sudo adduser testuserOnce added, you can connect to the shell using the role as shown:
$ sudo -u testuser psql
psql (15.1)
Type "help" for help.
testuser=# e. Creating Tables in PostgreSQL 15
Now you can create tables in the connected database. The command for this bears the below syntax:
CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);For example:
CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);Once created, verify with the command:
testuser# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | playground | table | testuser
(1 row)For a more detailed view, execute:
testuser# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+----------
public | playground | table | testuser
public | playground_equip_id_seq | sequence | testuser
(2 rows)f. Adding, Querying, and Deleting Data in a Table
After creating a table, we can add the desired data to it. Below is sample data added to the created table above:
INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');You can now view the added data in the table:
testuser=# SELECT * FROM playground;
equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
1 | slide | blue | south | 2017-04-28
2 | swing | yellow | northwest | 2018-08-16
(2 rows)Now you can delete the entries as desired. For example, to delete “slide” from the table, we’ll use:
DELETE FROM playground WHERE type = 'slide';Now verify the made changes:
testuser=# SELECT * FROM playground;
equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
2 | swing | yellow | northwest | 2018-08-16
(1 row)g. Updating Data in a Table
You can also modify the data in the tables using the UPDATE command. For example:
UPDATE playground SET color = 'red' WHERE type = 'swing';Check the made changes:
testuser=# SELECT * FROM playground;
equip_id | type | color | location | install_date
----------+-------+-------+-----------+--------------
2 | swing | red | northwest | 2018-08-16
(1 row)After the desired actions have been performed, exit the shell with the command:
testuser=# \q#4. Configure PostgreSQL 15 for Remote Access
It is possible to configure PostgreSQL 15 to be accessed remotely. This requires you to be familiar with a few concepts. Now am convinced that you know the default authentication in PostgreSQL is the ident authentication. There are several other authentication types. These are:
- Trust: allows a role to connect as long as conditions are defined in the
pg_hba.conffile are satisfied. - Peer: similar to ident authentication but it is only supported on local connections.
- Password: here a role connects using a password.
To configure remote access for PostgreSQL, we need to modify the pg_hba.conf file and allow the instance to be accessed either everywhere or on specific IP addresses/networks.
sudo vim /var/lib/pgsql/15/data/pg_hba.confIn the file, you will make the desired changes whether to allow it to be accessed everywhere or on specific IP addresses/networks.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all scram-sha-256
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.205.1/24 md5
#host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 scram-sha-256
host all all 192.168.205.1/24 md5
#host all all 0.0.0.0/0 md5To enable the server to be accessed everywhere set the network as 0.0.0.0/0. For this guide, we have specified remote access to any device in the 192.168.205.1/24 network. It is also possible to specify the user and database to connect to the server remotely instead of “all”. For password authentication to work on localhost, set scram-sha-256 as the authentication method.
The next thing is to set the listening address. This can be done in the config file below:
sudo vim /var/lib/pgsql/15/data/postgresql.conf This can be set to * (all interfaces) or a specific address.
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#-----------------------------------------------------------------------------
.......
#listen_addresses='*'
listen_addresses='192.168.205.2'For the changes made to apply, restart the service:
sudo systemctl restart postgresql-15#5. Access PostgreSQL 15 Remotely
The default listening port for PostgreSQL is 5432. To verify if the service is listening to this port, use the command
$ ss -tunelp | grep 5432
tcp LISTEN 0 128 192.168.205.2:5432 0.0.0.0:* uid:26 ino:54644 sk:f <-> If you have a firewall running, allow the service through it with the command:
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reloadNow you can access the service from a remote host with the PostgreSQL 15 Client using a command with the below syntax:
psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'For example:
psql 'postgres://testuser:[email protected]:5432/postgres?sslmode=disable'Sample Output:
Recommended books:
Final Thoughts
We have successfully installed PostgreSQL 15 on Rocky Linux 8 / AlmaLinux 8. We have also learned how to create roles, databases, and tables, and also configure PostgreSQL 15 on Rocky Linux 8 / AlmaLinux 8 for remote access. I hope this was important to you.
See more:
