opendbteam workspace

opendbteam workspace
opendbteam workspace

Thursday, September 29, 2016

#PostgreSQL #9.6 #Released!

PostgreSQL 9.6, the latest version of the world's leading open source
database, was released today by the PostgreSQL Global Development
Group.  This release will allow users to both scale up and scale out
high performance database workloads.  New features include parallel
query, synchronous replication improvements, phrase search, and
improvements to performance and usability, as well as many more
features.

Scale Up with Parallel Query
----------------------------

Version 9.6 adds support for parallelizing some query operations,
enabling utilization of several or all of the cores on a server to
return query results faster.  This release includes parallel
sequential (table) scan, aggregation, and joins.  Depending on details
and available cores, parallelism can speed up big data queries by as
much as 32 times faster.

"I migrated our entire genomics data platform - all 25 billion legacy
MySQL rows of it - to a single Postgres database, leveraging the row
compression abilities of the JSONB datatype, and the excellent GIN,
BRIN, and B-tree indexing modes. Now with version 9.6, I expect to
harness the parallel query functionality to allow even greater
scalability for queries against our rather large tables," said Mike
Sofen, Chief Database Architect, Synthetic Genomics.

Scale Out with Synchronous Replication and postgres_fdw
-------------------------------------------------------

Two new options have been added to PostgreSQL's synchronous
replication feature which allow it to be used to maintain consistent
reads across database clusters.  First, it now allows configuring
groups of synchronous replicas.  Second, the "remote_apply" mode
creates a more consistent view of data across multiple nodes.  These
features support using built-in replication to maintain a set of
"identical" nodes for load-balancing read workloads.

The PostgreSQL-to-PostgreSQL data federation driver, postgres_fdw, has
new capabilities to execute work on remote servers.  By "pushing down"
sorts, joins, and batch data updates, users can distribute workload
across multiple PostgreSQL servers.  These features should soon be
added to other FDW drivers.

"With the capabilities of remote JOIN, UPDATE and DELETE, Foreign Data
Wrappers are now a complete solution for sharing data between other
databases and PostgreSQL.  For example, PostgreSQL can be used to
handle data input going to two or more different kinds of databases,"
said Julyanto Sutandang, Director of Business Solutions at Equnix.

Better Text Search with Phrases
-------------------------------

PostgreSQL's full text search feature now supports "phrase search."
This lets users search for exact phrases, or for words within a
specified proximity to each other, using fast GIN indexes.  Combined
with new features for fine-tuning text search options, PostgreSQL is
the superior option for "hybrid search" which puts together
relational, JSON, and full text searching.

Smoother, Faster, and Easier to Use
-----------------------------------

Thanks to feedback and testing by PostgreSQL users with high-volume
production databases, the project has been able to improve many
aspects of performance and usability in this release.  Replication,
aggregation, indexing, sorting, and stored procedures have all been
made more efficient, and PostgreSQL now makes better use of resources
with recent Linux kernels.  Administration overhead for large tables
and complex workloads was also reduced, especially through
improvements to VACUUM.

Other Features
--------------

Version 9.6 has many other features added over the last year of
development, among them:

* New system views and functions: pg_stat_wal_receiver, pg_visbility,
  pg_config, pg_blocking_pids, pg_notification_queue_usage
* Command progress reporting support
* Cascade support for installing extensions
* pg_basebackup concurrency and replication slot support
* Wait Event support
* View editing and crosstabs in psql
* User-defined expiration for old snapshots
* Index-only scans for partial indexes

Additionally, the project has changed and improved the API for binary
hot backups.  As such, developers of custom backup software for
PostgreSQL should do additional testing around the new version.  See
the Release Notes for more detail.

Links
-----

* Downloads:
  https://www.postgresql.org/downloads
* Release Notes:
  https://www.postgresql.org/docs/current/static/release-9-6.html
* What's New in 9.6:
  https://wiki.postgresql.org/wiki/NewIn96
* Press Kit:
  https://www.postgresql.org/about/press/presskit96

Tuesday, September 27, 2016

#BARMAN #2.0 RELEASED !

Barman 2.0 is out! New features involve support for replication slots and Windows support!


"Barman (Backup and Recovery Manager) is an open-source administration
tool for disaster recovery of PostgreSQL servers written in Python.
Barman enables the remote online hot backup of multiple servers in
business critical environments, helping make a DBA's life easier during
the recovery stage - now with zero data loss.


What's New

The major release of version 2.0 is an important milestone in Barman's
journey as it introduces support for a “STREAMING ONLY” BACKUP of a
PostgreSQL server, removing the need for SSH connections altogether.

This is made possible by two significant improvements related to
streaming replication:

-   Transparent integration with pg_basebackup for backup operations
-   Full support of replication slots for WAL streaming

Through streaming-only backups, Barman is now able to reach Docker and
Windows users of PostgreSQL. From an architectural point of view, Barman
is now able to offer even more versatility and flexibility by combining
traditional SSH communications with PostgreSQL’s native streaming
connections.

Another important addition is support for synchronous WAL streaming that
allows zero data loss backups and reduces implementation costs of
PostgreSQL clusters with “RPO=0” business continuity requirements.

Barman 2.0 also supports the new concurrent backup API introduced in
PostgreSQL 9.6, eliminating the need for the pgespresso extension when
taking backups from a standby server.

The new 'barman-cli' package improves integration of Barman with repmgr
for better high availability outcomes.


Disaster Recovery

Recovery planning is frequently overlooked during business continuity
planning, but failure is always an option. Unfortunately, our experience
has shown us that typically, disaster recovery plans are only
implemented after suffering severe data loss or down-time. Business
critical environments must be prepared for the unexpected and Barman is
the right tool organisations can use to be prepared.

Now, through synchronous WAL streaming, Barman allows a Recovery Point
Objective (RPO) of 0. Barman now also supports PostgreSQL on Windows
(experimental) and Docker. Improved support of very large databases and
clusters is currently being developed for a future Barman release.


Professional Support for Barman

In addition to the disaster recovery and business continuity support
already available through 2ndQuadrant, as the developers of Barman, the
team at 2ndQuadrant provides superior support for Barman users. The
turnkey disaster recovery solution service includes architecture
analysis, installation, training and a final simulation with internal
personnel. 2ndQuadrant offers custom solutions, development of new
features, training, and 24/7 production support.


Upgrading from Barman 1.X

The 2.0 release requires that users explicitly configure their archiving
strategy. Before, the file based archiver controlled by the ‘archiver’
option was enabled by default. When you upgrade your Barman installation
to 2.0, make sure you add the appropriate line globally or for any
server that requires it. Barman using officially distributed RPM and
Debian packages will be automatically updated.

For installation or upgrade instructions, please see the "Links" section
below.

For the comprehensive release notes, please read "Barman 2.0 Release
Notes".

If you have any questions about Barman and disaster recovery scenarios
using Barman, you can reach the dev team using the community mailing
list.

To submit private inquiries regarding implementation, customised
features or sponsorship opportunities email the team directly at
info@pgbarman.org.


Links

-   Website: http://www.pgbarman.org/
-   Download: https://sourceforge.net/projects/pgbarman/files/2.0/
-   Online Documentation: http://docs.pgbarman.org/release/2.0/
-   Man page, section 1: http://docs.pgbarman.org/release/2.0/barman.1.html
-   Man page, section 5: http://docs.pgbarman.org/release/2.0/barman.5.html
-   Support: http://www.pgbarman.org/support/
-   Client utilities for Barman: https://github.com/2ndquadrant-it/barman-cli
-   pgespresso extension: https://github.com/2ndquadrant-it/pgespresso "

Tuesday, September 6, 2016

#PostGIS 2.3.0beta1 release #postgresql http://blog.opendbteam.com

The PostGIS development team is proud to release PostGIS 2.3.0beta1.

PostGIS 2.3 is feature complete, so we're looking for testing and feedback!
Best served with PostgreSQL 9.6.

Please give this beta a try and report back any issues you encounter.

Download -
http://download.osgeo.org/postgis/source/postgis-2.3.0beta1.tar.gz
HMTL docs -
http://download.osgeo.org/postgis/docs/doc-html-2.3.0beta1.tar.gz
PDF docs - http://download.osgeo.org/postgis/docs/postgis-2.3.0beta1.pdf


* Important / Breaking Changes *

  - #3466, Casting from box3d to geometry now returns a 3D
           geometry (Julien Rouhaud of Dalibo)

  - #3604, pgcommon/Makefile.in orders
               CFLAGS incorrectly leading to wrong liblwgeom.h
                   (Greg Troxel)

* New Features *

-- new functions can be found here -
http://postgis.net/docs/manual-dev/PostGIS_Special_Functions_Index.html#NewF
unctions_2_3


  - Add support for custom TOC in postgis_restore.pl
    (Christoph Moench-Tegeder)
  - Add support for negative indexing in ST_PointN and ST_SetPoint
    (Rémi Cura)
  - Add parameters for geography ST_Buffer (Thomas Bonfort)
  - TopoGeom_addElement, TopoGeom_remElement (Sandro Santilli)
  - populate_topology_layer (Sandro Santilli)
  - #454,  ST_WrapX and lwgeom_wrapx (Sandro Santilli)
  - #1758, ST_Normalize (Sandro Santilli)
  - #2236, shp2pgsql -d now emits "DROP TABLE IF EXISTS"
  - #2259, ST_Voronoi* (Dan Baston)
  - #2991, Enable ST_Transform to use PROJ.4 text (Mike Toews)
  - #3059, Allow passing per-dimension parameters in ST_Expand (Dan Baston)
  - #3339, ST_GeneratePoints (Paul Ramsey)
  - #3362, ST_ClusterDBSCAN (Dan Baston)
  - #3364, ST_GeometricMedian (Dan Baston)
  - #3391, Add table inheritance support in ST_EstimatedExtent
           (Alessandro Pasotti)
  - #3424, ST_MinimumClearance (Dan Baston)
  - #3428, ST_Points (Dan Baston)
  - #3465, ST_ClusterKMeans (Paul Ramsey)
  - #3469, ST_MakeLine with MULTIPOINTs (Paul Norman)
  - #3549, Support PgSQL 9.6 parallel query mode, as far as possible
    (Paul Ramsey, Regina Obe)
  - #3557, Geometry function costs based on query stats (Paul Norman)
  - #3591, Add support for BRIN indexes. PostgreSQL 9.4+ required.
                  (Giuseppe Broccolo of 2nd Quadrant, Julien Rouhaud and
Ronan Dunklau of Dalibo)
  - #3496, Make postgis non-relocateable (for extension install),
                schema qualify calls in functions (Regina Obe)
                Should resolve once and for all for extensions #3494, #3486,
#3076

  - #3547, Update tiger geocoder to support TIGER 2016
   and to support both http and ftp. (Regina Obe)

 * Performance Enhancements *

  - #75, Enhancement to PIP short circuit (Dan Baston)
  - #3383, Avoid deserializing small geometries during index operations
    (Dan Baston)
  - #3400, Minor optimization of PIP routines (Dan Baston)
  - Make adding a line to topology interruptible (Sandro Santilli)
  - #3613, Segmentize geography using equal length segments, (Hugo Mercier
of Oslandia)

Monday, September 5, 2016

#pgBadger v9.0 released #postgresql http://blog.opendbteam.com

pgBadger is a PostgreSQL performance analyzer, built for speed with
fully detailed reports based on your PostgreSQL log files.

pgBadger 9.0 was released today, this major release of pgBadger is
a port to bootstrap 3 and a version upgrade of all resources files
(CSS and Javascript). There's also some bugs fixes and features
enhancements.

Backward compatibility with old incremental report might be preserved.

Here are the complete list of changes:

  - Sources and licences of resources files are now on a dedicated
    subdirectory. A script to update their minified version embedded
    in pgbager script has been added. Thanks to Christoph Berg for
    the help and feature request.

  - Try to detect user/database/host from connection strings if
    log_connection is enabled and log_line_prefix doesn't include
    them.

    Extend the regex to autodetect database name, user name, client
    ip address and application name. The regex now are the following:

        db => qr/(?:db|database)=([^,]*)/;
        user => qr/(?:user|usr)=([^,]*)/;
        client => qr/(?:client|remote|ip|host)=([^,]*)/;
        appname => qr/(?:app|application)=([^,]*)/;

  - Add backward compatibility with older version of pgbadger in
    incremental mode by creating a subdirectory for new CSS and
    Javascript files. This subdirectory is named with the major
    version number of pgbadger.

  - Increase the size of the pgbadger logo that appears too small
    with the new font size.

  - Normalize detailed information in all reports.

  - Fix duplicate copy icon in locks report.

  - Fix missing chart on histogram of session time. Thanks to
    Guillaume Lelarge for the report.

  - Add LICENSE file noting the licenses used by the resource
    files. Thanks to Christoph Berg for the patch.

  - Add patch to jqplot library to fix an infinite loop when trying
    to download some charts. Thanks to Julien Tachoires for the help
    to solve this issue.

  - Script tools/updt_embedded_rsc.pl will apply the patch to resource
    file resources/jquery.jqplot.js and doesn't complain if it has
    already been applied.

  - Remove single last comma at end of pie chart dataset. Thanks to
    Julien Tachoires for the report.

  - Change display of normalized error

  - Remove unused or auto-generated files

  - Update all resources files (js+css) and create a directory to
    include source of javascript libraries used in pgbadger. There is
    also a new script tools/updt_embedded_rsc.pl the can be used to
    generate the minified version of those files and embedded them
    into pgbadger.  This script will also embedded the FontAwesome.otf
    open truetype font into the fontawesome.css file.


Previous release, v8.3, was maintenance release that fix some minor
bugs and adds replication command messages statistics to the Events
reports.

   - Fix auto-detection of stderr format with timestamp as epoch (%n).

   - Fix histogram over multiples days to be cumulative per hour, not
     an average of the number of event per day.

   - Fix parsing of remote file that was failing when the file does
     not exists locally. Thanks to clomdd for the report.

   - Detect timezones like GMT+3 on CSV logs. Thanks to jacksonfoz
     for the patch.

   - Add replication command messages statistics to the Events
     reports. Thanks to Michael Paquier for the feature request.



**About pgBadger** :

pgBagder is a new generation log analyzer for PostgreSQL, created by
Gilles Darold (also author of ora2pg, the powerful migration tool).
pgBadger is a fast and easy tool to analyze your SQL traffic and create
HTML5 reports with dynamics graphs. pgBadger is the perfect tool to
understand the behavior of your PostgreSQL servers and identify which
SQL queries need to be optimized.