3

I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package.

I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be entirely dedicated to this database).

My pgdata is located in var/lib/postgresql/9.1/main. I plan to stop the postgresql service, copy the pgdata content to the new partition, then make a symbolic link to the new partition, chown the new directory to postgres user, restart postgresql... but I'm afraid this all looks more like a hack.

Is there a way to create a database specifically on a specified partition ? Something more "canonical" (not a play of word with ubuntu)

Improve this question
6
  • It depends on your operating system and whether it's a self-compiled or pre-packaged postgres install, you need to provide these details in the question Commented Mar 19, 2013 at 15:07
  • @DanielVérité ubuntu 12.04., ubuntu depot package installed Commented Mar 19, 2013 at 15:15
  • Check the answers to this question. Commented Mar 19, 2013 at 15:18
  • ok I found it @ var/lib/postgresql/9.1/main Commented Mar 19, 2013 at 15:59
  • 1
    You don't need the symbolic link, just change the configuration of the postmaster startup script to point to the new data directory. You could also create a tablespace on the new partition and create the new database in that. No need to mess around with the data directory. Commented Mar 19, 2013 at 16:35

1 Answer 1

Reset to default
8

CREATE TABLESPACE could be used.

create tablespace dedicated_datastore owner postgres location '/my/mount/point/'

and now the tablespace can be used as parameter when creating a database, (or a table, or an index)

create database my_database tablespace dedicated_datastore
Improve this answer
2
  • 2
    I'm not sure about using the phrase should be used!! If you only have one database (and/or cluster) then it might be better to use either mount points, symlinks, or change the PGDATA location (in order of preference from various guides). Tablespaces have lots of caveats when it comes to backing up where as having the whole data directory mapped does not. Commented Nov 30, 2016 at 15:37
  • @AdamGent noticed your comment only today. I changed it to "could"/ Commented Jul 10, 2020 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.