postgresql-9.5.0

[Fernando de Oliveira]

​http://ftp.postgresql.org/pub/source/v9.5.0/postgresql-9.5.0.tar.bz2

​http://ftp.postgresql.org/pub/source/v9.5.0/postgresql-9.5.0.tar.bz2.md5

e58fffe9359e311ead94490a06b7147c postgresql-9.5.0.tar.bz2

​http://www.postgresql.org/about/news/1636/

PostgreSQL 9.5: UPSERT, Row Level Security, and Big Data
Posted on Jan. 7, 2016

7 JANUARY 2016: The PostgreSQL Global Development Group announces the
release of PostgreSQL 9.5. This release adds UPSERT capability, Row
Level Security, and multiple Big Data features, which will broaden the
user base for the world's most advanced database. With these new
capabilities, PostgreSQL will be the best choice for even more
applications for startups, large corporations, and government agencies.

...

UPSERT

A most-requested feature by application developers for several years,
"UPSERT" is shorthand for "INSERT, ON CONFLICT UPDATE", allowing new and
updated rows to be treated the same. UPSERT simplifies web and mobile
application development by enabling the database to handle conflicts
between concurrent data changes. This feature also removes the last
significant barrier to migrating legacy MySQL applications to
PostgreSQL.

Developed over the last two years by Heroku programmer Peter Geoghegan,
PostgreSQL's implementation of UPSERT is significantly more flexible and
powerful than those offered by other relational databases. The new ON
CONFLICT clause permits ignoring the new data, or updating different
columns or relations in ways which will support complex ETL (Extract,
Transform, Load) toolchains for bulk data loading. And, like all of
PostgreSQL, it is designed to be absolutely concurrency-safe and to
integrate with all other PostgreSQL features, including Logical
Replication.

Row Level Security

PostgreSQL continues to expand database security capabilities with its
new Row Level Security (RLS) feature. RLS implements true per-row and
per-column data access control which integrates with external
label-based security stacks such as SE Linux. PostgreSQL is already
known as "the most secure by default." RLS cements its position as the
best choice for applications with strong data security requirements,
such as compliance with PCI, the European Data Protection Directive, and
healthcare data protection standards.

RLS is the culmination of five years of security features added to
PostgreSQL, including extensive work by KaiGai Kohei of NEC, Stephen
Frost of Crunchy Data, and Dean Rasheed. Through it, database
administrators can set security "policies" which filter which rows
particular users are allowed to update or view. Data security
implemented this way is resistant to SQL injection exploits and other
application-level security holes.

Big Data Features

PostgreSQL 9.5 includes multiple new features for bigger databases, and
for integrating with other Big Data systems. These features ensure that
PostgreSQL continues to have a strong role in the rapidly growing open
source Big Data marketplace. Among them are:

BRIN Indexing: This new type of index supports creating tiny, but
effective indexes for very large, "naturally ordered" tables. For
example, tables containing logging data with billions of rows could be
indexed and searched in 5% of the time required by standard BTree
indexes.

Faster Sorts: PostgreSQL now sorts text and NUMERIC data faster, using
an algorithm called "abbreviated keys". This makes some queries which
need to sort large amounts of data 2X to 12X faster, and can speed up
index creation by 20X.

CUBE, ROLLUP and GROUPING SETS: These new standard SQL clauses let users
produce reports with multiple levels of summarization in one query
instead of requiring several. CUBE will also enable tightly integrating
PostgreSQL with more Online Analytic Processing (OLAP) reporting tools
such as Tableau.

Foreign Data Wrappers (FDWs): These already allow using PostgreSQL as a
query engine for other Big Data systems such as Hadoop and Cassandra.
Version 9.5 adds IMPORT FOREIGN SCHEMA and JOIN pushdown making query
connections to external databases both easier to set up and more
efficient.

TABLESAMPLE: This SQL clause allows grabbing a quick statistical sample
of huge tables, without the need for expensive sorting.

"The new BRIN index in PostgreSQL 9.5 is a powerful new feature which
enables PostgreSQL to manage and index volumes of data that were
impractical or impossible in the past. It allows scalability of data and
performance beyond what was considered previously attainable with
traditional relational databases and makes PostgreSQL a perfect solution
for Big Data analytics," said Boyan Botev, Lead Database Administrator,
Premier, Inc.

​http://www.postgresql.org/docs/current/static/release-9-5.html

E.1. Release 9.5

    Release Date: 2016-01-07

E.1.1. Overview

Major enhancements in PostgreSQL 9.5 include:

   • Allow INSERTs that would generate constraint conflicts to be turned
     into UPDATEs or ignored
   • Add GROUP BY analysis features GROUPING SETS, CUBE and ROLLUP
   • Add row-level security control
   • Create mechanisms for tracking the progress of replication,
     including methods for identifying the origin of individual changes
     during logical replication
   • Add Block Range Indexes (BRIN)
   • Substantial performance improvements for sorting
   • Substantial performance improvements for multi-CPU machines The
     above items are explained in more detail in the sections below.

E.1.2. Migration to Version 9.5

A dump/restore using pg_dumpall, or use of pg_upgrade, is required for
those wishing to migrate data from any previous release.

Version 9.5 contains a number of changes that may affect compatibility
with previous releases. Observe the following incompatibilities:

   • Adjust operator precedence to match the SQL standard (Tom Lane)

     The precedence of <=, >= and <> has been reduced to match that of
     <, > and =. The precedence of IS tests (e.g., x IS NULL) has been
     reduced to be just below these six comparison operators. Also,
     multi-keyword operators beginning with NOT now have the precedence
     of their base operator (for example, NOT BETWEEN now has the same
     precedence as BETWEEN) whereas before they had inconsistent
     precedence, behaving like NOT with respect to their left operand
     but like their base operator with respect to their right operand.
     The new configuration parameter operator_precedence_warning can be
     enabled to warn about queries in which these precedence changes
     result in different parsing choices.

   • Change pg_ctl's default shutdown mode from smart to fast (Bruce
     Momjian)

     This means the default behavior will be to forcibly cancel existing
     database sessions, not simply wait for them to exit.

   • Use assignment cast behavior for data type conversions in PL/pgSQL
     assignments, rather than converting to and from text (Tom Lane)

     This change causes conversions of Booleans to strings to produce
     true or false, not t or f. Other type conversions may succeed in
     more cases than before; for example, assigning a numeric value 3.9
     to an integer variable will now assign 4 rather than failing. If no
     assignment-grade cast is defined for the particular source and
     destination types, PL/pgSQL will fall back to its old I/O
     conversion behavior.

   • Allow characters in server command-line options to be escaped with
     a backslash (Andres Freund)

     Formerly, spaces in the options string always separated options, so
     there was no way to include a space in an option value. Including a
     backslash in an option value now requires writing \\.

   • Change the default value of the GSSAPI include_realm parameter to
     1, so that by default the realm is not removed from a GSS or SSPI
     principal name (Stephen Frost)

     Replace configuration parameter checkpoint_segments with
     min_wal_size and max_wal_size (Heikki Linnakangas)

   • If you previously adjusted checkpoint_segments, the following
     formula will give you an approximately equivalent setting:

     max_wal_size = (3 * checkpoint_segments) * 16MB

     Note that the default setting for max_wal_size is much higher than
     the default checkpoint_segments used to be, so adjusting it might
     no longer be necessary.

   • Control the Linux OOM killer via new environment variables
     PG_OOM_ADJUST_FILE and PG_OOM_ADJUST_VALUE, instead of compile-time
     options LINUX_OOM_SCORE_ADJ and LINUX_OOM_ADJ (Gurjeet Singh)

   • Decommission server configuration parameter
     ssl_renegotiation_limit, which was deprecated in earlier releases
     (Andres Freund)

     While SSL renegotiation is a good idea in theory, it has caused
     enough bugs to be considered a net negative in practice, and it is
     due to be removed from future versions of the relevant standards.
     We have therefore removed support for it from PostgreSQL. The
     ssl_renegotiation_limit parameter still exists, but cannot be set
     to anything but zero (disabled). It's not documented anymore,
     either.

   • Remove server configuration parameter autocommit, which was already
     deprecated and non-operational (Tom Lane)

   • Remove the pg_authid catalog's rolcatupdate field, as it had no
     usefulness (Adam Brightwell)

   • The pg_stat_replication system view's sent field is now NULL, not
     zero, when it has no valid value (Magnus Hagander)

   • Allow json and jsonb array extraction operators to accept negative
     subscripts, which count from the end of JSON arrays (Peter
     Geoghegan, Andrew Dunstan)

     Previously, these operators returned NULL for negative subscripts.

E.1.3. Changes

Below you will find a detailed account of the changes between PostgreSQL
9.5 and the previous major release.

...

[Fernando de Oliveira]

Sorry, could not make the server run. Probably needs a more deep study, before updating. Or someone who really uses it.

[Fernando de Oliveira]

OK. The migration from 8.4.x seems to have been the cause. I don't have any particular db, so just deleted /srv/pgsql/data, followed the book's instructions, everything is fine, now.

[Fernando de Oliveira]

Fixed at r16796.