whats new in_postgres_enterprise_db_20130124
DESCRIPTION
TRANSCRIPT
1 EnterpriseDB, Postgres Plus and Dynatune are trademarks of EnterpriseDB Corporation. Other names may be trademarks of their respective owners. © 2010. All rights reserved.
EnterpriseDB: What’s New in Postgres?"
"EnterpriseDB Corporation"January 2013"
© 2013 EnterpriseDB. All rights reserved.
Postgres in the News"
2 © 2013 EnterpriseDB. All rights reserved.
3 © 2013 EnterpriseDB. All rights reserved.
Who is EnterpriseDB?"
EnterpriseDB Facts"
4 © 2013 EnterpriseDB. All rights reserved.
u The Enterprise PostgreSQL company"u Founded in 2004, first product GA in 2005"u 130+ employees"u 1,900+ customers across all market segments"u PostgreSQL service, support, training, and add-on tools"u PostgresPlus enhanced products for enterprise needs"u Largest commercial entity exclusively focused on PostgreSQL"u Strong financial backing:"
EnterpriseDB Products and Services"
5 © 2013 EnterpriseDB. All rights reserved.
The PostgreSQL Community & EnterpriseDB"u Independent & Thriving Development Community – the last
truly independent open source RDBMS u EnterpriseDB is a posi?ve contribu?ng development and
marke?ng force in the community u 6 core team members (2 employed by EnterpriseDB) u EnterpriseDB’s Bruce Momjian co-‐founded the PostgreSQL
Global Development Group, and has worked on PostgreSQL since 1996
u 9 PostgreSQL contributors at EnterpriseDB u Es?mated 9,000,000+ downloads/year u Thousands of deployments worldwide
6 © 2013 EnterpriseDB. All rights reserved.
What’s new in Postgres Plus Advanced Server 9.2?"
7 © 2013 EnterpriseDB. All rights reserved.
Version 9.2 Enhancements..."
u help organizations reduce IT costs:
• using existing Oracle skills and features
• with faster results without expensive hardware
• better scaling to serve more users
• keeping data secure
• increasing application developer’s productivity
• allows DBAs to manage more databases easily
8 © 2013 EnterpriseDB. All rights reserved.
Oracle: Object types enhancements
u User defined data types with Attributes and functions for manipulating data
u Allows for smaller and more modular data types and operations that map cleanly to object oriented client applications making developer’s work faster and easier
u New: • member functions and procedures"
• syntax compatibility for FINAL, NOT INSTANTIABLE, and OVERRIDING"
u Also complete syntax support for objects in dump and restore operations
u Helps makes migration of Oracle apps easier
© 2013 EnterpriseDB. All rights reserved.
Reduced costs using existing Oracle skills and features
Oracle: Object types enhancements
CREATE TYPE CustomType AS OBJECT ( attribute1 INT, attribute2 REAL, MEMBER FUNCTION funcplus( arg1 INT) RETURNS INT, MEMBER FUNCTION funcminus( arg1 INT) RETURNS INT ) CREATE TYPE BODY CustomType AS MEMBER FUNCTION funcplus( arg1 INT) RETURNS INT IS BEGIN RETURN attribute1 + attribute2; END funcplus; MEMBER FUNCTION funcminus( arg1 INT) RETURNS INT IS BEGIN RETURN attribute1 - attribute2; END funcminus; END; /
Object Type Spec
Object Type Body
Reduced costs using existing Oracle skills and features
© 2013 EnterpriseDB. All rights reserved.
Oracle: PL/SQL like Sub-Types
u Sub-type derived from an existing base type u Adds additional constraints u Defined in SPL (procedures/functions/triggers/packages)
DECLARE SUBTYPE INTEGER IS NUMBER (38,0); SUBTYPE NAME IS VARCHAR (20) NOT NULL; var1 INTEGER; var3 NAME := 'hello'; -- NOT NULL constraint, must initialize BEGIN var1 := 38.38; -- can't have fractional part, it will be lost because of constraint DBMS_OUTPUT.PUT_LINE ('Var1 = '|| var1); END;
© 2013 EnterpriseDB. All rights reserved.
Reduced costs using existing Oracle skills and features
Oracle: new Functions, Syntax, Variable support
u DROP TABLE mytable CASCADE CONSTRAINTS; u "current_date" can now be a variable name u "Log" can now be a function name u "STRING" is now a data type (maps to VARCHAR2) u "NVARCHAR2" is now a data type (maps to VARCHAR) u Table() Expressions for Nested Tables:
CREATE OR REPLACE TYPE string_a IS TABLE OF VARCHAR2(765); select * from table(string_a('abc','xyz')) ; column_value -------------- abc xyz (2 rows)
© 2013 EnterpriseDB. All rights reserved.
Reduced costs using existing Oracle skills and features
Performance: Read Scaling up to 64 cores
© 2013 EnterpriseDB. All rights reserved.
Reduced costs with faster results without expensive hardware
Performance: Index Only Scans
u a.k.a. Covering Indexes u All the columns the query needs must be available in the
index, and every row is visible to running transactions u Then fetching table data is skipped
• no disk read"
u Up to 5x faster in some cases
© 2013 EnterpriseDB. All rights reserved.
Reduced costs with faster results without expensive hardware
Performance: Append Hint for INSERTS
u Adds new rows to the end of the relation • Skips the Free Space Map"
u Improves INSERT performance for tables with frequent record deletions
u Also useful when Bulk Loading data
© 2013 EnterpriseDB. All rights reserved.
INSERT /*+APPEND*/ INTO sales VALUES (10, 10, '01-Mar-2011', 10, 'OR');
INSERT INTO sales_history SELECT /*+APPEND*/ FROM sales;
Reduced costs with faster results without expensive hardware
Scalability/HA: xDB Multi-Master Replication
© 2013 EnterpriseDB. All rights reserved.
Reduced costs by scaling to serve more users
u True multi-master replication – edit any data from any master
u more details in a moment...
Privileges on Data Types
u Restricts which users can create dependencies on types
u Ensures the object owner can alter a type
u Supports the SQL-conforming USAGE privilege on types and domains
© 2013 EnterpriseDB. All rights reserved.
Reduced costs keeping data secure
VIEW Security Barriers
u Prevents data leakage in certain VIEW use cases
u Use when a VIEW provides row-level security
u Prevents using functions and operators on non-View rows until after the VIEW has done its work
© 2013 EnterpriseDB. All rights reserved.
Reduced costs keeping data secure
JSON Data Type
u Stores JSON (JavaScript Object Notation)
u Makes for easier web application development
u Validates data on Insert/Update
u Supporting functions: • array_to_json() Returns the array as JSON"
• row_to_json() Returns the row as JSON"
© 2013 EnterpriseDB. All rights reserved.
Reduced costs by increasing application developer’s productivity
RANGE Data Type
u Stores a range of data for a given type
u Supports operators to calculate containment, overlaps, intersections, emptiness, and upper/lower bounds
u e.g. prevent overlapping bookings for a room
u Easier creation of calendaring, scientific, and financial applications
© 2013 EnterpriseDB. All rights reserved.
Reduced costs by increasing application developer’s productivity
Management Enhancements
u New pg_dump options support faster restores • Allows custom ordering of the restore of meta-data and data"• table structure and check constraints first, "• then data, "• then indexes, unique constraints, foreign keys"
u pg_upgrade enhancements • in-place upgrades without dump/restore"• handles more use cases"• improve logging and failure reporting"
u Postgres Enterprise Manager 3.0 • more in a few minutes..."
© 2013 EnterpriseDB. All rights reserved.
Reducing costs by managing more databases
Advanced Server v9.2 - Conclusion"
u Oracle shops can continue to reap investments in their Oracle skills and Oracle features used without the high costs of Oracle licenses
u Performance and scaling improvements mean less expensive hardware and serving more users
u Secure data helps reduce costly mishaps and intrusions
u Developers are more productive
u DBAs are more productive easily managing more databases with less downtime"
22 © 2013 EnterpriseDB. All rights reserved.
What’s new in xDB Replication Server 5.0?"
23 © 2013 EnterpriseDB. All rights reserved.
24 © 2013 EnterpriseDB. All rights reserved.
What is xDB Replication Server?"u Single Master logical database replication system u Publication / Subscription Architecture u Replicate one or more tables u Define and apply row filters u Heterogeneous replication between Postgres and Oracle or SQL Server u Graphical Console and CLI u Replication History Viewer u Improves Read Scalability, Read Availability, Read Performance u Used for: offload reporting, warm standby servers, migrating data,
testing systems in parallel u Reduces IT costs:
• deploying on existing commodity hardware"• substituting Postgres for Oracle or SQL Server licenses"
Read Write
Read Only
25 © 2013 EnterpriseDB. All rights reserved.
u Multi-Master logical database replication system u Publication / Subscription Architecture u Replicate one or more tables u Define and apply row filters u Heterogeneous replication between Postgres and Oracle or SQL Server u Graphical Console and CLI u Replication History Viewer u Improves Write Availability, Write Performance u Used for: HA, faster access to data, testing in parallel u Reduces IT costs:
• deploying on existing commodity hardware"• avoiding expensive hardware and networking upgrades to support write intensive
applications"
xDB Multi-Master Replication"
All Read
All Write
Any table Any row
xDB MMR - Features"u True Master-to-Master Replication for 2 or more nodes
• Any data can be edited from any master in the cluster"• Equal data access and editing capabilities on each master "• Updates to other masters occurs in near real time"
u Trigger-based and Asynchronous • Update delay between masters of a couple of seconds or longer"• Appropriate for latency-tolerant applications"• Ideal for geo-dispersed user populations"• Dramatic improvement over batch updates between servers"
u Automatic Conflict Detection • Uniqueness"• Update"• Delete"
26 © 2013 EnterpriseDB. All rights reserved.
xDB MMR - Features"u Multiple Conflict Resolution Options
• Earliest or Latest Timestamp"• Node priority – one node always wins"• Manual – Administrator reviews and actions"
u Support Operating Systems • Linux 32/64"• Windows 32/64"• Solaris x86 / SPARC"• HP-UX"
u Supported Database Servers • PostgreSQL or Postgres Plus Advanced Server"• Support for versions 8.4 thru 9.2"
27 © 2013 EnterpriseDB. All rights reserved.
xDB MMR - Ideal for Geo-dispersed servers"
28 © 2013 EnterpriseDB. All rights reserved.
xDB MMR - Ideal for Geo-dispersed servers"u Each geography updates a local master faster than to a single
common master for all geographies
u Network latency for local writes is reduced compared to remote access
u If any master fails, its traffic can be re-routed to another geography's master until recovery, or
u Each geography can also have its own Hot-Standby
u All locations can utilize commodity hardware
u All masters continuously synchronize eliminating batch updates from a single master which results in stale data
29 © 2013 EnterpriseDB. All rights reserved.
xDB MMR - Replication Monitoring"u GUI Monitoring for Replication Events
u DBAs can view In-Progress and Completed Replication Events • Replication Event Start Time"• Completion Time"• Count of Replicated Transactions"• Replication Status (Completed, Failed)"
u Data Conflict Monitoring • Conflict Detection Time"• Source and Target Master Nodes"• Conflict Transaction details"• Resolution Strategy employed"• Resolution Status (Pending, Resolved)"
30 © 2013 EnterpriseDB. All rights reserved.
xDB MMR - Conclusion"
u Multi-Master Replication Benefits • Improves write availability"• Improves read scalability"• Improves write performance for latency-tolerant applications"
u Cost Saving Benefits: • Lower costs using commodity hardware vs scaling up to expensive
hardware"• Serves more users at lower cost"• More up to date information is better for the top and bottom line"
31 © 2013 EnterpriseDB. All rights reserved.
What’s New in Postgres Enterprise Manager 3.0?"
32 © 2013 EnterpriseDB. All rights reserved.
33 © 2013 EnterpriseDB. All rights reserved.
What is Postgres Enterprise Manager (PEM)?"
An EnterpriseDB tool for DBAs and Developers to monitor, manage, and tune
large Postgres deployments en masse!
It’s the only solution of its kind for Postgres!
Version 3.0 Enhancements"
u This release contains new features in these areas:
u Synchronize with pgAdmin 1.16
u Client
u Logging
u Secure Monitoring Server access
u Monitoring information
u Platform Support
34 © 2013 EnterpriseDB. All rights reserved.
Synchronize with pgAdmin v1.16
u New Data Import Wizard u Search for database objects by name u Auto-refresh for objects when clicked in the display tree u Support for Postgres v9.2 features such as Security labels,
VIEW Security barriers, and Object type privileges u SSL compression option for SSL connections u Copy table structure for creating new tables u Support for optimized pg_dump/pg_restore options: Pre-
data, Data, and Post-data
© 2013 EnterpriseDB. All rights reserved.
New Browser based Web Client
u Access monitoring data from common internet browsers u View monitoring data anytime anywhere
© 2013 EnterpriseDB. All rights reserved.
New Log Manager"
u Configures logging parameters for multiple databases at once • log file locations"• logging frequency"• log message selection"• log format"
u Optionally configure log collection into a centralized table u Dashboard for all collected logs u Simplifies a DBAs job and saves time
© 2013 EnterpriseDB. All rights reserved.
New SSH Tunneling"
u SSH Tunneling provides access to the monitoring server from outside the firewall
u Works only for authorized individuals
u Simplifies access to Enterprise Servers from outside the network
© 2013 EnterpriseDB. All rights reserved.
ssh
Platform Support
u The Enterprise Server now supports Postgres Plus Advanced Server as the backend
© 2013 EnterpriseDB. All rights reserved.
What’s Coming in the Future?
© 2013 EnterpriseDB. All rights reserved.
Futures: Postgres Plus Advanced Server v9.3 "
u Horizontal Scaling Solutions u Performance: Materialized Views
• Performance/convenience at the expense of currency"u Oracle Compatibility: Object Types
• support explicit object type constructors"u Oracle Compatibility: Packages and Functions
• DBMS_RANDOM, REGEXP_INSTR and REGEXP_SUBSTR"u Ease of Use/Management: EDB*Loader
• commit rows in batches and use environment variables in control file"
41 © 2013 EnterpriseDB. All rights reserved.
All items are Work in Progress (no commitment to delivery)
Reduce costs with scaling, performance, Oracle compatibility, and easily managing large database deployments
Futures: xDB Replication Server v5.x"
u MMR Conflict Resolution: Custom handlers
u Expanded DDL Replication support
u SMR and MMR in the same cluster
u MMR Record filtering
42 © 2013 EnterpriseDB. All rights reserved.
All items are Work in Progress (no commitment to delivery)
Reduce costs solving more problems with replication tools
Futures: Postgres Enterprise Manager 4.0"
u Integrated Tuning Wizard u Log file Alerts u Update Manager u User customizable dashboards u Improved graphs u Bulk probe configuration
43 © 2013 EnterpriseDB. All rights reserved.
All items are Work in Progress (no commitment to delivery)
Reduce costs by allowing a single DBA to manage more databases
Now what? Download and save!"
44 © 2013 EnterpriseDB. All rights reserved.
/download-xdb-replication-server-mmr
/download-advanced-server
/download-enterprise-manager
www.enterprisedb.com
Questions?
For more info, contact [email protected]