public inbox for [email protected]
help / color / mirror / Atom feedAlternate Datafile Location
11+ messages / 5 participants
[nested] [flat]
* Alternate Datafile Location
@ 2024-08-29 23:11 Sam Stearns <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Sam Stearns @ 2024-08-29 23:11 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>; +Cc: Peter Garza <[email protected]>; Henry Ashu <[email protected]>
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]$
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. I don't see
anything in the version 16 documentation on how to accomplish this. I must
be missing it somewhere. Could someone please advise?
Thanks,
Sam
--
*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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-29 23:17 Ron Johnson <[email protected]>
parent: Sam Stearns <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Johnson @ 2024-08-29 23:17 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Thu, Aug 29, 2024 at 7:11 PM Sam Stearns <[email protected]> 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*
<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!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-29 23:29 Sam Stearns <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Sam Stearns @ 2024-08-29 23:29 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 <[email protected]> wrote:
> On Thu, Aug 29, 2024 at 7:11 PM Sam Stearns <[email protected]> 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*
> <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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-29 23:33 Scott Ribe <[email protected]>
parent: Sam Stearns <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2024-08-29 23:33 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: Pgsql-admin <[email protected]>
> On Aug 29, 2024, at 5:29 PM, Sam Stearns <[email protected]> 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>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-29 23:40 Sam Stearns <[email protected]>
parent: Scott Ribe <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Sam Stearns @ 2024-08-29 23:40 UTC (permalink / raw)
To: Scott Ribe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
[postgres@thiludbapql01 ~]$ createdb baptest -D /PGDATA
createdb: error: database creation failed: ERROR: tablespace "/PGDATA"
does not exist
[postgres@thiludbapql01 ~]$
The directory /PGDATA exists.
[postgres@thiludbapql01 ~]$ df -h /PGDATA
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/pgdatavg-pgdatavol 2.3T 28K 2.2T 1% /PGDATA
[postgres@thiludbapql01 ~]$
On Fri, Aug 30, 2024 at 9:03 AM Scott Ribe <[email protected]>
wrote:
> > On Aug 29, 2024, at 5:29 PM, Sam Stearns <[email protected]> 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>
--
*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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-29 23:44 David G. Johnston <[email protected]>
parent: Sam Stearns <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: David G. Johnston @ 2024-08-29 23:44 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: Scott Ribe <[email protected]>; Pgsql-admin <[email protected]>
On Thursday, August 29, 2024, Sam Stearns <[email protected]> wrote:
> [postgres@thiludbapql01 ~]$ createdb baptest -D /PGDATA
> createdb: error: database creation failed: ERROR: tablespace "/PGDATA"
> does not exist
>
>
Clusters, created using initdb, are what interact with the file system.
Individual databases don’t. They exist within a cluster.
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-29 23:47 Scott Ribe <[email protected]>
parent: Sam Stearns <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2024-08-29 23:47 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-29 23:55 Sam Stearns <[email protected]>
parent: Scott Ribe <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Sam Stearns @ 2024-08-29 23:55 UTC (permalink / raw)
To: Scott Ribe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 <[email protected]>
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
[image: DAT]
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-30 00:14 Ron Johnson <[email protected]>
parent: Sam Stearns <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Ron Johnson @ 2024-08-30 00:14 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Thu, Aug 29, 2024 at 7:56 PM Sam Stearns <[email protected]> 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 <[email protected]>
> 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!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-30 00:17 Sam Stearns <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Sam Stearns @ 2024-08-30 00:17 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Got it. Thank you everyone for your help!
Sam
On Fri, Aug 30, 2024 at 9:44 AM Ron Johnson <[email protected]> wrote:
> On Thu, Aug 29, 2024 at 7:56 PM Sam Stearns <[email protected]> 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 <[email protected]>
>> 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!
>
--
*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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternate Datafile Location
@ 2024-08-30 00:18 ji hwan song <[email protected]>
parent: Sam Stearns <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: ji hwan song @ 2024-08-30 00:18 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: Scott Ribe <[email protected]>; Pgsql-admin <[email protected]>
Inint db로 생성시키면 해당 경로에 시스템 테이블생성이됩니다..이게..사용이 많으면
문제가되겠죠..wall은별도로지정가능하고요..사용자클러스터 디비는..별도로 생성해서 관히하시면 되구요..기본 설치시 생성된디렉토리는
변경이어려우니.추가하려면 별도의 파일시스템을먼들어..링크를 걸어주세요.
2024년 8월 30일 (금) 오전 8:56, Sam Stearns <[email protected]>님이 작성:
> 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 <[email protected]>
> 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
> [image: DAT]
> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2024-08-30 00:18 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-29 23:11 Alternate Datafile Location Sam Stearns <[email protected]>
2024-08-29 23:17 ` Ron Johnson <[email protected]>
2024-08-29 23:29 ` Sam Stearns <[email protected]>
2024-08-29 23:33 ` Scott Ribe <[email protected]>
2024-08-29 23:40 ` Sam Stearns <[email protected]>
2024-08-29 23:44 ` David G. Johnston <[email protected]>
2024-08-29 23:47 ` Scott Ribe <[email protected]>
2024-08-29 23:55 ` Sam Stearns <[email protected]>
2024-08-30 00:14 ` Ron Johnson <[email protected]>
2024-08-30 00:17 ` Sam Stearns <[email protected]>
2024-08-30 00:18 ` ji hwan song <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox