pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUnique cluster / instance identifier?
7+ messages / 4 participants
[nested] [flat]
* Unique cluster / instance identifier?
@ 2019-02-18 17:08 S. Bob <sbob@quadratum-braccas.com>
2019-02-18 17:15 ` Re: Unique cluster / instance identifier? MichaelDBA <MichaelDBA@sqlexec.com>
2019-02-18 18:03 ` Re: Unique cluster / instance identifier? Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 2 replies; 7+ messages in thread
From: S. Bob @ 2019-02-18 17:08 UTC (permalink / raw)
To: pgsql-admin
Hi all;
Does PostgreSQL store a unique cluster or instance identifier that we
can access to identify our instances?
Thanks in advance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unique cluster / instance identifier?
2019-02-18 17:08 Unique cluster / instance identifier? S. Bob <sbob@quadratum-braccas.com>
@ 2019-02-18 17:15 ` MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 0 replies; 7+ messages in thread
From: MichaelDBA @ 2019-02-18 17:15 UTC (permalink / raw)
To: S. Bob <sbob@quadratum-braccas.com>; +Cc: pgsql-admin
Since 9.5, you could use cluster_name for this purpose.
> S. Bob <mailto:sbob@quadratum-braccas.com>
> Monday, February 18, 2019 12:08 PM
> Hi all;
>
>
> Does PostgreSQL store a unique cluster or instance identifier that we
> can access to identify our instances?
>
>
> Thanks in advance
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unique cluster / instance identifier?
2019-02-18 17:08 Unique cluster / instance identifier? S. Bob <sbob@quadratum-braccas.com>
@ 2019-02-18 18:03 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-02-18 18:07 ` Re: Unique cluster / instance identifier? S. Bob <sbob@quadratum-braccas.com>
2019-02-18 18:07 ` Re: Unique cluster / instance identifier? MichaelDBA <MichaelDBA@sqlexec.com>
2019-02-18 18:40 ` Re: Unique cluster / instance identifier? Joe Conway <mail@joeconway.com>
1 sibling, 3 replies; 7+ messages in thread
From: Andrew Gierth @ 2019-02-18 18:03 UTC (permalink / raw)
To: S. Bob <sbob@quadratum-braccas.com>; +Cc: pgsql-admin
>>>>> "S" == S Bob <sbob@quadratum-braccas.com> writes:
S> Hi all;
S> Does PostgreSQL store a unique cluster or instance identifier that
S> we can access to identify our instances?
Yes. But it's not easy to get at: it's the "Database system identifier"
shown in the output of pg_controldata. (pg12+ will have a function to
get it from SQL, but released versions don't.)
e.g.
Database system identifier: 6659016342798875639
The number is mostly generated from the time the instance was created,
with some bits modified. So for example:
$ perl -le 'print scalar gmtime +(shift >> 32)' 6659016342798875639
Sun Feb 17 17:04:21 2019
PG uses this internally to make sure that replication never tries to
apply WAL records generated on one system to a different system.
Physical replication secondary systems share the primary's system
identifier since they always originate as a copy (via base backup) of
the primary.
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unique cluster / instance identifier?
2019-02-18 17:08 Unique cluster / instance identifier? S. Bob <sbob@quadratum-braccas.com>
2019-02-18 18:03 ` Re: Unique cluster / instance identifier? Andrew Gierth <andrew@tao11.riddles.org.uk>
@ 2019-02-18 18:07 ` S. Bob <sbob@quadratum-braccas.com>
2 siblings, 0 replies; 7+ messages in thread
From: S. Bob @ 2019-02-18 18:07 UTC (permalink / raw)
To: Andrew Gierth <andrew@tao11.riddles.org.uk>; +Cc: pgsql-admin
Perfect! Thanks
On 2/18/19 11:03 AM, Andrew Gierth wrote:
>>>>>> "S" == S Bob <sbob@quadratum-braccas.com> writes:
> S> Hi all;
>
> S> Does PostgreSQL store a unique cluster or instance identifier that
> S> we can access to identify our instances?
>
> Yes. But it's not easy to get at: it's the "Database system identifier"
> shown in the output of pg_controldata. (pg12+ will have a function to
> get it from SQL, but released versions don't.)
>
> e.g.
>
> Database system identifier: 6659016342798875639
>
> The number is mostly generated from the time the instance was created,
> with some bits modified. So for example:
>
> $ perl -le 'print scalar gmtime +(shift >> 32)' 6659016342798875639
> Sun Feb 17 17:04:21 2019
>
> PG uses this internally to make sure that replication never tries to
> apply WAL records generated on one system to a different system.
> Physical replication secondary systems share the primary's system
> identifier since they always originate as a copy (via base backup) of
> the primary.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unique cluster / instance identifier?
2019-02-18 17:08 Unique cluster / instance identifier? S. Bob <sbob@quadratum-braccas.com>
2019-02-18 18:03 ` Re: Unique cluster / instance identifier? Andrew Gierth <andrew@tao11.riddles.org.uk>
@ 2019-02-18 18:07 ` MichaelDBA <MichaelDBA@sqlexec.com>
2 siblings, 0 replies; 7+ messages in thread
From: MichaelDBA @ 2019-02-18 18:07 UTC (permalink / raw)
To: Andrew Gierth <andrew@tao11.riddles.org.uk>; +Cc: S. Bob <sbob@quadratum-braccas.com>; pgsql-admin
ahhh good to know regarding pg12, thanks
> Andrew Gierth <mailto:andrew@tao11.riddles.org.uk>
> Monday, February 18, 2019 1:03 PM
>
> S> Hi all;
>
> S> Does PostgreSQL store a unique cluster or instance identifier that
> S> we can access to identify our instances?
>
> Yes. But it's not easy to get at: it's the "Database system identifier"
> shown in the output of pg_controldata. (pg12+ will have a function to
> get it from SQL, but released versions don't.)
>
> e.g.
>
> Database system identifier: 6659016342798875639
>
> The number is mostly generated from the time the instance was created,
> with some bits modified. So for example:
>
> $ perl -le 'print scalar gmtime +(shift >> 32)' 6659016342798875639
> Sun Feb 17 17:04:21 2019
>
> PG uses this internally to make sure that replication never tries to
> apply WAL records generated on one system to a different system.
> Physical replication secondary systems share the primary's system
> identifier since they always originate as a copy (via base backup) of
> the primary.
>
> S. Bob <mailto:sbob@quadratum-braccas.com>
> Monday, February 18, 2019 12:08 PM
> Hi all;
>
>
> Does PostgreSQL store a unique cluster or instance identifier that we
> can access to identify our instances?
>
>
> Thanks in advance
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unique cluster / instance identifier?
2019-02-18 17:08 Unique cluster / instance identifier? S. Bob <sbob@quadratum-braccas.com>
2019-02-18 18:03 ` Re: Unique cluster / instance identifier? Andrew Gierth <andrew@tao11.riddles.org.uk>
@ 2019-02-18 18:40 ` Joe Conway <mail@joeconway.com>
2019-02-18 18:51 ` Re: Unique cluster / instance identifier? Andrew Gierth <andrew@tao11.riddles.org.uk>
2 siblings, 1 reply; 7+ messages in thread
From: Joe Conway @ 2019-02-18 18:40 UTC (permalink / raw)
To: Andrew Gierth <andrew@tao11.riddles.org.uk>; S. Bob <sbob@quadratum-braccas.com>; +Cc: pgsql-admin
On 2/18/19 1:03 PM, Andrew Gierth wrote:
>>>>>> "S" == S Bob <sbob@quadratum-braccas.com> writes:
>
> S> Hi all;
>
> S> Does PostgreSQL store a unique cluster or instance identifier that
> S> we can access to identify our instances?
>
> Yes. But it's not easy to get at: it's the "Database system identifier"
> shown in the output of pg_controldata. (pg12+ will have a function to
> get it from SQL, but released versions don't.)
Umm, that function has been there since pg 9.6
select left(version(),17), system_identifier from pg_control_system();
left | system_identifier
-------------------+---------------------
PostgreSQL 9.6.11 | 6320270156373568679
(1 row)
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unique cluster / instance identifier?
2019-02-18 17:08 Unique cluster / instance identifier? S. Bob <sbob@quadratum-braccas.com>
2019-02-18 18:03 ` Re: Unique cluster / instance identifier? Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-02-18 18:40 ` Re: Unique cluster / instance identifier? Joe Conway <mail@joeconway.com>
@ 2019-02-18 18:51 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Gierth @ 2019-02-18 18:51 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: S. Bob <sbob@quadratum-braccas.com>; pgsql-admin; MichaelDBA <MichaelDBA@sqlexec.com>
>>>>> "Joe" == Joe Conway <mail@joeconway.com> writes:
Joe> Umm, that function has been there since pg 9.6
my mistake, you are of course correct
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-02-18 18:51 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-18 17:08 Unique cluster / instance identifier? S. Bob <sbob@quadratum-braccas.com>
2019-02-18 17:15 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-02-18 18:03 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-02-18 18:07 ` S. Bob <sbob@quadratum-braccas.com>
2019-02-18 18:07 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-02-18 18:40 ` Joe Conway <mail@joeconway.com>
2019-02-18 18:51 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox