Greenplum
®
Database 4.2
Administrator Guide
P/N: 300-013-163
Rev: A01
The Data Computing Division of EMC
Copyright © 2011 EMC Corporation. All rights reserved.
EMC believes the information in this publication is accurate as of its publication date. The information is subject to
change without notice.
THE INFORMATION IN THIS PUBLICATION IS PROVIDED “AS IS.” EMC CORPORATION MAKES NO REPRESENTATIONS
OR WARRANTIES OF ANY KIND WITH RESPECT TO THE INFORMATION IN THIS PUBLICATION, AND SPECIFICALLY
DISCLAIMS IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.
Use, copying, and distribution of any EMC software described in this publication requires an applicable software
license.
For the most up-to-date listing of EMC product names, see EMC Corporation Trademarks on EMC.com
All other trademarks used herein are the property of their respective owners.
Table of Contents iii
Greenplum Database Administrator Guide 4.2 - Contents
Greenplum Database Administrator Guide 4.2 - Contents
Preface ............................................................................................... 1
About This Guide.............................................................................. 1
Document Conventions .................................................................... 2
Text Conventions........................................................................ 2
Command Syntax Conventions ................................................... 3
Getting Support ............................................................................... 3
Product information .................................................................... 3
Technical support ....................................................................... 4
Section I: Introduction to Greenplum
Chapter 1: About the Greenplum Architecture........................ 6
About the Greenplum Master............................................................ 7
About the Greenplum Segments....................................................... 7
About the Greenplum Interconnect .................................................. 7
About Redundancy and Failover in Greenplum Database .................. 8
About Segment Mirroring............................................................ 8
About Master Mirroring ............................................................... 9
About Interconnect Redundancy ................................................. 9
About Parallel Data Loading ............................................................10
About Management and Monitoring .................................................10
Chapter 2: About Distributed Databases .................................12
Understanding How Data is Stored..................................................12
Understanding Greenplum Distribution Policies................................13
Chapter 3: Summary of Greenplum Features .........................14
Greenplum SQL Standard Conformance ..........................................14
Core SQL Conformance..............................................................14
SQL 1992 Conformance .............................................................15
SQL 1999 Conformance .............................................................16
SQL 2003 Conformance .............................................................16
SQL 2008 Conformance .............................................................17
Greenplum and PostgreSQL Compatibility .......................................18
Chapter 4: About Greenplum Query Processing ....................25
Understanding Query Planning and Dispatch ...................................25
Understanding Greenplum Query Plans ...........................................26
Understanding Parallel Query Execution ..........................................27
Section II: Access Control and Security
Chapter 5: Managing Roles and Privileges ..............................30
Security Best Practices for Roles and Privileges...............................30
Creating New Roles (Users).............................................................31
Altering Role Attributes..............................................................31
Creating Groups (Role Membership)................................................32
Managing Object Privileges .............................................................33
Simulating Row and Column Level Access Control .....................34
Encrypting Data ..............................................................................34
Time-based Authentication..............................................................35
Table of Contents iv
Greenplum Database Administrator Guide 4.2 - Contents
Required Permissions.................................................................35
How to Add Time-based Constraints ..........................................35
Chapter 6: Configuring Client Authentication.........................39
Allowing Connections to Greenplum Database.................................39
Editing the pg_hba.conf File.......................................................40
Limiting Concurrent Connections .....................................................41
Encrypting Client/Server Connections .............................................42
Chapter 7: Accessing the Database...........................................44
Establishing a Database Session .....................................................44
Supported Client Applications..........................................................45
Greenplum Database Client Applications....................................46
pgAdmin III for Greenplum Database ........................................47
Database Application Interfaces.................................................50
Third-Party Client Tools .............................................................51
Troubleshooting Connection Problems .............................................52
Chapter 8: Managing Workload and Resources .....................53
Overview of Greenplum Workload Management ..............................53
How Resource Queues Work in Greenplum Database.................53
Steps to Enable Workload Management .....................................57
Configuring Workload Management .................................................58
Creating Resource Queues ..............................................................59
Creating Queues with an Active Query Limit ..............................59
Creating Queues with Memory Limits.........................................60
Creating Queues with a Query Planner Cost Limits ....................60
Setting Priority Levels................................................................61
Assigning Roles (Users) to a Resource Queue..................................62
Removing a Role from a Resource Queue ..................................62
Modifying Resource Queues.............................................................62
Altering a Resource Queue.........................................................63
Dropping a Resource Queue ......................................................63
Checking Resource Queue Status ....................................................63
Viewing Queued Statements and Resource Queue Status ..........64
Viewing Resource Queue Statistics ............................................64
Viewing the Roles Assigned to a Resource Queue ......................64
Viewing the Waiting Queries for a Resource Queue....................65
Clearing a Waiting Statement From a Resource Queue ..............65
Viewing the Priority of Active Statements ..................................66
Resetting the Priority of an Active Statement.............................66
Section III: Database Administration
Chapter 9: Defining Database Objects......................................68
Creating and Managing Databases ..................................................68
About Template Databases ........................................................68
Creating a Database ..................................................................68
Viewing the List of Databases ....................................................69
Altering a Database ...................................................................69
Dropping a Database .................................................................69
Creating and Managing Tablespaces................................................70
Table of Contents v
Greenplum Database Administrator Guide 4.2 - Contents
Creating a Filespace...................................................................70
Creating a Tablespace ...............................................................71
Using a Tablespace to Store Database Objects ..........................71
Viewing Existing Tablespaces and Filespaces .............................72
Dropping Tablespaces and Filespaces ........................................72
Creating and Managing Schemas.....................................................72
The Default ‘Public’ Schema.......................................................73
Creating a Schema ....................................................................73
Schema Search Paths ................................................................73
Dropping a Schema ...................................................................74
System Schemas .......................................................................74
Creating and Managing Tables ........................................................75
Creating a Table ........................................................................75
Altering a Table .........................................................................82
Dropping a Table .......................................................................83
Partitioning Large Tables.................................................................83
Understanding Table Partitioning in Greenplum Database..........84
Deciding on a Table Partitioning Strategy ..................................85
Creating Partitioned Tables........................................................85
Loading Partitioned Tables .........................................................89
Verifying Your Partition Strategy................................................89
Viewing Your Partition Design ....................................................90
Maintaining Partitioned Tables ...................................................90
Creating and Using Sequences ........................................................94
Creating a Sequence..................................................................94
Using a Sequence ......................................................................95
Altering a Sequence...................................................................95
Dropping a Sequence.................................................................95
Using Indexes in Greenplum Database ............................................95
Index Types...............................................................................97
Creating an Index......................................................................99
Examining Index Usage .............................................................99
Managing Indexes ...................................................................100
Dropping an Index...................................................................100
Creating and Managing Views........................................................100
Creating Views.........................................................................100
Dropping Views........................................................................100
Chapter 10: Managing Data.......................................................101
About Concurrency Control in Greenplum Database ...................... 101
Inserting New Rows ......................................................................102
Updating Existing Rows .................................................................103
Deleting Rows ............................................................................... 103
Truncating a Table ...................................................................104
Working With Transactions............................................................104
Transaction Isolation Levels.....................................................104
Vacuuming the Database ..............................................................105
Configuring the Free Space Map ..............................................105
- 1
- 2
前往页