Er zijn updates verschenen voor alle nog ondersteunde versies van PostgreSQL. Dit populaire 'opensource relational database management system' draait op een groot aantal besturingssystemen en is daardoor uitstekend inzetbaar in diverse omgevingen. Het is een afgeleide van Ingres, nadat de hoofdontwikkelaar daarvan voor zichzelf is begonnen en deze database van open source tot closed source werd. De versienummers van deze uitgaven zijn vastgezet op 15 beta 1, 14.3, 13.7, 12.11, 11.16 en 10.21. De bijbehorende aankondigingen zien er als volgt uit:
PostgreSQL 14.3, 13.7, 12.11, 11.16, and 10.21 Released!
The PostgreSQL Global Development Group has released an update to all supported versions of PostgreSQL, including 14.3, 13.7, 12.11, 11.16, and 10.21. This release closes one security vulnerability and fixes over 50 bugs reported over the last three months. We encourage you to install this update at your earliest possible convenience. If you have any GiST indexes on columns using the ltree data type, you will need to reindex them after upgrading. For the full list of changes, please review the release notes.
PostgreSQL 10 EOL Notice
PostgreSQL 10 will stop receiving fixes on November 10, 2022. If you are running PostgreSQL 10 in a production environment, we suggest that you make plans to upgrade to a newer, supported version of PostgreSQL. Please see our versioning policy for more information.
Security Issues
CVE-2022-1552: Autovacuum, REINDEX, and others omit "security restricted operation" sandbox.
Versions Affected: 10 - 14. The security team typically does not test unsupported versions, but this problem is quite old.
Autovacuum, REINDEX, CREATE INDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and pg_amcheck made incomplete efforts to operate safely when a privileged user is maintaining another user's objects. Those commands activated relevant protections too late or not at all. An attacker having permission to create non-temp objects in at least one schema could execute arbitrary SQL functions under a superuser identity.
While promptly updating PostgreSQL is the best remediation for most users, a user unable to do that can work around the vulnerability by disabling autovacuum, not manually running the above commands, and not restoring from output of the pg_dump command. Performance may degrade quickly under this workaround. VACUUM is safe, and all commands are fine when a trusted user owns the target object.
The PostgreSQL project thanks Alexander Lakhin for reporting this problem.
Bug Fixes and Improvements
This update fixes over 50 bugs that were reported in the last several months. The issues listed below affect PostgreSQL 14. Some of these issues may also affect other supported versions of PostgreSQL.
Included in this release:This update also contains the tzdata release 2022a for DST law changes in Palestine, plus historical corrections for Chile and Ukraine. For the full list of changes available, please review the release notes.
- Fix issue that could lead to corruption of GiST indexes on ltree columns. After upgrading, you will need to reindex any GiST indexes on ltree columns.
- Column names in tuples produced by a whole-row variable (e.g. tbl.*) outside of a top-level of a SELECT list are now always associated with those of the associated named composite type, if there is one. The release notes detail a workaround if you depend on the previous behavior.
- Fix incorrect rounding when extracting epoch values from interval types.
- Prevent issues with calling pg_stat_get_replication_slot(NULL).
- Fix incorrect output for types timestamptz and timetz in table_to_xmlschema().
- Fix errors related to a planner issue that affected asynchronous remote queries.
- Fix planner failure if a query using SEARCH or CYCLE features contains a duplicate common-table expression (WITH) name.
- Fix ALTER FUNCTION to support changing a function's parallelism property and its SET-variable list in the same command.
- Fix incorrect sorting of table rows when using CLUSTER on an index whose leading key is an expression.
- Prevent data loss if a system crash occurs shortly after a sorted GiST index build.
- Fix risk of deadlock failures while dropping a partitioned index.
- Fix race condition between DROP TABLESPACE and checkpointing that could fail to remove all dead files from the tablespace directory.
- Fix potential issue in crash recovery after a TRUNCATE command that overlaps with a checkpoint.
- Re-allow _ as the first character in a custom configuration parameter name.
- Fix PANIC: xlog flush request is not satisfied failure during standby promotion when there is a missing WAL continuation record.
- Fix possibility of self-deadlock in hot standby conflict handling.
- Ensure that logical replication apply workers can be restarted when the server is near the max_sync_workers_per_subscription limit.
- Disallow execution of SPI functions during PL/Perl function compilation.
- libpq now accepts root-owned SSL private key files, which matches the rules the server has used since the 9.6 release.
- Re-allow database.schema.table patterns in psql, pg_dump, and pg_amcheck. Several fixes for pageinspect to improve overall stability.
- Disable batch insertion in postgres_fdw when BEFORE INSERT ... FOR EACH ROW triggers exist on the foreign table.
- Update JIT code to work with LLVM 14.
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. However, if you have any GiST indexes on columns using the ltree data type, you will need to reindex them after upgrading. 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.
PostgreSQL 15 Beta 1 Released!
The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 15 is now available for download. This release contains previews of all features that will be available when PostgreSQL 15 is made generally available, though some details of the release can change during the beta period. You can find information about all of the features and changes found in PostgreSQL 15 in the release notes. In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 15 on your systems to help us eliminate bugs or other issues that may exist. While we do not advise you to run PostgreSQL 15 Beta 1 in 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 15 release upholds our standards of delivering a stable, reliable release of the world's most advanced open source relational database. Please read more about our beta testing process and how you can contribute.
PostgreSQL 15 Feature Highlights
Developer Experience
PostgreSQL 15 adds new features for simplifying and enhancing the developer experience.
This release introduces MERGE, a SQL standard command for conditionally performing write operations (INSERT, UPDATE, or DELETE) on data. Prior to this release, the same behavior could be accomplished either using stored procedures or, on a limited-basis, with INSERT ... ON CONFLICT. With PostgreSQL 15, developers can write simple, expressive queries to choose the appropriate data modification action to take.
PostgreSQL added support for JSON in 2012 as part of the 9.2 release. The SQL/JSON standard, published five years later, specified a variety of interfaces for accessing and manipulating JSON data stored in relational databases. PostgreSQL 15 builds on its existing support for the SQL/JSON path language by including more standard SQL/JSON functions. These include SQL/JSON constructors, query / introspection functions, and the ability to convert JSON data into a table.
PostgreSQL 15 adds more regular expression functions, including regexp_count , regexp_instr, regexp_like, and regexp_substr. the range_agg function, introduced in PostgreSQL 15 for aggregating range data types into multirange types, now supports aggregating multirange types too.
Performance
PostgreSQL 15 continues to build on its performance gains over the past several releases. This release includes a significant speedup for sorting data when sorting over larger data sets. In particular, these are data sets that exceed the work_mem parameter.
The performance gains of PostgreSQL 15 extend to a variety of query types. This includes the introduction of parallelization for SELECT DISTINCT statements and improvements in performance to window functions that use row_number(), rank(), and count(). Applications that use the PostgreSQL foreign data wrapper postgres_fdw to manage data on remote PostgreSQL servers can now enable transactions to be committed in parallel. There are also several performance enhancements for queries involving tables with partitions.
PostgreSQL system and TOAST tables, used for storing data that is larger than a single page (8kB), can now utilize index deduplication and benefit from smaller indexes and faster lookups.
The psql \copy command, used to bulk load data, also has performance improvements in PostgreSQL 15.
Backups, Archiving, and Compression
Building on the previous release that introduced LZ4 compression, PostgreSQL 15 adds support for Zstandard (zstd) compression to various components.
pg_basebackup, a utility used to take full backups of a PostgreSQL cluster, now supports server-side compression using Gzip, LZ4, or Zstandard compression. The pg_basebackup client can now also decompress backups that use LZ4 and Zstandard compression.
Write-ahead log (WAL) files can now be compressed using both LZ4 and Zstandard compression through the wal_compression configuration parameter. Additionally, PostgreSQL 15 also adds the recovery_prefetch option that can help speed up recovery operations by prefetching data blocks. This release adds a new module called pg_walinspect that lets you inspect information about WAL files directly from SQL.
PostgreSQL 15 lets you perform WAL archiving without running a shell command by specifying an archive_library. An example WAL archiving library can be found in the basic_archive module. Additionally, extensions can now define their own WAL resource managers that lets them work with logical replication.
Logical Replication
PostgreSQL 15 introduces both row filtering and column filtering for logical replication. Additionally, users can now conveniently specify to publish all tables in a schema, where previously it was only possible to do this for all tables in a database.
This release also adds more support for handling conflicts. A subscriber can now specify disable_on_error to automatically stop logical replication on an error. PostgreSQL 15 makes it possible to skip applying changes using the ALTER SUBSCRIPTION ... SKIP command.
Administration
PostgreSQL 15 introduces the jsonlog format for logging. This allows PostgreSQL logs to be consumed by many programs that perform structured log aggregation and analysis. PostgreSQL 15 now by default logs checkpoints and autovacuum operations that are slow.
The psql client now has a command called \dconfig for inspecting the values of configuration parameters. By default, \dconfig displays any configuration parameter that is not set to its default value.
PostgreSQL 15 now stores all server-level statistics in shared memory. The server no longer runs a statistics collector process.
The ALTER TABLE command can now modify a table's ACCESS METHOD, which is used to set the storage system used by the table.
Security
PostgreSQL 15 introduces new defaults when databases are created within a PostgreSQL cluster. When a new database is created, users outside of the database owner (represented by pg_database_owner) and superusers can no longer create objects in the default public schema.
PostgreSQL 15 adds support for "security invoker views", which uses the privileges of the user executing the query instead of the user who created the view.
Unprivileged users in PostgreSQL 15 can now be granted privileges to change server variables via SET and ALTER SYSTEM.
Additional Features
Many other new features and improvements have been added to PostgreSQL 15. Many of these may also be helpful for your use cases. 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 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 15. 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.
Beta Schedule
This is the first beta release of version 15. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2022. For further information please see the Beta Testing page.
:fill(white):strip_exif()/i/1395573402.jpeg?f=thumbmedium)
/u/239221/crop5db17928dbd60_cropped.png?f=community){kind=small}
:strip_exif()/u/53522/crop583d477015a24.gif?f=community){kind=small}
/u/698825/crop560452d273fa4_cropped.png?f=community){kind=small}