DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=KjVEeKUvBHm1mQZt+d0QcK6hws719kt1ypQeTr2vXYAf4Pg6kDjHPgTZoIrfuhP33AccEhrLbJUdZC1h2KhonACp1CmacMFOre4iVoSPFy+0dSIWqoGEr2oeznjp7bGzvt+WQt8go6HowDzUSXLyRwHVR5EIaS/ZGIeuQcusBV0=;
References: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl>
 <1391600088.5800.YahooMailNeo@web133204.mail.ir2.yahoo.com>
 <0aa401cf226a$12eb9320$38c2b960$@comarch.pl>
 <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com>
Message-ID: <1391611865.95042.YahooMailNeo@web133202.mail.ir2.yahoo.com>
Date: Wed, 5 Feb 2014 14:51:05 +0000 (GMT)
From: Glyn Astill <glynastill@yahoo.co.uk>
Reply-To: Glyn Astill <glynastill@yahoo.co.uk>
Subject: Re: IDENTIFY_SYSTEM
To: Cezariusz Marek <cezariusz.marek@comarch.pl>,
 "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

----- Original Message -----

> From: Glyn Astill <glynastill@yahoo.co.uk>
> To: Cezariusz Marek <cezariusz.marek@comarch.pl>; "pgsql-sql@postgresql.o=
rg" <pgsql-sql@postgresql.org>
> Cc:=20
> Sent: Wednesday, 5 February 2014, 14:45
> Subject: Re: [SQL] IDENTIFY_SYSTEM
>=20
> ----- Original Message -----
>=20
>>  From: Cezariusz Marek <cezariusz.marek@comarch.pl>
>>  To: pgsql-sql@postgresql.org
>>  Cc:=20
>>  Sent: Wednesday, 5 February 2014, 12:01
>>  Subject: Re: [SQL] IDENTIFY_SYSTEM
>>=20
>>> =A0 That's part of the streaming replication protocol
>>>=20
>>> =A0 http://www.postgresql.org/docs/9.3/static/protocol-replication.html
>>> =A0 As long as you're using wal_level >=3D archive and the=20
> replication=20
>>  connection is enabled you can retrieve it via psql
>>=20
>>  Yes, I know, but there is no way to get the systemid value from a funct=
ion=20
> using=20
>>  just SQL or plpgsql?
>>=20
>=20
> I don't think so no, but you may have better luck finding someone more=20
> knowledgable posting to pgsql-general.=A0 You could do it by calling=20
> pg_controldata via an untrusted procedural language, not so sure how happ=
y=20
> I'd be with that myself.=A0 E.g. with plperlu:
>=20
> CREATE OR REPLACE FUNCTION get_system_identifier_unsafe(text)=20
> RETURNS text AS=20
> $BODY$
> =A0=A0=A0 my $rv;
> =A0=A0=A0 my $data;
> =A0=A0=A0 my $pg_controldata_bin =3D $_[0];
> =A0=A0=A0 my $sysid;
> =A0=A0=A0=20
> =A0=A0=A0 $rv =3D spi_exec_query('SHOW data_directory', 1);
> =A0=A0=A0 $data =3D $rv->{rows}[0]->{data_directory};
> =A0=A0=A0=20
> =A0=A0=A0 open(FD,"$pg_controldata_bin $data | ");
> =A0=A0=A0=20
> =A0=A0=A0 while(<FD>) {
> =A0=A0=A0 =A0=A0=A0 if (/Database system identifier:/) {
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 $sysid =3D $_;
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 for ($sysid) {
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 s/Database system identifier://;
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 s/[^0-9]//g;
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 }
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 last;
> =A0=A0=A0 =A0=A0=A0 }
> =A0=A0=A0 }
> =A0=A0=A0 close (FD);
> =A0=A0=A0 return $sysid;=A0=A0=A0=20
>=20
> $BODY$
> LANGUAGE plperlu;
>=20
>=20

So if I actually ran that:

test=3D# select get_system_identifier_unsafe('pg_controldata');
=A0get_system_identifier_unsafe
------------------------------
=A05667443312440565226

>=20
>>> =A0 If it's just for licencing perhaps inet_server_addr() or a plperl=20
>>  function to grab the mac address of the machine might suffice?
>>=20
>>  I have to license each database, not just the whole machine. And the=20
> systemid is=20
>>  the only unique database identifier I've found.
>=20
> Is it each database or each postgresql instance / cluster?=A0=A0 How exac=
tly do you=20
> want your licencing to work? There may be a better way.
>


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql