CENG301 Database Management Systems

Session-3
Asst. Prof. Mustafa YENIAD
myeniad@gmail.com
 Brief info about PostgreSQL
PostgreSQL (https://www.postgresql.org) is
• an advanced, highly effective in enterprise-class, and open-source relational database system.
• is a powerful system that allows companies to safely store and scale all their data workloads.

• Supports both SQL (relational) and JSON (non-relational) querying.

• Highly stable and backed by more than 30 years of development by the open-source community.
• The entire source code for PostgreSQL is available for anyone to look, edit, customize, and use
DBMS

according to their choice for free.

PostgreSQL has a great reputation for:

• Reliability
• Superior Performance
• Data Integrity
• Data Correctness
• Robust set of features
• Extensibility
• Community Support
 Brief info about PostgreSQL
PostgreSQL is used by many industries:
DBMS
 Brief info about PostgreSQL
• Some of the most sophisticated features that make PostgreSQL an enterprise-class database
management system are:
• User-defined data types
• Table Inheritance
• Sophisticated Locking Mechanism
• Views and Regulations
• Sub-Queries
DBMS

• Nested Transactions
• Multi-Version Concurrency (MVCC)
• Hot Standby

• With every new release, more features are added to the PostgreSQL DBMS
by a huge community of developers who are passionate about making
PostgreSQL a great tool!
• The list of features of PostgreSQL is abundant.
• You can always go to their official website (https://www.postgresql.org/)
to find out the list of features available based on groups and versions.
 Why Choose PostgreSQL?
• PostgreSQL is ACID Compliant
• ACID is a concept in Computer Science that stands for
• Atomicity
• Consistency
• Isolation
• Durability
• Each of these terms has a special meaning and
DBMS

significance when it comes to databases.

• They represent the key guarantees that must be
supported by any database transaction to avoid
validity errors and to maintain data integrity.
 Why Choose PostgreSQL?
• Here's what ACID means:
• Atomicity: Either the transaction will occur completely or not at all. If a transaction is aborted
during its execution, all the operations performed up to that point must be completely reverted.

• Consistency: All the constraints that are imposed on data must be implemented before allowing
data to be written. If a transaction occurs that doesn't follow the "rules" of the database, the
transaction must be reversed to achieve a consistent state.
DBMS

• Isolation: Every transaction must occur at a point in time, regardless of any other transaction
happening on the database. For example, one user might be reading the database while someone else
is writing to the database, and both of them won't see the change.

• Durability: As long as the logs of the transactions are preserved, the database should be able to be
restored to the original state, even if there is a system crash.

• Since PostgreSQL relies on a single database engine, all the transactions that occur on PostgreSQL are ACID-
compliant. According to PostgreSQL, they have been ACID-compliant ever since the year 2001. This means
that any operation that you perform on PostgreSQL is safe and secure.
 Important Term: Transaction
• A transaction is an action or series of actions that are being performed by a single user or application
program, which reads or updates the contents of the database.

• A transaction can be defined as a logical unit of work on the database. This may be an entire program, a
piece of a program, or a single command (like the SQL commands such as INSERT or UPDATE), and it may
engage in any number of operations on the database.

• According to the Database Fundamentals book (Sharma, et al., 2010, p. 162), a transaction or unit of work is
DBMS

a set of database operations all of which should be executed successfully in order to call the transaction
successful.

• For example, if a bank has to transfer 1,000 dollars from account A to account B, then the following steps are
required before the transfer is successful:
1. Reduce the balance of account A by the amount of 1,000
2. Increase the balance of account B by the amount of 1,000

• The main operation of a transaction are read and write.

 Important Term: Transaction
• Incomplete steps result in the failure of the transaction:
• A database transaction, by definition, must be atomic, consistent, isolated, and durable.
• These are popularly known as ACID properties. These properties can ensure the concurrent
execution of multiple transactions without conflict.
• To explain the concept of a database transaction, let us use a typical example of transferring money between Account A
and Account B. Let’s say you want to move 5 dollars from Account A to Account B. This action can be broken down into
the following simple operations:
DBMS

1. Create a record to transfer 5 dollars from Account A to Account B.

This is typically called the begin of a database transaction.
2. Read the balance from Account A.
3. Subtract 5 dollars from the balance of Account A.
4. Read the balance from Account B.
5. Add 5 dollars credit to Account B.

• Now, if your database is running this transaction as one whole atomic unit, and the system fails due to a power outage,
the transaction can be undone, reverting your database to its original state. Typically, a term like rollback refers to the
process that undoes any changes made by the transaction, and the term commit is used to refer to a permanent change
made by the transaction.
 Preparing the environment: Install PostgreSQL - Step-1
• PostgreSQL is available on many Linux platforms by default.
• Since this can often mean a different version than preferred, the PostgreSQL project provides a
repository of packages of all supported versions for the most common distributions.
• PostgreSQL is available from default AppStream software repository, and there are multiple
versions which you can install. You can choose between these versions by enabling the
appropriate collection of packages and dependencies that align with the version you want to
install, with each collection referred to as a module stream.
DBMS

• List out the available streams for the postgresql module using the dnf command:
$ dnf module list postgresql

• To install PostgreSQL version 15, you must enable that version’s module stream:
$ sudo dnf module enable postgresql:15

• After enabling the version 15 module stream, you can install the postgresql-server package:
$ sudo dnf install postgresql-server

• Then, you will perform some initialization steps to prepare a new database cluster for PostgreSQL.
 Preparing the environment: Install PostgreSQL - Step-2
• You have to create a new PostgreSQL database cluster before you can start creating tables and
loading them with data.
• A database cluster is a collection of databases that are managed by a single server instance.
Creating a database cluster consists of creating the directories in which the database data will be
placed, generating the shared catalog tables, and creating the template1 and postgres databases.
• The template1 database is a template of sorts used to create new databases; everything that is
stored in template1, even objects you add yourself, will be placed in new databases when they’re
DBMS

created.
• The postgres database is a default database designed for use by users, utilities, and third-party
applications. The Postgres package we installed in the previous step comes with a handy script
called postgresql-setup which helps with low-level database cluster administration.
• To create a database cluster:
$ sudo postgresql-setup --initdb
• Now start the PostgreSQL service using systemctl:
$ sudo systemctl start postgresql
• Then, use systemctl once more to enable the service to start up whenever the server boots:
$ sudo systemctl enable postgresql
 Preparing the environment: Install PostgreSQL - Step-3
• Then you should use PostgreSQL Roles and Databases:
• PostgreSQL uses a concept called roles to handle client authentication and authorization.
These are in some ways similar to regular Unix-style accounts.
• Upon installation, Postgres is set up to use ident authentication, meaning that it associates
Postgres roles with a matching Unix/Linux system account.
• If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as
that role.
DBMS

• The installation procedure created a user account called postgres that is associated with the
default postgres role. In order to use PostgreSQL, you can log in to that account.

• Switch over to the postgres account on your server by typing:

$ sudo -i -u postgres
• You can now access a postgres prompt immediately by typing:
postgres@[hostname]:~ $ psql
• This will log you into the PostgreSQL prompt, and from here you are free to interact with the
database management system right away.
postgres=# SELECT version(); # check the PostgreSQL version
 Preparing the environment: Install PostgreSQL - Step-3
• Once logged in, you can check your current connection information by typing:
postgres=# \conninfo
• To get databases list:
postgres=# \l
• PostgreSQL uses the pg_hba.conf configuration file that is stored in the database data directory to
control the client authentication. "hba" means host-based authentication.
• hba_file parameter specifies the configuration file for host-based authentication (customarily
DBMS

called pg_hba.conf).
• To get path of pg_hba.conf file:
postgres=# show hba_file;

• The main configuration file is generally located in this path: /var/lib/pgsql/data/pg_hba.conf

• To quit from Postgres prompt:
postgres=# \q

• Then go back to your bash prompt:

postgres@[hostname]:~ $ logout
 PostgreSQL GUI Tool: pgAdmin
• pgAdmin is the most used Open-source management tool for Postgres.
• pgAdmin is designed to meet the needs of both novice and experienced Postgres users alike,
providing a powerful graphical interface that simplifies the creation, maintenance and use of
database objects.
• To install: https://www.pgadmin.org/download/pgadmin-4-rpm/
DBMS