PostgreSQL is a well-known open-source relational database, and its motto states what the project intends to be: the most advanced open-source database in the world.
The main qualities that attract masses of new users every year and keep current users enthusiastic about their projects are its rock-solid stability, scalability, and safeness, as well as the features that an enterprise-level database management system provides.
But PostgreSQL is not just a database; it has grown to be a whole ecosystem of extensions, tools, and languages tied together by communities spread around the world.
PostgreSQL is an open-source project and is fully developed in the open-source world. That means that there is no single entity in charge of the project and the result is that PostgreSQL is not a commercial product. In other words, PostgreSQL belongs to everyone, and...
You can find the code for this chapter at the following GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL.
PostgreSQL at a glance
As a relational database, PostgreSQL provides a lot of features, and it is quite difficult to "scare" a PostgreSQL instance. In fact, a single instance can contain more than 4 billion individual databases, each with unlimited total size and capacity for more than 1 billion tables, each containing 32 TB of data. Moreover, if there's any concern that those upper limits won't suffice, please consider that a single table can have 1,600 columns, each 1 GB in size, with an unlimited number of multi-column (up to 32 columns) indexes. In short, PostgreSQL can store much more data than you can possibly think of!
Therefore, there is no amount of data that PostgreSQL cannot handle, but of course, in order to perform well with certain big databases, you need to understand PostgreSQL and its features.
PostgreSQL is fully ACID-compliant and has a very strong foundation in data integrity and concurrency. It ships with a procedural language, named PL/pgSQL...
Exploring PostgreSQL terminology
A PostgreSQL instance is called a cluster because a single instance can serve and handle multiple databases. Every database is an isolated space where users and applications can store data.
A database is accessed by allowed users, but users connected to a database cannot cross the database boundaries and interact with data contained in another database, unless they explicitly connect to the latter database too.
A database can be organized into namespaces, called schemas. A schema is a mnemonic name that the user can assign to organize database objects, such as tables, into a more structured collection. Schemas cannot be nested, so they represent a flat namespace.
Database objects are represented by everything the user can create and manage within the database—for instance, tables, functions, triggers, and data types. Every object belongs to one and only one schema that, if not specified, is the default public schema.
Users are defined at a cluster...
Installing PostgreSQL 12 or higher
PostgreSQL can run on several Unix and Unix-like operating systems, such as Linux, as well as on Microsoft Windows. So far, the most supported platform remains Linux because most PostgreSQL developers work on this platform, and so it is the one with the most tested use cases. However, deploying on other platforms should not present any problems and, most importantly, is not going to put your data at any risk.
This section will focus on installing PostgreSQL 12, since it is the latest stable version available worldwide. You will learn, however, how to build your own version of PostgreSQL, and this may also be the way to install PostgreSQL 13 on your system.
Before installing PostgreSQL 12, you need to choose, or at least evaluate, how to install it. There are two main ways to get PostgreSQL 12 up and running, as follows:
- Compiling from sources
- Using a binary package
Binary packages are provided by the PostgreSQL community or the operating system, and...
This chapter has introduced you to PostgreSQL, its history, and its main features. You have learned about PostgreSQL terminology, as well as how to install a cluster on Unix-like operating systems, such as GNU/Linux Debian, Fedora, and FreeBSD, as well as installing the tool from various sources.
In the following chapters, you will start using this great database engine and learn details about every main single feature it provides.
- PostgreSQL 12 release note: https://www.postgresql.org/docs/12/release-12.html
- Upgrading documentation: https://www.postgresql.org/docs/current/upgrading.html
- PostgreSQL version policy: https://www.postgresql.org/support/versioning/
- PostgreSQL initdb official documentation: https://www.postgresql.org/docs/12/app-initdb.html
- PostgreSQL pg_ctl official documentation: https://www.postgresql.org/docs/12/app-pg-ctl.html
- pgenv GitHub repository and documentation: https://github.com/theory/pgenv