PostgreSQL: Up and Running

by Regina O. Obe, Leo Hsu

If you’re thinking about migrating to the PostgreSQL open source database system, this guide provides a concise overview to help you quickly understand and use PostgreSQL’s unique features. Not only will you learn about the enterprise class features in the 9.2 release, you’ll also discover that PostgeSQL is more than just a database system—it’s also an impressive application platform.

With numerous examples throughout this book, you’ll learn how to achieve tasks that are difficult or impossible in other databases. If you’re an existing PostgreSQL user, you’ll pick up gems you may have missed along the way.


Learn basic administration tasks, such as role management, database creation, backup, and restore
Apply the psql command-line utility and the pgAdmin graphical administration tool
Explore PostgreSQL tables, constraints, and indexes
Learn powerful SQL constructs not generally found in other databases
Use several different languages to write database functions
Tune your queries to run as fast as your hardware will allow
Query external and variegated data sources with Foreign Data Wrappers
Learn how to replicate data, using built-in replication features

Genres: Programming, Computer Science, Nonfiction, Technology, Software, Coding, Technical

166 pages, Paperback

First published January 1, 2012

Reviews

[Sebastian Gebski · Rating 4 out of 5]

Very useful.

Doesn't start with (re-)introducing SQL standard, focusing on PostgreSQL specifics instead. Covers specific topics like table inheritance or FDW. 3rd edition (the one I've read) is up-to-date with PostgreSQL 10 - which is really nice. All (well, can't guarantee that ...) Postgres-specific syntax enhancements are also described.

What did I miss then? Each DB has it's own implementation quirks - ones you should know to be able to truly optimize now only your queries, but also your operations. One related to large data migrations, conversions, etc. Such elements are barely mentioned in this book - I find it a significant drawback.

Anyway, it's truly decent book if you (like myself) have worked a lot with different RDBMSes & now you're (for whatever reason) moving to PostgreSQL.

[Louis · Rating 4 out of 5]

Compared to most books that introduce a full featured relational database management system, PostgreSQL: Up and Running is surprisingly short. It achieves this by being focused on its purpose, and doing it well. It is aimed at someone who knows a little bit about databases, although not necessarily a full featured client-server one, and brings them up to speed on both database server administration in general and PostgreSQL administration in general. It continues to introduce you to features that makes PostgreSQL special, making this a very valuable book to someone coming in to PostgreSQL from some other database, whether that database is simpler or a peer competitor. And since my background is from simpler databases, it works for someone like me.

First, this is not a book for someone who knows nothing about databases. Things such as database design, basic database concepts, and SQL is not covered, with the assumption that you get this knowledge somewhere else before coming here. (In my case, I learned on MS Access and SQLite) So what someone with this background may not have is how to work with a server based database management system.

This also has many sections that discuss some special features of PostgreSQL. The one that I thought was most interesting was the fact that you could write functions in one of many languages. Since I do much of my technical programming in Python, seeing how it is done was intriguing. (I've known that PostgreSQL has PL/Python, but the descriptions that I had seen in the past were obtuse and I had no idea how it would work.)

But this book also knows where to stop. Once I understand a topic to a base level, I'm used to looking to the web to learn more. And the authors consider the PostgreSQL Online Journal as an extension of the book, introducing an advanced topic, then pointing the reader to a quality source as a step in learning more. One advantage of this is that it keeps this book from becoming an intimidating door stopper.

In short, PostgreSQL: Up and Running has decided it will serve as a market that are not complete neophytes, but only bring people up to the point where they are better served by a rich set of advanced resources available on the web. And since this is probably a pretty good description of me, I found this to be a very good resource.

Note: I received a free electronic copy of PostgreSQL: Up and Running as part of the OReilly Blogger Program

[Kaido · Rating 5 out of 5]

At the beginning, when I saw that this book was just 164 pages, I thought that "Ok, you can't say anything special with so few pages" but in the end I was facinated how well this technical book was writen. The authors really wanted to give you something new in every chapter. Like in Data Types chapter, they didn't go this old number-varchar-date way, they provided some useful and tricky types and exaples where and how to use them.

I also liked that exaples were designed for both windows and linux users.

With this book you can surely get your postgre DB up and running and even more.

[Arthur · Rating 5 out of 5]

Like I have already mentioned in my previous blog post databases are lately in the spotlight, left and right. This was the primary reason for me to choose yet another book on databases for review*. I know that NoSQL data stores are more trendy for now, but the traditional RDBMS' would not give its sheer install base out quite easily like that yet to them. The secondary reason was, while I am a full time in SQL Server, I suspected I may be missing something by not getting familiar with what most IT pros may state competition.

Indeed, having a backdoor or more correctly a mechanism to allowing custom extensions (called add-ons from PG 9.1) to be baked into the database engine allow taking PostgreSQL to new heights without going through costly upgrades. One of the intersting ones (at least to me) is the key-value store called HStore http://tinyurl.com/7ebg3nw. Just for reference, starting SQL Server 2014 the In-Memory engine is part of the core database. Did I mention the RDMBS' don't give up just yet?

The book mentions so many different versions of PostgreSQL so many times at time my head was spinning trying to recall what is used in what version or different. After finished reading the book I started to suspect it would be better to for the author to concentrate on the latest version because the previous builds are so different. Overall, I fail to grasp what was the main objective of this book. The material coverage is sparse or not in depth, of course as a result the book is quite short, and you can always buy another book or solicit various forums or IRC chats.

Well, the book has answered my primary and secondary interests, and seems that I am not the biggest fan of the PostgreSQL as a database engine yet. Why? This is probably because I am too spoiled by the SQL Server install and forget way of operating. Me, as a database developer and DBA needing to restart the database after a simple security file modification or setting the memory via SHMMAX or threads for multiple backup restores make me chuckle.

However, PostgreSQL has many advantages, I admit, too. What I liked is the ability to backup a single table or have backups restorable to any version of the database engine is a big plus. Not to mention triggers on views, unlogged tables or exclusion constraints. Read the book to know a lot more.

When it comes to the book itself, Regina and Leo did a fantastic job, they know the product really well, 5 out of 5 is my mark.

*Disclaimer: I received this book for free in exchange for a review as part of the O'Reilly Reader Review Program.

[Anton Antonov · Rating 4 out of 5]

The best PostgresSQL book to date. I was reading it this year in early Q2 and just now I remembered that I forgot to check it in goodreads.

Either way the book does not offer more than you can read in the PostgresSQL docs, but it's way more accessible. I'm not a fan of the PostgresSQL documentation, in fact I find it extremely confusing and hard to read. But through the PostgresSQL: Up and Running book I undestood a lot of new concepts about Postgres between versions 9.1 - 9.4.

The psql chapter is the one that captured me the most. While I am not the most vivid user of the CLI tool, I find it useful to know some power commands specific to Postgres outside of SQL syntax.


I definitely recommend the book to anyone working with Postgres, the de facto standard relational database!

[Ulas Tuerkmen · Rating 3 out of 5]

The idea for this book is in fact rather good: A quick reminder of features that set PostgreSQL apart and how one can start using them quickly. This is also achieved to a decent extent. PostgreSQL is an extremely versatile and complete database, with lots of features the average programmer does not know about. This causes many developers to resort to hacky alternative methods (such as writing slow code to achieve what PostgreSQL does natively and faster), or to other persistent stores that tout their horn louder (*cough*, nosql,*cough*). A quick skim of this book would help them achieve the same functionality without committing to maintenance work or less feature-rich platforms.

In the first few chapters of the book, the authors explain the base cases and useful deviations of simple functionality such as getting PostgreSQL to run with different settings, making backups and restoring from them, monitoring and terminating database activity,etc. An important feature of the book is that the authors, due to their experiences with managing bigger clusters, always mention options and alternatives that might be useful when one is working with a large database that is live. The feature-rich commandline client psql, a major feature of PostgreSQL, and its visual companion pgAdmin, both of these get their own chapters with decent treatments.

The rest of the book deals with useful extensions of PostgreSQL to the SQL standard, such as different kinds of indices, constraints, views,window functions, and common table expressions. It is really astonishing how many powerful features are packed into PostgreSQL,such as functional indices, which allow the user to define an index on a function of a column, or recursive CTEs through which the user can traverse trees through simple relations. Recursive CTEs are not available in any other DBMSs, by the way. The authors do a good job of giving a foretaste of what these features can accomplish, and how they set PostgreSQL apart from other DBMSs, including proprietary ones. The last two chapters deal with topics very important for people who want to use PostgreSQL in challenging live setups: Optimization and replication. Instead of detailed guidelines, the reader is presented with initial solutions and ways to gather more information on which directions can be taken.

Despite doing a good job of whetting apetite for PostgreSQL, and giving important pointers, the book has serious flaws, and leaves a lot to be desired. First of all, there are big gaps of information in the text on certain definitely interesting topics, and the reader is simply pointed to either PostgreSQL documentation, or a blog post written by the authors somewhere else. An example is the treatment of custom types, where the creation of operators on these types is simply omitted, although it would be just another page or so (p.72). Providing at least the gist of the referred posts and documentation pages would have been rather beneficial. The authors sometimes use incredibly weird and maybe even nonexistent words. Effectuate, turducken? Why do I have to look up a word that essentially means the same thing with a widely known word with just four extra letters? In one instance, the authors refer to someone as a "data entry dude", which might be OK in a blog, but not in an O'Reilly book. The most annoying shortcoming for me though was the fact that half of the book used the US census data in database form for sample queries, but this database is not provided by the authors for trying things out. If you Google for it, there are some howtos on how to dump the census data into a database, but none of these are as simple as restoring a dump or running a sql file. I expect from the authors of a technical book to provide the sample data they use so that the users can do some experimentation and try other things than what the authors have written.

[Antoni Heba · Rating 3 out of 5]

A decent primer on PostgreSQL for people that know databases and SQL, but more of a choice of features deemed interesting by the authors than a structured guide. I found the style is too casual by moments and noticed that some errors have slipped through.

The authors mostly deliver on the promise made early in the book: to teach the key points of PostgreSQL. I got a good walk through the features that make it a relational database, with a stress on its original features. The authors are unapologetic fans of PostgreSQL, they don't hide it, and it certainly helps them to sell the reader on PostgreSQL's level of ANSI compliance, its customizability and the richness of its features.

On the other hand, this book contains some inaccuracies, most of them due to changes in newer PostgreSQL versions. The treatment of auto-increment as something expected in a database is annoying. I also find that too much space is wasted on printscreens describing PgAdmin features. This would be better allocated to covering other basic features, like the truncate vs delete distinction.

[Federico · Rating 4 out of 5]

A nice brief summary of things you should know if you're using PostgreSQL (like backups and ANSI-SQL compatibility), as well as a few which you might never have thought about and perhaps shouldn't (like running Python scripts and XPath queries directly in your SQL queries). Most of this has not changed after version 9. I was surprised by the amount of space given to Windows and Oracle users, but I guess they exist too. Some copyediting to reduce typos would be welcome.

If you're trying to decide *whether* to use PostgreSQL in the first place, this book won't help. All it has to say is "look at all those features!".

[Erlend · Rating 4 out of 5]

I have been using PostgreSQL for years, but only learned things as I went. I read this book to get an overview of modern PostrgreSQL usage and to fill gaps in my knowledge, and for that it worked quite well. The only negative thing is that quite a lot of the book is really just summarizing documentation without really adding anything to it.

[Vitali · Rating 3 out of 5]

book trying to show the distinguishing features of postgres, but do that on 300 pages only so it's more of overview and listing features instead of explaining them. short examples are enough for overview, for understanding and deeper dive need to google yourself

[Kev · Rating 5 out of 5]

Great primer.

[rahman]

ظنپذپز

[ZeV · Rating 4 out of 5]

As a quick introduction to the wide array of features offered by PostgreSQL, PostgreSQL: Up and Running strikes the right balance of being practical as well as informative in a referential way. For the former, the author introduces and walks users through by steps using minimal examples of basic yet essential operations. For the latter, the author has a tendency to reference and delegate more to outside resources like external web-site articles. This is better than leaving the readers only with skin-deep understanding without pointers for further self-learning, but the website links will inevitably be dead sooner than later. PostgreSQL is on a quick and active development cycle, and as such a book like this does not age well unless updated frequently. At the time of my reading, Version 10 was already a bit behind, but there is a plenty of ageless materials in the book. For users who want a quick exposure to what PostgreSQL offers relative to other relational databases, I found the book to be effective. Look for the most up-to-date edition, if available.

[Suvash Thapaliya · Rating 4 out of 5]

amazed how well packed this book is, though I skipped admin related chapters and mostly skimmed through the rest. will come back to this as a reference for postgres specific information.

Also, postgres is a beast.

[Leandro López · Rating 4 out of 5]

There's a waste of space with a whole chapter to an external tool, pgAdmin. I understand it might make things easier, but it seems like the authors made a profit every time they mention it.

The chapter on replication is a bit disappointing.

[Katjp · Rating 3 out of 5]

Useful book for anyone who has used postgres before and wants to improve their efficiency.

[Ney Guerrero · Rating 5 out of 5]

This book is super clear, to the point and offers an intro to all Postgres niceties, is great for beginners in Postgres but not for beginners in Databases.

[Seto Elkahfi · Rating 5 out of 5]

A brief-yet-complete introduction to the most advance database system.

[David · Rating 4 out of 5]

Nice intro to key features of PostgreSQL.

[Henrik Lindberg · Rating 4 out of 5]

Clear, concise and inspiring.

[Ivaylo · Rating 4 out of 5]

A good guide for experienced db developers.