Re: Alternate Datafile Location

[Sam Stearns <sam.stearns@xxxxxxx>]

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 AM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:

On Thu, Aug 29, 2024 at 7:11 PM Sam Stearns <sam.stearns@xxxxxxx> 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/1242651222
tmpfs                            16G     0   16G   0% /run/user/1271246868
[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 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 

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