Thread: Alternate Datafile Location

On Thu, Aug 29, 2024 at 7:11 PM Sam Stearns <sam.stearns@dat.com> 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!

> On Aug 29, 2024, at 5:29 PM, Sam Stearns <sam.stearns@dat.com> wrote:
>
> 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.

-D <directory>

You should take a step back and read the basic documentation on PG administration. For instance: https://www.postgresql.org/docs/16/creating-cluster.html

We (at least I) have been assuming that you wanted to create a new database cluster, to run a server against that storage, using initdb. Using an already running server to put data elsewhere, using createdb is different, and requires that you create a tablespace first: https://www.postgresql.org/docs/16/manage-ag-tablespaces.html

On Thu, Aug 29, 2024 at 7:56 PM Sam Stearns <sam.stearns@dat.com> wrote:

Ok, we were going with the approach of the Postgres software (cluster) being on the root filesystem then creating databases with data file storage on an alternate filesystem.

 

PG isn't like SQL Server, where you have a set of catalog databases, and then can put user databases anywhere you please.

It's sounding like the better approach would be to install the Postgres software (cluster with initdb) on a filesystem other than root.  Do I have that right?

Correct.  Postgresql has tablesspaces, but tools like pg_restore and PgBackRest are least confusing when all user databases live in the same $PGDATA/base.  "CREATE DATABASE foo;" handles all that for you.

If you really want your user databases spread all over, then one possibility is that each has its own instance (aka cluster) and port number.

Ubuntu has pg_lsclusters to help manage that, but other OS's require something like "netstat -an | grep :543[2345]" (which doesn't help if an instance is shut down.

 

On Fri, Aug 30, 2024 at 9:17 AM Scott Ribe <scott_ribe@elevated-dev.com> wrote:

You should take a step back and read the basic documentation on PG administration. For instance: https://www.postgresql.org/docs/16/creating-cluster.html

We (at least I) have been assuming that you wanted to create a new database cluster, to run a server against that storage, using initdb. Using an already running server to put data elsewhere, using createdb is different, and requires that you create a tablespace first: https://www.postgresql.org/docs/16/manage-ag-tablespaces.html

--

Death to America, and butter sauce.

Iraq lobster!

Ok, we were going with the approach of the Postgres software (cluster) being on the root filesystem then creating databases with data file storage on an alternate filesystem.

It's sounding like the better approach would be to install the Postgres software (cluster with initdb) on a filesystem other than root.  Do I have that right?

On Fri, Aug 30, 2024 at 9:17 AM Scott Ribe <scott_ribe@elevated-dev.com> wrote:

You should take a step back and read the basic documentation on PG administration. For instance: https://www.postgresql.org/docs/16/creating-cluster.html

We (at least I) have been assuming that you wanted to create a new database cluster, to run a server against that storage, using initdb. Using an already running server to put data elsewhere, using createdb is different, and requires that you create a tablespace first: https://www.postgresql.org/docs/16/manage-ag-tablespaces.html

--

Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com