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 1sjoaF-004Il7-Mr for pgsql-admin@arkaria.postgresql.org; Thu, 29 Aug 2024 23:29:43 +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 1sjoaC-009PtM-Ox for pgsql-admin@arkaria.postgresql.org; Thu, 29 Aug 2024 23:29:41 +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 1sjoaC-009PtD-Bg for pgsql-admin@lists.postgresql.org; Thu, 29 Aug 2024 23:29:40 +0000 Received: from mx0a-0039f802.pphosted.com ([205.220.164.45]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sjoa3-0029td-W8 for pgsql-admin@lists.postgresql.org; Thu, 29 Aug 2024 23:29:39 +0000 Received: from pps.filterd (m0209981.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 47THCTuG020074 for ; Thu, 29 Aug 2024 16:29:30 -0700 Received: from mail-yw1-f199.google.com (mail-yw1-f199.google.com [209.85.128.199]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 419pum2sx7-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Thu, 29 Aug 2024 16:29:29 -0700 (PDT) Received: by mail-yw1-f199.google.com with SMTP id 00721157ae682-6886cd07673so26086237b3.3 for ; Thu, 29 Aug 2024 16:29:29 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724974169; x=1725578969; h=cc: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=fxEtIv97E+ksqo/VH95n50FcrSsv0n7MoCLVAQw/tk8=; b=k9jHhYSY/CZ36ZFRXhvopCcG2Hk4plgP8tZtzq+eWZTpQTrnMqM3lrIO7EuGAGESLC PgFNVVvexTkeGaJvMdMWOzMsn4sFvIHOfibZfYlhw32+ECjAgx1I2fxdgS3bTePiLow6 5DxUN5rGkX0yZ5Iiy6wHELds2hM4ldnf3n0BJeFkAwXXuSgoBDbl/AEZbWci3SE1Jdrp P2Ka3y/yMqzLthL/sEVWo5fHtNKrR3l79Y8hYm09NdoFxhFaRTJi1I5EeCil+hGg7eN1 uHr+VnPClnhz3Idbq0zeOBpxvBQ7NUuzSK9pnkuHOIyMDcei0XO8FPe3tGFQaQ+OfhmD vUDQ== X-Gm-Message-State: AOJu0Yw4Px8V9B72hQKqWbt/bjhrySalFlg+bWb3q60PEnLs2TYfeXIr LYAuC1qWvql2PT7nyXtsppT7rNZJJ05rYPwylW7EHSzydysUs2/MHAOQ3tOeECNGJ+ZrjQ23K9P /GQm8BrioO78IsLUTFJ9xDBglUVwmzeIWVgPQvWeERTZuRu6b6K852/pkJEuH9+xKWYRDihdIkG ZDWLb4SkbYkvDEPPfCEsffTzFl0FXW3/YmpL/Eqy9ivAoJks76HP2R8SQ9QY5BQ7T9Ns4= X-Received: by 2002:a05:690c:6009:b0:6d0:f91e:2ff0 with SMTP id 00721157ae682-6d40f049d49mr2605277b3.11.1724974168581; Thu, 29 Aug 2024 16:29:28 -0700 (PDT) X-Google-Smtp-Source: AGHT+IGNS4dM0t1PM6egDsTPuR0Va9PkuwZwkvsvk1cr3N0Bw5zLXLgNqGU5QN6IgP1i8udZT6Bg514MhMVdHCE+Wa4= X-Received: by 2002:a05:690c:6009:b0:6d0:f91e:2ff0 with SMTP id 00721157ae682-6d40f049d49mr2604977b3.11.1724974167909; Thu, 29 Aug 2024 16:29:27 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sam Stearns Date: Fri, 30 Aug 2024 08:59:16 +0930 Message-ID: Subject: Re: Alternate Datafile Location To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000086ab580620dad8d5" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.28.16 definitions=2024-08-29_06,2024-08-29_02,2024-05-17_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000086ab580620dad8d5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks for the advice on the /PGDATA directory naming convention. I'm not sure how our Linux team created the instance but it is created on root: [postgres@thiludbapql01 ~]$ echo $PGDATA /var/lib/pgsql/16/data [postgres@thiludbapql01 ~]$ df -h /var/lib/pgsql/16/data Filesystem Size Used Avail Use% Mounted on /dev/mapper/rhel-root 28G 4.5G 23G 17% / [postgres@thiludbapql01 ~]$ That's fine as long as we can create databases with datafile storage on an alternate filesystem. I don't see anywhere in the documentation how to do that. On Fri, Aug 30, 2024 at 8:47=E2=80=AFAM Ron Johnson wrote: > On Thu, Aug 29, 2024 at 7:11=E2=80=AFPM Sam Stearns = wrote: > >> Howdy, >> >> Here's our filesystems: >> >> [postgres@xxx data]$ df -h >> Filesystem Size Used Avail Use% Mounted on >> devtmpfs 80G 0 80G 0% /dev >> tmpfs 80G 44M 80G 1% /dev/shm >> tmpfs 80G 716K 80G 1% /run >> tmpfs 80G 0 80G 0% /sys/fs/cgroup >> /dev/mapper/rhel-root 28G 4.5G 23G 17% / >> tmpfs 80G 8.0K 80G 1% /tmp >> /dev/sda2 1014M 457M 558M 46% /boot >> /dev/sda1 599M 5.9M 594M 1% /boot/efi >> /dev/mapper/pgwalvg-pgwalvol 570G 28K 541G 1% /PG_WAL >> /dev/mapper/pgdatavg-pgdatavol 2.3T 28K 2.2T 1% /PGDATA >> tmpfs 16G 0 16G 0% /run/user/12426512= 22 >> tmpfs 16G 0 16G 0% /run/user/12712468= 68 >> [postgres@xxx data]$ >> >> > "df -Ph -x tmpfs -x devtmpfs" would make that look a lot neater. > > >> Our Postgres software install and data directory is on root: >> >> [postgres@xxx data]$ pwd >> /var/lib/pgsql/16/data >> [postgres@thiludbapql01 data]$ df -h . >> Filesystem Size Used Avail Use% Mounted on >> /dev/mapper/rhel-root 28G 4.5G 23G 17% / >> [postgres@xxx data]$ >> >> >> We want our database datafile storage to be on /PGDATA. >> > > Making the $PGDATA environment variable the same as the directory name is > bound to lead to shell errors. > > >> I don't see anything in the version 16 documentation on how to accomplis= h >> this. >> > > Where did you look? > > How was the instance (aka cluster) created? > > >> I must be missing it somewhere. Could someone please advise? >> > > https://www.postgresql.org/docs/16/app-initdb.html > > -D *directory* > --pgdata=3D*directory* > > > This option specifies the directory where the database cluster should be > stored. This is the only information required by initdb, but you can > avoid writing it by setting the PGDATA environment variable, which can be > convenient since the database server (postgres) can find the data > directory later by the same variable. > > -- > Death to America, and butter sauce. > Iraq lobster! > --=20 *Samuel Stearns* Lead Database Administrator *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com [image: DAT] --00000000000086ab580620dad8d5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks for the advice on the /PGDATA directory naming conv= ention.

I'm not sure how our Linux team created the = instance but it is created on root:

[postgres@thil= udbapql01 ~]$ echo $PGDATA
/var/lib/pgsql/16/data
[postgres@thiludbap= ql01 ~]$ df -h /var/lib/pgsql/16/data
Filesystem =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 Size =C2=A0Used Avail Use% Mounted on
/dev/mapper/r= hel-root =C2=A0 28G =C2=A04.5G =C2=A0 23G =C2=A017% /
[postgres@thiludba= pql01 ~]$


That's fine as lo= ng as we can create databases with datafile storage on an alternate filesys= tem.=C2=A0 I don't see anywhere in the documentation how to do that.

On Fri, Aug 30, 2024 at 8:47=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
=
On Thu, Aug 29, 2024 at 7:11=E2=80=AFPM Sam Stearns <sam.stearns@dat.com> = wrote:
Howdy,

Here's our= filesystems:

[postgres@xxx data]$ df -h
Filesy= stem =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Size =C2=A0Used Avail Use% Mounted on
devtmpfs =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 80G =C2= =A0 =C2=A0 0 =C2=A0 80G =C2=A0 0% /dev
tmpfs =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A080G = =C2=A0 44M =C2=A0 80G =C2=A0 1% /dev/shm
tmpfs =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A080= G =C2=A0716K =C2=A0 80G =C2=A0 1% /run
tmpfs =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A080G = =C2=A0 =C2=A0 0 =C2=A0 80G =C2=A0 0% /sys/fs/cgroup
/dev/mapper/rhel-roo= t =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A028G =C2=A04.5G =C2=A0 23G =C2=A0= 17% /
tmpfs =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A080G =C2=A08.0K =C2=A0 80G =C2=A0 1% /= tmp
/dev/sda2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A01014M =C2=A0457M =C2=A0558M =C2=A046% /boot
/dev/sda= 1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 599M =C2=A05.9M =C2=A0594M =C2=A0 1% /boot/efi
/dev/mapper/pgwalvg-p= gwalvol =C2=A0 =C2=A0570G =C2=A0 28K =C2=A0541G =C2=A0 1% /PG_WAL
/dev/m= apper/pgdatavg-pgdatavol =C2=A02.3T =C2=A0 28K =C2=A02.2T =C2=A0 1% /PGDATA=
tmpfs =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A016G =C2=A0 =C2=A0 0 =C2=A0 16G =C2=A0 0% = /run/user/1242651222
tmpfs =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A016G =C2=A0 =C2=A0 0 =C2= =A0 16G =C2=A0 0% /run/user/1271246868
[postgres@xxx data]$

=

"df -Ph -x tmpfs -x= devtmpfs" would make that look a lot neater.
=C2=A0
Our Postgres software install and data directory is on root:

[postgres@xxx data]$ pwd
/var/lib/pgsql/16/data
= [postgres@thiludbapql01 data]$ df -h .
Filesystem =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 Size =C2=A0Used Avail Use% Mounted on
/dev/mapper/r= hel-root =C2=A0 28G =C2=A04.5G =C2=A0 23G =C2=A017% /
[postgres@xxx data= ]$


We want our database datafile st= orage to be on /PGDATA.=C2=A0

M= aking the $PGDATA environment variable the same as the directory name is bo= und to lead to shell errors.
=C2=A0
I don't see anything i= n the version 16 documentation on how to accomplish this.=C2=A0
=

Where did you look?

How was the instance (aka cluster) created?
=C2=A0
I must b= e missing it somewhere.=C2=A0 Could someone please advise?


-D=C2=A0directory
--pgdata=3Ddirectory<= /code>=C2=A0

This option specifies the directo= ry where the database cluster should be stored. This is the only informatio= n required by=C2=A0initdb<= /code>, but you can avoid writing it by setting the=C2=A0PGDATA=C2=A0environment variable, whi= ch can be convenient since the database server (postgres) can find the data directory later by= the same variable.


--
Death to America, and butter sauce.
Iraq lobster= !


--

Samuel Stearns
Lead Database Administrat= or
c:=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0|=C2=A0DAT.com

3D=
--00000000000086ab580620dad8d5--