opendbteam workspace

opendbteam workspace
opendbteam workspace

Monday, November 21, 2016

#Ora2Pg #17.6 #released

Version 17.6 of Ora2Pg, a free and reliable tool used to migrate an
Oracle database to PostgreSQL, has been officially released and is
publicly available for download.

This release adds several new features:

  * Adds export of Oracle Text Indexes into FTS (CONTEXT) or pg_trgm
    (CTXCAT) based indexes.
  * Add export of indexes defined on materialized views.
  * Allow export of materialized views as foreign tables when export
    type is FDW.
  * Add replacement of trim() by btrim().

Two new configuration directives have been added:

  * USE_INDEX_OPCLASS: when value is set to 1, this will force Ora2Pg
    to export all indexes defined on varchar2() and char() columns
    using *_pattern_ops operators. If you set it to a value greater
    than 1 it will only change indexes on columns where the character
    limit is greater or equal than this value.

  * CONTEXT_AS_TRGM: when enabled force Ora2Pg to translate all Oracle
    Text indexes (CONTEXT and CTXCAT) into PostgreSQL indexes using
    the pg_trgm extension. Default is to translate CONTEXT indexes
    into FTS indexes and CTXCAT indexes using pg_trgm. Some time using
    pg_trgm based indexes is enough.

As usual these also some bug fixes and Oracle to PostgreSQl conversion
adjustments, see https://github.com/darold/ora2pg/blob/master/changelog
for a complete list.

Thanks to all contributors, they are all cited in the changelog file.

Useful Links:

    - Website: http://ora2pg.darold.net/
    - Download: http://sourceforge.net/projects/ora2pg/
    - Development: https://github.com/darold/ora2pg
    - Changelog: https://github.com/darold/ora2pg/blob/master/changelog
    - Documentation: https://github.com/darold/ora2pg/blob/master/README

About Ora2Pg :

Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL.
It is developed since 2001 and can export most of the Oracle objects
(table, view, materialized view, tablespace, sequence, indexes, trigger,
grant, function, procedure, package, partition, data, blob and external
table).

Ora2Pg works on any platform and is available under the GPL v3 licence.

Docs, Download & Support at http://ora2pg.darold.net/

#pgPool #3.6 #released!

Pgpool-II is a tool to add usefull features to PostgreSQL, including
connection pooling, load balancing, automatic fail over and more.

Pgpool-II 3.6.0 is now officially released.

V3.6 has a new feature:

* Improve the behavior of fail-over.
* New PGPOOL SET command has been introduced.
* Watchdog is significantly enhanced.
* Handling of extended query protocol (e.g. used by Java applications)
  in streaming replication mode speeds up if many rows are returned in a result set.
* Import parser of PostgreSQL 9.6.
* In some cases pg_terminate_backend() now does not trigger a fail-over.
* Change documentation format from raw HTML to SGML.
* etc.

You can download it from:
http://pgpool.net/mediawiki/index.php/Downloads

Wednesday, November 2, 2016

#pgpool-II #3.6 #Beta1 released!

Pgpool-II is a tool to add usefull features to PostgreSQL, including
connection pooling, load balancing, automatic fail over and more.

Pgpool-II 3.6 beta1 is now relased.
Users are encouraged to test their applications with this release.

V3.6 has a new feature:

* Improve the behavior of fail-over.
* New PGPOOL SET command has been introduced.
* Watchdog is significantly enhanced.
* Handling of extended query protocol (e.g. used by Java applications)
in streaming replication mode speeds up if many rows are returned in a result set.
* Import parser of PostgreSQL 9.6.
* In some cases pg_terminate_backend() now does not trigger a fail-over.
* Change documentation format from raw HTML to SGML.
* etc.
More information: http://pgpool.net/mediawiki/index.php/Developer_releases

Thursday, October 27, 2016

#PostgreSQL #9.6.1, #9.5.5, #9.4.10, #9.3.15, #9.2.19 and #9.1.24 #Released!



There is a new update for each version of PostgreSQL database system (9.6.1, 9.5.5, 9.4.10, 9.3.15, 9.2.19, and 9.1.24). The 9.1 update is also the last seeing as it is now end-of-life. 
Other than the typical small bug fixes, this release fixes two instances of data corruption (see below).
Apply this update at the next possible downtime.


WAL-logging of truncated relations
Prior to this release, there was a chance that a PostgreSQL instance would try to access data that no longer existed on disk. If the free space map was not updated to be aware of the truncation, a PostgreSQL database could return a page that was already truncated and produce an error such as:
ERROR:  could not read block 28991 in file "base/16390/572026": read only 0 of 8192 bytes
If checksumming is enabled, checksum failures in the visibility map could also occur.

This issue is present in the 9.3, 9.4, 9.5, and 9.6 series of PostgreSQL releases.


pg_upgrade issues on big-endian machines

On big-endian machines (e.g. many non-Intel CPU architectures), pg_upgrade would incorrectly write the bytes of the visibility map leading to pg_upgrade failing to complete.

If you are using a big-endian machine (many non-Intel architectures are big-endian) and have used pg_upgrade to upgrade from a pre-9.6 release, you should assume that all visibility maps are incorrect and need to be regenerated. It is sufficient to truncate each relation's visibility map with contrib/pg_visibility's pg_truncate_visibility_map() function. Please read the "Updating" section for post-installation instructions on how to resolve this issue on your PostgreSQL instances.

This issue is present only in the PostgreSQL 9.6.0 release.

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)