Description
This training will teach you how to install and administer your PostgreSQL database server on a daily basis. You will see how to manipulate data, how to manage a database and you will learn to master the main administration techniques and tools.
Who is this training for ?
For whom ?
Project managers, developers, database administrators and system administrators.
Prerequisites
Training objectives
Training program
- PostgreSQL Overview
- Presentation of projects and tools.
- Comparison with other RDBMS engines.
- Installation
- Installation steps.
- Linux distributions: from RPM or sources.
- Binaries for MS-Windows.
- Installation wizard.
- Preparing the operating system.
- Initializing and Running an Instance
- Principle.
- Stopping and starting the server.
- Server process architecture.
- Creating additional instances.
- Connections and administrative tools
- Sessions.
- Server side (pg_hba.conf).
- Security (SSL.
- Access rights.
- Role management (users and groups).
- Granting and revoking privileges.
- Command line and graphical tools.
- psql (shell SQL).
- PgAdminIII, phpPgAdmin, Pgpool.
- Data definition
- Creating schemas, tables, indexes, domains.
- Data types, operators and functions.
- Programming for administration
- Server side.
- Stored procedures.
- PL/pgSQL.
- PL/Perl.
- Extensions in C.
- Triggers.
- Stored functions.
- Client side.
- Web applications in PHP.
- PostgreSQL and PDO drivers.
- Web applications in Java.
- » Daily administration
- Definition of files.
- Creation of namespace (tablespaces, files).
- Tree of directories and files of the server.
- Server administration.
- Configuration.
- Binary logs and memory.
- Statistics collector and activity logs.
- Maintenance and operation tasks.
- Analyzing queries with Explain.
- Analyzing and cleaning table spaces and indexes with Vacuum .
- Index reconstruction with Reindex.
- Backups and restoration
- Backups with pg_dump and pg_dumpall.
- Restores with pg_restore and psql.
- Binary logs: Write Ahead Logging (WAL).
