Thread: migrating/spliting Postgres data directory on OpenIndiana

On 25 October 2012 19:46, Boris Epstein <borepstein@gmail.com> wrote:
> And if I want to split the storage - i.e., put databases into different
> directories - can I do that?

Take a look at the tablespace feature:

http://www.postgresql.org/docs/current/static/sql-createtablespace.html

You can move existing tables with ALTER TABLE (likewise for indexes),
and change the default for new tables created in future in a database
with ALTER DATABASE:

http://www.postgresql.org/docs/9.2/static/sql-altertable.html
http://www.postgresql.org/docs/9.2/static/sql-alterdatabase.html

Before the tablespace feature was added, people used to create
symlinks in the data directory if they wanted to use different storage
for different objects (say, a special fast disk array for certain
data).  The tablespace feature actually works the same way, but it's
managed for you via DDL commands.

Hope that helps,
Thomas

On 10/25/12 11:46 AM, Boris Epstein wrote:
>
> At this point I tried to just move the directory and use a soft link
> to repoint to it - but so far I have not even been able to do that
> properly, using pg_ctl. Apparently the process would not die. What
> should I look into to debug this one?

try the -m fast   switch to pg_ctl stop ...  this tells it to cleanly
exit any pending queries and terminate the client connections.  without
this, the postmaster waits for all the client apps to exit on their own,
which might be a rather long time.

stop the database server, THEN move the data directory.  you can then
use a symlink, or just change your startup scripts to set $PGDATA to the
new location.   if you do it that way, I suggest also setting PGDATA in
the postgres user's unix profile so commands like pg_ctl know where to look.


> And if I want to split the storage - i.e., put databases into
> different directories - can I do that?

there's little point in using different directories (tablespaces) on the
same device, but there are many scenarios where putting different
tablespaces on separate devices can be useful.

note a tablespace can be the default for a whole database (or more than
one), or it can be used on a table by table basis, so you can spread
tables across multiple file systems and physical devices.

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast