Postgres Pro
Downloads
Home   >   mailing lists

Thread: Multiple clusters with same tablespace location

Multiple clusters with same tablespace location

From
Nate Dudenhoeffer
Date:
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

Re: Multiple clusters with same tablespace location

From
Melvin Davidson
Date:


On Wed, Jul 20, 2016 at 10:59 AM, Nate Dudenhoeffer <ndudenhoeffer@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: Multiple clusters with same tablespace location

From
Nate Dudenhoeffer
Date:
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 <melvin6925@gmail.com> wrote:


On Wed, Jul 20, 2016 at 10:59 AM, Nate Dudenhoeffer <ndudenhoeffer@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: Multiple clusters with same tablespace location

From
Tom Lane
Date:
Nate Dudenhoeffer <ndudenhoeffer@gmail.com> 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: Multiple clusters with same tablespace location

From
Nate Dudenhoeffer
Date:
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 <tgl@sss.pgh.pa.us> wrote:
Nate Dudenhoeffer <ndudenhoeffer@gmail.com> 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