Re: IDENTIFY_SYSTEM

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

> From: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
> To: Cezariusz Marek <cezariusz(dot)marek(at)comarch(dot)pl>; "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
> Cc:
> Sent: Wednesday, 5 February 2014, 14:45
> Subject: Re: [SQL] IDENTIFY_SYSTEM
>
> ----- Original Message -----
>
>> From: Cezariusz Marek <cezariusz(dot)marek(at)comarch(dot)pl>
>> To: pgsql-sql(at)postgresql(dot)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.
>