Duration: Hours

PostgreSQL is commonly known as Postgres and it is also open source database. PostgreSQL is a powerful, open source object-relational database system. A database administrator’s (DBA) primary job is to ensure that data is available, protected from loss and corruption, and easily accessible as needed.

Training Mode: Online

Description

Introduction

1. History of PostgreSQL
2. Major Features
3. New Features of PostgreSQL 9.2
4. Multi Version Concurrency Control
5. Write-Ahead Logging
6. Architectural Overview
7. Limits

PostgreSQL System Architecture

1. Architectural Summary
2. Shared Memory
3. Inter-process Communications
4. Statement Processing
5. Utility Processes
6. Disk Read Buffering
7. Write Buffering
8. Background Writer Cleaning Scan
9. Commit & Checkpoint
10. Physical Database Architecture
11. Data Directory Layout
12. Installation Directory Layout
13. Page Layout

Installation

1. OS User & Permissions
2. Installation
3. Setting environment variables
4. Clusters
5. Creating a database cluster
6. Starting and Stopping the Server (pg_ctl)
7. Connect to the server using psql

 Configuration

1. Setting PostgreSQL Parameters
2. Access Control
3. Connection Settings
4. Security and Authentication Settings
5. Memory Settings
6. Query Planner Settings
7. WAL Settings
8. Log Management
9. Background Writer Settings
10. Statement Behavior
11. Vacuum Cost Settings
12. Autovacuum Settings

Creating and Managing Databases

1. Object Hierarchy
2. Creating Databases
3. Creating Schemas
4. Schema Search Path
5. Roles
6. Users
7. Groups
8. Access Control

Postgres Data Dictionary

1. The System Catalog Schema
2. System Information views/tables
3. System Information Functions

Introduction to PSQL

1. Introduction
2. Conventions
3. Connecting to PostgreSQL
4. PSQL Command Line Parameters
5. Entering PSQL Commands
6. PSQL Meta-Commands
7. PSQL SET Parameters
8. Information Commands

pgAdmin III

1. Registering a server
2. Viewing and Editing Data
3. Query Tool
4. Databases
5. Languages
6. Schemas
7. Domains
8. Functions
9. Sequences
10. Tables
11. Columns
12. Constraints
13. Indexes
14. Maintenance
15. Rules
16. Triggers
17. Types
18. Views
19. Table Space

Security Basics

1. Authentication
2. Authorization
3. Levels of security
4. pg_hba.conf file
5. Users
6. Object ownership
7. Access control
8. Application access parameters

SQL

1. Data Types
2. Tables
3. SQL Queries
4. Quoting
5. Using SQL Functions
6. Constraints
7. Dropping or Removing Database ObjectsViews
8. Sequences
9. Indexes

Backup and Recovery & Point-in Time Recovery

1. Backup Types
2. SQL Dump
3. Cluster Dump
4. Offline Copy Backup
5. Continuous Archiving
6. pg_basebackup
7. Point-In Time Recovery
8. pg_upgrade

Routine Maintenance

1. Explain and Explain Analyze
2. Table Statistics
3. Updating Planner Statistics
4. Vacuuming
5. Scheduling Auto Vacuum
6. Preventing Transaction ID Wraparound Failures
7. The Visibility Map
8. Routine Reindexing

Moving Data

1. The COPY Command
2. Examples

 

For more inputs on PostgreSQL you can connect here.
Contact the L&D Specialist at Locus IT.

Reviews

There are no reviews yet.

Be the first to review “PostgreSQL Administration”

Your email address will not be published.