Обсуждение: 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.
--
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
____________________________ > 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