So finally PostgreSQL 9.4 has been released. This version introduces a new schema less type, the jsonb which joins the other fantastic features abused and misused by the developers all around the globe.

The DBA improvements in this release is much more interesting and can push seriously the adoption in the enterprises of this DBMS.

Alter system

Finally is possible to change the postgresql.conf settings using the ALTER SYSTEM SET command. The cool part is the values are validated when the command is issued. No more hosed clusters by typos. Kudos!

The book is now complete. There’s still a lot to do for reviewing the writing and fixing the bad grammar. Anyway is about 107 pages and I’m pretty satisfied. I’ve worked on this document for 4 months in my spare time and I became more confident with my English during the  writing.

So, what’s next? I’ll spend the next couple of weeks reviewing and fixing the book. After that I’ll put the pdf  on lulu.com free for download and in hardcopy. The latter will have a production/shipping cost of course. I’ll also put the mobi version on amazon for kindle distribution. Unfortunately amazon doesn’t allow free books. I’ll put the minimum price possible 0.99$. Anyway if anybody wants the mobi for free just give me a shout.

 This is almost the entire chapter 11. I’m still writing the final section, I’d like to put into a separate post though. I’ve also almost finished the restore’s performance. After this the book is complete. I will start a review to make it a decent writing before publishing onto lulu.com and amazon kindle.

I’m not sure amazon permits to sell books for free I’ll find a solution anyway.

A couple of things to know before start coding…

This chapter is completely different from the rest of the book. It’s dedicated to the developers. PostgreSQL is a fantastic infrastructure for building powerful and scalable applications. In order to use al its potential there are some things to consider. In particular if coming from other DBMS there are subtle caveats that can make the difference between a magnificent success or a miserable failure.

I’ve started the sixth chapter, the one on the data integrity I’ve forgotten. There are the first two parts alongside with the introduction. I’ve also updated the book on slideshare with the new cover and the last incomplete chapter for the developers. The beautiful cover is made by Chiaretta & Bon. Kudos and many thanks.

I’ve also uploaded the latex sources on github for anybody to fork and review my crappy english. My former colleague and friend Craig Barnes already started reviewing the tex files, many thanks for the priceless help.

Here’s the github repository url : https://github.com/the4thdoctor/pgdba_books

Data integrity

There’s just one thing worse than losing the database. Having the data set full of rubbish. The data integrity has been part of PostgreSQL since the beginning. It offers various levels of strength ensuring the data is clean and consistent. In this chapter we’ll have a brief look to the various constraints available. The PostgreSQL’s constraints can be grouped in two kind. The table constraints and the column constraints. The table constraints are defined on the table’s definition after the field’s list. The column constraints appear in the field’s definition after the data type. Usually for the primary keys and the unique keys the definition is written as table constraint.
The constraint applies the enforcement to any table’s row without exclusion. When creating a table constraint on a fully populated table the data is validated first. Any validation error aborts the constraint creation. However, the foreign keys and check constraints accept the clause NOT VALID. With this clause the database assumes the data is valid and skips the validation. The cration is almost immediate. The new constraint is then enforced only for the new data. When using this option the data must be consistent.