+91 (22) 2687 9402    training@unotechsoft.com

PostgreSQL Administration and Development

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

Duration : 40 hrs
Course Fee: 18,000

Curriculum

Installation and Configuration

  • Setting up basic server level and db level parameters
    • Setting up kernel parameters for PostgreSQL database server
    • Pre-checks to be performed on the server
    • Allocating memory and disk space to PostgreSQL
    • Configuring basic postgresql.conf parameters
    • Creating PostgreSQL Cluster

 

  • PostgreSQL Architecture
    • PostgreSQL Memory Structures
    • PostgreSQL Processes
    • Understanding PostgreSQL blocks
    • PostgreSQL Physical structure (datafiles, WAL files, archive files and other files
      in the data directory)
    • PostgreSQL behavior on transactions+

 

  • PostgreSQL Installation
    • Installation of PG-9.3.X from source
    • Production installation procedure (on Unix environment)
    • Precautions to be taken on production
    • Various installation options available

 

  • PostgreSQL Storage (logical and physical)
    • Understanding PostgreSQL storage system (blocks, pages and files)
    • Finding out objects and database size
    • Increasing storage when required
    • Reclaiming the space

 

  • PostgreSQL DB Objects and Data Types
    • Types of objects in PostgreSQL and understand their usage
      (like Tables, Indexes etc)
    • Data types available in PostgreSQL and their usage

 

  • PostgreSQL Security and Authentication
    • Authenticating remote clients
    • Understanding importance of pg_hba.conf and pgpass file
    • Understanding the importance of user and schema
    • Understanding PostgreSQL Roles and Privileges
    • Understanding importance of pg_ident.conf file

 

 Backup, HA and DR Planning

  • Back up and Recovery
    • Logical Backup
    • How to logically backup the data (Table level, schema level and DB level)
    • Pg_dump and Pg_restore
    • How to improve backup and restore performance

 

  •  Physical Back up
    • How to perform cold backup of data directory and number of ways
    • How to perform Online Hot Backup of the data directory and number of ways
    • Perform Backup strategy for production server
    • Backup strategy for WAL Archiving

 

Database Maintenance

  • Vacuuming
    • Understanding and managing PostgreSQL Auto-vacuuming with VERBOSE option
    • Configuring and tuning auto-vacuuming parameters
    • How to perform manual vacuuming
    • How to choose objects to be vacuumed
    • Tuning Transaction wrap around problem
    • Improving VACUUM performance
    • VACUUM FULL
    • CLUSTERING TABLES
    • RE-INDEXING: Choosing which Indexes are to be re-indexed

 

High Availability

  •  Table level replication
    •  Understanding Slony replication
    •  How to setup Slony replication on production
    • How and when to recommend Slony replication for production applications
    • Advantageous and dis-advantageous

 

  •  Replication
    • Identify the need for streaming replication
    • Setting up streaming replication
    • How to optimize the streaming replication behavior on critical production systems
    • Monitoring of the streaming replication

 

Troubleshooting

  • Managing PostgreSQL Locks
    • Understanding Row level and Table level locking
    • Impact of prepared statements

 

  • Performance Tuning of PostgreSQL Database server
    • Understanding importance of ANALYZE and impact of statistics on optimizer
    • Understanding PostgreSQL Optimizer (Explain and Explain analyze)
    • Database, Cluster and Server performance
    • Application Tuning
    • Memory tuning
    • PostgreSQL parameter tuning
    • Kernel parameter tuning
    • IO tuning
    • CPU tuning
    • Monitoring
    • Monitoring database level activities using catalog views
    • PG Catalog views and their usage
    • How to install contrib modules for monitoring and capacity planning
    • Tools
    • Various PostgreSQL tools available for monitoring, maintenance and performance tuning

 

Application Development

  • C++ Driver
    • Introduction
    • API Introduction
    • Querying
    • QueryBuilder and Dynamic Queries
    • Asynchronous Querying
    • Driver Policies

 

  • ODBC Driver
    • Introduction
    • API Introduction
    • Querying
    • QueryBuilder and Dynamic Queries
    • Asynchronous Querying
    • Driver Policies

Write us to know more about the course

Write us to know more about the course

  • +91 (22) 2687 9402
  • training@unotechsoft.com

Student Speaks