Differing data directories

[Ron Watkins]

I have a postgresql server setup/installed (finally).

Currently, it's installed data directory is: /var/lib/pgsql/10/data

However, I need to put a database into a custom filesystem.

Is it possible to have multiple PGDATA directories, and if so, how?



Currently, the postgres database is under /var/lib/pgsql/10/data, but I need
my main database to be in a different filesystem.

Ron

[David G. Johnston]

Post by Ron Watkins
I have a postgresql server setup/installed (finally).
Currently, it’s installed data directory is: /var/lib/pgsql/10/data
However, I need to put a database into a custom filesystem.
Is it possible to have multiple PGDATA directories, and if so, how?
Currently, the postgres database is under /var/lib/pgsql/10/data, but I
need my main database to be in a different filesystem


Short answer, just "initdb" a new cluster and point it to the desired
location.​

https://www.postgresql.org/docs/10/static/app-initdb.html

Now, the answer becomes longer since you will now have two clusters. You
will either want to remove the existing one or ensure the two play nicely.
It is further complicated by the means by which you installed and what
services have been setup.

All databases in a cluster share the same storage location by default
though you can create tablespaces and put different databases on different
tablespaces. But that likely adds more complexity to the running setup
than you need - at least by creating a new cluster and getting it to work
properly you only need to deal with complexity during the initial setup.

David J.

[Laurenz Albe]

Post by Ron Watkins
I have a postgresql server setup/installed (finally).
Currently, it’s installed data directory is: /var/lib/pgsql/10/data
However, I need to put a database into a custom filesystem.
Is it possible to have multiple PGDATA directories, and if so, how?
Currently, the postgres database is under /var/lib/pgsql/10/data,
but I need my main database to be in a different filesystem…

One simple solution would be:

# systemctl stop postgresql-10 # or whatever it takes to stop the server
# mv /var/lib/pgsql/10/data /your/mountpoint
# ln -s /your/mountpoint/data /var/lib/pgsql/10/data

Then you have to make sure that the operating system user has
permissions to enter the mount point.

Start your server and you are done.

Yours,
Laurenz Albe