Basic Database Components in PostgreSQL With Code Example

Database Components in PostgreSQL

PostgreSQL was created by Berkeley University Professor Michael Stonebraker as an object-relational database management system. It competes with leading relational database providers and is renowned for its extensive features and extensibility. The PostGIS system for geographic data is one of the many research and production applications that employ PostgreSQL, which is compatible with the majority of contemporary operating systems, including Windows, Mac, and Linux.

In order to conduct database operations, client programs link to a server process using the client/server architecture of the PostgreSQL system. Besides accepting client connections and handling database files, postgres server handles client-related tasks. Every new client connection forks a new process in PostgreSQL, allowing several clients to execute.

Here is a breakdown of its fundamental elements:

Database Cluster

In PostgreSQL, a database cluster is a basic organisational unit that consists of a group of databases that are all under the control of a single instance of a database server. A PostgreSQL installation’s shared catalogue tables, configuration files, and data are all included in this cluster.

Initialization and Storage: A database storage region on disc, also known as a database cluster, needs to be initialised before PostgreSQL can be used.This is usually done with PostgreSQL’s initdb command. The file system directory /usr/local/pgsql/data, which is usually used for the database cluster, is necessary for Initdb to function. To ensure accurate file ownership and access permissions, run initdb as postgres, not root. To avoid inadvertently overwriting an existing installation, initdb will not execute if the provided data directory already exists and contains files.

Contents of a New Cluster: Postgres is a default database for utilities, users, and third-party apps. The default template for generating new user databases is template1, which immediately copies any objects or settings added to it. The template0 database is a clean, unmodifiable clone of template1 upon cluster initialisation. It can be used to create new databases with different encoding or locale settings or to restore template1 if it becomes corrupted.

Security and Management: Unauthorised access to the data directory, which contains all of the data, must be prevented. Everyone’s access permissions are automatically revoked by initdb, with the exception of the PostgreSQL user and, if desired, the group. In a newly initialised cluster, any local user may be able to login and even become a database superuser due to the default client authentication setup.Initdb’s password-related options (-W, –pwprompt, or –pwfile) should be used to set a password for the database superuser and replace the default trust with scram-sha-256 to boost security. Pg_hba.conf manages client authentication by allowing logins from which IP addresses and using which authentication method.

Each PostgreSQL cluster can share a physical server with a unique data directory and TCP port. For instance, multiple PostgreSQL server versions can run simultaneously.

Code Example:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT
);
INSERT INTO students (name) VALUES
('Alice'),
('Bob');
SELECT * FROM students;

Output:

CREATE TABLE
INSERT 0 2
 id | name  
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Template Databases

Within a PostgreSQL cluster, template databases are unique databases that are used as templates when building new databases. In essence, when a new database is created, it is a clone of a template database, taking on all of its settings and contents.

Template 1:

Default Template: The CREATE DATABASE command’s default database is template1. Template 1 is utilised when a new database is created if no template is specifically provided.

Modifiability: Template 1 is adaptable. All databases that are later generated from template1 will immediately inherit any objects (such extensions, tables, views, or functions) that are added to it. Installing the PL/Perl procedural language in template1, for instance, enables it to be used in all new user databases without the need for additional processes.

Maintenance: Template 1 can be dropped and regenerated from template 0 to return to its original condition if it gets corrupted or gathers undesired “junk.” Template 1’s datistemplate flag in pg_database needs to be set to false before it can be dropped.

Template0:

Pristine Copy: template0 is intended to stay an exact replica of template1 as it was just after the database cluster was initialised. Once initialised, it should never be altered.

Specific Use Cases: Template0 is very helpful in the following situations:

Restoring Backups: To prevent conflicts with site-local additions that may be present in template1, it is advised to restore a pg_dump backup into a pristine database that was cloned from template 0.

Encoding and Locale: Unlike template1, template0 is devoid of locale and encoding-specific information. This enables users to explicitly copy template0 to construct new databases with varied locale and encoding settings.

Protection: To stop any new connections and hence unintentional changes, the template0 database is usually designated with datallowconn = false in the pg_database catalogue table.

Custom Template Databases: Any database that already exists in a cluster can be identified as a template database in addition to the conventional templates. In the pg_database system catalogue, this is accomplished by setting its datistemplate flag to TRUE. PostgreSQL prevents a database that has been marked as a template from being changed or removed without first returning datistemplate to FALSE. The inability to connect to the database during the copy process is a major restriction when cloning from any database (except from template0).

User Databases

In a PostgreSQL cluster, user databases serve as the central containers where users define and administer their SQL objects. Each user database has named SQL objects such schemas, tables, views, functions, indexes, sequences, triggers, and data types.

Creation: Create user databases with the shell program createdb or the SQL command CREATE DATABASE. The command CREATE DATABASE test; creates a “test” database. The role running this procedure automatically owns the new database. Only superusers and teams with CREATEDB can create databases. A distinct user or role’s database can be created using the OWNER clause with establish DATABASE (e.g., CREATE DATABASE dbname OWNER rolename;), which normally requires superuser rights.

Access and Isolation: PostgreSQL design restricts client connections to one database per connection request. Unless dblink or FDW are installed, databases cannot directly exchange data. While related projects can live in the same database but use various schemas for modularity and fine-grained access control, different projects or users can be housed in separate databases for isolation with the physical separation and connection-level access control. CREATE DATABASE instructions require a database connection, thus many user databases use postgres, which was created at cluster initialisation.

Management: The pg_database system catalogue table can be queried (e.g., SELECT datname FROM pg_database) or the psql \l meta-command can be used to list cluster databases. SQL dropdb deletes user databases. Carefully examine this irrevocable step, which physically deletes all connected files. Running DROP DATABASE while connected to the dropped database is impossible.

Code Example:

CREATE SCHEMA sales_app;
CREATE SCHEMA hr_app;
CREATE TABLE sales_app.customers (
    id SERIAL PRIMARY KEY,
    customer_name TEXT
);
CREATE TABLE hr_app.staff (
    id SERIAL PRIMARY KEY,
    staff_name TEXT
);
INSERT INTO sales_app.customers (customer_name) VALUES ('Ravi'), ('Meena');
INSERT INTO hr_app.staff (staff_name) VALUES ('Anil'), ('Kavya');
SELECT * FROM sales_app.customers;
SELECT * FROM hr_app.staff;

Output:

CREATE SCHEMA
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
 id | customer_name 
----+---------------
  1 | Ravi
  2 | Meena
(2 rows)
 id | staff_name 
----+------------
  1 | Anil
  2 | Kavya
(2 rows)

In conclusion, PostgreSQL’s modular architecture database clusters, template databases, and user databases provides a reliable and adaptive data management, extensibility, and security environment.