Re: Avoiding Tablespace path collision for primary and standby

On Wed, Jun 20, 2018 at 10:50 AM Andres Freund <andres(at)anarazel(dot)de> wrote:

>
>
> On June 20, 2018 10:31:05 AM PDT, Ashwin Agrawal <aagrawal(at)pivotal(dot)io>
> wrote:
> >On Wed, Jun 20, 2018 at 9:39 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >
> >> On Fri, May 25, 2018 at 02:17:23PM -0700, Ashwin Agrawal wrote:
> >> >
> >> > On Fri, May 25, 2018 at 7:33 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> >wrote:
> >> >
> >> > Ashwin Agrawal <aagrawal(at)pivotal(dot)io> writes:
> >> > > Proposing to create directory with timestamp at time of
> >creating
> >> > tablespace
> >> > > and create symbolic link to it instead.
> >> >
> >> > I'm skeptical that this solves your problem. What happens when
> >the
> >> CREATE
> >> > TABLESPACE command is replicated to the standby with sub-second
> >> delay?
> >> >
> >> >
> >> > I thought timestamps have micro-second precision. Are we expecting
> >> tabelspace
> >> > to be created, wal logged, streamed, and replayed on mirror in
> >> micro-second ?
> >>
> >> I didn't see anyone answer your question above. We don't expect
> >> micro-second replay, but clock skew, which Tom Lane mention, could
> >make
> >> it appear to be a micro-second replay.
> >>
> >
> >Thanks Bruce for answering. Though I still don't see why clock skew is
> >a
> >problem here. As I think clock skew only happens across machines. On
> >same
> >machine why would it be an issue. Problem is only with same machine,
> >different machines anyways paths don't collide so even if clock skew
> >happens is not a problem. (I understand there may be reservations for
> >putting timestamp in directory path, but clock skew argument is not
> >clear.)
>
> Clock skew happens within machines too. Both because of multi socket
> systems and virtualization systems. Also clock adjustments.
>

Thank You that helps.

Okay just bouncing another approach, how about generating UUID for a
postgres instance during initdb and pg_basebackup ? (unlike
`system_identifier` used in pg_controldata store it in separate independent
file which is excluded in pg_basebackup, instead created by pg_basebackup)
Read only once during startup and used in tablespace path ? (Understand
generating uuid maybe little heavy-lifting for just same node tablespace
path collision, but having unique identifier for each postgres instance
primary or standby maybe useful for long term for other purposes as well)