Created attachment 177185 [details] Add checksumming hint to pkg-message Here's a patch that adds a hint about enabling data checksumming via pkg-message. Data checksumming is very important, as it extends D in ACID, especially if ZFS is not used, and the idea is to hint this to the user. It is available since PostgreSQL 9.3, and since pkg-message is used in the PostgreSQL ports for various important tips and tricks, I believe this to be a conditio sine qua non. Even though this is a just a pkg-message addition, I've tested postgresql95-server (that I currently use in production with checksumming enabled), it builds with Poudriere 11.0 amd64.
Maintainer feedback?
Thanks, this is a good idea. Sorry it's been lying around here for a year. I'll add it to the list and will commit at next planned update.
Another thing might be to actually add --data-checksums to the default postgresql_initdb_flags string? Any thought on that: Index: files/postgresql.in =================================================================== --- files/postgresql.in (revision 458857) +++ files/postgresql.in (working copy) @@ -12,7 +12,7 @@ # # optional # postgresql_data="%%PREFIX%%/%%PG_USER%%/data" # postgresql_flags="-w -s -m fast" -# postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C" +# postgresql_initdb_flags="--data-checksums --encoding=utf-8 --lc-collate=C" # postgresql_class="default" # postgresql_profiles="" # @@ -36,7 +36,7 @@ postgresql_user=${postgresql_user:-"%%PG_USER%%"} eval postgresql_data=${postgresql_data:-"~${postgresql_user}/data"} postgresql_class=${postgresql_class:-"default"} -postgresql_initdb_flags=${postgresql_initdb_flags:-"--encoding=utf-8 --lc-collate=C"} +postgresql_initdb_flags=${postgresql_initdb_flags:-"--data-checksums --encoding=utf-8 --lc-collate=C"} name=postgresql rcvar=postgresql_enable
That would of course require a different pkg-message... :)
Any thoughts on adding --data-checksums per default for new clusters?
I'm not sure defaulting to this is a good idea. There are performance implications and people not using Postgres on ZFS (where this'd be redundant) are maybe doing so for performance reasons.
Furthermore, the problem that once init'd, the setting cannot be changed without full dump+drop+import of the cluster. Personally, I'd be hesitant to force such settings. Everyone should use ZFS anyway ;)
Using ZFS can be devastating, at least when not using a proper JBOD setup. We used a HP P400 standard controller with ZFS and postgresql just suddenly just stopped one morning when load was too high. The only fix was reducing load or going back to UFS. This was after months of measuring with pgbench. Lesson learned: know your work load. It was two or three years ago, and I'm not sure exactly what happened there, but it might be that we used a standard battery backed RAID controller instead of a proper JBOD setup. ZFS loves to know its disks inside out. But I agree, a pkg-message hint is perfect. Thanks.
A commit references this bug: Author: girgen Date: Thu Feb 8 17:38:39 UTC 2018 New revision: 461251 URL: https://svnweb.freebsd.org/changeset/ports/461251 Log: Update to latest versions of PostgreSQL 2018-02-08 Security Update Release ================================== The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 10.2, 9.6.7, 9.5.11, 9.4.16, 9.3.21. This release fixes two security issues. This release also fixes issues with VACUUM, GIN indexes, and hash indexes that could lead to data corruption, as well as fixes for using parallel queries and logical replication. All users using the affected versions of PostgreSQL should update as soon as possible. Please see the notes on "Updating" below for any post-update steps that may be required. Please note that PostgreSQL changed its versioning scheme with the release of version 10.0, so updating to version 10.2 from 10.0 or 10.1 is considered a minor update. Security Issues --------------- Two security vulnerabilities have been fixed by this release: * CVE-2018-1052: Fix the processing of partition keys containing multiple expressions * CVE-2018-1053: Ensure that all temporary files made with "pg_upgrade" are non-world-readable Local fixes to the FreeBSD ports -------------------------------- Inform users about data checksums [1]. Make sure /usr/bin/su is used regardless of PATH settings [2]. Enable DTRACE by default [3]. PR: 214671 [1], 223157 [2], 215028 [3] Security: c602c791-0cf4-11e8-a2ec-6cc21735f730 Changes: head/databases/postgresql10-server/Makefile head/databases/postgresql10-server/distinfo head/databases/postgresql10-server/files/pkg-message-server.in head/databases/postgresql10-server/files/postgresql.in head/databases/postgresql10-server/pkg-plist-client head/databases/postgresql10-server/pkg-plist-server head/databases/postgresql92-client/Makefile head/databases/postgresql92-contrib/Makefile head/databases/postgresql92-server/Makefile head/databases/postgresql92-server/files/pkg-message-server.in head/databases/postgresql92-server/files/postgresql.in head/databases/postgresql93-server/Makefile head/databases/postgresql93-server/distinfo head/databases/postgresql93-server/files/pkg-message-server.in head/databases/postgresql93-server/files/postgresql.in head/databases/postgresql93-server/pkg-plist-server head/databases/postgresql94-server/Makefile head/databases/postgresql94-server/distinfo head/databases/postgresql94-server/files/pkg-message-server.in head/databases/postgresql94-server/files/postgresql.in head/databases/postgresql94-server/pkg-plist-server head/databases/postgresql95-server/Makefile head/databases/postgresql95-server/distinfo head/databases/postgresql95-server/files/pkg-message-server.in head/databases/postgresql95-server/files/postgresql.in head/databases/postgresql95-server/pkg-plist-server head/databases/postgresql96-server/Makefile head/databases/postgresql96-server/distinfo head/databases/postgresql96-server/files/pkg-message-server.in head/databases/postgresql96-server/files/postgresql.in head/databases/postgresql96-server/pkg-plist-server
Committed. Thanks!
A commit references this bug: Author: girgen Date: Mon Feb 26 10:00:37 UTC 2018 New revision: 463010 URL: https://svnweb.freebsd.org/changeset/ports/463010 Log: MFH: r458764 r460386 r461251 r461263 Convert to USES=autoreconf. Fix ICU depend for postgresql10-server. PR: 225049 Submitted by: mat Approved by: maintainer timeout Sponsored by: Absolight Update to latest versions of PostgreSQL 2018-02-08 Security Update Release ================================== The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 10.2, 9.6.7, 9.5.11, 9.4.16, 9.3.21. This release fixes two security issues. This release also fixes issues with VACUUM, GIN indexes, and hash indexes that could lead to data corruption, as well as fixes for using parallel queries and logical replication. All users using the affected versions of PostgreSQL should update as soon as possible. Please see the notes on "Updating" below for any post-update steps that may be required. Please note that PostgreSQL changed its versioning scheme with the release of version 10.0, so updating to version 10.2 from 10.0 or 10.1 is considered a minor update. Security Issues --------------- Two security vulnerabilities have been fixed by this release: * CVE-2018-1052: Fix the processing of partition keys containing multiple expressions * CVE-2018-1053: Ensure that all temporary files made with "pg_upgrade" are non-world-readable Local fixes to the FreeBSD ports -------------------------------- Inform users about data checksums [1]. Make sure /usr/bin/su is used regardless of PATH settings [2]. Enable DTRACE by default [3]. PR: 214671 [1], 223157 [2], 215028 [3] Security: c602c791-0cf4-11e8-a2ec-6cc21735f730 Remove DTRACE from OPTIONS_DEFAULT, causes segmentation fault during build Approved by: portmgr Changes: _U branches/2018Q1/ branches/2018Q1/databases/postgresql10-server/Makefile branches/2018Q1/databases/postgresql10-server/distinfo branches/2018Q1/databases/postgresql10-server/files/pkg-message-server.in branches/2018Q1/databases/postgresql10-server/files/postgresql.in branches/2018Q1/databases/postgresql10-server/pkg-plist-client branches/2018Q1/databases/postgresql10-server/pkg-plist-server branches/2018Q1/databases/postgresql92-client/Makefile branches/2018Q1/databases/postgresql92-contrib/Makefile branches/2018Q1/databases/postgresql92-server/Makefile branches/2018Q1/databases/postgresql92-server/files/pkg-message-server.in branches/2018Q1/databases/postgresql92-server/files/postgresql.in branches/2018Q1/databases/postgresql93-server/Makefile branches/2018Q1/databases/postgresql93-server/distinfo branches/2018Q1/databases/postgresql93-server/files/pkg-message-server.in branches/2018Q1/databases/postgresql93-server/files/postgresql.in branches/2018Q1/databases/postgresql93-server/pkg-plist-server branches/2018Q1/databases/postgresql94-server/Makefile branches/2018Q1/databases/postgresql94-server/distinfo branches/2018Q1/databases/postgresql94-server/files/pkg-message-server.in branches/2018Q1/databases/postgresql94-server/files/postgresql.in branches/2018Q1/databases/postgresql94-server/pkg-plist-server branches/2018Q1/databases/postgresql95-server/Makefile branches/2018Q1/databases/postgresql95-server/distinfo branches/2018Q1/databases/postgresql95-server/files/pkg-message-server.in branches/2018Q1/databases/postgresql95-server/files/postgresql.in branches/2018Q1/databases/postgresql95-server/pkg-plist-server branches/2018Q1/databases/postgresql96-server/Makefile branches/2018Q1/databases/postgresql96-server/distinfo branches/2018Q1/databases/postgresql96-server/files/pkg-message-server.in branches/2018Q1/databases/postgresql96-server/files/postgresql.in branches/2018Q1/databases/postgresql96-server/pkg-plist-server