Discuss, share, learn about the world's best open source database, PostgreSQL. News, articles, tips, scripts, forums, and more.

OmniDB 2.8 Available - Now with Support for 3rd Generation Postgres-BDR

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 [email protected]

Continue reading...

Call for papers - PostgresOpen 2018, September 5-7 in San Francisco, CA

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:

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

Continue reading...

PostgreSQL 10.4, 9.6.9, 9.5.13, 9.4.18, and 9.3.23 released!

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.


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:


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_rewrite(tsquery, tsquery, tsquery) 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)


Continue reading...

pgBackRest 2.02 Released

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.


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).


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.


Continue reading...

Announcing The Release of repmgr 4.0.5

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 [email protected]

Continue reading...