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 1sl6ul-005Ghl-U1 for pgsql-admin@arkaria.postgresql.org; Mon, 02 Sep 2024 13:16:16 +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 1sl6uk-00E3dP-KA for pgsql-admin@arkaria.postgresql.org; Mon, 02 Sep 2024 13:16:14 +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 1sl6uk-00E3dG-4X for pgsql-admin@lists.postgresql.org; Mon, 02 Sep 2024 13:16:14 +0000 Received: from mail-oa1-x2b.google.com ([2001:4860:4864:20::2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sl6uf-000IBU-Dn for pgsql-admin@lists.postgresql.org; Mon, 02 Sep 2024 13:16:13 +0000 Received: by mail-oa1-x2b.google.com with SMTP id 586e51a60fabf-27045e54272so1928865fac.0 for ; Mon, 02 Sep 2024 06:16:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725282968; x=1725887768; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Oi6LIEFuCDwPRhcDnx9NblOmknxjCrqepTc9K2yRdPM=; b=CaAF3PXqbMEcFtVWuLUJcnnH8H5b5V1h341p+FfctWoolpUFS+Y85mtbevdTFUn3iR UIHY07mK57Hlsu6zJOG5gVL3c097YsxBWxbQVKfL8GynppzJYYAZHNLMRTY6Ll/n3vFC yLfQUegQRqIKTN3u5i/aGOPXwGBReUrgJlOZCL3qgu2RAT7fN2z/qlJR/HuP++COhHfF 4fbchtX6y9JtLZFBVnUfLMp+p5kjqpD4j9x85wqGwgI86A2M4DK6n1XUkQSCNX3ZLS63 /quwHyz8TnPAYKqegygqG7tUKph3uL7Tf3lF637iWyAsBYeDXpeTu9j8+anZOic0pB6K NgBA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725282968; x=1725887768; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Oi6LIEFuCDwPRhcDnx9NblOmknxjCrqepTc9K2yRdPM=; b=Ya955CeF2QO7wn60K7z4SBLMetmc+nO/IzlqWIhq2xUcv4YzGREutGngmZI2INlzWh sJQMwN3n0x8PbVVtVbl7Apd0udKNlMbcG837YNu7dOSdOIx4k+tMlUUajpxEEClCNLUn 0rYZYhIHGLgVHJ81/zE5RaXjSh1Bdw4WM5GdrJXMVnD5P44xQSRRGceFhhY3cznnWXdW FGCVu2iRMgV4IS1sKFjI01JwzmwJQDhb+YXG2E7g5MPnIwylQcZzt6Xoo+Z8w1W0Eqa7 YIAuxSEeID6MapqiFanI4IsVENYpfUDwgOvj0Ln7cWlbaL2xHpe9Ml5J8PMXZ2j0IYFe JirA== X-Gm-Message-State: AOJu0YzmkRRkvdkVXNdLU/jL76p7+Ttq0VyOXyQTu6cX7B6OR7K0ojCB 7J6xdyRQfi7hnLUJDiEnQygq1NfL6ZRrzBA5kHv/WC7+mNBXeybf2gtljBg0PHGeZo/rmAgOu+V p4+k3n2CVPK4ysNU3pw4E7mVtuYgjMM6K X-Google-Smtp-Source: AGHT+IGNgyFSs8E+tj61S5q5168IhNjDdzK1zpvp89JpLAVeUqWHMvH6nvrdZ/DXA+AWZ/wTlV/VmvEBtffZ7D9C6C0= X-Received: by 2002:a05:6870:5cc7:b0:270:64ed:c125 with SMTP id 586e51a60fabf-277c804bc6bmr8235043fac.16.1725282968215; Mon, 02 Sep 2024 06:16:08 -0700 (PDT) MIME-Version: 1.0 References: <5BDE97CE-242C-479A-BDC7-CF263BBD554B@gmail.com> <2fba534e999d3233dc4fe43aabf84622f93064eb.camel@cybertec.at> In-Reply-To: <2fba534e999d3233dc4fe43aabf84622f93064eb.camel@cybertec.at> From: Ron Johnson Date: Mon, 2 Sep 2024 09:15:57 -0400 Message-ID: Subject: Re: Postgresql Database and PG_WAL locations To: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000752990062122beca" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000752990062122beca Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Sep 2, 2024 at 6:30=E2=80=AFAM Laurenz Albe wrote: > On Thu, 2024-08-29 at 15:02 -0400, Ron Johnson wrote: > > On Thu, Aug 29, 2024 at 2:22=E2=80=AFPM Matthew Tice = wrote: > > > > On Aug 29, 2024, at 12:18=E2=80=AFPM, Henry Ashu wrote: > > > > > > > > I have a database that's about 2TB in size, and I want to place the > database > > > > files in a separate mount point(PGDATA) and the log files in a > different mount > > > > point(PG_WAL). What's your take on this?. > > > > > > > > > Take a look at > https://wiki.postgresql.org/wiki/Installation_and_Administration_Best_pra= ctices > > > > > > Essentially, yes, you will want your WAL and data stored on different > devices > > > (or the very least, different partitions). > > > > Is that recommendation still valid? After all, that was written when 1= 5 > years old > > Sun Studio 12 was still pertinent. Times have changed since then. > Disks are much, much bigger. > > I think the advice is still valid. > > Today you'd have different filesystems on different logical volumes rathe= r > than different physical disks, None of our disks are physical; they're all SAN LUNs. > but it is still a good idea to separate data and WAL, > so that they cannot fill up each other's file system. > Regular checkpoints, transactions(*) that don't stay open for hours or days, and monitoring replication to ensure that it keeps replicating data instead of piling up on the primary server all solve that problem Honestly... it's been *YEARS* since I've seen that problem. Besides, "disks are cheap", right? *COPY statements don't count. I'd actually define a third file system for the PostgreSQL log files, for > the same reason. > I set log_directory to /var/log/postgresql because logs go in /var/log. =F0=9F=98=80 (pg_basebackup won't replicate it, which is also h= andy.) On a separate partition so as to isolate PG data from other application and OS data (and lets me manage capacity via a script). Ditto the PgBackrest directory: isolate PG data from other application and OS data. We'd have to put them on separate mount points anyway, since the VM build process doesn't like large / and /boot disks. --=20 Death to America, and butter sauce. Iraq lobster! --000000000000752990062122beca Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Sep 2, 2024 at 6:30=E2=80=AFAM La= urenz Albe <laurenz.albe@cyb= ertec.at> wrote:
On Thu, 2024-08-29 at 15:02 -0400, Ron J= ohnson wrote:
> On Thu, Aug 29, 2024 at 2:22=E2=80=AFPM Matthew Tice <mjtice@gmail.com> wrote: > > > On Aug 29, 2024, at 12:18=E2=80=AFPM, Henry Ashu <henry.ashu@dat.com>= wrote:
> > >
> > > I have a database that's about 2TB in size, and I want t= o place the database
> > > files in a separate=C2=A0mount point(PGDATA) and the log fil= es in a different mount
> > > point(PG_WAL). What's your take=C2=A0on this?.
> >
> >
> > Take a look at=C2=A0https://wiki.postgresql.org/wiki/Installation_and_Administration_B= est_practices
> >
> > Essentially, yes, you will want your WAL and data stored on diffe= rent devices
> > (or the very least, different partitions).
>
> Is that recommendation=C2=A0still valid?=C2=A0 After all, that was wri= tten when=C2=A015 years old
> Sun Studio 12 was still pertinent.=C2=A0 Times have changed since then= .=C2=A0 Disks are much, much bigger.

I think the advice is still valid.

Today you'd have different filesystems on different logical volumes rat= her
than different physical disks,

None of our = disks are physical; they're all SAN LUNs.
=C2=A0
but it is still a good idea to = separate data and WAL,
so that they cannot fill up each other's file system.
<= div>
Regular checkpoints, transactions(*) that don't stay= open for hours or days, and monitoring replication to ensure that it keeps= replicating data instead of piling up on the primary server all solve that= problem
=C2=A0
Honestly... it's been YEARS= =C2=A0since I've seen that problem.

Besides, &= quot;disks are cheap", right?

*COPY statement= s don't count.

I'd actually define a third file system for the PostgreSQL log files, f= or
the same reason.

I set=C2= =A0log_directory to=C2=A0/var/log/postgresql because logs go in /var/log.= =C2=A0=C2=A0=F0=9F=98=80=C2=A0 (pg_basebackup won't replicate it, which= is also handy.) On a separate partition so as to isolate PG data from othe= r application and OS data (and lets me manage capacity via a script).

Ditto the PgBackrest directory: isolate PG = data from other application and OS data.

We'd = have to put them on separate mount points anyway, since the VM build proces= s doesn't like large / and /boot disks.

--
Death to America, and butter sauce.
Iraq lob= ster!
--000000000000752990062122beca--