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 1qnJUb-00AZ83-73 for pgsql-general@arkaria.postgresql.org; Mon, 02 Oct 2023 14:01:49 +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 1qnJUZ-00FQQ9-Ts for pgsql-general@arkaria.postgresql.org; Mon, 02 Oct 2023 14:01:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qnJUZ-00FQQ1-Fo for pgsql-general@lists.postgresql.org; Mon, 02 Oct 2023 14:01:47 +0000 Received: from mail-ed1-x533.google.com ([2a00:1450:4864:20::533]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qnJUX-007nN2-1l for pgsql-general@postgresql.org; Mon, 02 Oct 2023 14:01:46 +0000 Received: by mail-ed1-x533.google.com with SMTP id 4fb4d7f45d1cf-533e7d127d4so17999440a12.3 for ; Mon, 02 Oct 2023 07:01:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1696255183; x=1696859983; darn=postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=011gc9Aio6CI8T88EiT3UQPlWz10MdR3ZD23UbPWrZs=; b=CAjQ+LjSXBH1HNEUfyaaPYR5NloB7XoHRWqAG6jrx9k1NIlnfk7ECweryAF2q9pY1c MsWTB5M5dTVJhitEUALimHrQI7UZ2825P1o1wkkAUNpiFqlKpxlThUerSpvfF6JD7LII A8RLXIKmj42BosE8U6JHbHFkj4QsXkAc2z6rwz+m5hTrew+ub06nBwBL5NoqQt/48vuX xsgHI5BVIr6hKWQiH5YVHKf3W5g4nM8L4NlhsfLYjLsmpn1VQYvntBxgwiBG07KvGZ4h j9ouLlfCtXpSpV2JJn7lBWEFdSry6BM4kYdMxnctfUMohZ9dM6BjmS7ZBzOQ3CPXnDze ZoEQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696255183; x=1696859983; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=011gc9Aio6CI8T88EiT3UQPlWz10MdR3ZD23UbPWrZs=; b=b2NytB7VQSd/vrxbXrHs06VMCsJebXaGljGo+wLx7ZvtN7g6VIPB9B4CYoKF4zcgt9 mmqzwR6Nbyjk7hJKGzQLxG5oKgGMliuHI7GgDuEbRtk/QpBSzNBkL6aoJ9K83Ao48MG/ xQFAdxFEwJBxBGLML4CgyFMLe3l2LULQcyBhHKpEuwwHVRdbJsSsCFm9htuZ93eiKF87 9bH7J4eslO86dVI4emzZh6dDI5Dn+sqfVw0Bkx+Ct8yZsBDrIAUv4SwOV55DklGLDX74 0AErhiGRatrJPKpo/E4nWn4THWrWi8r/tz7xbO7J+lVhwOPoz5Ov5xcLQ0xlUUyp73zk /JNQ== X-Gm-Message-State: AOJu0YyaI+bjWfSOW5IiSs3tT/z62CgbgRofssa6fnGN3oY7xVeQ305Q v+zVjplr30pxzkk0LwOqf7aXgg== X-Google-Smtp-Source: AGHT+IFAVJxlMcp/1Z7u4TleGL5a2zsG2+cgupLOrl2xdPqT6IeBrR025fDcZfZZUkS9Ml98LSIK/A== X-Received: by 2002:a17:907:1dd6:b0:9ae:4b25:d89f with SMTP id og22-20020a1709071dd600b009ae4b25d89fmr9969373ejc.6.1696255182865; Mon, 02 Oct 2023 06:59:42 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:6cf4:1982:ecd7:8449:ce90]) by smtp.gmail.com with ESMTPSA id h9-20020a17090619c900b009ae54eba5casm17067392ejd.102.2023.10.02.06.59.42 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 02 Oct 2023 06:59:42 -0700 (PDT) Message-ID: <584a8f4d0effccd69b9ed83248cd432bd08a7bc2.camel@cybertec.at> Subject: Re: Cancelling "vacuum full" in single user mode? From: Laurenz Albe To: Colin 't Hart , pgsql-general@postgresql.org Date: Mon, 02 Oct 2023 15:59:41 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 2023-10-02 at 14:39 +0200, Colin 't Hart wrote: > I have a customer approaching transaction wraparound, about 3million > transaction IDs away at the moment. > Postgres 9.5 (yes, I know...) >=20 > Somewhat mislead by the message to vacuum the database in single user > mode, they are now in single user mode and are running "vacuum full" > on the "worst" database, which has 18.5 million tables, and is about > 350GB on disk. >=20 > Now we want to cancel the "vacuum full" and run a "vacuum freeze" > instead... or preferably, start back up in multiuser mode and run > vacuum there, but that was running very slowly while autovacuum was > trying to run (so we would need to disable that too, for now). >=20 > So, >=20 > 1. Can we cancel safely? > 2. Any other suggestions? Upgrade to a newer version right now? VACUUM (FULL) is exactly the wrong thing to do, so yes, cancel that. Normally you don't need single-user mode, since VACUUM does not consume a transaction ID. I'd run SELECT oid::regclass AS table_name, age(relfrozenxid) AS oldest_unfrozen_= row FROM pg_class WHERE relkind =3D 'r' ORDER BY age(relfrozenxid) DESC; And VACUUM the tables (FREEZE is not necessary) starting with the top ones on the list. Upgrade as soon as possible. Not only are you missing out on tons of fixed bugs, but VACUUM performance and smartness has greatly improved since 9.5. Yours, Laurenz Albe