Re: Avoiding Tablespace path collision for primary and standby

On June 20, 2018 10:31:05 AM PDT, Ashwin Agrawal <aagrawal@pivotal.io> wrote:
>On Wed, Jun 20, 2018 at 9:39 AM Bruce Momjian <bruce@momjian.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@sss.pgh.pa.us>
>wrote:
>> >
>> >     Ashwin Agrawal <aagrawal@pivotal.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)