Getting started with PostgreSQL on Linux

PostgreSQL is an open source object-relational database system. It is considered to be the world's most advanced open source relational database designed for extensibility and customization.

Major features of PostgreSQL

• Portability – PostgreSQL is written in ANSI C and supports different flavors of operating systems such as Windows, Linux, macOS, and all other major UNIX platforms.
• Reliability – PostgreSQL is ACID-compliant and supports transactions and savepoints. It uses the concept of write-ahead logging (WAL) for transaction logging.
• Scalability – PostgreSQL uses multi-version concurrency control, supports tablespaces and parallel sequential scans.
• Recovery and Availability – PostgreSQL supports hot backup using a built-in backup utility called pg_basebackup, manages streaming replication using replication slots, and contains synchronous and asynchronous options.
• Security – PostgreSQL uses host-based authentication for enhanced database security, supports logging, provides object-level permissions, and offers row-level security.

[ You might also like: Running rootless Podman as a non-root user ]

Installation and setup of PostgreSQL

There are numerous ways to install PostgreSQL, and which one is most appropriate for your situation will depend on your environment and specific needs. PostgreSQL can be downloaded and installed using installers or ready-to-use software bundles for various OS flavors from the PostgreSQL website, or often found packaged for specific operating systems. The latest available version of PostgreSQL is 13.2. At the time I am writing this, the latest available version of PostgreSQL on my version of Red Hat Enterprise Linux (RHEL) is 12.5.

On a RHEL 8.3 system, these are the steps I followed to install and setup PostgreSQL. Be sure to check the official documentation before proceeding with an installation in your environment.

• Add RHEL 8.3 repositories from CDN to Satellite Server.
• Sync the repository Red Hat Enterprise Linux 8 for x86_64 - AppStream (RPMs).
• Enable the AppStream repository for the RHEL 8.3 host using this command:

[root@rheltest ~]# subscription-manager repos --enable rhel-8-for-x86_64-appstream-rpms

• Install PostgreSQL using this command:

[root@rheltest ~]# dnf install @postgresql

• This step downloads the postgresql-server and postgresql packages.
• Initiate the postgresql database using the following command:

[root@rheltest ~]# /usr/bin/postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

• Enable the PostgreSQL database for auto-start of Postgres service at boot:

[root@rheltest ~]# systemctl enable postgresql

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

• Start the PostgreSQL service using the systemctl command:

[root@rheltest ~]# systemctl start postgresql

• Check the PostgreSQL service status using the command:

[root@rheltest ~]# systemctl status postgresql

postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2021-03-03 11:06:28 IST; 14s ago

• Log in to Postgres and enter the command '\l+' to view the list of databases in PostgreSQL terminal psql:

[root@rheltest ~]# su - postgres
[postgres@rheltest ~]$ psql
psql (12.5)
Type "help" for help.
postgres=# \l+

Introduction to pgAdmin

pgAdmin is an open source PostgreSQL GUI tool used for administration and managing PostgreSQL databases version 9.5 and above. This lightweight web-based tool can be used on Linux, Unix, macOS, and Windows platforms. The latest available version is pgAdmin version 4.30. You can try a free demo of pgAdmin sandbox.

Use cases for PostgreSQL

• Geographic Information System – PostgreSQL has a powerful spatial database extender called PostGIS. This adds support for geographical data for locations in various formats using SQL queries. PostGIS adds spatial functions such as geometry data types, distance, area, etc. to PostgreSQL. The latest release of PostGIS is 3.1.1.
• Financial Industry – PostgreSQL is recommended for use in the financial industry. Since PostgreSQL is ACID-compliant, it is considered ideal for use in OLTP workloads. PostgreSQL integrates well with mathematical software like R and Matlab, making it suitable for performing database analytics in OLAP workloads.
• Research purposes – PostgreSQL has in-built analytical capabilities and a powerful SQL engine that can store and process vast amounts of data. Research and scientific workloads require storing data in exabytes, and PostgreSQL has evolved to fill future data requirements.
• Manufacturing industry – PostgreSQL is considered to be highly reliable and is used by industrial manufacturers. In manufacturing, each transaction is critical, and point in time recovery is vital to ensure minimal data loss in the event of a disaster. As a result, the fact that PostgreSQL offers automatic failover, streaming replication, near-zero downtime for upgrades, etc., makes it suitable for manufacturing industries.
• Web technology – There has been an increase in the use of LAPP (Linux – Apache – PHP – Postgres) as a new infrastructure stack, preferred for developing scalable web applications due to its ease of installation and setup. LAPP is also the preferred infrastructure stack in cloud deployments by various startups. PostgreSQL works well with all modern web frameworks such as Django (Python), Node.js (JavaScript), Hibernate (Java), PHP, etc.

[ Getting started with containers? Check out this free course. Deploying containerized applications: A technical overview. ]

Wrap up

The highly active open source community has helped PostgreSQL garner attention the world over. To wrap up, PostgreSQL is open source, free, and easy to use. It is filled with powerful built-in features and is highly extensible.