Ingeniero de Sistemas, con especialidad en Base de Datos,
Oracle Certified Professional con más de 20 años de experiencia
en Administración, Respaldo, Recuperación, Afinamiento, y Replicación de Base de Datos.
Especialista y Certificado en Alta Disponibilidad (OCP 9i, 10g, 11g & 12c, OCE RAC & Tuning)
Especialista en Soluciones de Contingencia (Standby, Dataguard, Golden Gate & Shareplex )
Especialista en Implementaciones de Datawarehouse, Datamarts & Big Data
Administrador de Servidor de Aplicaciones Oracle (OAS) y conocimientos de Linux and UNIX.
Administrador Oracle DBA-APPS - Oracle E-Business Suite 11i
PostgreSQL, SQL Server & Informix Dynamic Server Database Administrator
SAP BASIS Administrator with NetWeaver on Windows, Linux and UNIX.
SAP HANA Administrator on Enterprise Linux
Ing. Yonogy Curi Pomahuali
Instructor since April 2004
At Cibertec, Oracle University, GBS,
New Horizons & Edutronic
Email: yonogy.curi@edutronic.pe
Skype ID: yonogy.orcl
Blog: http://orclperunet.blogspot.com/
Facebook Group: Oracle Peruvian Network
Facebook Page: Edutronic Global Services
PostgreSQL DBA Admin Workshop I
Session 01:
• PostgreSQL Overview
• PostgreSQL Install & Upgrade
Session 02:
• PostgreSQL Database Administration
• PostgreSQL Database Monitoring
Session 03:
• PostgreSQL Database Backup
• PostgreSQL Database Recovery
Session 04:
• PostgreSQL Standby DB Implementation
• PostgreSQL Standby DB Replication
Session 05:
• PostgreSQL Standby Switchover
• PostgreSQL Standby Rebuild
Session 06:
• PostgreSQL Standby DB Monitoring
• PostgreSQL Backup and Recovery
Session 07:
• Big Data Overview
• PostgreSQL & Big Data Integration
Session 08:
• PostgreSQL & Oracle Comparation
• Oracle to PostgreSQL Migration
PostgreSQL Architecture
PostgreSQL Architecture
CLIENT SERVER
Comprised of two main parts:
The client application and the client interface library.
• Many different client applications, many of which run on
different OS's, some include: PGAdmin, PGAccess,
Mergeant, PGInhaler, SQirreL and more.
• Client interface library is the way that each of those
applications can talk to the Server because the client
interface library will convert to the proper SQL queries
that the server can understand and parse.
PostgreSQL Architecture
POSTMASTER
Is a daemon thread that runs constantly.
• Uses an implicit invocation architecture to listen for any
and all calls to the database.
• When it receives a call from a client, it creates a back-
end process (postgres server) to match it, using 1-1
correspondence.
• Once the process is created, it links the client and postgres
process so that they no longer have to communicate
through the postmaster.
PostgreSQL Architecture
PARSER
• Accepts an SQL query in the form of ASCII text.
• The lexer does pattern matching on the query to recognize
identifiers and keywords.
• The parser then assembles these into a parse tree.
• The parser checks that the SQL query has valid syntax but
does not understand the semantics.
PostgreSQL Architecture
PLANNER OPTIMIZER
• SQL queries can be executed in many different orders and
produce the same results.
• The planner / optimizer will choose the best path or a
reasonably efficient one if too many possibilities exist.
• It will then pass on the path to the executor.
PostgreSQL Architecture
EXECUTOR
Receives plan from planner / optimizer in the form of a tree.
• Extracts the necessary data tables.
• Recursively goes through the plan, and performs the
necessary action at each node..
PostgreSQL Architecture
ACCESS
• The Access Subsystem is in charge of:
o indexing
o scanning
o searching
o compiling and returning data
• PostgrSQL server processes retrieve data using the Access
Subsystem.
• Access subsytem can use a variety
PostgreSQL Architecture
STORAGE
• Stored data is accessed through the Storage subsystem.
• In charge of maintaining a series of shared buffers.
• Allows for multiple accesses to the same tables using a
multiversion concurency controle model (MVCC).
• Maintains table locks and insures data concurrency.
PostgreSQL Architecture
PostgreSQL Directory Structure
PostgreSQL Cluster Physical Structure
PostgreSQL Archive_ Mode Options
PostgreSQL DB Relation
PostgreSQL Database Structure
PostgreSQL & Oracle Compare
PostgreSQL Main Objects Types
• Database
• Schema
• Table
• Sequence
• Event trigger
• Function
• View
• Materialized view
PostgreSQL Schema Definition
PostgreSQL Tables Definition
PostgreSQL Describe Table Definition
PostgreSQL Alter Table Definition
PostgreSQL Constraints Definition
PostgreSQL Constraints Definition
PostgreSQL Check Constraint
PostgreSQL Data Manipulation
PostgreSQL Data Manipulation
PostgreSQL Data Manipulation
PostgreSQL Truncate Table
PostgreSQL Manteinance: ANALYZE
PostgreSQL Manteinance: VACUUM
PostgreSQL Manteinance: REINDEX
