PostreSQL.org

The pgagroal community is happy to announce version 0.8.0.

New features

• Authnetication query support
• Failover support
• systemd support

Plus various improvements.

pgagroal

pgagroal is a high-performance protocol-native connection pool for PostgreSQL.

Features

• High performance
• Connection pool
• Limit connections for users and databases
• Prefill support
• Remove idle connections
• Perform connection validation
• Enable / disable database access
• Graceful / fast shutdown
• Prometheus support
• Remote management
• Authnetication query support
• Failover support
• Transport Layer Security (TLS) v1.2+ support
• Daemon mode
• User vault

Learn more on our web site or GitHub. Follow on Twitter.

pgagroal is released under the 3-clause BSD license, and is sponsored by Red Hat.

July 28, 2020: Crunchy Data announces the release of pgBackRest 2.28, the latest version of the reliable, easy-to-use backup and restore solution that can seamlessly scale up to the largest databases and workloads.

pgBackRest has introduced many exciting new features this year including Azure repository storage, time-based retention for full backups, ad hoc backup expiration, Zstandard/lz4/bzip2 compression, backup auto-select for time-based PITR, and a pure C implementation.

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, multiple compression types, encryption, partial/failed backup resume, backup from standby, tablespace and link support, S3/Azure 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 Guides
• Release Notes
• Support

New Features Azure Repository Storage

Repositories may now be located on Azure blob storage using either shared key or SAS authentication. See Documentation.

Time-Based Retention for Full Backups

Full backups can be expired based on the age of the backup rather than the total number of backups. This prevents violating the retention period in the case where a new full backup is made outside the regular schedule. See Documentation.

Ad Hoc Backup Expiration

Any backup may be expired even if it is still inside the retention period. This allows a custom retention scheme to be implemented or a single backup made by mistake to be removed. See Documentation.

Zstandard/lz4/bzip2 Compression

These new compression options allow for more choices between compression speed and ratio. Note that only bzip2 is available on all supported platforms. lz4 is available on most non-EOL platforms and Zstandard on only newer platforms. See Documentation.

Backup Auto-Select for Time-Based PITR

When restoring with the --target-time option the optimal backup will be selected automatically based on the time provided. See Documentation.

Pure C Implementation

pgBackRest is now implemented in pure C99. This implementation improves performance, simplifies dependencies, and allows deep integration with PostgreSQL data structures on any architecture.

Mons, Belgium, July 28, 2020

check_pgbackrest is designed to monitor pgBackRest backups from Nagios, relying on the status information given by the info command.

It allows to monitor the backups retention and the consistency of the archived WAL segments.

Changes in check_pgbackrest 1.9

• The archives service will now only look at the archives listed between the oldest backup start archive and the max WAL returned by the pgBackRest info command. This should avoid unnecessary alerts. To extend the check to all the archives found, the new --extended-check argument has been implemented (suggested by blogh).
• Remove refresh of pgBackRest info return after getting the archives list. That avoids CRITICAL alert if an archive is generated between those two steps. Instead, a WARNING message "max WAL is not the latest archive" will be displayed (suggested by blogh).
• Fix S3 archives detection (reported by khadijahvf).
• New enable-internal-pgbr-cmds argument, for pgBackRest >= 2.28. Internal pgBackRest commands will then be used to list and get the content of files in the repository instead of Perl specific drivers. This is, for instance, needed to access encrypted repositories. This should become the default and only access method in the next release, removing some Perl dependencies.

Links & Credits

check_pgbackrest is part of the Dalibo Labs initiative. It is mainly developed by Stefan Fercot. This is an open project, licensed under the PostgreSQL license. Any contribution to improve it is welcome.

Links:

• [Download]: https://github.com/dalibo/check_pgbackrest/releases
• [Support]: https://github.com/dalibo/check_pgbackrest/issues

We are very glad to announce the 3.4.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 removes the limitation regarding TRUNCATE SQL verbs. These statements can now be logged and rolled back by E-Maj, without any restrictions.

The User Interface of the Emaj_web web client has also been largely improved.

The version includes 2 minor fixes.

It now supports from 9.5 to v13 PostgreSQL versions.

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

The Emaj_web client is also available at github.org.

Have fun with E-Maj !

This maintenance release improves the support for spatial datatypes. When postgis is installed on the target database then the spatial data types point,geometry,linestring,polygon, multipoint, multilinestring, geometrycollection are converted to geometry and the data is replicated using the Well-Known Binary (WKB) Format. As the MySQL implementation for WKB is not standard pg_chameleon removes the first 4 bytes from the decoded binary data before sending it to PostgreSQL.

When keep_existing_schema is set to yes now drops and recreates indices, and primary keys during the init_replica process. The foreign keys are dropped as well and recreated when the replica reaches the consistent status. This way the init_replica may complete successfully even when there are foreign keys in place and with the same speed of the usual init_replica.

The setup.py now forces PyMySQL to version <0.10.0 because it breaks the python-mysql-replication library (issue #117).

Thanks to @porshkevich which fixed issue #115 by trimming the space from PK index name.

This release requires a replica catalogue upgrade, therefore is very important to follow the upgrade instructions provided below.

• If working via ssh is suggested to use screen or tmux for the upgrade
• Stop all the replica processes with chameleon stop_all_replicas --config your_config
• Take a backup of the schema sch_chameleon with pg_dump as a good measure.
• Install the upgrade with pip install pg_chameleon --upgrade
• Check if the version is upgraded with chameleon --version
• Upgrade the replica schema with the command chameleon upgrade_replica_schema --config your_config
• Start all the replicas.

If the upgrade procedure can't upgrade the replica catalogue because of running or errored replicas is it possible to reset the statuses by using the command chameleon enable_replica --source source_name.

If the catalogue upgrade is still not possible then you can downgrade pgchameleon to the previous version. Please note that you may need to install manually PyMySQL to fix the issue with the version 0.10.0.

pip install pg_chameleon==2.0.13

pip install "PyMySQL<0.10.0"

• pg_chameleon on Github
• pg_chameleon on pypi

Crunchy Data is pleased to announce the release of the Crunchy PostgreSQL Operator 4.4, which automates and simplifies deploying and managing of open source PostgreSQL clusters on Kubernetes and other Kubernetes-enabled Platforms. Instructions for getting started with the PostgreSQL Operator can be found here:

https://access.crunchydata.com/documentation/postgres-operator/latest/quickstart/

This release furthers the ability to create new PostgreSQL clusters from existing setups by allowing one to create a cluster from a pgBackRest repository. This means you can use a pgBackRest repository that is not attached to a current PostgreSQL cluster to create new PostgreSQL clusters. The Postgres Operator also adds support for S3 URI paths, making it easier to work with object storage systems such as MinIO.

Major features of the 4.4 release include:

• Cluster Creation from Existing pgBackRest Repositories
• Improved Compatibility with S3 Object Storage Systems
• Certificate-based Authentication for Replication
• Support for Helm v3

For a full list of features, including detailed descriptions of these changes, please see the release notes.

Links

• Project Repo
• Download
• Documentation
• Release Notes

Crunchy Data is proud to support the development and maintenance of the PostgreSQL Operator.

This maintenance release adds the EXPERIMENTAL support for Point datatype thanks to the contribution by @jovankricka-everon.

The support is currently limited to only the POINT datatype with hardcoded stuff to keep the init_replica and the replica working. However as this feature is related with PostGIS, the next point release will rewrite this part of code using a more general approach.

The release adds the keep_existing_schema parameter in the MySQL source type. When set to Yes init_replica,refresh_schema and sync_tables do not recreate the affected tables using the data from the MySQL source. Instead the existing tables are truncated and the data is reloaded.

A REINDEX TABLE is executed in order to have the indices in good shape after the reload. The next point release will very likely improve the approach on the reload and reindexing.

When keep_existing_schema is set to Yes the parameter grant_select_to have no effect.

From this release the codebase switched from tabs to spaces, following the guidelines in PEP-8.

• pg_chameleon on Github
• pg_chameleon on pypi

Oxford, United Kingdom - July 9, 2020

2ndQuadrant is proud to announce the release of Barman version 2.11.

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.

This release fully supports the upcoming PostgreSQL 13, and introduces the barman-cli-cloud package, which contains all cloud related utilities previously released with version 2.10, such as barman-cloud-wal-archive and barman-cloud-backup, as well as 3 additional ones:

• barman-cloud-wal-restore: script to be used as restore_command in PostgreSQL to directly retrieve WAL files from an object store such as AWS S3.
• barman-cloud-restore: script to be used to retrieve a backup from an object store and prepare the recovery of a PostgreSQL instance.
• barman-cloud-backup-list: script to be used to list the content of an object store.

If you are upgrading from version 2.10 and are already using barman-cloud-wal-archive or barman-cloud-backup, you must install the barman-cli-cloud package as described in the release notes.

In terms of security, Barman now supports non superuser connections to PostgreSQL 10 or higher. The user requires pg_read_all_settings and pg_read_all_stats privileges, as well as execution grants on some backup related functions. Please refer to the documentation for more details.

Minor bugs have also been fixed.

We strongly recommend upgrading to Barman v2.11 at the earliest opportunity available.

A complete list of changes and bug fixes is available here.

Download and installation instructions are available here.

Source files are available for download here.

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

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

AGE, a multi-model graph database extension for PostgreSQL has been announced. AGE is the successor to AgensGraph. AGE will offer the same integration of SQL and Cypher without users having to discard their existing solutions, allow for a cleaner integration of AGE with PostgreSQL’s robust collection of other extensions, and expand scalability without sacrificing performance.

AGE has been recently elected as an incubation podling project by the Apache Software Foundation (ASF). The project has been donated to ASF as an open-source initiative.

AGE v0.2.0 Release Notes:

• Extend Agtype
  • Support for numeric, edge, and path types.

• Type Casting
  • Casting to a numeric, float, vertex, edge, and path.
  • Addition of float constants (inf, -inf, NaN) to string input and output.
  • Casting to a vertex ‘::vertex’ from an agtype object.
  • Casting to an edge “::edge’ from an agtype object.
  • Casting to a path ‘::path’ from an agtype array.

• Scalar Functions
  • Id, start_id, end_id, type,properties, head, last, length, size, startNode, endNode, timestamp, toBoolean, toFloat, toInteger, and coalesce.

• Labels for Vertices/Edges
  • Newly created Edges can be assigned with a label.
  • Newly created Labels vertices do not require a label.

• Improve Match Clause
  • Support Edges - Match clauses support paths.

• Improve Create Clause
  • Supports vertices, edges, patterns, and edges and paths with or without variables.

• Support Match Clause Before Create Clause
  • CREATE clause can use variables declared in any previous MATCH clause.

• Support Return Clause After Create Clause
  • Variables assigned in any Clauses before the CREATE clause can be used in the RETURN clause.

The version is immediately available for download.

pg_probackup is a utility to manage backup and recovery of PostgreSQL database cluster. It is designed to perform periodic full and incremental page-level backups of the PostgreSQL instance that enable you to restore the server in case of a failure.

Notable changes:

New features:

• Incremental restore. During incremental restore the valid unchanged pages available in the destination data directory are not rewritten. This greatly increases the speed, reduces the network load and I/O consumption.
• New option for restore command: -I | --incremental-mode mode. Two incremental restore modes are available: checksum and lsn.

Improvements:

• The memory consumption during backup and merge operations is reduced.
• The speed of restore for incremental backup chains is greatly improved, in some extreme cases of particularly long chains - by an order of magnitude.
• Remote backup speed is improved thanks to moving the whole process of scanning the remote data directory tree to the remote agent. On systems with high network latency and large amount of files the speed gain can be significant.

Bugfixes:

• archive-push and archive-get output to PostgreSQL text log no longer breaks pgbadger parsing. Reported by @triwada and Mikhail Kulagin.
• Previously running backup right after replica promotion resulted in error, because timeline ID was obtained before pg_start_backup execution.

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

You can find information about all of the features and changes found in PostgreSQL 13 in the release notes:

https://www.postgresql.org/docs/13/release-13.html

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

You can read more about our beta testing process and how you can contribute here:

https://www.postgresql.org/developer/beta/

Upgrading to PostgreSQL 13 Beta 2

To upgrade to PostgreSQL 13 Beta 2 from Beta 1 or an earlier version of PostgreSQL, you will need 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 Since Beta 1

There have been many bug fixes for PostgreSQL 13 reported during the Beta 1 period and applied to the Beta 2 release. This includes:

• The effective_io_concurrency configuration parameter is now simpler to use. To convert from the old value to the new value, you can use this formula: SELECT round(sum(OLD / n::float)) from generate_series(1, OLD) s(n);.
• The enable_hashagg_disk configuration parameter is renamed to hashagg_avoid_disk_plan and defaults to off.
• The enable_groupingsets_hash_disk configuration parameter has been removed.
• EXPLAIN ANALYZE output for parallel query plans that have hash aggregates spill to disk now show max memory and disk usage per worker.
• Several additional fixes and improvements for the hash aggregation spilling to disk feature.
• Fix a crash in the WAL sender when starting physical replication.

Please see the release notes for a complete list of new and changed features:

https://www.postgresql.org/docs/13/release-13.html

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 general availability of PostgreSQL 13. As this is a Beta, 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 13. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2020. For further information please see the Beta Testing page.

Links

• Download
• Beta Testing Information
• PostgreSQL 13 Beta Release Notes
• PostgreSQL 13 Open Issues
• Submit a Bug
• Follow @postgresql on Twitter

PgBouncer 1.14.0 is out. This release contains several significant enhancements.

Encrypted SCRAM secrets in PgBouncer can now be used for server-side login, so storing plain-text passwords in PgBouncer is no longer necessary to be able to use SCRAM. The systemd integration has been enhanced to support socket activation. Also, PgBouncer now supports Unix-domain sockets on Windows, matching the new capabilities of PostgreSQL 13.

See

https://www.pgbouncer.org/2020/06/pgbouncer-1-14-0

for more information, the detailed changelog, and download links.

PgBouncer is a lightweight connection pooler for PostgreSQL.

This is a maintenance release. As a result of switching to Gradle for building the code we had a small mistake in the dependencies. This cause previously optional dependencies on org.osgi.core, org.osgi.enterprise, and waffle-jna to be made mandatory. This release fixes that problem. There are no code changes in this release. See the changelog for all the details

Database .NET v30.4 is an innovative, powerful and intuitive multiple database management tool.

Major New features from version 29.5 to 30.4:

• (Main) Added support for PostgreSQL 13
• (Main) Performance improvements
• (Main) Added Vertical Tabs Mode
• (SQL Editor) Added automatically jump to the error line or position
• (Code Manager) Added support for sorting files by modified
• (Data Browser) Added filtering data with the selected cells
• (Main) Improved SQL Editor
• (Main) Improved Generating Scripts
• (Main) Improved Bookmark Manager
• (Main) Improved Connection Manager
• (Main) Improved Process Manager
• (Main) Improved AutoComplete and IntelliSense
• (Main) Improved user experience and user interface
• ...and more

The new version is immediately available for download.

Due to an XXE reported in the XML parsers we have released what should be the last of the 42.2.x versions. More details on the XXE can be found here. See changelog for the complete changelog. Thanks to David Dworken for reporting it and Sehrope Sarkuni for working through the fixes.

The pgagroal community is happy to announce version 0.7.0.

New features

• Prometheus support
• Remote management
• Ability to disconnect idle clients after the specified number of seconds

Plus various improvements and bug fixes.

pgagroal

pgagroal is a high-performance protocol-native connection pool for PostgreSQL.

Features

• High performance
• Connection pool
• Limit connections for users and databases
• Prefill support
• Remove idle connections
• Perform connection validation
• Enable / disable database access
• Graceful / fast shutdown
• Prometheus support
• Remote management
• Transport Layer Security (TLS) v1.2+ support
• Daemon mode
• User vault

Learn more on our web site or GitHub. Follow on Twitter.

pgagroal is released under the 3-clause BSD license, and is sponsored by Red Hat.

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:

• 4.1.2
• 4.0.9
• 3.7.14
• 3.6.21
• 3.5.25

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.

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

You can find information about all of the features and changes found in PostgreSQL 13 in the release notes:

https://www.postgresql.org/docs/13/release-13.html

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

Your testing and feedback will help the community ensure that the PostgreSQL 13 release upholds our standards of providing a stable, reliable release of the world's most advanced open source relational database. You can read more about our beta testing process and how you can contribute here:

https://www.postgresql.org/developer/beta/

PostgreSQL 13 Feature Highlights Functionality

There are many new features in PostgreSQL 13 that help improve the overall performance of PostgreSQL while making it even easier to develop applications.

B-tree indexes, the standard index of PostgreSQL, received improvements for how they handle duplicate data. These enhancements help to shrink index size and improve lookup speed, particularly for indexes that contain repeated values.

PostgreSQL 13 adds incremental sorting, which accelerates sorting data when data that is sorted from earlier parts of a query are already sorted. Additionally, queries with OR clauses or IN/ANY constant lists can use extended statistics (created via CREATE STATISTICS), which can lead to better planning and performance gains. PostgreSQL 13 can now use disk storage for hash aggregation (used as part of aggregate queries) with large aggregation sets.

There are more improvements added to PostgreSQL's partitioning functionality in this release, including an increased number of cases where a join directly between partitioned tables can occur, which can improve overall query execution time. Partitioned tables now support BEFORE row-level triggers, and a partitioned table can now be fully replicated via logical replication without having to publish individual partitions.

PostgreSQL 13 brings more convenience to writing queries with features like FETCH FIRST WITH TIES, which returns any additional rows that match the last row. There is also the addition of the .datetime() function for jsonpath queries, which will automatically convert a date-like or time-like string to the appropriate PostgreSQL date/time datatype. It is also even easier now to generate random UUIDs, as the gen_random_uuid() function can be used without having to enable any extensions.

Administration

One of the most anticipated features of PostgreSQL 13 is the ability for the VACUUM command to process indexes in parallel. This functionality can be accessed using the new PARALLEL option on the VACUUM command (or --parallel on vacuumdb), which allows you to specify the number of parallel workers to use for vacuuming indexes. Note that this does not work with the FULL option.

The reindexdb command has also added parallelism with the new --jobs flag, which lets you specify the number of concurrent sessions to use when reindexing a database.

PostgreSQL 13 introduces the concept of a "trusted extension", which allows for a superuser to specify extensions that a user can install in their database so long as they have a CREATE privilege.

This release includes more ways to monitor activity within a PostgreSQL database: PostgreSQL 13 can now track WAL usage statistics and the progress of streaming base backups, and the progress of an ANALYZE command. pg_basebackup can also generate a manifest that can be used to verify the integrity of a backup using a new tool called pg_verifybackup. It is also now possible to limit the amount of WAL space reserved by replication slots.

A new flag for pg_dump, --include-foreign-data, includes data from servers referenced by foreign data wrappers in the dump output.

The pg_rewind command also has improvements in PostgreSQL 13. In addition to pg_rewind automatically performing crash recovery, you can now use it to configure standby PostgreSQL instances using the --write-recovery-conf flag. pg_rewind can also use the restore_command of the target instance to fetch needed write-ahead logs.

Security

PostgreSQL continues to improve on its security capabilities in this latest release, introducing several features to help further deploy PostgreSQL safely.

libpq, the connection library that powers psql and many PostgreSQL connection drivers, includes several new parameters to help secure connections. PostgreSQL 13 introduces the channel_binding connection parameters, which lets a client specify that they want to require the channel binding functionality as part of SCRAM. Additionally, a client that is using a password protected TLS certificate can now specify its password using the sslpassword parameter. PostgreSQL 13 also adds support for DER encoded certificates.

The PostgreSQL foreign data wrapper (postgres_fdw) also received several enhancements to how it can secure connections, including the ability to use certificate-based authentication to connect to other PostgreSQL clusters. Additionally, unprivileged accounts can now connect to another PostgreSQL database via the postgres_fdw without using a password.

Other Highlights

PostgreSQL 13 continues to improve operability on Windows, as now users who run PostgreSQL on Windows now have the option to connect over UNIX domain sockets.

The PostgreSQL 13 documentation adds a glossary of terms to help people familiarize themselves with both PostgreSQL and general database concepts. This coincides with a significant rework in the display of functions and operators in tables, which helps to improve readability both on the web and in the PDF documentation.

The pgbench utility, used for performance testing, now supports the ability to partition its "accounts" table, making it easier to benchmark workloads that contain partitions.

psql now includes the \warn command that is similar to the \echo command in terms of outputting data, except \warn sends it to stderr. And in case you need additional guidance on any of the PostgreSQL commands, the --help flag now includes a link to https://www.postgresql.org.

Additional Features

Many other new features and improvements have been added to PostgreSQL 13, some of which may be as or more important to your use case than what is mentioned above. Please see the release notes for a complete list of new and changed features:

https://www.postgresql.org/docs/13/release-13.html

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 general availability of PostgreSQL 13. 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 13. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2020. For further information please see the Beta Testing page.

Links

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

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 12.3, 11.8, 10.13, 9.6.18, and 9.5.22. This release fixes one security issue found in the PostgreSQL server and over 75 bugs reported over the last three months.

Please plan to update at your earliest convenience.

Security Issues

• CVE-2020-10733: Windows installer runs executables from uncontrolled directories.

Versions Affected: 9.5 - 12. The security team does not test unsupported versions, but this issue existed before PostgreSQL 9.5.

The Windows installer for PostgreSQL invokes system-provided executables that do not have fully-qualified paths. Executables in the directory where the installer loads or the current working directory take precedence over the intended executables. An attacker having permission to add files into one of those directories can use this to execute arbitrary code with the installer's administrative rights.

The PostgreSQL project thanks Hou JingYi (@hjy79425575) for reporting this problem.

Bug Fixes and Improvements

This update also fixes over 75 bugs that were reported in the last several months. Some of these issues affect only version 12, but may also affect all supported versions.

Some of these fixes include:

• Several fixes for GENERATED columns, including an issue where it was possible to crash or corrupt data in a table when the output of the generated column was the exact copy of a physical column on the table, e.g. if the expression called a function which could return its own input.
• Several fixes for ALTER TABLE, including ensuring the SET STORAGE directive is propagated to a table's indexes.
• Fix a potential race condition when using DROP OWNED BY while another session is deleting the same objects.
• Allow for a partition to be detached when it has inherited ROW triggers.
• Several fixes for REINDEX CONCURRENTLY, particularly with issues when a REINDEX CONCURRENTLY operation fails.
• Fix crash when COLLATE is applied to an uncollatable type in a partition bound expression.
• Fix performance regression in floating point overflow/underflow detection.
• Several fixes for full text search, particularly with phrase searching.
• Fix query-lifespan memory leak for a set-returning function used in a query's FROM clause.
• Several reporting fixes for the output of VACUUM VERBOSE.
• Allow input of type circle to accept the format (x,y),r, which is specified in the documentation.
• Allow for the get_bit() and set_bit() functions to not fail on bytea strings longer than 256MB.
• Avoid premature recycling of WAL segments during crash recovery, which could lead to WAL segments being recycled before being archived.
• Avoid attempting to fetch nonexistent WAL files from archive storage during recovery by skipping irrelevant timelines.
• Several fixes for logical replication and replication slots.
• Fix several race conditions in synchronous standby management, including one that occurred when changing the synchronous_standby_names setting.
• Several fixes for GSSAPI support, include a fix for a memory leak that occurred when using GSSAPI encryption.
• Ensure that members of the pg_read_all_stats role can read all statistics views.
• Fix performance regression in information_schema.triggers view.
• Fix memory leak in libpq when using sslmode=verify-full.
• Fix crash in psql when attempting to re-establish a failed connection.
• Allow tab-completion of the filename argument to \gx command in psql.
• Add pg_dump support for ALTER ... DEPENDS ON EXTENSION.
• Several other fixes for pg_dump, which include dumping comments on RLS policies and postponing restore of event triggers until the end.
• Ensure pg_basebackup generates valid tar files.
• pg_checksums skips tablespace subdirectories that belong to a different PostgreSQL major version
• Several Windows compatibility fixes

This update also contains tzdata release 2020a for DST law changes in Morocco and the Canadian Yukon, plus historical corrections for Shanghai. The America/Godthab zone has been renamed to America/Nuuk to reflect current English usage ; however, the old name remains available as a compatibility link. This also updates initdb's list of known Windows time zone names to include recent additions.

For the full list of changes available, please review the release notes.

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

For more details, please see the release notes.

Links

• Download
• Release Notes
• Security Page
• Versioning Policy
• Follow @postgresql on Twitter

SQL Data Analysis

See and understand your data using SQL Data Analysis. Supports PostgreSQL 8 to 12.

Benefits:

• Connect to your PostgreSQL database, run your query, and visualize your data in seconds.
• Analyze your data with intuitive drag & drop interface. No programming, just insights.
• Cross database queries allow you to join a PostgreSQL data set with another database's data set (SQL Server, Oracle, MySQL, etc).
• Flexible layouts allow you to combine multiple data views to best visualize your data.
• Share and export your discoveries and insights to PDF, Excel, and HTML files.

Free trial

SQL Data Analysis is commercial software and a free trial is available. Learn more at https://www.yohz.com/sda_details.htm.