PostreSQL.org

The JDBC project is proud to announce the latest version 42.2.3. This release fixes some annoyances such as:

• Reduce the severity of the error log messages when an exception is re-thrown. The error will be thrown to caller to be dealt with so no need to log at this verbosity by pgjdbc
• Support parenthesis in {oj ...} JDBC escape syntax
• getString for PGObject-based types returned "null" string instead of null
• Field metadata cache can be disabled via databaseMetadataCacheFields=0
• Properly encode special symbols in passwords in BaseDataSource
• Adjust date, hour, minute, second when rounding nanosecond part of a timestamp
• Encode URL parameters in BaseDataSource

As well as some performance enhancements:

• reduce memory allocations in query cache
• reduce memory allocations in SQL parser

The complete changelog can be reviewed here https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.3

You can download the PostgreSQL JDBC Driver at https://jdbc.postgresql.org/download.html

The JDBC team would like to thank everyone who contributed code and reviews: contributors

We are very glad to announce the 2.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 new version supports from 9.2 to v11 PostgreSQL versions. It is a new step in giving more flexibility in tables groups management. In particular, it allows to add tables or sequences to groups or move tables or sequences from one group to another without being obliged to stop/restart the logging mecanism (and loose the capability to 'rollback' to a previous point in time).

Both web clients, Emaj_web and the phpPgAdmin plugin, have been enhanced to take benefit from this feature.

Two new characters mode clients have been added. Written in perl, they bring the same features as the existing php clients: launch parallel E-Maj rollback and monitor E-Maj rollbacks.

The full documentation is available on line. The core extension is available at pgxn.org or github.org.

The phpPgAdmin plugin and the Emaj_web application are also available at github.org.

Have fun with E-Maj !

The PostgreSQL Global Development Group announces that the second beta release of PostgreSQL 11 is now available for download. This release contains previews of all features that will be available in the final release of PostgreSQL 11 (though some details of the release could change before then) as well as bug fixes that were reported during the first beta.

In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 11 in your database systems to help us eliminate any bugs or other issues that may exist. While we do not advise for you to run PostgreSQL 11 Beta 2 in your production environments, we encourage you to find ways to run your typical application workloads against this beta release.

Upgrading to PostgreSQL 11 Beta 2

To upgrade to PostgreSQL 11 Beta 2 from Beta 1 or an earlier version of PostgreSQL, you will to use a strategy similar to upgrading between major versions of PostgreSQL (e.g. pg_upgrade or pg_dump / pg_restore). For more information, please visit the documentation section on upgrading.

Changes to Major Features of PostgreSQL 11

The PostgreSQL 11 Beta 1 release announced "Channel Binding for SCRAM Authentication" as a major feature as it could prevent man-in-the-middle attacks when using SCRAM authentication. During the course of testing, it was determined that libpq, the client connection library for PostgreSQL, is unable force the use of channel binding.

While this functionality can be fixed in a future version of PostgreSQL, for now channel binding for SCRAM authentication will not be considered a major feature for PostgreSQL 11.

Changes Since Beta 2

There have been many bug fixes for PostgreSQL 11 reported during the Beta 1 period and applied to the Beta 2 release. Several bug fixes reported for version 10 or earlier that also affected version 11 are included in the Beta 2 release. These fixes include:

• Several fixes for XML support, including using the document node as the context for XPath queries as defined in the SQL standard, which affects the xpath and xpath_exists functions, as well as XMLTABLE
• Several fixes related to VACUUM, including potential data corruption issues
• Fixes for partitioning, including ensuring partitioning works with temporary tables, eliminating a needless additional partition constraint checks on INSERTs and generating incorrect paths for partitionwise aggregates
• Fix for potential replica server crashes where a replica would attempt to read a recycled WAL segment
• Fixes for pg_replication_slot_advance including returning NULL if slot is not advanced and changes for how the slot is updated depending on if it is a physical or logical replication slot
• Ensure pg_resetwal works with relative paths to data directory
• Fixes for query parallelism, including preventing a crash by ignoring "parallel append" for parallel unsafe paths in a query plan
• Fix returning accurate results with variance and similar aggregate functions when executed using parallel query
• Fix issue where COPY FROM .. WITH HEADER would drop a line after every 4,294,967,296 lines processed
• Ensure the "B" (bytes) parameter is accepted by all memory-related configuration parameters
• Several fixes for the JSONB transform in PL/Python and PL/Perl
• Fix for plpgsql checking statements where it needs to check original write statement before rewrite, could cause crash
• Fix for SHOW ALL to display superuser configuration settings to roles that are allowed to read all settings
• Fix for pg_upgrade that ensures defaults are written when using the "fast ALTER TABLE .. ADD COLUMN" feature with a non-NULL default
• Several fixes for memory leaks
• Several fixes specific to the Windows platform

This update also contains tzdata release 2018e, with updates for North Korea. The 2018e also reintroduces the negative-DST changes that were originally introduced in 2018a, which affects historical and present timestamps for Ireland (1971-), as well as historical timestamps for Namibia (1994-2017) and the former Czechoslovakia (1946-1947). If your application is storing timestamps with those timezones in the affected date ranges, we ask that you please test to ensure your applications behave as expected.

Testing for Bugs & Compatibility

The stability of each PostgreSQL release greatly depends on you, the community, to test the upcoming version with your workloads and testing tools in order to find bugs and regressions before the release of PostgreSQL 11. 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 please test in the near future. The quality of user testing helps determine when we can make a final release.

A list of open issues is publicly available in the PostgreSQL wiki. You can report bugs using this form on the PostgreSQL website:

https://www.postgresql.org/account/submitbug/

Beta Schedule

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

Links

• Download
• Beta Testing Information
• PostgreSQL 11 Beta Release Notes
• PostgreSQL 11 Open Issues
• Submit a Bug

RazorSQL 8, an SQL editor, database query tool, database browser, and database administration tool, is now available for Windows, Mac, and Linux. Using RazorSQL, users can query, edit, browse, and manage dozens of databases from one tool. RazorSQL supports over 40 databases, including PostgreSQL. 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, JSON, 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 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 8 include database conversion support from Firebird to nine different databases including PostgreSQL.

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

RazorSQL 8 is available for immediate download at https://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: https://razorsql.com/

Download URL: https://razorsql.com/download.html

Change Log: https://razorsql.com/updates.html

PostgreSQL Conference Europe 2018 in Lisbon, Portugal, on October 23-26 is now open for registrations for conference attendance at https://2018.pgconf.eu/registration/.

Early Bird discounted tickets will be available until September 10th, and is limited to 75 tickets which is a great reason to get your registration in early!

There will be training sessions provided on the Tuesday before the main event, but these sessions have not yet been scheduled and are not yet available for registration. Any regular registration made today can later be upgraded to include training if desired.

Our call for papers is still open! If you have already submitted a talk, or are planning to submit one, we suggest you wait to register until you have received a confirmation if the talk was accepted or not. The early bird rate will be available long enough for you to register after you have received this notification - and if your talk is accepted, attendance is of course free!

We are accepting proposals for talks in English. Each session will last 45 minutes, and may be on any topic related to PostgreSQL. The submission deadline is August 6th. Selected speakers will be notified before August 22nd, 2018.

Please submit your proposals by going to https://2018.pgconf.eu/callforpapers/ and following the instructions.

The proposals will be considered by committee who will produce a schedule to be published nearer the conference date. The members of the committee are listed on the website linked above.

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.

And finally, our Call for Sponsors is also still open. Our Platinum sponsor slots are sold out at this time, but we have free slots at all other levels remaining. Take your chance to present your services or products to the PostgreSQL community - or see it as a give back opportunity. All sponsorship levels also include one or more free entrance tickets, depending on level.

As usual, if you have any questions, don't hesitate to contact us at contact@pgconf.eu.

We look forward to seeing you in Lisbon in October!

Oxford, United Kingdom - June 14, 2018

2ndQuadrant today announced the release of repmgr 4.0.6.

repmgr is the most popular tool for PostgreSQL replication and failover management. It enhances and complements the built-in replication capabilities in PostgreSQL, making it easy to set up and manage standby servers, check replication status, and perform administrative tasks such as switchover operations. The repmgrd daemon provides automatic failover capability to maximize database uptime, and can easily be integrated with other tools such as PgBouncer for high application availability.

This release contains usability enhancements and bug fixes. Packages are available via public RPM and APT repositories from 2ndQuadrant. Installation packages documentation is available here.

Usability enhancements included in repmgr 4.0.6 are:

• Detect node connection issues. Return non-zero exit code in case repmgr_ cluster_ crosscheck and repmgr_ cluster_ matrix return an error.
• Improve handling of external file copying configuration. Additional checks are performed before cloning a standby, making sure configuration files are properly copied.
• Force log level to INFO. Ensure --dry-run option output is always displayed.

For a detailed list of all changes and bug fixes, please read the full release notes here

We recommend upgrading to this version as soon as possible. This release can be installed as a simple package upgrade from repmgr 4.x; repmgrd (if running) should be restarted.

Source files can be downloaded here and installation instructions are available here.

repmgr is distributed under GPL v3.

For more information, please send an email to info@2ndQuadrant.com

What is Pgpool-II?

Pgpool-II is a tool to add useful features to PostgreSQL, including:

• connection pooling
• load balancing
• automatic fail over and more.

Minor releases

Pgpool Global Development Group is pleased to announce the availability of following versions of Pgpool-II:

• 3.7.4
• 3.6.11
• 3.5.15
• 3.4.18

These are the latest stable minor versions of each major versions of Pgpool-II. Release notes for versions prior 3.6 are no longer included in the source code.

Please take a look at release notes.

You can download the source code and RPMs.

Hi,

PGconf.ASIA 2018 will be held on December 10 to 12 in Tokyo and we are now accepting proposals for talks.

Join us and other users and developers from around the world at the home of the oldest and largest user group; Japan!

(See last year's conference program).

About the conference:

• Location: Akihabara convention hall (Tokyo)
• Targeted number of attendees: 700 in total
• URL

About the call for papers:

• To submit a paper, please include the following details and send to: pgconf-asia-2018-submission(at)pgconf(dot)asia
• Title
• Abstract
• Description
• Language spoken during the talk: Japanese or English

• Language of the talk material: Japanese and/or English

• Submission deadline is midnight, 31st July, 2018 (Japan time).

• Submissions should be sent in English, Japanese, or both. Japanese-only submissions will be translated into English for discussion within the program committee.

• Presentation materials will be released the day after the conference and will be made available to the public. The copyright of the material will be retained by the author.

• We ask that you share your materials under a Creative Commons license.

• If your presentation material cannot be shared, please let us know.

• Talks may be recorded or photographed. In both cases, the content may be made public under a Creative Commons license. The copyright of the material is retained by the speaker.

• Speakers will be informed of the result of the selection by the end of August. Speakers will be requested to submit brief biographies and photos to be published in the conference program.

• The exact length of each session is not decided yet (Last year it was 40 minutes and we expect no big change for this year).

• Suggested topic areas include but not limited to:

• Large-scale PostgreSQL deployments.

• Migrations from other databases to PostgreSQL
• Operations and administration
• Performance and feature implementation
• Replication, clustering, HA, sharding.
• Tools and utilities for PostgreSQL
• Benchmarking and hardware, tuning.
• PostgreSQL community and hacking.
• Studies, surveys on PostgreSQL ecosystem
• Asian PostgreSQL community & user groups
• Data warehousing
• Location-aware and mapping software with PostGIS
• Research and teaching with PostgreSQL

• Case studies, including but not limited to: IoT/Cloud, Healthcare, Education and Academy

• If you have any questions regarding the event, feel free to contact the organization committee at pgconf-asia-2018-submission(at)pgconf(dot)asia(dot)

See you in Tokyo :)

PGConf.ASIA 2018 Steering Committee

[Please feel free to redistribute this CFP]

Oxford, United Kingdom — May 27, 2018

2ndQuadrant is proud to announce the release of Barman version 2.4. Barman (Backup and Recovery Manager) is an open-source administration tool for managing backup and disaster recovery of PostgreSQL servers. It allows you to perform remote backups of multiple servers in business critical environments and helps DBAs during the recovery phase.

Barman 2.4 presents new features including:

• Add standard and retry hook scripts for backup deletion (pre/post)
• Add standard and retry hook scripts for recovery (pre/post)
• Add standard and retry hook scripts for WAL deletion (pre/post)
• Add --standby-mode option to barman recover to set standby_mode = on in the pre-generated recovery.conf
• Add --target-action option to barman recover, allowing users to specify shutdown, pause or promote actions in the pre-generated recovery.conf file
• Improve usability of point-in-time recovery with consistency checks (e.g., recovery time is after end time of backup)

The most important bug fixes included in this release are:

• Fix remote get_file_content method, preventing incremental recovery from happening
• Unicode issues with command
• Add --wal-method=none when pg_basebackup >= 10

With this release Barman has dropped support for Python 3.3.

A complete list of changes is available here.

Download and installation instructions are available here.

Barman is developed and maintained by 2ndQuadrant and distributed under GPL v3.

For more information, please email info@2ndQuadrant.com.

The Elephant Shed PostgreSQL Appliance bundles and integrates proven components, required for easy management of a PostgreSQL server. Proven tools for all relevant aspects are already preinstalled and preconfigured. The majority of these tools can be controlled via a comfortable web interface. Even experienced PostgreSQL administrators will hardly find an area not covered by Elephant Shed PostgreSQL appliance.

New in version 1.2 is support for Ubuntu Bionic (18.04), next to the existing support for Debian Stretch (9). Both distributions are fully supported on the amd64 (x86_64) and ppc64el (IBM POWER) architectures.

For version 1.2, the Grafana dashboard was overhauled based on user feedback. Multiple PostgreSQL clusters per machine are now distinguished using the cluster name such as "10/main". All metrics panels feature proper units, i.e. axes are now labeled "bytes per second" or "operations per minute". Grafana was updated to version 5.

The Prometheus timeseries database was updated to version 2, yielding better performance and more compact storage. Unfortunately, the storage format is not compatible, so old monitoring data is not preserved.

Bug fixes and improvements:

• prometheus-sql-exporter autodetection of PostgreSQL clusters and databases rewritten, more stable
• Prometheus uses host name and cluster name in timeseries labels
• prometheus-node-exporter upgraded to version 0.15
• pgBadger log_line_prefix handling fixed

Elephant Shed is an open source project hosted on GitHub. Feedback and contributions are welcome! For the Elephant Shed PostgreSQL appliance, credativ offers comprehensive technical support with service level agreements, which are also available on 365 days a year and 24 hours a day as an option.

The Citus 7.4 extension release is here. This release primarily focused on improving speed to move faster through refactoring and clean-up of unused functionality. In addition to much of the refactoring for the Citus executors included improvements are:

• Support for non-pushdownable subqueries and CTEs in UPDATE/DELETE queries
• Support for pushdownable subqueries and joins in UPDATE/DELETE queries
• Faster shard pruning for subqueries
• Support for (VACUUM | ANALYZE) VERBOSE
• Support for multiple ANDs in HAVING for pushdown planner
• Support for quotation needy schema names
• much more

You can read more in our release announcement.

The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 11 is now available for download. This release contains previews of all features that will be available in the final release of PostgreSQL 11, though some details of the release could change before then.

In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 11 in your database systems to help us eliminate any bugs or other issues that may exist. While we do not advise for you to run PostgreSQL 11 Beta 1 in your production environments, we encourage you to find ways to run your typical application workloads against this beta release.

Major Features of PostgreSQL 11

PostgreSQL 11 major features center around performance, working with larger data sets, and ease-of-use.

Major Improvements to Partitioning

PostgreSQL 11 contains several features that improve working with data in partitions, including:

• The ability to partition by a hash key ("hash partitioning")
• UPDATE statements issued to a partition key now move affected rows to the appropriate partitions
• Improved SELECT query performance due to enhanced partition elimination during query processing and execution as well as parallelized partition scans
• Support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitions

and more.

PostgreSQL 11 also introduces the ability for a query to distribute grouping and aggregation to partitioned tables before the final aggregation. This is disabled by default, to enable, you need to set enable_partitionwise_aggregate = on in your configuration file or during your session or transaction.

Improvements to Parallelism

Several features added to PostgreSQL 11 take advantage of the parallel query infrastructure and provide significant performance enhancements, including:

• Parallelized hash joins
• Parallelized CREATE INDEX for B-tree indexes
• Parallelized CREATE TABLE .. AS, CREATE MATERIALIZED VIEW, and certain queries with UNION

SQL Stored Procedures

PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure. Procedures can be created using the CREATE PROCEDURE command and executed using the CALL command.

Just-In-Time (JIT) Compilation Introduced

PostgreSQL 11 introduces support for Just-In-Time (JIT) compilation to optimize the execution of code and other operations at run time. Using components of the LLVM project, the introduction of JIT support in PostgreSQL accelerates queries that utilize expressions (e.g. WHERE clauses), target lists, aggregates, projections, as well as some internal operations.

The JIT framework in PostgreSQL is designed to allow future work for optimizations that can be added in the future. If you are building PostgreSQL 11 from source, you can enable JIT compilation using the --with-llvm flag.

Window Functions Fully Support SQL:2011 Standard

As of PostgreSQL 11, window functions now support all options shown in the SQL:2011 standard, including RANGE _distance_ PRECEDING/FOLLOWING, GROUPS mode, and frame exclusion options.

Channel Binding for SCRAM Authentication

The previous version of PostgreSQL introduced SCRAM authentication to improve the storage and transmission of passwords using an accepted standard protocol. PostgreSQL 11 introduces channel binding for SCRAM authentication, which adds to the security of using SCRAM by preventing man-in-the-middle attacks.

User Experience Enhancements

The PostgreSQL Global Development Group recognizes that while PostgreSQL itself contains a robust set of features, not all of them may be easy-to-use and could require workarounds in both development and production environments.

Prior to PostgreSQL 11, one such feature was using the ALTER TABLE .. ADD COLUMN command where the newly created column had a DEFAULT value that was not NULL. Prior to PostgreSQL 11, when executing aforementioned statement, PostgreSQL would rewrite the whole table, which on larger tables in active systems could cause a cascade of problems. PostgreSQL 11 removes the need to rewrite the table in most cases, and as such running ALTER TABLE .. ADD COLUMN .. DEFAULT .. will execute extremely quickly.

Another feature that fell into this category was the inability to intuitively quit from the PostgreSQL command-line (psql). There has been numerous recorded complaints of users trying to quit with the quit and exit commands, only to learn that the command to do so was \q.

We have heard your frustrations and have now added the ability to quit the command-line using the keywords quit and exit and hope that quitting a PostgreSQL session is now as enjoyable as using PostgreSQL.

Additional Features

Many other new features and improvements have been added to PostgreSQL 11, some of which may be as or more important to specific users than what is mentioned above. Please see the Release Notes for a complete list of new and changed features.

Testing for Bugs & Compatibility

The stability of each PostgreSQL release greatly depends on your, the community, to test the upcoming version with your workloads and testing tools in order to find bugs and regressions before the release of PostgreSQL 11. 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 please test in the near future. The quality of user testing helps determine when we can make a final release.

A list of open issues is publicly available in the PostgreSQL wiki. You can report bugs using this form on the PostgreSQL website:

https://www.postgresql.org/account/submitbug/

Beta Schedule

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

Links

• Download
• Beta Testing Information
• PostgreSQL 11 Beta Release Notes
• PostgreSQL 11 Open Issues
• Submit a Bug

Introduction

The PostgreSQL Frontend (Pgfe) is client C++ API to PostgreSQL servers. This software is "beta" quality, and the API is a subject to change. Any feedback is highly appreciated.

Features

Current API allows to work with:

• database connections (in both blocking and non-blocking IO manner);
• prepared statements (named parameters are supported);
• SQLSTATE codes (as simple as with enums);
• dynamic SQL;
• extensible data type conversions (including support of PostgreSQL arrays to/from STL containers conversions).

Features of the near future

The urgent TODO-list includes:

• support for Large Objects via IO streams of the Standard C++ library;
• support of COPY command;
• support of work with SQL queries separately of C++ code;
• the Composite data type to work with composite types;
• the Dynamic_array data type to work with arrays of variable dimensions;
• C API.

Details

For details please refer to:

• the Pgfe documentation;
• the Pgfe project page on GitHub.

Oxford, United Kingdom — May 17, 2018

2ndQuadrant announced today the release of OmniDB 2.8. OmniDB is an interactive and powerful, yet lightweight, browser-based database management tool. OmniDB 2.8 allows users to manage multiple databases in a unified workspace with a user-friendly and fast-performing interface.

This new release presents important performance improvements: OmniDB is capable of connecting and identifying main structures such as tables, keys, indexes, and constraints in an impressive response time averaging less than 0.1 seconds.

OmniDB 2.8 introduces the following new features:

• Support for BDR 3.0. OmniDB now manages BDR groups, allowing the user to view/add/remove nodes from groups and perform all tasks in every node.
• Connection to any database via SSH tunnels. When connecting to a remote database through the Internet, SSH tunnels provide improved security to the database connection.

This release presents improvements including:

• Backslash commands supporting PostgreSQL 10 partitioned tables and partitions.
• DDL for PostgreSQL 10 partitioned tables and partitions.
• Tree view shows PostgreSQL 10 partitioned tables and partitions with different icons.
• Improvements to the OmniDB app window container.

The bug fixes included in this release are:

• Fixed displaying table data in treeview when using MySQL 5.7.
• Fixed column autocomplete when using MySQL and MariaDB.
• Fixed JSON and JSONB editing.

For a complete list of updates, read the OmniDB change tracker here.

OmniDB 2.8 is available for download here and installation instructions are available here.

For more information, please email info@2ndQuadrant.com

The Call For Papers for PostgresOpen SV 2018 is now open!

PostgresOpen, the U.S. PostgreSQL community conference, is back this year taking place in downtown San Francisco from September 5th to 7th.

If you are working with PostgreSQL, please go here 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
• Data science

The 2018 PostgresOpen Committee looks forward to bringing the best PostgreSQL presentations and tutorials from speakers around the world to San Francisco! We're only able to do that with support from our great sponsors!

PostgresOpen would not be possible without the support of our sponsors, a special thanks to our sponsors:

• Citus Data
• 2nd Quadrant
• EnterpriseDB

If you are interested in sponsoring, please visit our sponsor prospectus here

Anyone and everyone in the PostgreSQL community is encouraged to submit a talk. Talks will be accepted up until June 12th. Speakers will be notified by June 19th, with the schedule to be published once selected speakers have confirmed. Join us in celebrating our 8th conference year! We look forward to seeing everyone in San Francisco!

Any questions? Please contact: program2018(at)postgresopen(dot)org

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 10.4, 9.6.9, 9.5.13, 9.4.18, 9.3.23. This release fixes one security issue as well as several bugs reported over the last three months. Users should plan to update at the next convenient downtime.

Please see the "Updating" section for post-installation steps for the security fix and the "incorrect volatility and parallel-safety markings" fix.

Please note that PostgreSQL changed its versioning scheme with the release of version 10.0, so updating to version 10.4 from 10.0, 10.1, 10.2, or 10.3 is considered a minor update.

Security Issues

One security vulnerability has been closed by this release:

• CVE-2018-1115: Too-permissive access control list on function pg_logfile_rotate()

Please see the "Updating" section below for post-update steps.

Bug Fixes and Improvements

This update also fixes over 50 bugs reported in the last several months. Some of these issues affect only version 10, but many affect all supported versions.

These fixes include:

• Fix incorrect volatility and parallel-safety markings on several built-in functions to ensure correct query planning optimizations
• Several fixes for partitioning, including potential crashes as well as allowing TRUE and FALSE to be used as partition bounds
• Fix where a new TOAST value could be assigned to a dead-but-not-yet-vacuumed TOAST OID, which would result in an error similar to "unexpected chunk number 0 (expected 1) for toast value nnnnn"
• Fix "CREATE TABLE ... LIKE" with bigint identity columns on 32-bit platforms
• Fix memory leak within the runtime of a query that repeatedly executes hash joins
• Several crash fixes around queries using GROUPING SET
• Avoid failure if a query-cancel or session-termination interrupt occurs while committing a prepared transaction
• Reduce locking during autovacuum worker scheduling, which prevents loss of potential worker concurrency
• Fix possible slow execution of REFRESH MATERIALIZED VIEW CONCURRENTLY
• Several fixes around query plans that use "index-only" scans
• Avoid deadlocks in concurrent CREATE INDEX CONCURRENTLY commands that are run under SERIALIZABLE or REPEATABLE READ transaction isolation
• Several fixes for SP-GiST indexes, including one collation-aware searches on text columns
• Fixes related to counting the number of tuples in partial GiST, SP-GiST, and Bloom indexes
• Several fixes for logical decoding and replication
• Fix misquoting of values for list-valued GUC variables (e.g. local_preload_libraries, session_preload_libraries, shared_preload_libraries, temp_tablespaces) in dumps
• Several fixes for pg_stat_activity
• Several fixes for ecpg
• Fix for pg_recvlogical to ensure compatibility with PostgreSQL versions released before 10
• Several fixes for pg_rewind

This update also contains tzdata release 2018d, with updates for Palestine and Antarctica (Casey Station), plus historical corrections for Portugal and its colonies, as well as Enderbury, Jamaica, Turks & Caicos Islands, and Uruguay.

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 to apply this update release (though for this release, please see the note about "volatility and parallel-safety markings"); 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.

Post-Installation Steps for CVE-2018-1115

If you installed "adminpack" in any PostgreSQL 9.6 or 10 release you will need to have your database administrator run the following command in every database that "adminpack" is installed in:

ALTER EXTENSION adminpack UPDATE; Post-Installation Steps for Function Markings

Functions that should be marked "volatile"

• cursor_to_xml
• cursor_to_xmlschema
• query_to_xml
• query_to_xml_and_xmlschema
• query_to_xmlschema

Functions that should be marked "parallel-unsafe"

• binary_upgrade_create_empty_extension,
• brin_desummarize_range
• brin_summarize_new_values
• brin_summarize_range
• cursor_to_xml
• cursor_to_xmlschema
• gin_clean_pending_list
• pg_import_system_collations
• ts_rewrite
• ts_stat

If you use any of the above functions, you can update the markings in the following ways:

Option 1: Update the markings manually in the "pg_proc" table in each database you use

these functions. You can achieve this by executing the following commands as a database superuser:

/* Functions that should be marked "volatile" */ ALTER FUNCTION pg_catalog.cursor_to_xml(refcursor, int, boolean, boolean, text) VOLATILE; ALTER FUNCTION pg_catalog.cursor_to_xmlschema(refcursor, boolean, boolean, text) VOLATILE; ALTER FUNCTION pg_catalog.query_to_xml(text, boolean, boolean, text) VOLATILE; ALTER FUNCTION pg_catalog.query_to_xml_and_xmlschema(text, boolean, boolean, text) VOLATILE; ALTER FUNCTION pg_catalog.query_to_xmlschema(text, boolean, boolean, text) VOLATILE; /* Functions that should be marked "parallel-unsafe" */ ALTER FUNCTION pg_catalog.binary_upgrade_create_empty_extension(text, text, bool, text, _oid, _text, _text) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.brin_desummarize_range(regclass, bigint) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.brin_summarize_new_values(regclass) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.brin_summarize_range(regclass, bigint) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.cursor_to_xml(refcursor, int, boolean, boolean, text) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.cursor_to_xmlschema(refcursor, boolean, boolean, text) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.gin_clean_pending_list(regclass) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.pg_import_system_collations(regnamespace) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.ts_rewrite(tsquery, text) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.ts_stat(text) PARALLEL UNSAFE; ALTER FUNCTION pg_catalog.ts_stat(text, text) PARALLEL UNSAFE;

Option 2: Run a pg_upgrade to a version containing the correct marking data (e.g. 10.4 and above)

Links

• Download: https://www.postgresql.org/download/
• Release Notes: https://www.postgresql.org/docs/current/static/release.html
• Security Page: https://www.postgresql.org/support/security/
• Versioning Policy: https://www.postgresql.org/support/versioning/
• Follow @postgresql on Twitter: https://twitter.com/postgresql

May 8, 2018: Crunchy Data announces the release of pgBackRest 2.02, the latest version of the simple, reliable backup and restore system that can seamlessly scale up to the largest databases and workloads.

pgBackRest 2.02 continues advancing in performance and configurability by introducing parallel asynchronous archive-get and a configuration include directory that allows configuration to be split into smaller fragments for easier management.

pgBackRest supports a robust set of features for managing your backup and recovery infrastructure, including: parallel backup/restore, full/differential/incremental backups, delta restore, parallel asynchronous archiving, per-file checksums, page checksums (when enabled) validated during backup, compression, encryption, partial/failed backup resume, backup from standby, tablespace and link support, S3 support, backup expiration, local/remote operation via SSH, flexible configuration, and more.

You can install pgBackRest from the PostgreSQL Yum Repository or the PostgreSQL APT Repository. Source code can be downloaded from Releases.

Links

• Website
• User Guide
• Release Notes
• Support

New Features Parallel Asynchronous Archive Get

The asynchronous archive-get command maintains a local queue of WAL to improve replay throughput. If a WAL segment is not found in the queue it is fetched from the repository along with enough consecutive WAL to fill the queue. Additional WAL is also fetched asynchronously when the queue is less than half full. Enabling parallelism increases throughput even more, especially for higher-latency connections and storage (such as S3).

Documentation

Configuration Includes

Configuration fragments can be dropped in the configuration includes directory (defaults to /etc/pgbackrest/conf.d) and they will be loaded along with the main configuration file, if it exists. For example, each stanza can be in a separate configuration fragment and general host settings in the main configuration file. This feature makes it easier to automate pgBackRest configuration with your favorite configuration management tool.

Documentation

Oxford, United Kingdom - May 3, 2018

2ndQuadrant today announced the release of repmgr 4.0.5. repmgr is the most popular tool for PostgreSQL replication and failover management. It enhances and complements the built-in replication capabilities in PostgreSQL, making it easy to set up and manage standby servers, check replication status, and perform administrative tasks such as switchover operations. The repmgrd daemon provides automatic failover capability to maximize database uptime, and can easily be integrated with other tools such as PgBouncer for high application availability.

This release is the first to provide packages via 2ndQuadrant’s new public RPM and APT repositories; for more details view the installation packages document here.

repmgr 4.0.5 includes usability enhancements related to pg_rewind usage, recovery.conf generation, and, in repmgrd, handling of various corner-case situations. These enhancements are:

• Poll demoted primary after restart as a standby during a switchover operation
• Add configuration parameter config_directory.
• Add sanity check if --upstream-node-id not supplied when executing repmgr standby register.
• Enable pg_rewind to be used with PostgreSQL 9.3/9.4
• When generating replication connection strings, set dbname=replication if appropriate.
• Enable provision of archive_cleanup_command in recovery.conf.
• Check actively for node to rejoin cluster.
• Set connect_timeout=2 in repmgrd if not explicitly set, when pinging a server.
• Various documentation improvements, with particular emphasis on the importance of setting appropriate service commands instead of relying on pg_ctl.

This release contains the following bug fixes:

• Fix display of conninfo parsing error messages.
• Fix minimum accepted value for degraded_monitoring_timeout.
• Fix superuser password handling.
• Fix parsing of archive_ready_critical configuration file parameter.
• Fix repmgr_cluster_crosscheck output.
• Fix memory leaks in witness code.
• Handle pg_ctl promote timeout in repmgrd.
• Handle failover situation in repmgrd with only two nodes in the primary location, and at least one node in another location .
• Prevent standby connection handle from going stale in repmgrd.

For a detailed list of all changes, please read the full release notes here.

We highly recommend upgrading to repmgr 4.0.5 at the earliest opportunity available. For this release, a simple package upgrade from repmgr 4.0 - 4.0.4 is required; repmgrd (if running) should be restarted.

Source files can be downloaded here and installation instructions are available here.

repmgr is developed and maintained by 2ndQuadrant - your diligent team of PostgreSQL experts.

repmgr is distributed under GPL v3.

For more information, please send an email to info@2ndQuadrant.com

The AgensGraph Development Team are pleased to announce the release of AgensGraph v1.3.

AgensGraph is a new generation multi-model graph database based on PostgreSQL for highly connected data.
AgensGraph offers the graph analysis environment in which users can write, edit, and execute
SQL and Cypher query together at the same time. AgensGraph comes along with PostgreSQL compatibility
and PostgreSQL Extensions. With AgensGraph, a PostgreSQL for graph database, PostgreSQL users are free
from the pain of data migration and easily develop services which provide advanced data analysis.

This release of AgensGraph v1.3 includes the bug fixes and of course, many improvements.
For more details please see the release notes.

Major Improvements and Bug Fixes:

• Improvements
• Enhanced Query Plan Performance
• Supports for More Cypher Expressions
• Enhanced Supports for External Module
• Improved Usability
• Optimized Memory Usage (v1.3.1)
• Bug Fixes
• Fixed Bugs Related to Using Multiple Cypher Keywords
• Fixed Memory Leak in Loading Data
• Fixed Bugs Related to Vertex Reference

The new version is immediately available for download.

What is Pgpool-II?

Pgpool-II is a tool to add useful features to PostgreSQL, including:

• connection pooling
• load balancing
• automatic fail over and more.

Minor releases

Pgpool Global Development Group is pleased to announce the availability of following versions of Pgpool-II:

• 3.7.3
• 3.6.10
• 3.5.14
• 3.4.17
• 3.3.21

These are the latest stable minor versions of each major versions of Pgpool-II. Release notes for versions prior 3.6 are no longer included in the source code.

Please take a look at release notes.

You can download the source code and RPMs.