Bug 214671 - databases/postgresql{93,94,95,96}-server: Inform users about data-checksumming via pkg-message
Summary: databases/postgresql{93,94,95,96}-server: Inform users about data-checksummin...
Status: Closed FIXED
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: Any Any
: --- Affects Some People
Assignee: pgsql
URL: https://www.postgresql.org/docs/9.3/s...
Keywords: patch
Depends on:
Blocks:
 
Reported: 2016-11-19 18:19 UTC by VK
Modified: 2018-02-26 10:00 UTC (History)
3 users (show)

See Also:
vlad-fbsd: maintainer-feedback? (pgsql)
vlad-fbsd: merge-quarterly?


Attachments
Add checksumming hint to pkg-message (3.36 KB, patch)
2016-11-19 18:19 UTC, VK
vlad-fbsd: maintainer-approval? (pgsql)
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description VK 2016-11-19 18:19:54 UTC
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.
Comment 1 Walter Schwarzenfeld 2018-01-14 00:54:09 UTC
Maintainer feedback?
Comment 2 Palle Girgensohn freebsd_committer freebsd_triage 2018-01-14 11:02:37 UTC
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.
Comment 3 Palle Girgensohn freebsd_committer freebsd_triage 2018-01-14 11:08:38 UTC
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
Comment 4 Palle Girgensohn freebsd_committer freebsd_triage 2018-01-14 11:09:24 UTC
That would of course require a different pkg-message... :)
Comment 5 Palle Girgensohn freebsd_committer freebsd_triage 2018-02-06 16:51:04 UTC
Any thoughts on adding --data-checksums per default for new clusters?
Comment 6 VK 2018-02-06 18:38:47 UTC
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.
Comment 7 VK 2018-02-06 18:42:34 UTC
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 ;)
Comment 8 Palle Girgensohn freebsd_committer freebsd_triage 2018-02-06 23:21:37 UTC
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.
Comment 9 commit-hook freebsd_committer freebsd_triage 2018-02-08 17:39:13 UTC
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
Comment 10 Palle Girgensohn freebsd_committer freebsd_triage 2018-02-08 17:44:39 UTC
Committed. Thanks!
Comment 11 commit-hook freebsd_committer freebsd_triage 2018-02-26 10:00:57 UTC
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