From: | Sam Stearns <sam(dot)stearns(at)dat(dot)com> |
---|---|
To: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Alternate Datafile Location |
Date: | 2024-08-30 00:17:43 |
Message-ID: | CAN6TVjko0k1ZZ+4Je7+dZBifh9YxjQC55si608hzW2Oo88yMdg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Got it. Thank you everyone for your help!
Sam
On Fri, Aug 30, 2024 at 9:44 AM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
> On Thu, Aug 29, 2024 at 7:56 PM Sam Stearns <sam(dot)stearns(at)dat(dot)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(at)elevated-dev(dot)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!
>
--
*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>
From | Date | Subject | |
---|---|---|---|
Next Message | ji hwan song | 2024-08-30 00:18:45 | Re: Alternate Datafile Location |
Previous Message | Ron Johnson | 2024-08-30 00:14:28 | Re: Alternate Datafile Location |