Re: [GENERAL] Multiple clusters with same tablespace location

[Nate Dudenhoeffer]

Tom, thanks for the advice. I brought up a new instance yesterday, with the
intent of trying it, and discovered that Wal-e with the "blind-restore"
option would put everything in the pg_tblspc directory, instead of
symlinking it. For this use case, that worked great.

Nate

On Wed, Jul 20, 2016 at 11:16 AM, Tom Lane  wrote:

> Nate Dudenhoeffer  writes:
> > The issue is that both clusters are using a base_backup and wal restore
> > from the same master database, so when they are restored, the tablespace
> > will already exist. Is there a way to change the tablespace location
> during
> > the recovery process?
>
> You would definitely need each slave to have its own copy of the
> tablespace.  I've not done this myself and would strongly recommend
> testing on non-production instances, but I believe you can make it work
> by adjusting each slave's $PGDATA/pg_tblspc symlinks to point to different
> locations.  When setting up new slave instances, pg_basebackup's
> --tablespace-mapping option would help you with that.  For an existing
> slave instance, you'd need to shut it down while manually moving the
> tablespace directory(s) and re-pointing the symlink(s).
>
> regards, tom lane
>

Re: [GENERAL] Multiple clusters with same tablespace location

[Tom Lane]

Nate Dudenhoeffer  writes:
> The issue is that both clusters are using a base_backup and wal restore
> from the same master database, so when they are restored, the tablespace
> will already exist. Is there a way to change the tablespace location during
> the recovery process?

You would definitely need each slave to have its own copy of the
tablespace.  I've not done this myself and would strongly recommend
testing on non-production instances, but I believe you can make it work
by adjusting each slave's $PGDATA/pg_tblspc symlinks to point to different
locations.  When setting up new slave instances, pg_basebackup's
--tablespace-mapping option would help you with that.  For an existing
slave instance, you'd need to shut it down while manually moving the
tablespace directory(s) and re-pointing the symlink(s).

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Multiple clusters with same tablespace location

[Nate Dudenhoeffer]

Thanks for the response Melvin.

The issue is that both clusters are using a base_backup and wal restore
from the same master database, so when they are restored, the tablespace
will already exist. Is there a way to change the tablespace location during
the recovery process?

On Wed, Jul 20, 2016 at 10:34 AM, Melvin Davidson 
wrote:

>
>
> On Wed, Jul 20, 2016 at 10:59 AM, Nate Dudenhoeffer <
> ndudenhoef...@gmail.com> wrote:
>
>> What happens if I have two postgresql clusters running on the same server
>> each with a tablespace pointing at the same location?
>>
>> I have a production server, where I intend to move some tables to a
>> second tablespace. I have a development/qa server which has multiple
>> clusters running, each with a restore from the production server.
>>
>> Thanks in advance,
>> Nate
>>
>
> Pointing multiple clusters to the same tablespace is a very bad idea. You
> run the risk of overwriting a file with the same name from a different
> cluster.
> Think of Ghostbusters ->"Don't cross the streams"!.
> There is no reason why you cannot have separate directories for each
> cluster/tablespace, so why ask for trouble?
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Multiple clusters with same tablespace location

[Melvin Davidson]

On Wed, Jul 20, 2016 at 10:59 AM, Nate Dudenhoeffer  wrote:

> What happens if I have two postgresql clusters running on the same server
> each with a tablespace pointing at the same location?
>
> I have a production server, where I intend to move some tables to a second
> tablespace. I have a development/qa server which has multiple clusters
> running, each with a restore from the production server.
>
> Thanks in advance,
> Nate
>

Pointing multiple clusters to the same tablespace is a very bad idea. You
run the risk of overwriting a file with the same name from a different
cluster.
Think of Ghostbusters ->"Don't cross the streams"!.
There is no reason why you cannot have separate directories for each
cluster/tablespace, so why ask for trouble?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Multiple clusters with same tablespace location

[Nate Dudenhoeffer]

What happens if I have two postgresql clusters running on the same server
each with a tablespace pointing at the same location?

I have a production server, where I intend to move some tables to a second
tablespace. I have a development/qa server which has multiple clusters
running, each with a restore from the production server.

Thanks in advance,
Nate