From owner-freebsd-performance@freebsd.org Mon Apr 5 21:31:49 2021 Return-Path: Delivered-To: freebsd-performance@mailman.nyi.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.nyi.freebsd.org (Postfix) with ESMTP id C614B5C7FCC for ; Mon, 5 Apr 2021 21:31:49 +0000 (UTC) (envelope-from raj@gusw.net) Received: from gateway36.websitewelcome.com (gateway36.websitewelcome.com [192.185.179.26]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 4FDkPc6Cfwz3lbR for ; Mon, 5 Apr 2021 21:31:48 +0000 (UTC) (envelope-from raj@gusw.net) Received: from cm13.websitewelcome.com (cm13.websitewelcome.com [100.42.49.6]) by gateway36.websitewelcome.com (Postfix) with ESMTP id 0F887406486A7 for ; Mon, 5 Apr 2021 16:31:13 -0500 (CDT) Received: from host2097.hostmonster.com ([67.20.114.243]) by cmsmtp with SMTP id TWoTlEpcKmJLsTWoTloA3Z; Mon, 05 Apr 2021 16:31:13 -0500 X-Authority-Reason: nr=8 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=schadow.us; s=default; h=Content-Transfer-Encoding:Content-Type:MIME-Version:Date: Message-ID:Subject:From:To:Sender:Reply-To:Cc:Content-ID:Content-Description: Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID: In-Reply-To:References:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=PYqOJuIRkc7AtFnOIKyWhwJHCQOLHauaCuhm0LC0hqw=; b=WvJ/z9xRHHcPVQxLJmd0nNH6dP KxDM+SzazKLPhQV9LJuLFRxpb+V4y7O2VAa7/NAPBYaqNjFJzXnzpq1PJ8Lwnlh6wJrOOQyGlVeRz KiYxOCLw9nXdziePDv2gWs2UW; Received: from [177.143.143.124] (port=50296 helo=[10.0.4.7]) by host2097.hostmonster.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.93) (envelope-from ) id 1lTWoS-003AeH-SO for freebsd-performance@freebsd.org; Mon, 05 Apr 2021 15:31:13 -0600 To: freebsd-performance@freebsd.org From: Gunther Schadow Subject: PosgtgreSQL hot standby reading WAL from muli-attached volume? Message-ID: Date: Mon, 5 Apr 2021 17:31:10 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101 Thunderbird/78.9.0 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - host2097.hostmonster.com X-AntiAbuse: Original Domain - freebsd.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - gusw.net X-BWhitelist: no X-Source-IP: 177.143.143.124 X-Source-L: No X-Exim-ID: 1lTWoS-003AeH-SO X-Source: X-Source-Args: X-Source-Dir: X-Source-Sender: ([10.0.4.7]) [177.143.143.124]:50296 X-Source-Auth: ebiz+schadow.us X-Email-Count: 1 X-Source-Cap: cHJhZ21hdDE7cHJhZ21hdDE7aG9zdDIwOTcuaG9zdG1vbnN0ZXIuY29t X-Local-Domain: yes X-Rspamd-Queue-Id: 4FDkPc6Cfwz3lbR X-Spamd-Bar: -- Authentication-Results: mx1.freebsd.org; dkim=pass header.d=schadow.us header.s=default header.b=WvJ/z9xR; dmarc=none; spf=softfail (mx1.freebsd.org: 192.185.179.26 is neither permitted nor denied by domain of raj@gusw.net) smtp.mailfrom=raj@gusw.net X-Spamd-Result: default: False [-2.29 / 15.00]; RCVD_VIA_SMTP_AUTH(0.00)[]; HAS_X_SOURCE(0.00)[]; TO_DN_NONE(0.00)[]; R_SPF_SOFTFAIL(0.00)[~all]; RCVD_COUNT_THREE(0.00)[4]; DKIM_TRACE(0.00)[schadow.us:+]; NEURAL_HAM_SHORT(-0.99)[-0.990]; HAS_X_ANTIABUSE(0.00)[]; FROM_EQ_ENVFROM(0.00)[]; RCVD_TLS_LAST(0.00)[]; MIME_TRACE(0.00)[0:+]; ASN(0.00)[asn:46606, ipnet:192.185.128.0/18, country:US]; MID_RHS_MATCH_FROM(0.00)[]; RBL_DBL_DONT_QUERY_IPS(0.00)[192.185.179.26:from]; ARC_NA(0.00)[]; NEURAL_HAM_MEDIUM(-1.00)[-1.000]; R_DKIM_ALLOW(-0.20)[schadow.us:s=default]; FREEFALL_USER(0.00)[raj]; FROM_HAS_DN(0.00)[]; TO_MATCH_ENVRCPT_ALL(0.00)[]; NEURAL_HAM_LONG(-1.00)[-1.000]; MIME_GOOD(-0.10)[text/plain]; PREVIOUSLY_DELIVERED(0.00)[freebsd-performance@freebsd.org]; DMARC_NA(0.00)[gusw.net]; RCPT_COUNT_ONE(0.00)[1]; SPAMHAUS_ZRD(0.00)[192.185.179.26:from:127.0.2.255]; SUBJECT_ENDS_QUESTION(1.00)[]; RCVD_IN_DNSWL_NONE(0.00)[192.185.179.26:from]; MAILMAN_DEST(0.00)[freebsd-performance] X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 05 Apr 2021 21:31:49 -0000 Hi, I wonder can we set up a hot standby in such a way that we don't need any log streaming nor shipping, where instead every hot standby just mounts the same disk in read-only mode which the master uses to write his WAL files? Even without a clustered file system, e.g., a UFS on FreeBSD, one can have the master mount in read-write mode while all the hot standbys would mount the volume read-only. Given that WAL logs are written out at a certain rate, one can at regular intervals issue mount -u /pg_wal and it should refresh the metadata, I assume. I am re-reading about hot-standby, and it strikes me that this method is essentially the "log shipping" method only that there is no actual "shipping" involved, the new log files simply appear all of a sudden on the disk. I suppose there is a question how we know when a new WAL file is finished appearing? And as I read the log-shipping method may not be suitable for hot standby use? Is this something that has been written about already? regards, -Gunther From owner-freebsd-performance@freebsd.org Mon Apr 5 22:20:30 2021 Return-Path: Delivered-To: freebsd-performance@mailman.nyi.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.nyi.freebsd.org (Postfix) with ESMTP id 360DB5C920F for ; Mon, 5 Apr 2021 22:20:30 +0000 (UTC) (envelope-from raj@gusw.net) Received: from gateway32.websitewelcome.com (gateway32.websitewelcome.com [192.185.145.184]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 4FDlTn0YHnz3pL1 for ; Mon, 5 Apr 2021 22:20:28 +0000 (UTC) (envelope-from raj@gusw.net) Received: from cm13.websitewelcome.com (cm13.websitewelcome.com [100.42.49.6]) by gateway32.websitewelcome.com (Postfix) with ESMTP id C26F938F009D for ; Mon, 5 Apr 2021 17:20:27 -0500 (CDT) Received: from host2097.hostmonster.com ([67.20.114.243]) by cmsmtp with SMTP id TXa7lFcpRmJLsTXa7lovg5; Mon, 05 Apr 2021 17:20:27 -0500 X-Authority-Reason: nr=8 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=schadow.us; s=default; h=Content-Transfer-Encoding:Content-Type:In-Reply-To:MIME-Version :Date:Message-ID:From:References:To:Subject:Sender:Reply-To:Cc:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=i+tfsD/IAgtXTjL75UI7eWE5FptBjSloDh0+j/KKLRw=; b=FNqr7Fum03HO7kgPXHOTfwWRgt /sArFtj7rC71SfbqZbp19KNwV/5w9fpgmce5lJuIXy161pflHaZngI7pRuRk7SqZL8DooFwaap0pQ V/2SJ25+CJAo7FdpHgcHuAmsH; Received: from [177.143.143.124] (port=50718 helo=[10.0.4.7]) by host2097.hostmonster.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.93) (envelope-from ) id 1lTXa6-003arL-Vn for freebsd-performance@freebsd.org; Mon, 05 Apr 2021 16:20:27 -0600 Subject: WRONG LIST, SORRY, PLEASE IGNORE Re: PosgtgreSQL hot standby reading WAL from muli-attached volume? To: freebsd-performance@freebsd.org References: From: Gunther Schadow Message-ID: <17377a85-81dc-656a-0460-772167f74b37@gusw.net> Date: Mon, 5 Apr 2021 18:20:23 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101 Thunderbird/78.9.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - host2097.hostmonster.com X-AntiAbuse: Original Domain - freebsd.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - gusw.net X-BWhitelist: no X-Source-IP: 177.143.143.124 X-Source-L: No X-Exim-ID: 1lTXa6-003arL-Vn X-Source: X-Source-Args: X-Source-Dir: X-Source-Sender: ([10.0.4.7]) [177.143.143.124]:50718 X-Source-Auth: ebiz+schadow.us X-Email-Count: 1 X-Source-Cap: cHJhZ21hdDE7cHJhZ21hdDE7aG9zdDIwOTcuaG9zdG1vbnN0ZXIuY29t X-Local-Domain: yes X-Rspamd-Queue-Id: 4FDlTn0YHnz3pL1 X-Spamd-Bar: -- Authentication-Results: mx1.freebsd.org; dkim=pass header.d=schadow.us header.s=default header.b=FNqr7Fum; dmarc=none; spf=softfail (mx1.freebsd.org: 192.185.145.184 is neither permitted nor denied by domain of raj@gusw.net) smtp.mailfrom=raj@gusw.net X-Spamd-Result: default: False [-2.30 / 15.00]; RCVD_VIA_SMTP_AUTH(0.00)[]; RWL_MAILSPIKE_GOOD(0.00)[192.185.145.184:from]; HAS_X_SOURCE(0.00)[]; TO_DN_NONE(0.00)[]; R_SPF_SOFTFAIL(0.00)[~all:c]; RCVD_COUNT_THREE(0.00)[4]; DKIM_TRACE(0.00)[schadow.us:+]; NEURAL_HAM_SHORT(-1.00)[-1.000]; HAS_X_ANTIABUSE(0.00)[]; FROM_EQ_ENVFROM(0.00)[]; RCVD_TLS_LAST(0.00)[]; MIME_TRACE(0.00)[0:+]; ASN(0.00)[asn:46606, ipnet:192.185.128.0/18, country:US]; MID_RHS_MATCH_FROM(0.00)[]; RBL_DBL_DONT_QUERY_IPS(0.00)[192.185.145.184:from]; ARC_NA(0.00)[]; NEURAL_HAM_MEDIUM(-1.00)[-1.000]; R_DKIM_ALLOW(-0.20)[schadow.us:s=default]; FREEFALL_USER(0.00)[raj]; FROM_HAS_DN(0.00)[]; TO_MATCH_ENVRCPT_ALL(0.00)[]; NEURAL_HAM_LONG(-1.00)[-1.000]; MIME_GOOD(-0.10)[text/plain]; PREVIOUSLY_DELIVERED(0.00)[freebsd-performance@freebsd.org]; DMARC_NA(0.00)[gusw.net]; RCPT_COUNT_ONE(0.00)[1]; SPAMHAUS_ZRD(0.00)[192.185.145.184:from:127.0.2.255]; SUBJECT_ENDS_QUESTION(1.00)[]; RCVD_IN_DNSWL_NONE(0.00)[192.185.145.184:from]; MAILMAN_DEST(0.00)[freebsd-performance] X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 05 Apr 2021 22:20:30 -0000 Sorry for this. Wrong email list. On 4/5/2021 5:31 PM, Gunther Schadow wrote: > Hi, > > I wonder can we set up a hot standby i