PostgreSQL Administration and Development

מק"ט: #42880 | משך קורס: 40 שעות אק'

PostgreSQL is the world's most powerful open-source database. Free to download, use, and deploy, and distributed with a liberal BSD/MIT license, PostgreSQL has been under development for more than 15 years by a global team of programmers, and is used by many companies, including Skype, BASF, and Affilias (which administers the .info and .org domains). It is widely acknowledged to offer the greatest number of features of any open-source database, and consistently performs better than other open-source database servers when benchmarking mixed read/write applications with many connections.


This course introduces students to relational databases in general, and to PostgreSQL in particular.


Students will learn the basics of creating a database, using the built-in types that come with PostgreSQL, and the many ways in which they can manipulate this data. They then learn how to ensure the integrity of their data using PostgreSQL's verification mechanisms, including triggers, defaults, and check clauses. Students then learn how to create their own custom data types, and their own functions (in several different languages) that know how to work with such types.
The course concludes with a discussion of query optimization, backups, replication, and server configuration.
 

הקורס פעיל לקבוצות מטעם ארגונים בלבד, ניתן לשלוח פנייה רק אם מדובר בקבוצה
*שדות חובה
PDF version

מטרות הקורס

  • Understand PostgreSQL database concepts
  • Install, configure, and upgrade PostgreSQL servers
  • Relational database design, including normalization
  • DDL (data definition language), DML (data manipulation language)
  • Query design, including advanced queries using various joins
  • Writing server-side functions
  • Optimizing queries
  • Security in PostgreSQL, and configuration for maximum security and flexibility
  • Optimizing storage space
  • Backups and restoration of data
  • Multi-server configuration
     

קהל היעד

Programmers, database administrators, and technical managers considering the use of PostgreSQL in their work
Programmers who are using PostgreSQL to store data, who want to get the most out of the system.

Database administrators familiar with other databases, and switching to PostgreSQL
 

תנאי קדם

Prior experience installing software and programming in any language, and particularly a high-level "scripting" language such as Python, Ruby, Perl, or PHP, is very strongly recommended. Familiarity with relational databases is helpful but not required.

תנאי קדם

Introduction to PostgreSQL

  • What is a relational database?
  • Why are relational databases so popular?
  • History of PostgreSQL
  • PostgreSQL open-source license (and what that means)
  • Current status of PostgreSQL
  • Who uses PostgreSQL?
  • Help and support
  • Course outline
  • Installing PostgreSQL
  • Pieces of a PostgreSQL installation
  • The "postgres" user
  • The "root" user (and lack thereof)
  • Clusters, databases, and schemas
  • Creating a database with CREATE DATABASE
  • Introduction to CREATE TABLE

 

Data types (and their functions)

  • Data types, strict typing
  • NULL
  • Boolean
  • Numeric (integer, float, double)
  • Numeric functions
  • Octal, hex, and binary
  • Sequences
  • Working with sequences
  • The SERIAL pseudo-type
  • Char, varchar, text types
  • String searching functions
  • String replacement functions
  • LIKE, SIMILAR, and regexps
  • Quotes (difference between single and double)
  • Timestamp and interval
  • Date-time functions (e.g., extract and date_part)
  • Time zones
  • IP addresses
  • BLOBs (binary)
  • Limitations on binary columns
  • Enum
  • Array
  • Casting from one type to another
  • Efficiencies of different types

 

Psql client program

  • Intro to psql
  • Executing commands
  • Command history
  • Editing commands
  • Displaying objects
  • Displaying vertically

 

Tables

  • Creating tables
  • Data types
  • Data sizes
  • Why use a primary key?
  • Why use a synthetic primary key?
  • NULL and NOT NULL
  • Default values
  • Data integrity with CHECK clauses
  • DROP TABLE

 


SELECT

  • SELECT * — why not?
  • Column aliases
  • ORDER BY
  • GROUP BY
  • Subselects
  • EXISTS
  • IN and NOT IN
  • ANY/SOME
  • ALL
  • SELECT INTO

 

Aggregates

  • SUM
  • COUNT
  • MIN
  • MAX
  • Using GROUP BY
  • Efficiency of aggregate functions

 

Data manipulation

  • INSERT
  • INSERTing multiple rows
  • INSERT .. RETURNING
  • Default values
  • NULL values
  • UPDATE
  • Don't forget the WHERE clause!
  • UPDATE .. RETURNING
  • DELETE
  • Don't forget the WHERE clause!

 

Indexes

  • Creating tables
  • Primary keys
  • Using sequences for primary keys
  • Indexes
  • Unique indexes
  • Unique vs. primary key
  • Why not index?
  • Index types
  • Multi-column indexes
  • Re-indexing?
  • Functional indexes
  • Conditional indexes
  • Clustered indexes
  • Concurrent indexing
  • Removing indexes

 

Modifying tables

  • ALTER TABLE
  • Adding, removing columns
  • Adding, removing defaults
  • Adding, removing CHECK clauses
  • Adding, removing indexes

 

Conditional and cleverness

  • CASE
  • COALESCE
  • NULLIF
  • GREATEST and LEAST

 

Relational data

  • The relational model
  • Normalization
  • One-to-one, one-to-many, many-to-many
  • REFERENCES clause
  • Referencing something other than the numeric ID
  • ON DELETE
  • ON UPDATE
  • CASCADE

 

Joins

  • What is a join?
  • Table aliases
  • Regular joins
  • Cartesian products
  • Inner joins
  • Outer joins
  • Self joins
  • Join efficiency
  • Join strategy

 

 

Views

  • Why views?
  • Additional abstraction layer
  • As a way of getting around constraints
  • CREATE VIEW
  • CREATE OR UPDATE VIEW
  • View inefficiencies

 

Hierarchical data

  • Why is this a problem?
  • Simple approach
  • Implementing Oracle's CONNECT BY
  • Other strategies

 

Transactions

  • What is a transaction?
  • BEGIN, COMMIT, and ROLLBACK
  • When is a transaction automatically committed?
  • When is a transaction automatically rolled back?
  • Sub-transactions: Savepoints
  • Reverting to a previous savepoint
  • Functions and transactions
  • Things you cannot do inside of a transaction

 

Temp tables

  • Why temp tables?
  • Where are temp tables stored?
  • Can I make them permanent?
  • ON COMMIT

 

Importing and exporting data

  • SELECT INTO
  • SELECT INTO OUTFILE
  • Using psql to import data
  • Importing CSV or tab-delimited
  • CSV/tab-delimited output
  • HTML output
  • XML input and output
  • Pg_dump
  • Making pg_dump a bit more efficient
  • Gotchas with pg_dump (e.g., users, permissions, roles)

 

Full-text indexing and search

  • Naive full-text indexing
  • Tsearch2
  • Character sets
  • Stemming
  • Languages and tsearch2
  • Working with indexed text
  • Limitations on indexed text
  • Speed of the full-text index

 

Cursors

  • What are cursors?
  • When are cursors unnecessary? (Pl/PgSQL FOR loops)
  • Creating a cursor
  • Retrieving rows from a cursor
  • Looping
  • Fetching forward and backward
  • Limitations

 

Object-oriented capabilities

  • Inheritance
  • Parents and children
  • When would this be useful?
  • Compatibility considerations

 

Optimizing queries

  • Trust the query optimizer
  • Explain
  • Explain verbose
  • What can you do to optimize a query?
  • Configuration parameters

 

Programming functions

  • Why server-side programming?
  • PL/PgSQL
  • Defining functions
  • Function-creation delimiters
  • Parameters
  • Variables
  • Assignment
  • Standard PostgreSQL functions are available in functions
  • Database manipulation
  • Return values
  • Returning nothing
  • Returning a row
  • Table functions
  • Trigger functions
  • NEW and OLD
  • Transactions and functions

 

Rewrite rules

  • What are rewrite rules?
  • How can they be helpful?

 

Other programming languages

  • Trusted and untrusted languages
  • PL/Perl
  • PL/Python
  • PL/ other languages
  • Connecting to C

 


Creating new data types

  • Why create a new type?
  • How to create a type
  • Casts to and from the new type
  • Writing functions for the new type

 

Geographical data with PostGIS

  • What is geographical data?
  • What is PostGIS?
  • Data types
  • Functions
  • Writing a PostGIS application
  • Integration with other PostgreSQL applications

 

Security

  • Pg_hba.conf
  • Users
  • Passwords
  • Setting, changing passwords
  • Roles
  • Setting permissions on a table
  • Setting permissions on a column
  • Permissions
  • .pgpass file
  • Environment variables
  • Connections

 

Logging

  • Configuring logfiles
  • Log levels
  • Debugging info

 

Backups, recovery, and database management

  • Phppgadmin
  • Viewing current states with pg_* tables
  • OIDs
  • Tablespaces
  • Vacuum and auto-vacuum
  • What happens if you never vacuum? (XOID rollover)
  • Backups and recovery
  • WAL
  • Savepoints
  • Hot standby
  • Streaming replication

 

תגיות