Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gvnKq-0008AQ-8X for pgsql-admin@arkaria.postgresql.org; Mon, 18 Feb 2019 18:08:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gvnKn-0000Hy-RU for pgsql-admin@arkaria.postgresql.org; Mon, 18 Feb 2019 18:08:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gvnKn-0000Dj-Hs for pgsql-admin@lists.postgresql.org; Mon, 18 Feb 2019 18:08:05 +0000 Received: from mout.perfora.net ([74.208.4.194]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gvnKl-0007xt-BF for pgsql-admin@postgresql.org; Mon, 18 Feb 2019 18:08:05 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MKsGU-1gvnKf2r9E-0003JI; Mon, 18 Feb 2019 19:07:57 +0100 Message-ID: <5C6AF47B.5030207@sqlexec.com> Date: Mon, 18 Feb 2019 13:07:55 -0500 From: MichaelDBA User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Andrew Gierth CC: "S. Bob" , pgsql-admin Subject: Re: Unique cluster / instance identifier? References: <92d53982-cf1d-ce3f-c541-60a252f85aef@quadratum-braccas.com> <87r2c5htm3.fsf@news-spur.riddles.org.uk> In-Reply-To: <87r2c5htm3.fsf@news-spur.riddles.org.uk> Content-Type: multipart/alternative; boundary="------------010002040806030000090907" X-Provags-ID: V03:K1:Nj8EMk8dHcqdj4yBoR9iXRKKlAKMtCLOJmXglBb/EZ2/SzOE9Kk AAcWERyEaLkJf/Pcis+IHRrU/xMxABekYm8U5PyPHw0gs6Ev16NgFNGSz8etiayBjdURxpa QGx6UgovRhpd3IVkKq0Z24C/uyAbAOAAOtFc+KxOhGHvQHJoFRt+pESvwL8hh4zz0HzfIkz +xeJTbh9TTmhX54uNBSHQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:47cB3oHKsAo=:q4ZjdStek3LieqQZ/yQAIP hvx03vA85tmRwx1z2+dFxtmWtVwee2VcXkh5ybwTCWeN9H910hK48Ny+DV3MbnTHZVSds3Ntw 1KE55owhUvsvnpct/pIv7TILPJasfVwBPHcCl3O6xeocZ0fdBhPH0ePQCPAzrnpPOdsjP1kNN irYJ7D9lHE4TS4cndaoyh/Ph++18b27gB520XjUTvW7DAacrRTewQLwoEJUXcSelD7mH/OOUM ZNKBPN6yfdGF5mChG16qTKaL2iNFedwoGMhC2Wi3t/BA+dOQ6QJwz75xgdIsHRGIrv65Xcu4H WpqXgaHWdtso0fVTF/8FRBAUrCbvjIS4YR8GXzOU1zTPfKJt95zog892J+Vs+LXrKPZSbujYP KVazUXR1HWyqvKJ4U0UDZVaicmXpUmz2J0ec/r42ah52BuzMzSObs1K6UzMyHeLZTDLJwqBfz TxR0VWwqnYCeon0AwRMp+p7XqtYcyLdj0k7wF3Bu1SJ+uBJ8YQtKqviWPus7OLHMthIjxFtAU PSHTkuwPwfBPCnmw7COsHVyqpgbwio27GjiyV17DDmUNpkbNmk5A+Hy/9IE+iip3HdPA6k8xt oqbiMPtyc+9cofLfRPJfLlFgoP/ILrWVoOvl/joX6seBVa6FihMfpTJR2sF5OAstORTWmtjmD 4YKr9Hg6enr8ovsv0Lv0eoBCbVbVtQc9Rh7y6gpArV3rt5upBDmCx2ll38rR26ZPHKe7BUuP8 8VVe3+Ob2Sm77Vt/Siq/wBUUF8Dgmduz+M3V6wp69W2OyWiQfRd6jPBvJ/8= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------010002040806030000090907 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit ahhh good to know regarding pg12, thanks > Andrew Gierth > 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 > 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 > > --------------010002040806030000090907 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 PM
Hi all;


Does PostgreSQL store a unique cluster or instance identifier that we can access to identify our instances?


Thanks in advance



--------------010002040806030000090907--