Re: Alternate Datafile Location

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(at)thiludbapql01 ~]$ echo $PGDATA
/var/lib/pgsql/16/data
[postgres(at)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(at)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 AM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> On Thu, Aug 29, 2024 at 7:11 PM Sam Stearns <sam(dot)stearns(at)dat(dot)com> wrote:
>
>> Howdy,
>>
>> Here's our filesystems:
>>
>> [postgres(at)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/1242651222
>> tmpfs 16G 0 16G 0% /run/user/1271246868
>> [postgres(at)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(at)xxx data]$ pwd
>> /var/lib/pgsql/16/data
>> [postgres(at)thiludbapql01 data]$ df -h .
>> Filesystem Size Used Avail Use% Mounted on
>> /dev/mapper/rhel-root 28G 4.5G 23G 17% /
>> [postgres(at)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 accomplish
>> 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=*directory*
> <https://www.postgresql.org/docs/16/app-initdb.html#APP-INITDB-OPTION-PGDATA>
>
> 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!
>

--

*Samuel Stearns*
Lead Database Administrator
*c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com
[image: DAT]
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>