Re: Alternate Datafile Location

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!