PostgreSQL is a powerful, open source, object-relational database system known for being reliable and precise. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server, and is also available for Linux, FreeBSD, OpenBSD, and Windows. Several organizations have adopted Postgre SQL for managing their IT systems. The objective of this five day advanced level training is to provide inputs to systems administrators and related technical manpower in organizations to manage the Postgre SQL server in a professional manner.
Rajeev Jha possesses 15+ years of experience in the IT industry with exclusive experience in Oracle 8i/9i/10g/11g/12c/18c//PL/SQL, SQL Server 2000/2005/2008/2012,2014,2016 & 2017, MySQL , MariaDB & PostgreSQL. He has conducted corporate training in the above areas for several leading organizations like Citibank, Bank of America, Capgemini, Tata Consultancy Services, Wipro, Mindtree, BSNL, etc.
He has conducted training programs for both experienced DBAs & fresh DBAs recruited by various companies as well as to faculty members to develop capabilities to conduct the training for Oracle DBA Topics (18c, 12c, 11g, 10g & 9i) along with RAC & PL/SQL. He has been conducting training for SQL Server 2000 /2005 /2008/ 2012/ 2016/2017 batches primarily focusing on Database Implementation, Maintenance, Database Administrator / the TSQL aspects like creating and managing databases, joins and Sub queries, functions and stored procedures, triggers, cursors, etc. He conducts MySQL Database Administration batches primarily focusing on Installation, Configuration, Partitioning, Maintenance, Export / Import, Backup & Recovery & many more topics as per customer's requirements.
Rajeev Jha holds professional certifications in Oracle Certified Expert (OCE),Oracle Certified Professional (OCP), Microsoft Certified Technology Specialist(MCTS), Microsoft Certified IT Professional (MCITP), Microsoft certified Database Administrator 2008 and Microsoft Certified Trainer (MCT).
Part 1: Introduction
➢ History of PostgreSQL
➢ Major Features
➢ New Features of PostgreSQL
➢ Multi Version Concurrency Control
➢ Write-Ahead Logging
➢ Architectural Overview
➢ Limits
Part 2: PostgreSQL System Architecture
➢ Architectural Summary
➢ Shared Memory
➢ Statement Processing
➢ Utility Processes
➢ Disk Read Buffering
➢ Write Buffering
➢ Background Writer Cleaning Scan
➢ Commit & Checkpoint
➢ Physical Database Architecture
➢ Data Directory Layout
➢ Installation Directory Layout
➢ Page Layout
Part 3: Installation
➢ OS User & Permissions
➢ Installation
➢ Setting environment variables
➢ Clusters
➢ Creating a database cluster
➢ Starting and Stopping the Server
➢ (pg_ctl)
➢ Connect to the server using psql
Part 4: Database Clusters
➢ Database Clusters
➢ Creating a Database Cluster
➢ initdb Utility
➢ Starting a Database Cluster
➢ Connecting to a Database Cluster
➢ Reload a Database Cluster
➢ Stopping a Database Cluster
➢ View Cluster Control Information
➢ Demo - Database Cluster
Part 5: Configuration
➢ Setting PostgreSQL Parameters
➢ Access Control
➢ Connection Settings
➢ Security and Authentication
➢ Settings
➢ Memory Settings
➢ Query Planner Settings
➢ WAL Settings
➢ Log Management
➢ Background Writer Settings
➢ Statement Behavior
➢ Vacuum Cost Settings
➢ Autovacuum Settings
Part 6: Creating and Managing Databases
➢ Object Hierarchy
➢ Creating Databases
➢ Creating Schemas
➢ Schema Search Path
➢ Roles, Users & Groups
➢ Access Control
Part 7: Postgres Data Dictionary
➢ The System Catalog Schema
➢ System Information views/tables
➢ System Information Functions
Part 8: Introduction to PSQL
➢ Introduction
➢ Conventions
➢ Connecting to PostgreSQL
➢ PSQL Command Line Parameters
➢ Entering PSQL Commands
➢ PSQL Meta-Commands
➢ PSQL SET Parameters
➢ Information Commands
Part 9: pgAdmin IV
➢ Registering a server
➢ Viewing and Editing Data
➢ Query Tool
➢ Databases
➢ Languages
➢ Schemas
➢ Domains
➢ Functions
➢ Sequences
➢ Tables
➢ Columns
➢ Constraints
➢ Indexes
➢ Maintenance
➢ Rules
➢ Triggers
➢ Types
➢ Views
➢ Table spaces
➢ Roles
Part 10: Security
➢ Authentication
➢ Authorization
➢ Levels of security
➢ pg_hba.conf file
➢ Users
➢ Object ownership
➢ Access control
➢ Application access parameters
Part 11: SQL
➢ Data Types
➢ Tables
➢ SQL Queries
➢ Quoting
➢ Using SQL Functions
➢ Constraints
➢ Dropping or Removing Database
➢ Objects
➢ Views
➢ Sequences
➢ Indexes
Part 12: Backup and Recovery & Point-in Time Recovery
➢ Backup Types
➢ SQL Dump
➢ Cluster Dump
➢ Offline Copy Backup
➢ Continuous Archiving
➢ pg_basebackup
➢ Point-In Time Recovery
Part 13: Routine Maintenance
➢ Explain and Explain Analyze
➢ Table Statistics
➢ Updating Planner Statistics
➢ Vacuuming
➢ Scheduling Auto Vacuum
➢ Preventing Transaction ID Wraparound
Failures
➢ The Visibility Map
➢ Routine Reindexing
Part 14: Moving Data
➢ The COPY Command
➢ Examples
Part 15: Transactions and
Concurrency
➢ Transaction Definition
➢ Effects of Concurrency on
➢ Transactions
➢ Transaction Isolation Levels
➢ Multi-Version Concurrency
Control
➢ Overview (MVCC)
➢ MVCC Example
➢ Internal Identifiers
➢ Transaction Wraparound
➢ MVCC Maintenance
➢ MVCC Demo
Part 16: Replication overview
➢ automatic failover techniques
Part 17: Performance Tuning
➢ Hardware Configuration
➢ OS Configuration
➢ Server Parameter Tuning
➢ Connection Settings
➢ Memory Parameters
➢ Memory settings for Planner
➢ WAL Parameters
➢ Statistics Collection
➢ Labs
Part 18: Monitoring
➢ Using Pgbench
➢ Using pgbouncer
➢ Using pgpool, pglogical.
Part 17: Test
Participants from government organizations, educational institutions, universities, MSMEs, startups. Participants from MSMEs and startups need to produce relevant certificates from concerned authorities indicating that the organization falls under the MSME/startup category. Rs. 32,450 ( Rs 27,500 + 18% G.S.T )
Participants from PSUs and private organizations: Rs. 35,400 ( 30,000 + 18% GST )
Bank/Electronic Fund Transfer to IIITM-K Account: Wire Transfer to
A/c No. 2762201000303 (Bank Name: Canara Bank – Kazhakuttom Branch, Branch Code: 2762,
NEFT/IFSC Code: CNRB0002762,
MICR Code: 695015016,
City Name: Thiruvananthapuram,
Type of Account: Current A/c).
Please transfer the specified amount and fill the registration form for confirmation.
OR
DD/ cheque drawn in favour of “Director, IIITM-K” payable at Thiruvananthapuram and fill the registration form, send scanned copy of DD by e-mail to mail.kcsepm@iiitmk.ac.in for confirmation.
Over the last few years, India has witnessed a significant growth in its communication and information technology industry in general and the software development industry in particular. The software industry has been identified as one of the main contributors to the Indian economy in the coming decades. With several IT projects being initiated, there is a need for developing skilled manpower in software engineering and software project management in order to implement projects successfully both in the government and the private industries. There is also need to adopt state of the art emerging processes, practices and technologies in the area. The proposed Centre of Excellence in Software Engineering and Software Project Management is expected to play an important role in education and research in this area so that quality manpower developed for the industries by training fresh graduates, working professionals and by providing technical consultancy services The Centre is expected to play a leading role in assisting government departments, e-governance initiatives and software industries in Kerala to enhance the quality of software delivered and in implementing IT projects successfully.
INDIAN INSTITUTE OF INFORMATION TECHNOLOGY AND MANAGEMENT – KERALA
(Established by Govt. of Kerala)
IIITMK Building, Technopark Campus
Karyavattom P.O., Thiruvananthapuram – 695 581, Kerala
Tel: 91-471 - 2527567, 2700777: Fax: 2527568
Mr. Umasankar uma.sankar@iiitmk.ac.in +91 - 7907469704 +91 - 471 278 4116 |
Indian Institute of Information Technology and Management-Kerala (IIITM-K) is an autonomous institute set up by Government of Kerala to promote higher education and research in the field of Information Technology and allied areas. IIITM-K has an enviable academic ambience. In addition to well-qualified faculty, IIITM-K utilizes the service of IT Professionals and Scientists from reputed organizations..IIITM-K encourages collaboration with leading Universities and Research Organizations within the country and abroad. The computational model and data science lab of IIITM-K focuses on mathematical and statistical applications for different branches of science technology and even in health care management. We focus on the development of novel anticancer, antimicrobian, antiasthmatic drugs using traditional knowledge of Kerala such as ayurveda, natural medicine etc.
