New versions of Postgres Migration Tookit has been released with improved support for schemas, default values, "pure" time and many other improvements.

Visit http://www.convert-in.com/pgskit.htm for details.

SQL Maestro Group announces the release of PostgreSQL Maestro 16.6, a powerful Windows GUI solution for PostgreSQL database server administration and database development.

The new version is immediately available for download.

Top 10 new features:

1. PostgreSQL 9.6 compatibility.
2. Support for Row Security Policies (PostgreSQL 9.5+).
3. Support for BRIN indexes (PostgreSQL 9.5+).
4. Support for "Allow Connections" and "Is Template" options (PostgreSQL 9.5+).
5. Generate Updatable View tool becomes available for PostgreSQL 9.2 and lower.
6. Support for indexes for materialized views.
7. Improved "Show SQL Help" command flexibility.
8. Support for Adobe Reader DC in BLOB Editor.
9. Default Data Import settings can now be set up in the Program Options dialog.
10. Some performance and usability improvements.

There are also some other useful things. Full press release is available at the SQL Maestro Group website.

There is only one more week left to get in on the Early Bird tickets for PostgresOpen 2016 being held in Dallas, TX!

Early Bird Tickets and All Tutorials can be purchased here: https://2016.postgresopen.org/tickets/

Early Bird ticket sales for PostgresOpen 2016 have been extended through Friday, July 8th, giving you one last week to get in at the Early Bird rate and save $200 off of your registration!

We have a bunch of great tutorials to choose from also, but space is limited!

Conference Tutorials

We’ve brought in some new presenters and topics this year for our tutorial sessions held on Tuesday, September 13th.

• Intro to PostGIS - Regina Obe, Leo Hsu
• Seamless failover with pgBouncer and Consul - Scott Mead
• Linux tuning to improve PostgreSQL performance: from hardware to postgresql.conf - Ilya Kosmodemiansky
• Advanced SQL + Functions - Joe Conway, Stephen Frost

In addition, Crunchy Data and RedHat are offering a free, all-day, 2-part tutorial covering Autoscaling PostgreSQL with Kubernetes and OpenShift presented by Jeff McCormick of Crunchy Data and Steven Pousty of RedHat.

All of the selected talks have been published for your review here: https://postgresopen.org/events/sessions/pgopen2016/

Event Hotel

Time to book your room at the Westin Galleria Dallas. Book your reservation now here: https://2016.postgresopen.org/venue/ at the discount room rate of $189/night, with free wifi and other cool stuff we'll be announcing later!

Sponsorship

PostgresOpen could not be put on without the support of our excellent sponsors!

https://2016.postgresopen.org/sponsors/

PostgresOpen 2016 is proud to announce 2ndQuadrant (Diamond), EnterpriseDB (Platinum), Crunchy Data (Gold), and OmniTI (Silver) as top-tier sponsors!

Keep an eye out on social media and the conference website for the schedule, which will be published in the next few days!

We look forward to seeing you in Dallas!

https://2016.postgresopen.org/

The PostgreSQL Global Development Group announces today that the second beta release of PostgreSQL 9.6 is available for download. This release contains previews of all of the features which will be available in the final release of version 9.6, including fixes to many of the issues found in the first beta. Users are encouraged to begin testing their applications against 9.6 beta 2.

Changes Since Beta1

Our users and contributors reported bugs against 9.6 beta 1, and some of them have been fixed in this release. This includes multiple fixes for failure and performance issues in parallel query. We urge our community to re-test to ensure that these bugs are actually fixed, including:

• update most contrib extensions for parallel query
• two fixes for pg_trgm (trigram) bugs
• rewrite code to estimate join sizes for better performance
• correct handling of argument and result datatypes for partial aggregation
• fix lazy_scan_heap so that it won't mark pages all-frozen too soon
• mark additional functions as parallel-unsafe
• check PlaceHolderVars before pushing down a join in postgres_fdw
• improve the situation for parallel query versus temp relations
• don't generate parallel paths for rels with parallel-restricted outputs
• make psql_crosstab plans more stable
• finish loose ends for SQL ACCESS METHOD objects, including pg_dump
• stop the executor if no more tuples can be sent from worker to leader
• several pg_upgrade fixes to support new features
• fix regression tests for phrase search
• add new snapshot fields to serialize/deserialize functions
• measure Bloom index signature-length reloption in bits, not words
• many improvements to parallel regression tests
• many documentation updates and clarifications
• multiple translation updates for the docs

Additionally, we've made some user-visible changes to parameters and functions. These may cause dump/restore between beta 1 and beta 2 to generate errors. This includes:

• rename max_parallel_degree to max_parallel_workers_per_gather
• add integrity-checking functions to pg_visibility
• add VACUUM (DISABLE_PAGE_SKIPPING) for emergencies
• add pg_truncate_visibility_map function
• add min_parallel_relation_size GUC
• change default of backend_flush_after GUC to 0 (disabled)

Note that some known issues remain unfixed. Before reporting a bug in the beta, please check the Open Items page.

Beta Schedule

This is the second beta release of version 9.6. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2016. For further information please see the Beta Testing page.

Links

• Downloads Page
• Beta Testing Information
• 9.6 Beta Release Notes
• What's New in 9.6
• 9.6 Open Items

You can now quickly create cross-region read replicas for your unencrypted Amazon RDS for PostgreSQL database instances with just a few clicks on the AWS Management Console. You can use this feature to reduce read latency for your customers in different geographic locations, to create a backup of your primary database for disaster recovery purposes, or quickly migrate your database to a different AWS Region.

Disaster Recovery: You can create cross-region read replicas of your primary database instance to have a disaster recovery solution. If your primary region faces a disruption, you can promote the replica to a master and keep your business operational.

Scaling: You can use cross-region read replicas to support read queries from your workloads across various geographic locations. This will reduce latency by serving your customers from a database that is close to them.

Cross-region Migration: If you would like to migrate your database instance quickly to another AWS region, you may do so by using cross-region replication. Simply create a replica in your target region, and once it is ready, promote it to master and point your application to it.

This feature is available for all RDS PostgreSQL databases that are version 9.5.2 or 9.4.7 and higher. To create a cross-region replica of a database instance operating on an older version, you can upgrade to a supported version by performing a database version upgrade. To learn more about cross-region replication for RDS PostgreSQL, please refer to the RDS documentation.

RazorSQL 7.0, an SQL editor, database query tool, database browser, and database administration tool, is now available. Using RazorSQL, users can query, edit, browse, and manage all major databases from one tool. RazorSQL supports over 30 databases, examples of which include the following: PostgreSQL, Amazon SimpleDB, Apache Derby, DB2, Firebird, FrontBase, H2, HSQLDB, Informix, Intersystems Cache, JavaDB, MS Access, MySQL, OpenBase, Oracle, Pervasive, SQLite, SQL Azure, SQL Server, Sybase, Sybase SQL Anywhere and Teradata. Connect to other databases via JDBC or ODBC (Windows only). Users can also use the built in relational database engine for their database needs.

Some of the main features contained in RazorSQL are visual tools for creating, editing, describing, altering, dropping, and viewing database objects such as tables, views, indexes, procedures, functions, and triggers; tools for importing and exporting data in various formats such as MS Excel, delimited files, SQL insert statements, HTML, XML, and text; a database browser for the viewing of database objects and structures; search tools for searching for database objects and database data, tools for building SQL queries and generating SQL and DDL, a SQL formatting tool, SQL history tracking and and SQL favorites tool for storing frequently used SQL statements, and a robust programming editor and query tool with support for SQL, PL/SQL, TransactSQL, SQL PL, PHP, Java, XML, HTML, and many other programming languages that features tools for searching and replacing data, programmable macros, coding sidekicks for various programming languages, a file system browser, function and method browsers, and much more.

New features in version 7.0 include database conversion support for converting DB2 database tables to PostgreSQL and several other database types, more support for the Amazon Redshift database, and enhanced DDL generation options.

Versions of RazorSQL are available for Windows, Mac OS X, Linux, and Solaris.

RazorSQL 7.0 is available for immediate download at http://www.razorsql.com. Pricing of RazorSQL starts at $99.95 USD for a single user, and tiered discounts are offered for multiple license purchases.

More Info:http://www.razorsql.com/

Download URL: http://www.razorsql.com/download.html

Change Log: http://www.razorsql.com/updates.html

Screenshot URL:

(Windows / Linux): http://www.razorsql.com/images/windows/main.gif

(Mac OS X): http://www.razorsql.com/images/mac/main.png

Yohz Software announces the release of DB Doc 3.0 on June 3, 2016, and is available for immediate download.

DB Doc benefits:

• Eliminate tedious and time-consuming manual documentation tasks
• Satisfy audit requirements by keeping complete and timely documentation
• Document a database in a couple of clicks
• Create documents in HTML, CHM, and PDF formats
• View inter-object and inter-database dependencies in your documents
• Document layouts can be fully customizable to suit your requirements
• Keep teams up to date by distributing documentation
• Runs on Windows XP to Windows 10
• Runs in Wine, so you can use DB Doc on your favorite Linux distribution
• Supports PostgreSQL 8.3 to 9.5, without the need for any additional database drivers.

For more details about DB Doc, visit the product page here.

I am happy to announce the new major release of dbMigration .NET v3. It is a simple, easy and intuitive multiple database migration and sync tool, With it you can easily migrate schema and data between different databases without complicated procedures. Supported databases: PostgreSQL, SQL Server, SQL Azure, LocalDB, MySQL, Oracle, IBM DB2, Informix, Vertica, NuoDB, Teradata, Sybase ASE, Firebird, SQLite, SQLCe, Access, dBase, FoxPro, Text, Excel, ODBC, OleDB...etc.

Free, All-In-One, Portable, and Single executable file.

Major New features from version 2.4 to 3.1:

• Compatible with PostgreSQL 8.4 ~ 9.5+
• Delimited text files support (CSV/TXT)
• Ability to Add custom columns
• Ability to Manage Indexes
• Ability to Create Table Schema Only
• Ability to Generate the command-line to Clipboard
• Added Batch Mode Settings
• Added Custom Mapping Types
• Massive performance improvements
• Improved Data Synchronization
• Improved Automatic Mapping Types
• Improved Command-Line
• ...and more

The new version is immediately available for download.

23 May 2016: 2ndQuadrant is proud to announce the release of Barman version 1.6.1, a Backup and Recovery Manager for PostgreSQL.

This minor release introduces support for '--peek' option for 'get-wal' command which allows users to implement parallel remote restore commands on standby servers and during recovery.

Through the new 'replication-status' command, Barman becomes a very practical tool for monitoring streaming replication status of every managed server.

A few important improvements have been added to the 'check' command, which should make installation and initial configuration easier. Another step in this direction is the 'switch-xlog' command, a new feature also included in this release.

Minor bugs have also been fixed, including support for 'archive_mode=always' for PostgreSQL 9.5, as well as 'FAILED' backups preventing new one from being taken.

Many thanks for funding towards the development of this release go to Jobrapido (www.jobrapido.com), Navionics (www.navionics.com), and Subito.it (www.subito.it).

However, for a complete list of changes, see the "Release Notes" section below.

Links

• Website
• Download
• Documentation
• Man page, section 1
• Man page, section 5
• Support
• pgespresso extension

Release notes

• Add '--peek' option to 'get-wal' command to discover existing WAL files from the Barman's archive

• Add 'replication-status' command for monitoring the status of any streaming replication clients connected to the PostgreSQL server. The '--target' option allows users to limit the request to only hot standby servers or WAL streaming clients

• Add the 'switch-xlog' command to request a switch of a WAL file to the PostgreSQL server. Through the '--force' it issues a 'CHECKPOINT' beforehand

• Add 'streaming_archiver_name' option, which sets a proper 'application_name' to 'pg_receivexlog' when 'streaming_archiver' is enabled (only for PostgreSQL 9.3 and above)

• Check for superuser privileges with PostgreSQL's standard connections (#30)

• Check the WAL archive is never empty

• Check for 'backup_label' on the master when server is down

• Improve barman-wal-restore contrib script

• Bug fixes:

  • Treat the "failed backups" check as non-fatal
  • Rename '-x' option for get-wal as '-z'
  • Add archive_mode=always support for PostgreSQL 9.5 (#32)
  • Properly close PostgreSQL connections when necessary
  • Fix receive-wal for pg_receive_xlog version 9.2

Download

• Release Notes
• Sources
• RPMs for RHEL/CentOS 5 (dependencies)
• RPMs for RHEL/CentOS 6 (dependencies)
• PyPI
• pgespresso on PostgreSQL Extension framework (PGXN)
• pgespresso RPM/Debian packages
• Online documentation
• PDF documentation

About

Barman (Backup and Recovery Manager) is an open source administration tool for disaster recovery of PostgreSQL servers written in Python. It allows your organisation to perform remote backups of multiple servers in business critical environments and help DBAs during the recovery phase. Barman's most requested features include backup catalogues, incremental backup, retention policies, remote backup and recovery, archiving and compression of WAL files and backups. Barman is distributed under GNU GPL 3.

PostgreSQL Conference Europe 2016 will be held on November 1-4 in the Radisson Blu Hotel Olümpia, in Tallin, Estonia. It will cover topics for PostgreSQL users, developers and contributors, as well as decision and policy makers. For more information about the conference, please see the website at http://2016.pgconf.eu/.

We are now accepting proposals for talks in English.

Each session will last 45 minutes, and may be on any topic related to PostgreSQL. Suggested topic areas include:

• Developing applications for PostgreSQL
• Administering large scale PostgreSQL installations
• Case studies and/or success stories of PostgreSQL deployments
• PostgreSQL tools and utilities
• PostgreSQL hacking
• Community & user groups
• Tuning the server
• Migrating from other systems
• Scaling/replication
• Benchmarking & hardware
• PostgreSQL related products

Of course, we're happy to receive proposals for talks on other PostgreSQL related topics as well.

We may also have a limited number of longer, 90-minute, slots available. Please indicate clearly in your submission if you wish to make a 90-minute talk.

Finally, there will be a session of five minute lightning talks. A separate call for proposals will be made for them further on.

The submission deadline is August 7th. Selected speakers will be notified before August 24th, 2016.

Please submit your proposals by going to http://2016.pgconf.eu/callforpapers/ and following the instructions there.

The proposals will be considered by committee who will produce a schedule to be published nearer the conference date.

All selected speakers will get free entry to the conference (excluding training sessions). We do not in general cover travel and accommodations for speakers, but may be able to do that in limited cases. If you require assistance with funding to be able to attend, please make a note of this in the submission notes field.

We are also looking for sponsors. PostgreSQL Conference Europe is a perfect opportunity for companies to reach out to users and developers of PostgreSQL, or to just give back to the community. Our sponsorship program covers a wide range of levels, from entry level to big scale. More informations on http://2016.pgconf.eu/becomesponsor/.

We look forward to hearing from you, and seeing you in Tallin in November!

2016-05-12 Cumulative Bug Fix Release

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 9.5.3, 9.4.8, 9.3.13, 9.2.17 and 9.1.22. This release fixes a number of issues reported by users over the last two months. Most database administrators should plan to upgrade at the next available downtime, unless they have been affected directly by the fixed issues.

Bug Fixes and Improvements

This update fixes several problems which caused downtime for users, including:

• Clearing the OpenSSL error queue before OpenSSL calls, preventing errors in SSL connections, particularly when using the Python, Ruby or PHP OpenSSL wrappers
• Fixed the "failed to build N-way joins" planner error
• Fixed incorrect handling of equivalence in multilevel nestloop query plans, which could emit rows which didn't match the WHERE clause.
• Prevented two memory leaks with using GIN indexes, including a potential index corruption risk.

The release also includes many other bug fixes for reported issues, many of which affect all supported versions:

• Fix corner-case parser failures occurring when operator_precedence_warning is turned on
• Prevent possible misbehavior of TH, th, and Y,YYY format codes in to_timestamp()
• Correct dumping of VIEWs and RULEs which use ANY (array) in a subselect
• Disallow newlines in ALTER SYSTEM parameter values
• Avoid possible misbehavior after failing to remove a tablespace symlink
• Fix crash in logical decoding on alignment-picky platforms
• Avoid repeated requests for feedback from receiver while shutting down walsender
• Multiple fixes for pg_upgrade
• Support building with Visual Studio 2015

This update also contains tzdata release 2016d, with updates for Russia, Venezuela, Kirov, and Tomsk.

Updating

All PostgreSQL update releases are cumulative. As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shut down PostgreSQL and update its binaries. Users who have skipped one or more update releases may need to run additional, post-update steps; please see the release notes for earlier versions for details.

Links:

• Download
• Release Notes
• Security Page
• Abbreviated Keys Issue

The PostgreSQL Global Development Group announces today that the first beta release of PostgreSQL 9.6 is available for download. This release contains previews of all of the features which will be available in the final release of version 9.6, although some details will change before then. Users are encouraged to begin testing their applications against this latest release.

Major Features of 9.6

Version 9.6 includes significant changes and exciting enhancements including:

• Parallel sequential scans, joins and aggregates
• Support for consistent, read-scaling clusters through multiple synchronous standbys and "remote_apply" synchronous commit.
• Full text search for phrases
• postgres_fdw can now execute sorts, joins, UPDATEs and DELETEs on the remote server
• Decreased autovacuum impact on big tables by avoiding "refreezing" old data.

In particular, parallel execution should bring a noticeable increase in performance to supported queries.

Help Test for Bugs

As with other major releases, the improvements in PostgreSQL include changes to large amounts of code. We count on you to test the altered version with your workloads and testing tools in order to find bugs and regressions before the release of PostgreSQL 9.6.0. In addition to testing that the new features work as documented, consider testing the following:

• Do parallel queries actually improve performance for you?
• Can you make parallel queries crash or lose data?
• Do our code changes cause PostgreSQL to not function on your platform?
• Does improved vacuum freezing safely reduce autovacuum of large tables?
• Does phrase search return the results you expect?

Version 9.6 Beta 1 also makes changes to the binary backup API. Administrators should test version 9.6 with PostgreSQL backup tools, including pgBackRest, Barman, WAL-E, and other packaged and in-house software.

As this is a Beta, minor changes to database behaviors, feature details, and APIs are still possible. Your feedback and testing will help determine the final tweaks on the new features, so test soon. The quality of user testing helps determine when we can make a final release.

Beta Schedule

This is the first beta release of version 9.6. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2016. For further information please see the Beta Testing page.

Links

• Downloads Page
• Beta Testing Information
• 9.6 Beta Release Notes
• What's New in 9.6
• 9.6 Open Items

2ndQuadrant, the leading developers of PostgreSQL, are delighted to announce the release of pglogical 1.1 – the next generation in replication systems for PostgreSQL.

The 1.1 release brings new features along with bug fixes to pglogical. Same salient features are listed below:

• Sequence replication support
• Support for replica triggers
• Foreign keys are no longer checked on the replica
• Multiple subscriptions between single pair of nodes
• The create_subscription function does not synchronize structure change by default
• User can specify affected replication sets in replicate_ddl_command function
• New functions for manipulating connection strings of nodes
• PGLogical processes are clearly marked in the pg_stat_activity
• Better behavior on worker crashes
• Logging improvements
• Ubuntu Xenial package

pglogical offers Logical Replication as a PostgreSQL extension, which provides the flexibility of trigger-based replication with the efficiency of log-based replication. This ground-breaking new technology has benefits for many key use cases

• UPGRADE Upgrade PostgreSQL from 9.4 to 9.5, without downtime
• SCALE OUT Copy all or a selection of database tables to other nodes in a cluster
• AGGREGATE Accumulate changes from sharded database servers into a Data Warehouse
• INTEGRATE Feed database changes in real-time to other systems

pglogical is open source and available for download as binary packages for PostgreSQL 9.4 and 9.5 versions. Visit http://2ndquadrant.com/pglogical/ for more detail.

2ndQuadrant’s respected 24/7 Production Support provides the fastest and highest rated response service for PostgreSQL anywhere and is available now worldwide.

2ndQuadrant leads the drive for improving the enterprise functionality for PostgreSQL, contributing major features every year in performance, replication, business intelligence and usability.

The Postgres-XL community is pleased to announce the 1st release of Postgres-XL 9.5. This release has been fully synced up till PostgreSQL 9.5.2.

Postgres-XL is a massively parallel database built on top of, and very closely compatible with PostgreSQL 9.5 and its set of advanced features. Postgres-XL is fully open source and many parts of it will feed back directly or indirectly into later releases of PostgreSQL, as we begin to move towards a fully parallel sharded version of core PostgreSQL.

Postgres-XL is different because it supports both Business Intelligence and OLTP workloads in the same horizontally scalable server. This allows Postgres-XL to support a diverse range of workloads

• OLTP workloads that need write-scalability as well as read-scalability
• Business Intelligence requiring OLAP with massive parallelism
• Operational Data Store/ Central Data Backbone
• Distributed Key-Value store using JSONB, similar to NoSQL
• Internet of Things applications
• Mixed-workload environments

Star schema style SQL queries exhibit large performance gains from massively parallel processing (MPP). Many queries show fully linear performance gains, for example a 16-node XL cluster is 16 times faster than PostgreSQL on one node. Postgres-XL is able to successfully complete the complex TPC-H business intelligence benchmark, showing its capability to address much more than basic operations.

Besides proving its mettle on Business Intelligence workloads, Postgres-XL has performed remarkably well on OLTP workloads when running pgBench (based on TPC-B) benchmark. In a 4-Node (Scale: 4000) configuration, compared to PostgreSQL, XL gives up to 230% higher TPS (-70% latency comparison) for SELECT workloads and up to 130% (-56% latency comparison) for UPDATE workloads. Yet, it can scale much, much higher than even the largest single node server.

Postgres-XL’s High Availability functionality has also been enhanced in this release. Popular features such as BRIN indexes, JSONB and GIN index compression are fully supported, as are many popular extensions.

Postgres-XL is available for download here: www.postgres-xl.org/download/ You can go through XL’s comprehensive documentation here: files.postgres-xl.org/documentation/

We expect R2 to bring further features to Postgres-XL 9.5 in the next few months.

2ndQuadrant has led the development of Postgres-XL 9.5, building upon the work of many others over a long period of continuous development, with easily more than 10 man years of development. After six months of professional formal alpha-level testing & tuning, and an additional 2 months in Beta phase with user feedback, the project has now reached the next stage of maturity and we are now ready to release it for production use.

The research leading to these results has received funding from the European Union’s Seventh Framework Programme (FP7/2007-2015) under grant agreement n° 318633. Postgres-XL has also received support and assistance from European Space Agency's Gaia project, as well as other users in industry.

For more details, please visit: 2ndQuadrant.com/resources/postgres-xl/

Greetings!

The Call For Papers for PostgresOpen 2016, being held in Dallas, Texas from September 13th to 16th, is now open!

If you are working with PostgreSQL, please visit:

https://2016.postgresopen.org/callforpapers/

and submit a talk!

Presentations can be on any topic related to PostgreSQL, including, but not limited to, case studies, experiences, tools and utilities, migration stories, existing features, new feature development, benchmarks, performance tuning, and more!

The 2016 PostgresOpen Committee looks forward to bringing the best PostgreSQL presentations and tutorials from speakers around the world to Dallas, Texas! We're only able to do that with support from our great sponsors! If you are interested in sponsoring, please visit our sponsoring campaign:

https://2016.postgresopen.org/becomesponsor/

Anyone and everyone in the PostgreSQL community is encouraged to submit a talk. Talks will be accepted up until May 30th, Anywhere on Earth (AoE), also known as: 2015-05-30 23:59:59-12:00.

Speakers will be notified by June 6th, 2015 AoE, with the schedule to be published once selected speakers have confirmed.

Early Bird registration for PostgresOpen 2016 will open on May 9th!

Join us in celebrating our 6th conference year! We look forward to seeing everyone in Dallas!

Any questions? Please contact: program2016@postgresopen.org.

Stephen Frost

PostgresOpen 2016 Committee Chair

http://2016.postgresopen.org

April 17, 2016: Crunchy Data is proud to announce pgBackRest 1.0, Reliable PostgreSQL Backup & Restore.

Release 1.0

The first stable of release of pgBackRest introduces a new, more capable repository format, simpler configuration, and comprehensive support for backup and restore of symlinked directories and files.

There are a number of important changes in pgBackRest 1.0 so be sure to read the release notes very carefully before upgrading.

Links

• Website
• Download
• Release Notes
• Documentation
• User Guide
• Support

Features

Multithreaded Backup & Restore

Compression is usually the bottleneck during backup operations but, even with now ubiquitous multi-core servers, most database backup solutions are still single-threaded. pgBackRest solves the compression bottleneck with multithreading.

Utilizing multiple cores for compression makes it possible to achieve 1TB/hr raw throughput even on a 1Gb/s link. More cores and a larger pipe lead to even higher throughput.

Local or Remote Operation

A custom protocol allows pgBackRest to backup, restore, and archive locally or remotely via SSH with minimal configuration. An interface to query PostgreSQL is also provided via the protocol layer so that remote access to PostgreSQL is never required, which enhances security.

Full, Incremental, & Differential Backups

Full, differential, and incremental backups are supported. pgBackRest is not susceptible to the time resolution issues of rsync, making differential and incremental backups completely safe.

Backup Rotation & Archive Expiration

Retention polices can be set for full and differential backups to create coverage for any timeframe. WAL archive can be maintained for all backups or strictly for the most recent backups. In the latter case WAL required to make older backups consistent will be maintained in the archive.

Backup Integrity

Checksums are calculated for every file in the backup and rechecked during a restore. After a backup finishes copying files, it waits until every WAL segment required to make the backup consistent reaches the repository.

Backups in the repository are stored in the same format as a standard PostgreSQL cluster (including tablespaces). If compression is disabled and hard links are enabled it is possible to snapshot a backup in the repository and bring up a PostgreSQL cluster directly on the snapshot. This is advantageous for terabyte-scale databases that are time consuming to restore in the traditional way.

All operations utilize file and directory level fsync to ensure durability.

Backup Resume

An aborted backup can be resumed from the point where it was stopped. Files that were already copied are compared with the checksums in the manifest to ensure integrity. Since this operation can take place entirely on the backup server, it reduces load on the database server and saves time since checksum calculation is faster than compressing and retransmitting data.

Streaming Compression & Checksums

Compression and checksum calculations are performed in stream while files are being copied to the repository, whether the repository is located locally or remotely.

If the repository is on a backup server, compression is performed on the database server and files are transmitted in a compressed format and simply stored on the backup server. When compression is disabled a lower level of compression is utilized to make efficient use of available bandwidth while keeping CPU cost to a minimum.

Delta Restore

The manifest contains checksums for every file in the backup so that during a restore it is possible to use these checksums to speed processing enormously. On a delta restore any files not present in the backup are first removed and then checksums are taken for the remaining files. Files that match the backup are left in place and the rest of the files are restored as usual. Since this process is multithreaded, it can lead to a dramatic reduction in restore times.

Advanced Archiving

Dedicated commands are included for both pushing WAL to the archive and retrieving WAL from the archive.

The push command automatically detects WAL segments that are pushed multiple times and de-duplicates when the segment is identical, otherwise an error is raised. The push and get commands both ensure that the database and repository match by comparing PostgreSQL versions and system identifiers. This precludes the possibility of misconfiguring the WAL archive location.

Asynchronous archiving allows compression and transfer to be offloaded to another process which maintains a continuous connection to the remote server, improving throughput significantly. This can be a critical feature for databases with extremely high write volume.

Tablespace & Link Support

Tablespaces are fully supported and on restore tablespaces can be remapped to any location. It is also possible to remap all tablespaces to one location with a single command which is useful for development restores.

File and directory links are supported for any file or directory in the PostgreSQL cluster. When restoring it is possible to restore all links to their original locations, remap some or all links, or restore some or all links as normal files or directories within the cluster directory.

Compatibility with PostgreSQL >= 8.3

pgBackRest includes support for versions down to 8.3, since older versions of PostgreSQL are still regularly utilized.

About

pgBackRest aims to be a simple, reliable backup and restore system that can seamlessly scale up to the largest databases and workloads. Instead of relying on traditional backup tools like tar and rsync, pgBackRest implements all backup features internally and uses a custom protocol for communicating with remote systems. Removing reliance on tar and rsync allows for better solutions to database-specific backup challenges. The custom remote protocol allows for more flexibility and limits the types of connections that are required to perform a backup which increases security.

Crunchy Data supports the ongoing and active development of pgBackRest as an entirely open source project, released under the BSD-compatible MIT license.

April 8, 2016 – Amazon RDS for PostgreSQL now supports major version 9.5, which contains several new features including UPSERT capability, Row Level Security (RLS) and several Big Data capabilities. In addition to supporting the new major version of PostgreSQL, 9.5, RDS for PostgreSQL also supports new minor versions 9.4.7 and 9.3.12, which contain several enhancements and fixes. All three versions are available to use starting today.

PostgreSQL 9.5 (supported via minor version 9.5.2) offers the following new features:

UPSERT: Shorthand for "INSERT, ON CONFLICT UPDATE", this feature allows new and updated rows to be treated the same way, enabling the database to handle conflicts between concurrent data changes and simplifying application development. Row Level Security (RLS): RLS implements true per-row and per-column data access control which integrates with external label-based security stacks, giving you more control over securing your data. Big Data features: PostgreSQL 9.5 includes multiple new features for integrating with Big Data systems, including BRIN Indexing to create tiny but effective indexes for "naturally ordered" tables; faster sorts using "abbreviated keys" algorithm to sort through text and NUMERIC fields faster; CUBE, ROLLUP and GROUPING SETS to produce reports with multiple levels of summarization to allow integration with OLAP tools such as Tableau; Foreign Data Wrappers (FDWs) with IMPORT FOREIGN SCHEMA and JOIN pushdown to make query connections to external databases easier and more efficient; and TABLESPACE to generate a statistical sample of huge tables to avoid expensive sorting. You can create a new PostgreSQL 9.5.2 database instance with a few clicks on the AWS Management Console (learn how). You may also upgrade an existing PostgreSQL 9.4 instance to version 9.5.2 using major version upgrade. If you wish to upgrade from version 9.3 to 9.5, you will have to perform point-and-click upgrade twice. Each upgrade operation involves a short period of unavailability for your database instance. Learn more about upgrading your database instances.

In addition to PostgreSQL 9.5.2, we also now support PostgreSQL 9.4.7 and 9.3.12, which include several fixes and enhancements as listed here. To learn more about these new versions, please refer to their release notes:

Release Notes for 9.5.2

Release Notes for 9.4.7

Release Notes for 9.3.12

All three versions also now provide visibility into the status of autovacuum by allowing rds_superuser access to autovacuum sessions in pg_stat_activity. Read more about the new features in PostgreSQL 9.5 on the PostgreSQL website. For more details on using Amazon RDS for PostgreSQL, please refer to the Amazon RDS User's Guide.

Amazon RDS

Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you up to focus on your applications and business.

Contact:

Kevin Jernigan

Product Manager, RDS for PostgreSQL

+1-415-710-8828

kmj (at) amazon (dot) com

I am proud to announce the 1.3.0 version of E-Maj.

E-Maj is a PostgreSQL extension which enables fine-grained write logging and time travel on subsets of the database.

This version mainly adds some functions to protect "tables groups" and "marks" from unattended "E-maj rollbacks". The E-Maj phpPgAdmin plugin has been improved as well to use these new features.

The core extension is available at pgxn.org or github.org. It includes a general presentation and a detailed documentation. The phpPgAdmin plugin is also available at github.org.

There is only 1 week left until one of the biggest PostgreSQL events in the world: PGConf US 2016, which is in New York from April 18th to 20th: http://www.pgconf.us/2016/schedule/

Some highlights:

• A keynote presented by Parag Goradia, Executive Director, Cloud Services Engineering, GE Digital
• An awesome set of general sessions on April 19th - 20th
• The Regulated Industry Summit on April 18th (free with regular conference pass!)
• Training sessions from seasoned Postgres trainers!
• A chance to meet fun people from all over the world

…and the biggest PostgreSQL party of the year - come celebrate the PostgreSQL community turning 20! You can signup for the 20th Anniversary Party separately here: http://www.meetup.com/postgresql-3/events/229101241/

Hope to see you at the conference!

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 9.5.2, 9.4.7, 9.3.12, 9.2.16, and 9.1.21. This release fixes two security issues and one index corruption issue in version 9.5. It also contains a variety of bug fixes for earlier versions. Users of PostgreSQL 9.5.0 or 9.5.1 should update as soon as possible.

Security Fixes for RLS, BRIN

This release closes security hole CVE-2016-2193, where a query plan might get reused for more than one ROLE in the same session. This could cause the wrong set of Row Level Security (RLS) policies to be used for the query.

The update also fixes CVE-2016-3065, a server crash bug triggered by using pageinspect with BRIN index pages. Since an attacker might be able to expose a few bytes of server memory, this crash is being treated as a security issue.

Abbreviated Keys and Corrupt Indexes

In this release, the PostgreSQL Project has been forced to disable 9.5's Abbreviated Keys performance feature for many indexes due to reports of index corruption. This may affect any B-tree indexes on TEXT, VARCHAR, and CHAR columns which are not in "C" locale. Indexes in other locales will lose the performance benefits of the feature, and should be REINDEXed in case of existing index corruption. The feature may be re-enabled in future versions if the project finds a solution for the problem. See the release notes, and the wiki page on this issue for more information.

Other Fixes and Improvements

In addition to the above, many other issues were patched in this release based on bugs reported by our users over the last few months. This includes bugs which affect multiple versions of PostgreSQL, such as:

• Fix two bugs in indexed ROW() comparisons
• Avoid data loss due to renaming files
• Prevent an error in rechecking rows in SELECT FOR UPDATE/SHARE
• Fix bugs in multiple json_ and jsonb_ functions
• Log lock waits for INSERT ON CONFLICT correctly
• Ignore recovery_min_apply_delay until reaching a consistent state
• Fix issue with pg_subtrans XID wraparound
• Fix assorted bugs in Logical Decoding
• Fix planner error with nested security barrier views
• Prevent memory leak in GIN indexes
• Fix two issues with ispell dictionaries
• Avoid a crash on old Windows versions
• Skip creating an erroneous delete script in pg_upgrade
• Correctly translate empty arrays into PL/Perl
• Make PL/Python cope with identifier names

This update also contains tzdata release 2016c, with updates for Azerbaijan, Chile, Haiti, Palestine, and Russia, and historical fixes for other regions.

Updating

Users of version 9.5 will want to REINDEX any indexes they created on character columns in non-C locales. Users of other versions who have skipped multiple update releases may need to perform additional post-update steps; see the Release Notes for details.

All PostgreSQL update releases are cumulative. As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shut down PostgreSQL and update its binaries.

Links:

• Download
• Release Notes
• Security Page
• Abbreviated Keys Issue