Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6fXh-001vn4-K0 for pgsql-general@arkaria.postgresql.org; Fri, 24 Nov 2023 23:25:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r6fXg-00GOdD-98 for pgsql-general@arkaria.postgresql.org; Fri, 24 Nov 2023 23:25:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6fXe-00GOd2-6k for pgsql-general@lists.postgresql.org; Fri, 24 Nov 2023 23:24:59 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6fXV-008Mr5-VP for pgsql-general@postgresql.org; Fri, 24 Nov 2023 23:24:57 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.west.internal (Postfix) with ESMTP id ED950320030E; Fri, 24 Nov 2023 18:24:45 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Fri, 24 Nov 2023 18:24:46 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm3; t= 1700868285; x=1700954685; bh=REBxOa6eV2qenG9dM5udITcihRj+ecrGW8q 9H420w2w=; b=eD0pwi7eGYZnPqz6W0PC9woKwVu84KeznSVmGT84lRCLOQ3cbod IJUiJe8OuYXqIKDjluzl+p+WuxFe3RdQbGnTT/Z1Sw4WUIR+oYm8r3ZAi58/+R3F shqaIimu3bQ14S/q09fuHwqeT8xou9LSzgxVeE1szsPk0n+gViDuHTeQW3GLQSrK 3cjgzGq5mpL1Lz+bMPjy8yiyZFMPHom9dGzjmN562ta2qBV9OnX4Pdmlzpoky3j+ lD3NuiyfsENLtDxiTvYn84Dyx7/IkMU9H+oZNjY68HVgkYTzfDBV1CA280aTx28V LMa/9abt7mkcI7GWfx1G1WZ5M2WGqExMzSw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1700868285; x=1700954685; bh=REBxOa6eV2qenG9dM5udITcihRj+ecrGW8q 9H420w2w=; b=RtMp5urZ/aeADdsPKMLOZaf7q4l+fivjDnle/pY7JHkHjbPZJpm JhHRaJOs1jn+OvN4toYuF3+ck4I9NlLUCDtn0iAKF5eaInU89e75D6HC7KxpmB2h +AmhFBT6aHaqtH9fHYfLuBY4nvxaX4pIYBK2D0/9Znjh0dZ73Nb6axG147+Em3zE 6I06EwlKeIqAXK+kn7jkiLk6A2ce8tWx8lT3Y+XKnXWnt8HaAOdL0Ssnm+XH+66q +2sTL1XU503qreJ9nri5BpAgeD6/V+iUoxYACryZSM26zKbaCjw0ZCvc1wrTisPZ f5+SjhhSC5pudmoS0CsaN4+zGCeQR3Xx4/w== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrudehiedgudduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfevfhfhjggtgfesth ekredttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeefgeefie eutdfggfetgefgheekjeehteeileeigfetieekjedvieeviefgheevtdenucevlhhushht vghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrg hvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 24 Nov 2023 18:24:44 -0500 (EST) Message-ID: Date: Fri, 24 Nov 2023 15:24:44 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: replication primary writting infinite number of WAL files To: Les Cc: pgsql-general References: <38f7d87c-ae22-48e8-a4c4-0acde1ad6eb9@aklaver.com> <430d83f0-65ff-47b0-9600-26094099fc17@aklaver.com> <1aec39e7-f64e-4afc-8ab2-3c723dec2b0b@aklaver.com> <592a4e9d-0d12-40ac-ad2c-0ddc4caa760a@aklaver.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/24/23 14:01, Les wrote: > > A single sequence for all id columns across all tables? > > How is the sequence value landing in the id column? > > In most cases it is by using "nextval(seq_name)" in the SQL statement. > But sometimes the sequence value is taken first, and then multiple > inserts are sent with fixed increasing values. (Sometimes records > reference each other, such records are inserted within a short > transaction with deferred foreign key constraints.) Leaving the reasoning behind this alone, the important part is that in order for the id values to jump like they did there was intensive processing of queries going on. That would be one thing you should track down, what kicked that off and why? > It is the main app that is using the database, using jdbc. Multiple > instances were running when the sequence jumped. Multiple instances of the app or something else? What where the instances doing? > When the dev db starts up (from the cloned data directory), it still has > the old repmgr conf. That config is deleted, and repmgr is disabled > right after the startup, but there is the possibility that the dev db > has connected the primary when it was cloned (7 days ago), because at > the beginning of startup, it is the exact clone of the standby from a > previous point of time. Hmm, that does not look good. At this point I would create a new post/thread where you organize the bits an pieces that you have posted over the course of this thread into a something more coherent. In particular a time line of the whole cloning/replication process. > >    Laszlo -- Adrian Klaver adrian.klaver@aklaver.com