Which Postgres is Right for Me? - Mil-OSS

cuttlefishblueΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 10 μήνες)

403 εμφανίσεις

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 Corporate Overview
Presented by EnterpriseDB Corporation
EnterpriseDB
-
the Company
2
© 2011 EnterpriseDB. All rights reserved.

The Enterprise PostgreSQL company

Founded in 2004, first product GA in 2005

120+ employees

1000+ customers across all market segments

70,000+ downloads / week of PostgreSQL and related products

Enabling database consolidation using PostgreSQL and advanced
Oracle compatibility

Saving customers millions through the power of open source

Strong financial backing:
PostgreSQL the Database
3
©
2011
EnterpriseDB. All rights reserved.

25+ years
of enterprise
-
class development
of enterprise
-
class features
Fully ACID Compliant
MVCC
Point in Time Recovery (PITR)

Data and Index Partitioning
Bitmap Indexes
ANSI Constraints
Triggers & Stored Functions
Views & Data Types
Nested Transactions
Online Backup
Online Reorganization
Foreign Keys
PostgreSQL and the Community
4
© 2011 EnterpriseDB. All rights reserved.

Independent & Thriving Development Community

6 core team members (2 employed by EnterpriseDB)

16 committers for v9.0 (4 employed by EnterpriseDB)

275 contributors for v9.0 (7 employed by
EnterpriseDB)

9,000,000+ downloads / year

EntepriseDB’s Bruce Momjian was one of the founders
of the PGSQL Open Source version and Postgres
community

thousands of active deployments worldwide in public
and private sector organizations of all sizes
PostgreSQL Popularity
5
© 2011 EnterpriseDB. All rights reserved.

3M+ downloads from EnterpriseDB & PostgreSQL.org

In every major Linux distribution (RHEL, Fedora, SLES, OpenSUSE)

The
only
independent open source database for enterprise apps
20027
13225
12829
10185
9457
4827
1161
90
1
0
5000
10000
15000
20000
25000
ALFRESCO
JBOSS
SPRINGSOURCE
POSTGRES
FEDORA
ECLIPSE
APACHE
MOZILLA (FIREFOX)
GOOGLE
Alexa
Traffic
Rank
(smaller is better)
Government PostgreSQL Customers
6
© 2011 EnterpriseDB. All rights reserved.
The Enterprise Open Source Stack
7
© 2011 EnterpriseDB. All rights reserved.
Enterprise Adoption
8
© 2011 EnterpriseDB. All rights reserved.
EnterpriseDB Solutions
9
©
2011
EnterpriseDB. All rights reserved.
EnterpriseDB Solution Applications
10
© 2011 EnterpriseDB. All rights reserved.
NetApp (OEM)
Burris Logistics
Sony Online
St. Jude
BioClinica
Aquent
DigitalSidebar
EMC
Amazon
NIC India
Union Pacific
Lexis Nexis
CA (OEM)
AAA
KT
NASA
FAA
NTT
4Over
Dotomi
iCrossing
Existing Applications
New Applications
Database
Augmentation
PostgreSQL
Users
Cloud
Platform
Change
New
Applications
11
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. ©
2010
. All rights reserved.
Postgres Plus
Technical Overview
EnterpriseDB PostgreSQL

Latest Community edition

Multi
-
platform certified

Certified One
-
click Installer

pgAdmin bundled

StackBuilder
12
© 2011 EnterpriseDB. All rights reserved.
PostgreSQL
Postgres Plus Standard Server

Bundles OSS components/tools

Component Integration Testing

Lags community GA edition

Install
-
time Tuning Wizard

MySQL Migration Wizard

Auto Update Notification

pgMigrator*

StackBuilder Plus*
13
© 2011 EnterpriseDB. All rights reserved.
Postgres Plus
Standard
Server
PostgreSQL
Postgres Plus Advanced Server

Postgres‏Plus‏Standard‏Server‏and…

Compatibility features

Oracle compliment / alternative /
replacement

Scalability Features

High Availability features

Performance features

Suite of Database Connectors

Suite of GUI Management tools

Suite of CLI Development tools

GUI and CLI Migration tools

Object Types support

Predefined Development Packages

Indemnification

Warranties
14
©
2011
EnterpriseDB. All rights reserved.
Postgres Plus
Advanced Server
Postgres Plus
Standard
Server
PostgreSQL
15
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. © 2010. All rights reserved.
Postgres Plus Advanced Server
What is Postgres Plus Advanced Server?
PostgreSQL

The‏world’s‏most‏advanced‏open‏
source database

In continuous development for more
than 20 years

Designed for mission
-
critical OLTP
environments

Used‏by‏the‏world’s‏largest‏
commercial and government
organizations
What Is Postgres Plus Advanced Server?
Compatibility

Run applications written for Oracle
virtually unchanged

No need to re
-
train Oracle DBAs
and developers

Support for PL/SQL language and
OCI interoperability

Replication for easy sharing of data
What Is Postgres Plus Advanced Server?
Performance

Built for high performance OLTP

Up to 200% faster than
PostgreSQL

Faster than MySQL for OLTP
What Is Postgres Plus Advanced Server?
Tools

Concurrently browse multiple
databases including EnterpriseDB,
MySQL, Oracle, PostgreSQL, SQL
Server, and Sybase from a single
interface

A single view of all pertinent
database functions

Online migration toolkit to migrate
all data and business logic from
Oracle to EnterpriseDB

Online migration toolkit to migrate
data and schema for MySQL, SQL
Server, and Sybase

Familiar and intuitive for DBAs and
developers

Postgres Plus HQ for monitoring
What Is Postgres Plus Advanced Server?
Company

Over 100 employees in 6 countries

24/7 follow
-
the
-
sun support

Comprehensive training

Installation, performance tuning,
and maintenance services

Software indemnification
21
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. © 2010. All rights reserved.
Oracle Compatibility
Oracle Compatibility

Run applications written for Oracle virtually unchanged

No need to re
-
train Oracle DBAs and developers

Support for PL/SQL language and OCI interoperability

Replication for easy sharing of data

Dramatic Cost Savings

No Vendor Lock
-
in
22
©
2011
EnterpriseDB. All rights reserved.
Migration Example
23
© 2011 EnterpriseDB. All rights reserved.
Compatibility Features

SQL extension support

Decode, NVL, Substr, NVL2

Date/time functions: add_months, extract, next_day

PL/SQL support

REF Cursors

Looping

Implicit and explicit cursors

Variables declarations

Conditional statements

Associative Arrays

Tools

EDB*Plus

SQL*Plus look
-
a
-
like

EDB*Loader

SQL*Loader equivalent

Features

Stored procedures

Packages

Hints
Compatibility Features (cont.)

Features‏con’t

Database Links

Hierarchical Queries

Synonyms

Rownum

Datatypes

Explicit Transaction Control

within a stored procedure

(not supported by PostgreSQL)

Object types

Create type … as object

Create type … as table

Object methods

Bulk Binding/Fetch

Users/Roles

Dynamic SQL
Compatibility Features (cont.)

Built
-
in Packages

DBMS_:

SQL, LOB, JOB, PIPE, ALERT, OUTPUT, UTILITY

UTL_:

FILE, MAIL, SMTP

Oracle
-
like Data Dictionary

ALL_, DBA_, USER_ views

Most commonly accessed views

Diagnostics
-
DRITA

System and session waits

Not exposed in PostgreSQL

Part of Advanced Server

Statspack
-
like reporting
Oracle Compatibility & Migration Strategies
Strategy
Benefits
Develop/Deploy
New LOB Applications

Significant cost savings for non mission critical
systems

Leverages all existing Oracle skills

Very low risk
Deploy Postgres Plus
as Oracle
Replication Server

Significant cost savings

Leverages Postgres Plus Replication Server

Leverages all existing Oracle skills

Improves transaction and query performance
Migrate non Mission
Critical Oracle Apps to
Postgres Plus

Significant cost savings

Leverages all existing Oracle skills

Very low risk
Migrate Mission Critical
Oracle Apps to
Postgres Plus

Biggest cost savings

Leverages all existing Oracle skills

Greatest deployment flexibility
© 2009 EnterpriseDB Corporation
How good is our Oracle Compatibility?

Good‏enough‏for‏Sony’s‏massive‏multi
-
player gaming

Good‏enough‏for‏IBM’s‏DB2‏v9

Good‏enough‏for‏Netezza’s‏TwinFin‏Migrator‏component

Good‏enough‏for‏FTD’s‏Oracle‏replication‏needs

Good enough to save 10s and 100s of thousands in license fees
28
©
2011
EnterpriseDB. All rights reserved.
Step 1 Oracle Migration Assessment
29
© 2011 EnterpriseDB. All rights reserved.

Paid for service from EnterpriseDB

What is it?

Pre
-
analysis interview

Analysis of Oracle schemas to be migrated

Finds possible migration issues

Unsupported Features

Unsupported Syntax

Implementation Differences

Findings documented with possible workarounds in final report

Post analysis discussions to review findings

Migration feasibility score assigned (1

10)
http:www.enterprisedb.com/solutions/oramig.do
Oracle Migration Factory Core Services

Customer provides data
-
less Oracle export of database

Migrate the database(s) to Postgres Plus Advanced Server

Resolve any issues

Implement workarounds for missing or different functionality

Perform sanity checking of unit functionality

Requires input/output from customer on same units in Oracle

Document the changes/workarounds

Educate Customer on changes/workarounds

Advise on application level changes needed

Return migrated databases to Customer

Via pg_dump

Customer can customize with add on options
30
© 2011 EnterpriseDB. All rights reserved.
Oracle Migration Factory Add
-
ons

Setup and Migrate test system

Installation of Postgres Plus Advanced Server on Customer server

Import of migrated database to Postgres Plus Advanced Server

Migration of test data from a test Oracle system

Application Functional Testing

Provide assistance in connecting application to database

Provide assistance in functional testing of application

Resolve functional testing issues

Performance Tuning of Migrated System

Production Migration Setup

Install Postgres Plus Advanced Server on target production server

Import migrated database from testing server

Setup of xDB Replication Server

Snapshot process will migrate production Oracle data

Deltas are captured by xDB triggers

Once snapshot is done, synchronization is performed
31
© 2011 EnterpriseDB. All rights reserved.
32
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. © 2010. All rights reserved.
Tools
DBA Management Server

The following three graphs are available per database:

Transactions

Displays how many transactions were committed and rolled
back

Memory Reads

Displays how many cache blocks were hit

Disk Reads

Displays the physical disk blocks being read
Each configured database is represented by a different colour.

You can grasp which database has the heaviest transactions by
one glance at the various graphs.
Database Migration Toolkit

Online Migration Toolkit enables point and click migration from
Oracle

Automatically Migrates:
34
© 2011 EnterpriseDB. All rights reserved.

Data

Schemas

Stored Procedures

Triggers

Functions

Sequences

Packages

Views

Database Links

Synonyms
xDB Replication Server

Supports EnterpriseDB and Oracle near real
-
time
heterogeneous replication

Supports Postgres to Postgres replication now

Trigger
-
based system with less than 10% performance impact

Applications

For customers “stuck” on an Oracle database

Offload reporting to EnterpriseDB

Keep Oracle database running fast for transactions
36
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. © 2010. All rights reserved.
High Availability
High Availability

Active/Passive Clustering

Near real
-
time Replication

Multi
-
Version Concurrency Control (MVCC)


Point
-
in
-
Time Recovery

Log Shipping for Standby (~ Oracle® Data Guard)


Online Operations
Active/Passive Clustering
EnterpriseDB Corporation Confidential
Standby Database using pg_standby
Archive
r
Disk
Storage
Live
Server
Instance
Postgres Plus
Advanced Server
Disk
Storage
Standby
Server
(in recovery mode)
Postgres Plus
Advanced Server
WAL
WAL
pg_standby
Postgres Plus Replication

Built on SLONY
-
I

Asynchronous master / slave

Can be used for:

Improved performance for geographically dispersed users

High availability

Load distribution (OLTP vs. reporting)

40
© 2011 EnterpriseDB. All rights reserved.
41
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. © 2010. All rights reserved.
Performance
Unlimited
Max Indexes/Table
1600+
Max Columns/Table
Unlimited
Max Rows/Table
2
GB
Max Field Size
1.6TB
Max Row Size
32
TB
Max Table Size
Unlimited
Max DB Size
Scalability and Performance

Developed from High Performance Transaction Systems

OLTP

Dynatune

Performance related features such as Partitioning, Functional
Indexes, Hints, Bulk Binding
Scalability

Infinite Cache

High performance horizontal scaling architecture for cache memory

Cache expands with inexpensive commodity hardware
43
© 2011 EnterpriseDB. All rights reserved.
Performance and Scalability

Infinite Cache
-
Features

Expands and Distributes buffer cache across multiple machines

Designed for Read
-
Mostly applications (e.g. Content Management, Query
Intensive, Business Intelligence, Data Warehouse)

Cache is transparent to client applications (no cache coding needed)

Compression feature enables caching entire databases (e.g. put a 250 GB
database into 32 GB RAM Cache)

Cache can be pre
-
warmed for immediate results

Cache scales using inexpensive commodity hardware

Infinite Cache can be run to boost single machine performance!

Created for:

DBAs and data managers overseeing large amounts of data requiring fast
response times for queries and reporting loads

Developers who don’t want to write specialized caching code
44
© 2011 EnterpriseDB. All rights reserved.
Scalability

Infinite Cache
-
Performance
Advanced Server is up to
5
X faster
on a
2
cache
27
GB setup than a single
machine with
8
GB
Shared Buffers.
Scalability

Infinite Cache

Single Machine Performance
Advanced Server is
16
X
faster
on a single
machine with large
amounts of memory
(e.g. greater than
2
GB)
Infinite Cache can be used
on a
single machine
!
Slide: 39
Scalabilty PostgreSQL vs MySQL
“ PostgreSQL is outperforming any MySQL version on Creating,
Loading, Created Indexes operations (this can be very usefull on a
DB recovery). “
http://www.randombugs.com/linux/mysql
-
postgresql
-
benchmarks.html
48
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. © 2010. All rights reserved.
Security
“By default, PostgreSQL is probably the
most security
-
aware database available
...”
Database Hacker's Handbook
Security

Object level privileges assigned to roles and users

Kerberos and LDAP authentication

SSL communication

Data Level Encryption (AES,
3
DES, etc)

Ability to utilize
3
rd party Key Stores in a full PKI Infrastructure

Foundation for full compliance with the strictest of security
standards (PCI Data Security Standard)

Flexible field level encryption and row level security
PL/Secure and EDB*Wrap

Encrypt your Pl/pgsql stored programs with PL/Secure

Encrypt your SPL procedurs with EDB*Wrap
SQL/Protect

DBA Managed SQL Injection Protection

Preventing attacks is normally the responsibility of the application
developer, but with SQL/Protect, DBAs can now provide another layer of
protection to prevent corruption or co
-
opting of the database.

Multiple Prevention Techniques

Unauthorized Relations

Utility Commands (e.g. DDL)

SQL Tautology

Unbounded DML
52
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. © 2010. All rights reserved.
Postgres Plus 9.0
Postgres Plus 9.0 Features

Hot Standby

Streaming Replication

Integrated Upgrade
-
in
-
Place (pg_upgrade)

RADIUS Authentication

Password Strength Checking (passwordcheck)

Easier Database Permissions Management Commands
(GRANT ON ALL and DEFAULT PERMISSIONS)

Deferrable Unique Constraints

Conditional Triggers

Column Triggers

Ordering in Aggregates

New Windowing Functions (ROWS PRECEDING and
FOLLOWING)
Postgres Plus 9.0 Features cont.

Anonymous Procedure Code Blocks (DO statement)

Postgres Plus Advanced Server already has anonymous blocks due to
Oracle compatibility.

This is alternate use case

Improved Perl and Python Stored Procedures (including support
for Python 3)

64
-
bit Windows Support

Unique Keys for Non
-
Scalar Data (Exclusion Constraints)

Expanded Support for Key
-
value Data (HStore)

JSON and XML Explain Plans
EnterpriseDB Corporation Confidential
Postgres Plus 9.0

Streaming Replication
»
Uses WAL for speed and
reliability
»
Highly cost effective, uses
commodity servers
»
Excellent HA solution when
clustering is not an option
56
EnterpriseDB, Postgres Plus and Dynatune are trademarks of
EnterpriseDB Corporation. Other names may be trademarks of their
respective owners. © 2010. All rights reserved.
Thank You
Questions?
Presented by EnterpriseDB Corporation