Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nXxYo-0000PU-R4 for pgsql-admin@arkaria.postgresql.org; Sat, 26 Mar 2022 03:57:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nXxXo-0003Qn-UB for pgsql-admin@arkaria.postgresql.org; Sat, 26 Mar 2022 03:56:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nXxXo-0003Qd-Fm for pgsql-admin@lists.postgresql.org; Sat, 26 Mar 2022 03:56:52 +0000 Received: from mail-oi1-x22b.google.com ([2607:f8b0:4864:20::22b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nXxXh-0007wl-NH for pgsql-admin@lists.postgresql.org; Sat, 26 Mar 2022 03:56:51 +0000 Received: by mail-oi1-x22b.google.com with SMTP id v75so10305142oie.1 for ; Fri, 25 Mar 2022 20:56:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=GUs4a7wlSP937sKT761AGBANVbkNFIq0oSYLns6f1Dg=; b=Kj/DXleHwzFsfBa6N/xBcNKIPyM1Ja9aAoDQaQOUZPT/QLnburnNz3JDWcRI9agdi1 nGlTemO9OIq/FYOs/BSWgGMf8KEusZFd5laqUTyv6Rdb+RR6fooIIw71nvnk4RDEGcoE PamC7JnrBlPwZtTN/MQR2ox0ZQKQ3H60msZcMq42rlpeNbwXI4yCK5BxODDqi3vEjxlB U4KleMzl9ApzdiI+hlJVpVGgBEkWTadD54DeYM3u5xRNhx/BIim0NYhJ/YjH0XAl1pP9 SDCDV0UWr+1TCAv5AfvX9ojOME3HVOz0+ThL3FE1owL2oBaRccVaDbXeIbxfq2eOrosi /cew== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=GUs4a7wlSP937sKT761AGBANVbkNFIq0oSYLns6f1Dg=; b=EKwv0cV79N+SDcr0/NU9NaHsaOzxgISaCNjWn3FLlvYVqJB6Ry7XWXHP6x7yFz9ENY pNbYFf4y7/Kd6n0OUJvl7Fgf409EA7i/PJKIIzM4Pstoyjcn7Ic5srxZn9BkyTTmTwbQ oHwntEgSPZJqWb7iZP62dcWEbAwcbZVwxDA/W7fdl0DSY6AA6bjxM7h/1jyBNthZCvnh +nmY8oU0FUKwihZAvgUKeuHOo/wUAcOB3mmIO0UA+PiFKkF4gvSVN2Ha4e0H6fUlEWaE k/+5eNIyvLage9HcJyQnMhxOEfc4y2VRSz0vF4LJRg9gl4i2r4bovjiLi+lSjpOjHcHu ivGA== X-Gm-Message-State: AOAM5317iwAksYrRraa8wAQLjMGIbinnSH1uZFGCgaH4meoAFYojSyln LjzxBiRwNysjO4NvEtV5ftJDj7XkE5o= X-Google-Smtp-Source: ABdhPJy+5fk86gPzE50IvWydQy8CrXQ714gJ4PIo0bbevEy45fn2XyTsegm7UuyaRgRGPMA1npactw== X-Received: by 2002:a05:6808:1a1e:b0:2da:5c5a:38f3 with SMTP id bk30-20020a0568081a1e00b002da5c5a38f3mr7185206oib.183.1648267004278; Fri, 25 Mar 2022 20:56:44 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id e4-20020a056808148400b002d9be41b179sm4112432oiw.50.2022.03.25.20.56.43 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 25 Mar 2022 20:56:43 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------teTfc0KKJToNRFuSVKq904Ma" Message-ID: <5b742974-2d28-f6a9-9340-96341389a1b6@gmail.com> Date: Fri, 25 Mar 2022 22:56:42 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.5.0 Subject: Re: Avoid Wraparound Failures Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------teTfc0KKJToNRFuSVKq904Ma Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/25/22 17:16, Loles wrote: > Hi! > > Suppose the databases on my instance are near to have a wraparound failure. Is this actually the case, or are you just afraid? > > (I think so, from what I see, but in the PostgreSQL log I haven't seen any > warning about It yet). > > What do I have to do? > > vacuum freeze; Kinda, but really... Heck No!!! vacuum --freeze --jobs=`nproc` --dbname=your_db_name That will VACUUM FREEZE every table in your database in multiple threads. However, you only need to freeze tables getting near wraparound. pg_class.relfrozenxid tells you which tables to worry about. > > better than, > > vacuum analyze; VACUUM ANALYZE does a plain vacuum *plus* collects statistics for the query analyzer.  Collecting query stats has nothing to do with vacuuming or protecting against wraparound. > > Or both? > > If the autovacuum_freeze configuration parameters have defaults values, > should I modify any first? > What version are you running?  Even the recently EOL versions protect from wraparound (though it's a painful last-ditch process). -- Angular momentum makes the world go 'round. --------------teTfc0KKJToNRFuSVKq904Ma Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 3/25/22 17:16, Loles wrote:
Hi!

Suppose the databases on my instance are near to have a wraparound failure.

Is this actually the case, or are you just afraid?


(I think so, from what I see, but in the PostgreSQL log I haven't seen any warning about It yet).

What do I have to do?

vacuum freeze;

Kinda, but really... Heck No!!!

vacuum --freeze --jobs=`nproc` --dbname=your_db_name

That will VACUUM FREEZE every table in your database in multiple threads.

However, you only need to freeze tables getting near wraparound.  pg_class.relfrozenxid tells you which tables to worry about.


better than,

vacuum analyze;

VACUUM ANALYZE does a plain vacuum plus collects statistics for the query analyzer.  Collecting query stats has nothing to do with vacuuming or protecting against wraparound.


Or both?

If the autovacuum_freeze configuration parameters have defaults values, should I modify any first?


What version are you running?  Even the recently EOL versions protect from wraparound (though it's a painful last-ditch process).

--
Angular momentum makes the world go 'round.
--------------teTfc0KKJToNRFuSVKq904Ma--