agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedIDENTIFY_SYSTEM
6+ messages / 3 participants
[nested] [flat]
* IDENTIFY_SYSTEM
@ 2014-02-03 08:35 Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 11:34 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Cezariusz Marek @ 2014-02-03 08:35 UTC (permalink / raw)
To: pgsql-sql
Hello,
Is there a way to call IDENTIFY_SYSTEM command from SQL? Or otherwise get
the unique system identifier from a function? I need some unique database
indentifier for the licensing purposes.
--
Cezariusz Marek
Mobile +48 608 646 494, Phone +48 33 484 6900, <http://www.comarch.com/;
http://www.comarch.com/
Comarch SA, ul. Michałowicza 12, 43-300 Bielsko-Biała, POLAND
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: IDENTIFY_SYSTEM
2014-02-03 08:35 IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
@ 2014-02-05 11:34 ` Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 12:01 ` Re: IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
0 siblings, 1 reply; 6+ messages in thread
From: Glyn Astill @ 2014-02-05 11:34 UTC (permalink / raw)
To: Cezariusz Marek <cezariusz.marek@comarch.pl>; pgsql-sql
____________________________
> From: Cezariusz Marek <cezariusz.marek@comarch.pl>
>To: pgsql-sql@postgresql.org
>Sent: Monday, 3 February 2014, 8:35
>Subject: [SQL] IDENTIFY_SYSTEM
>
>
>
>Hello,
>
>Is there a way to call IDENTIFY_SYSTEM command from SQL? Or otherwise get the unique system identifier from a function? I need some unique database indentifier for the licensing purposes.
>
That's part of the streaming replication protocol
http://www.postgresql.org/docs/9.3/static/protocol-replication.html
As long as you're using wal_level >= archive and the replication connection is enabled you can retrieve it via psql
http://www.postgresql.org/message-id/AANLkTimFVHvhG73rPykX1z57MvPgskxJY1JuRYrD9Cf_@mail.gmail.com
glyn@test:~$ psql "replication=1" -c "IDENTIFY_SYSTEM"
systemid | timeline | xlogpos
---------------------+----------+-----------
5972513070019772415 | 1 | 0/2EE0368
If it's just for licencing perhaps inet_server_addr() or a plperl function to grab the mac address of the machine might suffice?
>--
>Cezariusz Marek
>Mobile +48 608 646 494, Phone +48 33 484 6900, http://www.comarch.com/
>Comarch SA, ul. Michałowicza 12, 43-300 Bielsko-Biała, POLAND
>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: IDENTIFY_SYSTEM
2014-02-03 08:35 IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 11:34 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
@ 2014-02-05 12:01 ` Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 14:45 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Cezariusz Marek @ 2014-02-05 12:01 UTC (permalink / raw)
To: pgsql-sql
> That's part of the streaming replication protocol
>
> http://www.postgresql.org/docs/9.3/static/protocol-replication.html
> As long as you're using wal_level >= archive and the replication connection is enabled you can retrieve it via psql
Yes, I know, but there is no way to get the systemid value from a function using just SQL or plpgsql?
> If it's just for licencing perhaps inet_server_addr() or a plperl function to grab the mac address of the machine might suffice?
I have to license each database, not just the whole machine. And the systemid is the only unique database identifier I've found.
--
Cezariusz Marek
Mobile +48 608 646 494, Phone +48 33 484 6900, http://www.comarch.com/
Comarch SA, ul. Michałowicza 12, 43-300 Bielsko-Biała, POLAND
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: IDENTIFY_SYSTEM
2014-02-03 08:35 IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 11:34 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 12:01 ` Re: IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
@ 2014-02-05 14:45 ` Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 14:51 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Glyn Astill @ 2014-02-05 14:45 UTC (permalink / raw)
To: Cezariusz Marek <cezariusz.marek@comarch.pl>; pgsql-sql
----- Original Message -----
> From: Cezariusz Marek <cezariusz.marek@comarch.pl>
> To: pgsql-sql@postgresql.org
> Cc:
> Sent: Wednesday, 5 February 2014, 12:01
> Subject: Re: [SQL] IDENTIFY_SYSTEM
>
>> That's part of the streaming replication protocol
>>
>> http://www.postgresql.org/docs/9.3/static/protocol-replication.html
>> As long as you're using wal_level >= archive and the replication
> connection is enabled you can retrieve it via psql
>
> Yes, I know, but there is no way to get the systemid value from a function using
> just SQL or plpgsql?
>
I don't think so no, but you may have better luck finding someone more knowledgable posting to pgsql-general. You could do it by calling pg_controldata via an untrusted procedural language, not so sure how happy I'd be with that myself. E.g. with plperlu:
CREATE OR REPLACE FUNCTION get_system_identifier_unsafe(text)
RETURNS text AS
$BODY$
my $rv;
my $data;
my $pg_controldata_bin = $_[0];
my $sysid;
$rv = spi_exec_query('SHOW data_directory', 1);
$data = $rv->{rows}[0]->{data_directory};
open(FD,"$pg_controldata_bin $data | ");
while(<FD>) {
if (/Database system identifier:/) {
$sysid = $_;
for ($sysid) {
s/Database system identifier://;
s/[^0-9]//g;
}
last;
}
}
close (FD);
return $sysid;
$BODY$
LANGUAGE plperlu;
>> If it's just for licencing perhaps inet_server_addr() or a plperl
> function to grab the mac address of the machine might suffice?
>
> I have to license each database, not just the whole machine. And the systemid is
> the only unique database identifier I've found.
Is it each database or each postgresql instance / cluster? How exactly do you want your licencing to work? There may be a better way.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: IDENTIFY_SYSTEM
2014-02-03 08:35 IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 11:34 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 12:01 ` Re: IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 14:45 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
@ 2014-02-05 14:51 ` Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 16:15 ` Re: IDENTIFY_SYSTEM bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Glyn Astill @ 2014-02-05 14:51 UTC (permalink / raw)
To: Cezariusz Marek <cezariusz.marek@comarch.pl>; pgsql-sql
----- Original Message -----
> From: Glyn Astill <glynastill@yahoo.co.uk>
> To: Cezariusz Marek <cezariusz.marek@comarch.pl>; "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
> Cc:
> Sent: Wednesday, 5 February 2014, 14:45
> Subject: Re: [SQL] IDENTIFY_SYSTEM
>
> ----- Original Message -----
>
>> From: Cezariusz Marek <cezariusz.marek@comarch.pl>
>> To: pgsql-sql@postgresql.org
>> Cc:
>> Sent: Wednesday, 5 February 2014, 12:01
>> Subject: Re: [SQL] IDENTIFY_SYSTEM
>>
>>> That's part of the streaming replication protocol
>>>
>>> http://www.postgresql.org/docs/9.3/static/protocol-replication.html
>>> As long as you're using wal_level >= archive and the
> replication
>> connection is enabled you can retrieve it via psql
>>
>> Yes, I know, but there is no way to get the systemid value from a function
> using
>> just SQL or plpgsql?
>>
>
> I don't think so no, but you may have better luck finding someone more
> knowledgable posting to pgsql-general. You could do it by calling
> pg_controldata via an untrusted procedural language, not so sure how happy
> I'd be with that myself. E.g. with plperlu:
>
> CREATE OR REPLACE FUNCTION get_system_identifier_unsafe(text)
> RETURNS text AS
> $BODY$
> my $rv;
> my $data;
> my $pg_controldata_bin = $_[0];
> my $sysid;
>
> $rv = spi_exec_query('SHOW data_directory', 1);
> $data = $rv->{rows}[0]->{data_directory};
>
> open(FD,"$pg_controldata_bin $data | ");
>
> while(<FD>) {
> if (/Database system identifier:/) {
> $sysid = $_;
> for ($sysid) {
> s/Database system identifier://;
> s/[^0-9]//g;
> }
> last;
> }
> }
> close (FD);
> return $sysid;
>
> $BODY$
> LANGUAGE plperlu;
>
>
So if I actually ran that:
test=# select get_system_identifier_unsafe('pg_controldata');
get_system_identifier_unsafe
------------------------------
5667443312440565226
>
>>> If it's just for licencing perhaps inet_server_addr() or a plperl
>> function to grab the mac address of the machine might suffice?
>>
>> I have to license each database, not just the whole machine. And the
> systemid is
>> the only unique database identifier I've found.
>
> Is it each database or each postgresql instance / cluster? How exactly do you
> want your licencing to work? There may be a better way.
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: IDENTIFY_SYSTEM
2014-02-03 08:35 IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 11:34 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 12:01 ` Re: IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 14:45 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 14:51 ` Re: IDENTIFY_SYSTEM Glyn Astill <glynastill@yahoo.co.uk>
@ 2014-02-05 16:15 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: bricklen @ 2014-02-05 16:15 UTC (permalink / raw)
To: Glyn Astill <glynastill@yahoo.co.uk>; +Cc: Cezariusz Marek <cezariusz.marek@comarch.pl>; pgsql-sql
On Wed, Feb 5, 2014 at 6:51 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
> >
> > I don't think so no, but you may have better luck finding someone more
> > knowledgable posting to pgsql-general. You could do it by calling
> > pg_controldata via an untrusted procedural language, not so sure how
> happy
> > I'd be with that myself. E.g. with plperlu:
> >
> > CREATE OR REPLACE FUNCTION get_system_identifier_unsafe(text)
> > RETURNS text AS
> > $BODY$
> > my $rv;
> > my $data;
> > my $pg_controldata_bin = $_[0];
> > my $sysid;
> >
> > $rv = spi_exec_query('SHOW data_directory', 1);
> > $data = $rv->{rows}[0]->{data_directory};
> >
> > open(FD,"$pg_controldata_bin $data | ");
> >
> > while(<FD>) {
> > if (/Database system identifier:/) {
> > $sysid = $_;
> > for ($sysid) {
> > s/Database system identifier://;
> > s/[^0-9]//g;
> > }
> > last;
> > }
> > }
> > close (FD);
> > return $sysid;
> >
> > $BODY$
> > LANGUAGE plperlu;
> >
> >
>
> So if I actually ran that:
>
> test=# select get_system_identifier_unsafe('pg_controldata');
> get_system_identifier_unsafe
> ------------------------------
> 5667443312440565226
>
Joe Conway wrote something a few years ago which could probably be brought
up to date and made into a Postgresql extension.
https://github.com/jconway/pg_controldata
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-02-05 16:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-02-03 08:35 IDENTIFY_SYSTEM Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 11:34 ` Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 12:01 ` Cezariusz Marek <cezariusz.marek@comarch.pl>
2014-02-05 14:45 ` Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 14:51 ` Glyn Astill <glynastill@yahoo.co.uk>
2014-02-05 16:15 ` bricklen <bricklen@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox