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 1qtGSu-00BfKH-9n for pgsql-admin@arkaria.postgresql.org; Thu, 19 Oct 2023 00:00:40 +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 1qtGSr-00ATOY-Pw for pgsql-admin@arkaria.postgresql.org; Thu, 19 Oct 2023 00:00:38 +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 1qtGSr-00ATOP-EC for pgsql-admin@lists.postgresql.org; Thu, 19 Oct 2023 00:00:38 +0000 Received: from mail-yb1-xb2d.google.com ([2607:f8b0:4864:20::b2d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qtGSn-001RjL-GW for pgsql-admin@lists.postgresql.org; Thu, 19 Oct 2023 00:00:37 +0000 Received: by mail-yb1-xb2d.google.com with SMTP id 3f1490d57ef6-d9ac9573274so8265879276.0 for ; Wed, 18 Oct 2023 17:00:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697673633; x=1698278433; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=QDmAQ1G3q8cHDDIlS60VQQQrTurX9vL+9xpO9IzysZg=; b=OR/it8H24bs1BlqAGNbA+nmR4UiJ1l4W5aXAsGdjirbiPJj/1T0M6flMStPSrCN4gy SinPlh644bJXWsKxGJvT0o1DOxq+zsDuo+oeppJv9Yc+JEPE8FWXtIiFYrSQqXFm2TDb CHqDOJ/sGvo6czfPjEdlM7NdFS2M/3B5V7vK2etPJ+kAymEjXrYgE76BBbQL/h/fYHhP NJyjGq3xwz7YxyX1dh+z0yy7+g2141qFCaSSdhr0005/Lq/nU24iXdmBckTwsCf7vAoG bs9eQ5SuPTSeM1X8RMpj0l54tQTvW5/pPmvmwRi4fpRZhhib3/FdEA8Qm0g9jnNJ2Itc K0Qw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697673633; x=1698278433; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=QDmAQ1G3q8cHDDIlS60VQQQrTurX9vL+9xpO9IzysZg=; b=KV5ULWvD8Ss8UWgwfKhs520h29kNfme9aZuMg4soD5QPeNSz3d6gk3uuGR1f8SZ0A8 IhTlEvf1Hm6XKSHCLBm1YQ4u57nkvg7f/NA3a9EarJolvzMQokl6SWse1NdBby+b7x0Q HxK9jVEdVBbEUbJBN7xD+t9P5Z8N6n1iUd7soJA3I4s/fdHRp3rQX8bxkykJjkuxzuqi gjyfUzKpa+7Bq5t6jYzzlK5xEov3y3rdB+JcWe4MDWStxWgNjbRjpL90+dT+7C0oZKDf KUnAftg5FgHp6xJKT+ejaaGUR51qBqmambXcX+wzHw9jmzPfIx7dzfJpdNKBTBEiJMoJ 9hOg== X-Gm-Message-State: AOJu0Yw/L0Li1wXFV/tGp6iqMVVUvOuIt5hkxRqOzvwpROQDP+Lslna7 yOclSB6ZUjLnjGNma3DC+owoqLgasms= X-Google-Smtp-Source: AGHT+IEZxy1G7bxF3/03+UpseaOneH0O5KqcQknasWOYTFxwF/7BhUUuosI1u4z2dvptUe2UTbfveQ== X-Received: by 2002:a25:8a0b:0:b0:d9b:4c61:26f1 with SMTP id g11-20020a258a0b000000b00d9b4c6126f1mr849462ybl.24.1697673632646; Wed, 18 Oct 2023 17:00:32 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:3d15:81b3:3a0:4d09? ([2600:1700:bd2c:b810:3d15:81b3:3a0:4d09]) by smtp.googlemail.com with ESMTPSA id r34-20020a25ac62000000b00d7e96c6eaf5sm1677147ybd.46.2023.10.18.17.00.32 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 18 Oct 2023 17:00:32 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------zZkW4QPXPtJ0Js50jd7RxS5H" Message-ID: <45e16ff2-da2d-46c3-be52-03cad8eb60a3@gmail.com> Date: Wed, 18 Oct 2023 19:00:31 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pg_dump -Z6 (the default) can be pretty slow Content-Language: en-US To: Pgsql-admin 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. --------------zZkW4QPXPtJ0Js50jd7RxS5H Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/18/23 18:42, Scott Ribe wrote: >> On Oct 18, 2023, at 5:21 PM, Ron wrote: >> >> It didn't occur to me to mention that I used it. Do people really still not use -Fd? > I don't know--I guess it depends on context. Certainly for upgrades I don't know any reason not to. Even for nightly backups (we still use pg_dump instead of pgbackrest on a few smaller systems), I always use directory format backups. >> I'm still using 9.6, so that feature isn't available yet. When I get the Pg 15 VMs stood up (Pg15 binaries are not available for RHEL 6), I'm definitely going to try that. > I thought of that mere seconds after posting my prior reply ;-) > > Have you considered pg_upgrade? Since "the migrated databases will be on new servers, and we purge off old partitions and add new partitions, pg_upgrade and logical replication are off the table". And, of course, 9.6 binaries aren't available at all (at least I can't find 9.6.24 RPMs anywhere under https://download.postgresql.org/pub/), so rsyncing the database files to the new server and then doing a pg_upgrade won't work. Even then, I'd have to rebuild many indices anyway, due to glibc locale changes. -- Born in Arizona, moved to Babylonia. --------------zZkW4QPXPtJ0Js50jd7RxS5H Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/18/23 18:42, Scott Ribe wrote:
On Oct 18, 2023, at 5:21 PM, Ron <ronljohnsonjr@gmail.com> wrote:

It didn't occur to me to mention that I used it.  Do people really still not use -Fd?

I don't know--I guess it depends on context. Certainly for upgrades I don't know any reason not to.

Even for nightly backups (we still use pg_dump instead of pgbackrest on a few smaller systems), I always use directory format backups.

I'm still using 9.6, so that feature isn't available yet.  When I get the Pg 15 VMs stood up (Pg15 binaries are not available for RHEL 6), I'm definitely going to try that.

I thought of that mere seconds after posting my prior reply ;-)

Have you considered pg_upgrade?

Since "the migrated databases will be on new servers, and we purge off old partitions and add new partitions, pg_upgrade and logical replication are off the table".

And, of course, 9.6 binaries aren't available at all (at least I can't find 9.6.24 RPMs anywhere under https://download.postgresql.org/pub/), so rsyncing the database files to the new server and then doing a pg_upgrade won't work.  Even then, I'd have to rebuild many indices anyway, due to glibc locale changes.



--
Born in Arizona, moved to Babylonia.
--------------zZkW4QPXPtJ0Js50jd7RxS5H--