Re: Alternate Datafile Location

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Got it.  Thank you everyone for your help!

Sam

On Fri, Aug 30, 2024 at 9:44 AM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:
On Thu, Aug 29, 2024 at 7:56 PM Sam Stearns <sam.stearns@xxxxxxx> 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@xxxxxxxxxxxxxxxx> 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

DAT

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux