PostgreSQL 9.1入门与实战教程

5星 · 超过95%的资源需积分: 9 144 浏览量更新于2024-07-27 收藏 5.94MB PDF 举报

《PostgreSQL：上手与运行》是由Regina Obe和Leo Hsu合著的一本详细教程，专为PostgreSQL 9.1提供全面的指南。该书作为最新版，旨在帮助读者快速理解和掌握这个强大的开源对象关系数据库管理系统（Object-Relational Database Management System, ORDBMS）。PostgreSQL以其稳定性、可扩展性和高级功能而闻名，适用于多种应用场景，包括企业级应用、大数据处理和云计算环境。书中内容覆盖了从安装和配置到日常管理、数据模型设计、SQL查询优化、事务处理、并发控制、备份恢复以及高级特性如视图、触发器和存储过程的深入讲解。对于初学者来说，它提供了实用的基础知识，同时对经验丰富的开发人员也具有参考价值，因为它包含了许多最佳实践和性能优化技巧。作者 Regina Obe 和 Leo Hsu 通过清晰的语言和易于理解的示例，确保读者能够有效地学习和应用PostgreSQL。此外，该书还包含了在线资源链接，如Wow!eBook网站，便于读者获取更多更新和支持。版权方面，所有权利由 Regina Obe 和 Leo Hsu 保留，本书是根据美国法律出版的，由 O'Reilly Media, Inc. 出版，地址位于加利福尼亚州塞巴斯托波尔。O'Reilly图书适用于教育、商业或销售推广用途，且电子版也在其官方网站上提供。对于更多信息，读者可以通过电话或电子邮件联系公司的企业/机构销售部门。本书的第一版修订历史记录显示，该版本于2012年7月2日首次发布，并提供了错误报告的链接，以便用户查找并报告任何已知问题。O'Reilly Media 的标志性元素，如Nutshell Handbook、Nutshell Handbook logo 和 O'Reilly logo，均为O'Reilly Media, Inc. 的注册商标。《PostgreSQL：上手与运行》是一本既适合新手入门又适合专业开发人员深入学习的实用指南，无论是在个人自学还是团队培训中，都是提升PostgreSQL技能的理想资源。

How to Contact Us

Please address comments and questions concerning this book to the publisher:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

800-998-9938 (in the United States or Canada)

707-829-0515 (international or local)

707-829-0104 (fax)

We have a web page for this book, where we list errata, examples, and any additional

information. You can access this page at:

http://bit.ly/PostgreSQL_UR

To comment or ask technical questions about this book, send email to:

bookquestions@oreilly.com

For more information about our books, courses, conferences, and news, see our website

at http://www.oreilly.com.

Find us on Facebook: http://facebook.com/oreilly

Watch us on YouTube: http://www.youtube.com/oreillymedia

xiv | Preface

All the aforementioned are proprietary, closed source forks. tPostgres and Postgres-

XC are two budding forks that we find interesting with open source licensing. tPostgres

braches off PostgreSQL 9.2 and targets Microsoft SQL Server users. For instance, with

tPostgres, you can write functions using T-SQL. Postgres-XC is a cluster server pro-

viding write-scalable, synchronous multi-master replication. What makes Postgres-XC

special is that it supports distributed processing and replication. It is now at version 1.0.

Administration Tools

There are three popular tools for managing PostgreSQL and these are supported by

PostgreSQL core developers; they tend to stay in synch with PostgreSQL versions. In

addition, there are plenty of commercial offerings as well.

psql

psql is a command-line interface for writing queries and managing PostgreSQL. It

comes packaged with some nice extras, such as an import and export commands

for delimited files, and a reporting feature that can generate HTML output. psql

has been around since the beginning of PostgreSQL and is a favorite of hardcore

PostgreSQL users. Newer converts who are more comfortable with GUI tools tend

to favor pgAdmin.

pgAdmin

This is the widely used, free, graphical administration tool for PostgreSQL. You

can download it separately from PostgreSQL. pgAdmin runs on the desktop and

can connect to multiple PostgreSQL servers regardless of version or OS. Even if

you have your database server on a window-less Unix-based server, install pgAd-

min and you’ll find yourself armed with a fantastic GUI. pgAdmin is pictured in

Figure 1-1.

Some installers, such as those offered by EnterpriseDB, package pgAdmin with the

database server install. If you’re unfamiliar with PostgreSQL, you should definitely

start with pgAdmin. You’ll get a great overview and gain an appreciation of the

richness of PostgreSQL just by exploring all the database objects in the main in-

terface. If you’re coming from SQL Server and used Management Studio, you’ll

feel right at home.

PHPPgAdmin

PHPPgAdmin, pictured in Figure 1-2, is a free, web-based administration tool pat-

terned after the popular PHPMyAdmin for MySQL. PostgreSQL has many more

kinds of database objects than MySQL, as such PHPPgAdmin is a step up from

PHPMyAdmin with additions to manage schemas, procedural languages, casts,

operators, and so on. If you’ve used PHPMyAdmin, you’ll find PHPPgAdmin to

be nearly identical.

2 | Chapter 1: The Basics

Why Upgrade?

If you’re using PostgreSQL 8.2 or below: upgrade now! Enough said.

If you’re using PostgreSQL 8.3: upgrade soon! 8.3 will be reaching end-of-life in early

2013. Details about PostgreSQL EOL policy can be found here: PostgreSQL Release

Support Policy. EOL is not a place you want to be. New security updates and fixes to

serious bugs will no longer be available. You’ll need to hire specialized PostgreSQL core

consultants to patch problems or to implement workarounds—probably not a cheap

proposition, assuming you can even locate someone to begin with.

Regardless of which version you are using, you should always try to run the latest micro-

versions for your version. An upgrade from say 8.4.8 to 8.4.11 requires just binary file

replacement, which can be generally done with a quick restart after installing the up-

grade. Only bug fixes are introduced in micro-versions, so there’s little cause for con-

cern and can in fact save you grief.

What to Look for in PostgreSQL 9.2

At time of writing, PostgreSQL 9.1 is the latest stable release, and 9.2 is waiting in the

wings to strut its stuff. All of the anticipated features in 9.2 are already set in stone and

available in the 9.2 beta release. The following list discusses the most notable features:

• Index-only scans. If you need to retrieve only columns that are already a part of an

index, PostgreSQL will skip the need to go to the table. You’ll see significant speed

improvement in these queries as well as aggregates such as COUNT(*).

• Sorting improvements that improve in-memory sort operations by as much as 20%.

• Improvements in prepared statements. A prepared statement is now parsed, ana-

lyzed, and rewritten, but not necessarily planned. It can also produce custom saved

plans of a given prepared statement which are dependent on argument inputs. This

reduces the chance that a prepared statement will perform worse than an equivalent

ad-hoc query.

• Cascading streaming replication supports streaming from a slave to another slave.

• SP-GiST, another advance in GiST index technology using space filling trees. This

should have great impact on the various extensions that rely on GiST for speed.

• ALTER TABLE IF EXISTS syntax for making changes to tables.

• Many new variants of ALTER TABLE ALTER TYPE commands that used to require

whole table rewrites and rebuild of indexes. (More details are available at More

Alter Table Alter Types.)

• Even more pg_dump and pg_restore options. (Read our article at 9.2 pg_dump

Enhancements.)

• plv8js is a new language handler that allows you to create functions in JavaScript.

4 | Chapter 1: The Basics

剩余163页未读，继续阅读

wadr52013145

粉丝: 0
资源: 6

PostgreSQL 9.1入门与实战教程

PostgreSQL: Up and Running, 3rd Edition

Obe & Hsu, PostgreSQL：Up and Running 3rd 原版PDF

PostgreSQL Up and Running, 3rd Edition

PostgreSQL Up and Running(3rd) epub

PostgreSQL Up and Running(3rd) mobi

PostgreSQL Up and Running(2nd) 无水印pdf

PostgreSQL Up and Running(3rd) 无水印原版pdf

PostgreSQL Up and Running(3rd)(EarlyRelease) 无水印pdf

PostgreSQL Up and Running A Practical Guide to the Advanced Open Source 无水印pdf

[PostgreSQL：Up.and.Running(2012.7)].Regina.Obe.文字版

最新资源