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
- Initiate the
postgresqldatabase 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
[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
[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
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.
PostGISadds spatial functions such as geometry data types, distance, area, etc. to PostgreSQL. The latest release of
- 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.
[ Getting started with containers? Check out this free course. Deploying containerized applications: A technical overview. ]
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.