Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

PostgreSQL 9.2 Evaluation

No description
by

Jerry Meng

on 9 August 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of PostgreSQL 9.2 Evaluation

PostgreSQL 9.2
Why PostgreSQL ?
Pgpool-II HA Solution
PostgreSQL HA Solution
Demo
PostgreSQL HA Online-recovery

FREE
PostgreSQL under PostgreSQL License like BSD or MIT
PostgreSQL JDBC Driver under BSD license
Pgpool-II under BSD license
RICH
ACID compliant
Support most of SQL:2008 data types and binary large objects
Write-Ahead Logging (WAL)
Payload support for LISTEN/NOTIFY
pg_basebackup tool
pg_receivexlog
http://www.postgresql.org/about/featurematrix/
Features
DIE HARD
Hot Standby
Cascading streaming replication
Synchronous replication
Supported by Pgpool-II
9.0 - 9.2
FAST
Vertical scalability to 64 cores
Up to 350,000 read queries per second
Up to 14,000 data writes per second
120 transactions per second (7200 RPM HDD)
Support index-only scans for data warehousing queries
LIMITATION
Maximum Database Size: Unlimited
Maximum Table Size: 32TB
Maximum Row Size: 1.6TB
Maximum Field Size: 1GB
Maximum Rows per Table: Unlimited
Maximum Columns per Table: 250 - 1600 depending on column types
Maximum Indexes per Table: Unlimited
No embedded server
ARCHITECTURE
Shared Disk Replication
Async Streaming Replication
Sync Streaming Replication
LIMITATION
No automatic failover
No load balancing
No write replication
No horizontal scalability
PostgreSQL
+
Pgpool-II

FEATURE
Connection Pooling
Replication
Load Balance
Limiting Exceeding Connections
Parallel Query
Online Recovery
ARCHITECTURE
LIMITATION
Windows is not supported
IPv6 for IP address/mask is not supported
REPLICATION MODE
STREAMING REPLICATION MODE
WHAT WE MISS?
Single Point Failure!
ARCHITECTURE
WATCHDOG
Life checking of pgpool service
Mutual monitoring of watchdog processes
Changing active/standby state in case of certain faults detected
Automatic virtual IP address assigning synchronous to server switching
Automatic registration of a server as standby in recovery
ENVIRONMENT
192.168.0.30: pgpool-II, pgpoolAdmin
192.168.0.31: PostgreSQL master
192.168.0.32: PostgreSQL master

TRY IT!!
1. Start pgpool-II with 1 PostgreSQL
2. Attach another PostgreSQL
3. Insert data and check both PostgreSQL
4. Kill one PostgreSQL
5. Insert data
6. Recover the failed PostgreSQL
7. Check the data recovery
FOLLOW UP
Data Access Frameworks
Spring Data JPA + EclipseLink + PostgreSQL Cluster

Missing Gadgets
PostgreSQL cluster deployment tool
Schema/Data migration plan
Periodically backup plan
Online Recovery Steps
1. CHECKPOINT
2. First stage of online recover
3. Wait until all clients have disconnected
4. CHECKPOINT
5. Second stage of online recovery
6. Start up postmaster
7. Node attach
Full transcript