Обсуждение: Unique cluster / instance identifier?
Hi all; Does PostgreSQL store a unique cluster or instance identifier that we can access to identify our instances? Thanks in advance
Since 9.5, you could use cluster_name for this purpose.
Monday, February 18, 2019 12:08 PMHi all;
Does PostgreSQL store a unique cluster or instance identifier that we can access to identify our instances?
Thanks in advance
>>>>> "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)
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. >
ahhh good to know regarding pg12, thanks
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.Monday, February 18, 2019 12:08 PMHi all;
Does PostgreSQL store a unique cluster or instance identifier that we can access to identify our instances?
Thanks in advance
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
Вложения
>>>>> "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)