PostgreSQL Tutorial

PostgreSQL Tutorial

Last updated on 13th Oct 2020, Blog, Tutorials

About author

Vinayk (Sr. PostgreSQL DBA )

He is a Award Winning Respective Industry Expert with 11+ Years Of Experience Also, He is a TOP Rated Technical Blog Writer Share's 1000+ Blogs for Freshers. Now He Share's this For Us.

(5.0) | 16456 Ratings 2062

PostgreSQL is a popular relational database management system (RDBMS). Our PostgreSQL tutorial provides basic and advanced concepts of PostgreSQL. In this tutorial, we are going to learn all the topics of PostgreSQL language, such as create a database, drop a database, select database, select table, update a record, create a table, delete record, drop table, triggers, functions, insert the record, procedures, cursors, etc.

We are also going to provide PostgreSQL interview questions and quizzes to help you better understand the PostgreSQL language. This tutorial will help us to understand all the primary features of PostgreSQL, also provides a knowledge on how to use PostgreSQL.

What is PostgreSQL?

PostgreSQL is an ORDBMS [Open-Source Object-Relational Database Management System]. It is used to store data securely; supporting best practices, and allow recovering them when the request is processed.

  • PostgreSQL is initially introduced on 8th July 1996 at the University of California.
  • It is the first DBMS, which perform MVCC [Multi-Version Concurrency Control] feature, even before Oracle. The multi-version concurrency control attribute is known as snapshot isolation in Oracle.
  • It is written in C programming language.
  • PostgreSQL is cross-platform and runs on various operating systems such as Microsoft Windows, UNIX, FreeBSD, Mac OS X, Solaris, HP-UX, LINUX, and so on.
  • The PostgreSQL is the existing database for the macOS server.
  • PostgreSQL is also pronounced as Post-gress-Q-L, which is developed by the PostgreSQL Global Development Group (a worldwide team of volunteers), any organization or other private entity does not control it.
  • PostgreSQL will offer us the facility to add custom functions with the help of various programming languages such as Java, C, and C++, etc.
  • In this, we can describe our functional languages, index types and data types, and we can also create a custom plugin to increase the reliability of our needs.
  • Its source code is accessible under PostgreSQL license; thus, we can use it freely, change and allocate PostgreSQL in any form.
  • The PostgreSQL follow the transaction along with the ACID (Atomicity, Consistency, Isolation, and Durability) properties.
  • The primary objective of PostgreSQL is to handle a variety of jobs from single technologies to web service or the data warehouse with several parallel users.

PostgreSQL Features

It is one of the most popular databases supporting JSON (non-relational) queries and SQL for (relational) queries. PostgreSQL is an object-relational database management system (ORDBMS). It contains the various advanced data types and robust feature sets, which increase the extensibility, reliability, and data integrity of the software.

In this section, we are going to discuss the most advanced features, Database administration tools of PostgreSQL, and benefits and drawbacks, which will help us to enhance our knowledge of PostgreSQL.

PostgreSQL includes multiple features that are designed to help the developers in developing the applications, manage our data in the datasets, and managers can keep the data integrity, and create the Risk-tolerant environments. Whenever the new release comes into the market, the PostgreSQL Global group of developers will enhance the previous features and adds some new features based on the user’s demand.

The essential features of PostgreSQL are as follows:

  • Free to download: It is open-source, and we can easily download it from the official website of PostgreSQL.
  • Compatible on several operation systems: PostgreSQL runs on all major operating systems such as Microsoft Windows, Linux, MacOS X, UNIX (AIX, BSD, HP-UX, SGI IRIX, Solaris, and Tru64), etc.
  • Compatible with various programming languages: It supports multiple programming interfaces such asC/C++, JAVA, Python, Perl, Ruby, Tcl, and ODBC (Open Database Connectivity).
  • 1. Compatible with Data Integrity: It supports data integrity which includes the following:
    • Primary Keys
    • UNIQUE, NOT NULL
    • Foreign Keys
    • Explicit Locks, Advisory Locks
    • Exclusion Constraints
  • 2. Support multiple features of SQL: PostgreSQL supports various features of SQL which include the followings:
    • MVCC (Multi-Version Concurrency Control).
    • It supports multiple Indexing such as Multicolumn, Partial, B-tree, and expressions.
    • SQL sub-selects.
    • Complex SQL queries.
    • Streaming Replication
    • It supports transactions, Nested Transactions through Savepoints.
    • Just-in-time compilation of expressions
    • Table partitioning
  • 3. Compatible with multiple data types: PostgreSQL support various data types such as:
    • Structured: Array, Date and Time, UUID (Universally Unique Identifier), Array, Range.
    • Primitives: String, Integer, Boolean, Numeric.
    • Customizations: Custom Types, Composite.
    • Geometry: Polygon, Circle, Line, Point,
    • Document: XML, JSON/JSONB, Key-value.
  • 4. Highly extensible: PostgreSQL is highly extensible in several phases which are as following:
    • It supports procedural Languages such as Perl, PL/PGSQL, and Python, etc.
    • JSON/SQL path expressions
    • Stored procedures and functions.
    • For tables, it supports a customizable storage interface.
    • It is compatible with foreign data wrappers, which connect to further databases with a standard SQL interface.
  • 5. Secure: It is safe because it follows several security aspects, which are as follows:
    • PostgreSQL provides a robust access control system.
    • It includes several Authentications such as Lightweight Directory Access Protocol(LDAP), Generic Security Service Application Program Interface (GSSAPI), SCRAM-SHA-256, Security Support Provider Interface (SSPI), Certificate, and so on.
    • PostgreSQL supports Column and row-level security.
  • 6. Highly Reliable: It is highly reliable and also provide disaster recovery such as:
    • Active standbys, PITR (Point in time recovery)
    • It supports WAL (Write-ahead Logging)
    • Tablespaces
    • It supports different types of Replication like Synchronous, Asynchronous, and Logical.
  • 7. PostgreSQL supports Internationalization, which means that the international character sets include ICU collations, accent- insensitive and case-sensitive collations, and full-text searches.
  • 8. In PostgreSQL, a table can be set to inherit their characteristics from a “parent” table.
  • 9. It is compatible with ANSI-SQL2008.
  • 10. PostgreSQL will help us to improve the functionality of Server-Side programming.
  • 11. We can install several extensions to add additional functionality to PostgreSQL.
Subscribe For Free Demo
[contact-form-7 404 "Not Found"]

PostgreSQL Database Administration

The PostgreSQL database administration covers the essential PostgreSQL database server administration actions. We have several open-sources as well as paid tools available in the market. Let us understand some of the most commonly used database management tools of PostgreSQL:

psql

It is a terminal-based front-end command-line tool; where we can directly enter the SQL queries or run them from a file and we can also see the query results. It offers us many Meta commands and several shells, such as automate extensive ranges of tasks and facilitate writing the scripts.

phpPgAdmin

It is a web-based management tool for PostgreSQL and built on the phpMyAdmin interface, which was initially written for MySQL administration. The phpPgAdmin is written in PHP programming language. It can be configured easily and provides simple data manipulation. It keeps up the slony master-slave replication machine and also available in 27 languages. Here, we can import the SQL scripts and Copy the data.

pgAdmin

It is one of the most famous and open-source management and development platforms for PostgreSQL. PgAdmin is the most advanced database around the world. It can be used on various operating systems such as UNIX, Linux, Mac OS X, and Windows to achieve PostgreSQL 9.2 and above versions.

PgFouine

It is a well-known query log analyzer for PostgreSQL, which is used to generate reports from the log files of PostgreSQL. It is accessible for RHEL (Red Hat Enterprise Linux) and CentOS Linux system through the EPEL (Extra Packages for Enterprise Linux) repositories. It is a PHP script, and it provides a moderate list of libraries. The pgFouine contains the older UNIX systems and the Windows as well.

pgDevOps

It is a suite of web tools that is installed and manages multiple PostgreSQL extensions and versions; it creates SQL queries and community components, monitors running databases, and identifies performance issues. It’s a WSGI Python27 Flask application which can be executed on different cross-platform such as Windows, Linux, and OSX. It is an open-source application that can be used by various users on multiple servers.

Some of the commonly used Proprietary tools are as follows:

  1. 1. Lightning Admin for PostgreSQL
  2. 2. Borland Kylix
  3. 3. DBOne
  4. 4. DBTools Manager PgManager
  5. 5. Rekall
  6. 6. Data Architect,
  7. 7. SyBase Power Designer
  8. 8. Microsoft Access
  9. 9. eRWin
  10. 10. DeZign for Databases
  11. 11. PGExplorer
  12. 12. Case Studio 2
  13. 13. pgEdit
  14. 14. RazorSQL
  15. 15. MicroOLAP Database Designer
  16. 16. Aqua Data Studio
  17. 17. EMS Database Management Tools for PostgreSQL
  18. 18. Navicat
  19. 19. SQL Maestro Group products for PostgreSQL
  20. 20. Datanamic DataDiff for PostgreSQL
  21. 21. Datanamic SchemaDiff for PostgreSQL
  22. 22. DB MultiRun PostgreSQL Edition
  23. 23. SQLPro
  24. 24. SQL Image Viewer
  25. 25. SQL Data Sets etc.

Advantage and disadvantage of PostgreSQL

The benefits of the PostgreSQL are as follows:

  • PostgreSQL is easy to use; that why we do not require much training.
  • It requires low maintenance management for enterprise as well as embedded usage.
  • PostgreSQL manages data in a relational database as it is very powerful and robust.
  • We can quickly get the source code of PostgreSQL as it is freely available in an open-source license, and we can immediately implement, change according to our requirements.
  • It can execute dynamic web-application and website as the LAMP stack option.
  • PostgreSQL is a highly risk-tolerant database.

The drawbacks of PostgreSQL are as follows:

  • PostgreSQL does not support the various open-source applications as compared to MySQL.
  • In this, creating replication is a bit complex.
  • It is not maintained by one company.
  • PostgreSQL speed performance is not as good as compare to further tools.
  • It is a bit slow as compared to MySQL.
  • Sometimes, the installation process is not easy for the learner.

Architecture of PostgreSQL

The physical structure of PostgreSQL is very simple, it consists of the following components:

  • Shared Memory
  • Background processes
  • Data directory structure / Data files

The below figure shows the PostgreSQL Architecture.

Post-Image

1. Shared Memory

Shared memory refers to the memory reserved for transactional and another log catches. Shared memory consist of the following components

Shared Buffers

  • We need to set some amount of memory to a database server for uses of shared buffers. The default value of shared buffers in 9.2 and the older version is 32 megabytes (32 MB) from 9.3 and the later default value of shared buffers is 128 megabytes (128 MB).
  • If we have a dedicated server for PostgreSQL, reasonable starting to set shared buffers value is 25% of total memory. The purpose of shared buffers is to minimize server DISK IO.

WAL Buffers

  • WAL buffers temporarily store changes in the database, which changes in the WAL buffers are written to the WAL file at a predetermined time. At the time of backup and recovery, WAL buffers and WAL files are very important to recover the data at some peak of time.
  • The minimum value of shared buffers is 32 KB. If we set this parameter as wal_buffers = -1 it will set based on shared_buffers.

Work Memory

  • Set the specific memory to per client connections to be used by internal sort of operations and hash tables to writing data into temporary disk files.
  • The default value of work memory in 9.3 and the older version is 1 megabyte (1 MB) from 9.4 and later default value of work memory is 4 megabytes (4 MB).

Maintenance Work Memory

  • We need to specify the maximum amount of memory for database maintenance operations such as VACUUM, ANALYZE, ALTER TABLE, CREATE INDEX, and ADD FOREIGN KEY, etc.
  • The default value of maintenance work memory in 9.3 and the older version is 16 megabytes (16 MB) from 9.4 and later default value of maintenance work memory is 64 megabytes (64 MB).
  • It is safe to set maintenance work memory is large as compared to work memory. Larger settings will improve the performance of maintenance (VACUUM, ANALYZE, ALTER TABLE, CREATE INDEX, and ADD FOREIGN KEY, etc.) operations.

2. Background Processes

Below are the background processes of PostgreSQL. Each process has its individual features and PostgreSQL internals. Each process details will be described as follows:

Background Writer process: In PostgreSQL 9.1 version background writer regularly does the checkpoint processing. But in PostgreSQL 9.2 version checkpointer process was separated from the background writer process. It will keep logs and backup information up to date.

WAL Writer: This process writes and flushes periodically the WAL data on the WAL buffer to persistent storage.

Logging Collector: This process also called a logger. It will write a WAL buffer to WAL file.

Autovacuum Launcher: When autovacuum is enabled, this process has the responsibility of the autovacuum daemon to carry vacuum operations on bloated tables. This process relies on the stats collector process for perfect table analysis.

Archiver: If we enable archive mode, this process has the responsibility to copy the WAL log files to a specified directory.

Stats Collector: In this process, Statistics information like pg_stat_activity and for pg_stat_database is collected. Information is passed from the stats collector via temporary files to requesting processes.

Checkpointer Process: In PostgreSQL 9.2 and later versions checkpoint process is performed. The actual work of this process is when a checkpoint occurs it will write dirty buffer into a file.

Checkpointer: Checkpointer will write all dirty pages from memory to disk and clean shared buffers area. If PostgreSQL database is crashed, we can measure data loss between last checkpoint time and PostgreSQL stopped time. The checkpoint command forces an immediate checkpoint when the command is executed manually. Only database superuser can call checkpoint.

Course Curriculum

Best In-Depth Practical Oriented QA Training By Expert Trainers

  • Instructor-led Sessions
  • Real-life Case Studies
  • Assignments
Explore Curriculum

The checkpoint will occur in the following scenarios:

  1. 1. The pages are dirty.
  2. 2. Starting and restarting the DB server (pg_ctl STOP | RESTART).
  3. 3. Issue of the commit.
  4. 4. Starting the database backup (pg_start_backup).
  5. 5. Stopping the database backup (pg_stop_backup).
  6. 6. Creation of the database.

3. Data Files / Data Directory Structure

  • PostgreSQL consist of multiple databases this is called a database cluster. When we initialize PostgreSQL database template0, template1 and Postgres databases are created.
  • Template0 and template1 are template databases for new database creation of user it contains the system catalog tables.
  • The user database will be created by cloning the template1 database.

PGDATA directory contains several subdirectories and control files are as follows.

  • pg_version: It contains database version information.
  • base: Containing database subdirectories.
  • global: Containing cluster wise tables such as pg_user.
  • pg_clog: Containing transaction commits status data.
  • pg_multixact: Containing multi transaction status data (used for shared row locks).
  • pg_notify: Containing LISTEN/NOTIFY status data.
  • pg_serial: Containing information about committed serializable transactions.
  • pg_snapshots: Containing exported snapshots.
  • pg_stat_tmp: Containing temporary files for the statistics subsystem.
  • pg_subtrans: Containing sub-transaction status data.
  • pg_tblspc: Containing symbolic links to tablespaces.
  • pg_twophase: Containing state files for prepared transactions.
  • pg_xlog: Containing WAL (Write Ahead Log) files.
  • pid: This file containing the current postmaster process ID (PID).

Installing PostgreSQL on Linux/Unix

Follow the given steps to install PostgreSQL on your Linux machine. Make sure you are logged in as root before you proceed for the installation.

  • Pick the version number of PostgreSQL you want and, as exactly as possible, the platform you want from EnterpriseDB
  • I downloaded postgresql-9.2.4-1-linux-x64.run for my 64 bit CentOS-6 machine. Now, let us execute it as follows −

[root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run

[root@host]# ./postgresql-9.2.4-1-linux-x64.run

————————————————————————

Welcome to the PostgreSQL Setup Wizard.

————————————————————————

Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.2]:

  • Once you launch the installer, it asks you a few basic questions like location of the installation, password of the user who will use database, port number, etc. So keep all of them at their default values except password, which you can provide password as per your choice. It will install PostgreSQL at your Linux machine and will display the following message −

Please wait while Setup installs PostgreSQL on your computer.

 Installing

 0% ______________ 50% ______________ 100%

 #########################################

———————————————————————–

Setup has finished installing PostgreSQL on your computer.

Follow the following post-installation steps to create your database −

[root@host]# su – postgres

Password:

bash-4.1$ createdb testdb

bash-4.1$ psql testdb

psql (8.4.13, server 9.2.4)

test=#

You can start/restart postgres server in case it is not running using the following command −

[root@host]# service postgresql restart

Stopping postgresql service:                               [  OK  ]

Starting postgresql service:                               [  OK  ]

If your installation was correct, you will have PotsgreSQL prompt test=# as shown above.

Installing PostgreSQL on Windows

Follow the given steps to install PostgreSQL on your Windows machine. Make sure you have turned Third Party Antivirus off while installing.

  • Pick the version number of PostgreSQL you want and, as exactly as possible, the platform you want from EnterpriseDB
  • I downloaded postgresql-9.2.4-1-windows.exe for my Windows PC running in 32bit mode, so let us run postgresql-9.2.4-1-windows.exe as administrator to install PostgreSQL. Select the location where you want to install it. By default, it is installed within Program Files folder.
Postgresql-Install-Dir
  • The next step of the installation process would be to select the directory where your data would be stored. By default, it is stored under the “data” directory.
Postgresql-Install-Data-Dir
  • Next, the setup asks for password, so you can use your favorite password.
Postgresql-Install-Password
  • The next step; keep the port as default.
Postgresql-Install-Port
  • In the next step, when asked for “Locale”, I selected “English, United States”.
  • It takes a while to install PostgreSQL on your system. On completion of the installation process, you will get the following screen. Uncheck the checkbox and click the Finish button.
Postgresql-Install-Finish

After the installation process is completed, you can access pgAdmin III, StackBuilder and PostgreSQL shell from your Program Menu under PostgreSQL 9.2.

Course Curriculum

Get PostgreSQL Training for Beginners from Industry Experts

Weekday / Weekend BatchesSee Batch Details

Installing PostgreSQL on Mac

Follow the given steps to install PostgreSQL on your Mac machine. Make sure you are logged in as administrator before you proceed for the installation.

  • Pick the latest version number of PostgreSQL for Mac OS available at EnterpriseDB
  • I downloaded postgresql-9.2.4-1-osx.dmg for my Mac OS running with OS X version 10.8.3. Now, let us open the dmg image in finder and just double click it which will give you PostgreSQL installer in the following window −
Postgresql-Install
  • Next, click the postgres-9.2.4-1-osx icon, which will give a warning message. Accept the warning and proceed for further installation. It will ask for the administrator password as seen in the following window −
Postgresql-Password

Enter the password, proceed for the installation, and after this step, restart your Mac machine. If you do not see the following window, start your installation once again.

Postgresql-Start-Installation
  • Once you launch the installer, it asks you a few basic questions like location of the installation, password of the user who will use database, port number etc. Therefore, keep all of them at their default values except the password, which you can provide as per your choice. It will install PostgreSQL in your Mac machine in the Application folder which you can check −
Postgresql-Application-On-Mac
  • Now, you can launch any of the program to start with. Let us start with SQL Shell. When you launch SQL Shell, just use all the default values it displays except, enter your password, which you had selected at the time of installation. If everything goes fine, then you will be inside postgres database and a postgress# prompt will be displayed as shown below −
Postgresql-SQL-Shell-Mac

Congratulations!!! Now you have your environment ready to start with PostgreSQL database programming.

Create Database using pgAdmin

When you install PostgreSQL, pgAdmin is installed. Start pgAdmin from your start menu.

PostgreSQL-PgAdmin

Right click on the Databases, then Create, Database.

PostgreSQL-Create-Database

A new window ‘Create – Database‘ appears as shown below.

PostgreSQL-Create-Database

Provide the database name against Database field. You can select the owner for this database. Also provide a comment.

This window has other tabs, where you can customize your Database while its creation.

Or you can click on Save button to create the database.

Postgresql Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

Create – Database: Definition

You can provide the details like Encoding, Template, Tablespace, Collation, Character type, and Connection limit to the database.

Database-Definition

Create – Database: Security

You can provide privileges to users and also add Security labels. Using Security labels, you can manage the security of all the databases.

Crete-Database

Database:Parameters

Database-Parameter

Create – Database: SQL

You can also provide your custom SQL code to create Database, using SQL Tab.

Create-Database

Save Database

Once you click on the Save button, a new PostgreSQL database is created as shown below.

PostgreSQL-New-Database-Created

Are you looking training with Right Jobs?

Contact Us

Popular Courses