Обсуждение: IDENTIFY_SYSTEM
<div class="WordSection1"><p class="MsoNormal"><span style="color:#1F497D">Hello,</span><p class="MsoNormal"><span style="color:#1F497D"> </span><pclass="MsoNormal"><span style="color:#1F497D">Is <span class="SpellE">there</span> a <spanclass="SpellE">way</span> to <span class="SpellE">call</span> IDENTIFY_SYSTEM <span class="SpellE">command</span> fromSQL? Or <span class="SpellE">otherwise</span> <span class="SpellE">get</span> the <span class="SpellE">unique</span>system <span class="SpellE">identifier</span> from a <span class="SpellE">function</span>? I<span class="SpellE">need</span> <span class="SpellE">some</span> <span class="SpellE">unique</span> <span class="SpellE">database</span><span class="SpellE">indentifier</span> for the <span class="SpellE">licensing</span> <spanclass="SpellE">purposes</span>.</span><p class="MsoNormal"><span style="color:#1F497D"> </span><p class="MsoNormal"><spanstyle="mso-fareast-font-family:"Times New Roman";color:#A6A6A6;mso-fareast-language:PL;mso-no-proof:yes">--</span><p class="MsoNormal"><span style="mso-fareast-font-family:"TimesNew Roman";color:#A6A6A6;mso-fareast-language:PL;mso-no-proof:yes">Cezariusz Marek</span><pclass="MsoNormal"><span style="mso-fareast-font-family:"Times New Roman";color:#A6A6A6;mso-fareast-language:PL;mso-no-proof:yes">Mobile+48 608 646 494, Phone +48 33 484 6900, </span><spanstyle="mso-fareast-font-family:"Times New Roman";color:#95B3D7;mso-fareast-language:PL;mso-no-proof:yes"><a href="http://www.comarch.com/"><spanstyle="color:#95B3D7">http://www.comarch.com/</span></a></span><p class="MsoNormal"><spanstyle="mso-fareast-font-family:"Times New Roman";color:#A6A6A6;mso-fareast-language:PL;mso-no-proof:yes">ComarchSA, ul. Michałowicza 12, 43-300 Bielsko-Biała, POLAND</span><pclass="MsoNormal"> </div>
____________________________ > 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 > > >
> 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 mightsuffice? I have to license each database, not just the whole machine. And the systemid is the only unique database identifier I'vefound. -- 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
----- 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 coulddo 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 bea better way.
----- 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. >
On Wed, Feb 5, 2014 at 6:51 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
So if I actually ran that:>
> 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;
>
>
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