Re: Database system identifier via SELECT?

Is there any way to get the database system identifier via a select
statement? I have a primary/secondary async replication setup, and I'd
like be able to verify from the client side that the provided primary and
secondary connection strings do in fact refer to the same data set...

Chris Redekop wrote:
> Is there any way to get the database system identifier via a select
> statement? I have a primary/secondary async replication setup, and I'd
> like be able to verify from the client side that the provided primary and
> secondary connection strings do in fact refer to the same data set...

Wow, that is a reasonable thing to want available via SQL, but I can't
see a way to get to it.

The only method I can suggest is to write a server-side C function that
calls GetSystemIdentifier().

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 12/08/2011 12:57 PM, Bruce Momjian wrote:
>
> Chris Redekop wrote:
>> Is there any way to get the database system identifier via a select
>> statement? I have a primary/secondary async replication setup, and I'd
>> like be able to verify from the client side that the provided primary and
>> secondary connection strings do in fact refer to the same data set...
>
> Wow, that is a reasonable thing to want available via SQL, but I can't
> see a way to get to it.
>
> The only method I can suggest is to write a server-side C function that
> calls GetSystemIdentifier().

This seems like something we should have in core, don't you think?

JD

>

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Bruce Momjian
Sent: Thursday, December 08, 2011 3:57 PM
To: Chris Redekop
Cc: pgsql-general
Subject: Re: [GENERAL] Database system identifier via SELECT?

Chris Redekop wrote:
> Is there any way to get the database system identifier via a select
> statement? I have a primary/secondary async replication setup, and
> I'd like be able to verify from the client side that the provided
> primary and secondary connection strings do in fact refer to the same data
set...

Wow, that is a reasonable thing to want available via SQL, but I can't see a
way to get to it.

The only method I can suggest is to write a server-side C function that
calls GetSystemIdentifier().

---------------------------------------------------

Maybe I mis-understand the question but how about:

SHOW listen_addresses; (of the functional equivalent)

You just need to make sure each server is listening to a specific IP and/or
Port and just tag off of that.

David J.

Joshua D. Drake wrote:
>
> On 12/08/2011 12:57 PM, Bruce Momjian wrote:
> >
> > Chris Redekop wrote:
> >> Is there any way to get the database system identifier via a select
> >> statement? I have a primary/secondary async replication setup, and I'd
> >> like be able to verify from the client side that the provided primary and
> >> secondary connection strings do in fact refer to the same data set...
> >
> > Wow, that is a reasonable thing to want available via SQL, but I can't
> > see a way to get to it.
> >
> > The only method I can suggest is to write a server-side C function that
> > calls GetSystemIdentifier().
>
> This seems like something we should have in core, don't you think?

Yeah, kind of, except this is the first request we ever got for this.
The identifier is passed as part of streaming replication, so maybe it
will be needed more in the future.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Thu, Dec 8, 2011 at 4:27 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> Joshua D. Drake wrote:
> >
> > On 12/08/2011 12:57 PM, Bruce Momjian wrote:
> > >
> > > Chris Redekop wrote:
> > >> Is there any way to get the database system identifier via a select
> > >> statement? I have a primary/secondary async replication setup, and
> I'd
> > >> like be able to verify from the client side that the provided primary
> and
> > >> secondary connection strings do in fact refer to the same data set...
> > >
> > > Wow, that is a reasonable thing to want available via SQL, but I can't
> > > see a way to get to it.
> > >
> > > The only method I can suggest is to write a server-side C function that
> > > calls GetSystemIdentifier().
>

select inet_server_addr()?

--Scott

> >
> > This seems like something we should have in core, don't you think?
>
> Yeah, kind of, except this is the first request we ever got for this.
> The identifier is passed as part of streaming replication, so maybe it
> will be needed more in the future.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

You can get the database system identifier from the OS shell as part of the
control data:
pg_controldata /Library/PostgreSQL/9.1/data

Here, '/Library/PostgreSQL/9.1/data' is my data directory on os x; replace
it with your own data directory.
>From there, you can isolate the database system identifier with grep:
pg_controldata /Library/PostgreSQL/9.1/data | grep "system identifier"

This is not the same as calling a function within a SELECT statement, but
using the shell command above, one could easily write a function that
returns the database system identifier as a string in a SQL query.

I hope this solves the problem.

On Thu, Dec 8, 2011 at 4:57 PM, Scott Mead <scottm(at)openscg(dot)com> wrote:

>
> On Thu, Dec 8, 2011 at 4:27 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>> Joshua D. Drake wrote:
>> >
>> > On 12/08/2011 12:57 PM, Bruce Momjian wrote:
>> > >
>> > > Chris Redekop wrote:
>> > >> Is there any way to get the database system identifier via a select
>> > >> statement? I have a primary/secondary async replication setup, and
>> I'd
>> > >> like be able to verify from the client side that the provided
>> primary and
>> > >> secondary connection strings do in fact refer to the same data set...
>> > >
>> > > Wow, that is a reasonable thing to want available via SQL, but I can't
>> > > see a way to get to it.
>> > >
>> > > The only method I can suggest is to write a server-side C function
>> that
>> > > calls GetSystemIdentifier().
>>
>
> select inet_server_addr()?
>
> --Scott
>
>
>
>
>> >
>> > This seems like something we should have in core, don't you think?
>>
>> Yeah, kind of, except this is the first request we ever got for this.
>> The identifier is passed as part of streaming replication, so maybe it
>> will be needed more in the future.
>>
>> --
>> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>> EnterpriseDB http://enterprisedb.com
>>
>> + It's impossible for everything to be true. +
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>

Yeah, it would be easy enough to write a custom extension to do it. I was
hoping for something built-in so I wouldn't require a pre-req extension be
installed on all servers by the superuser....sysadmins tend to resist
making such changes. But oh well what you gotta do you gotta do....thanks
guys.

FYI this isn't the first time it's been asked for...
http://archives.postgresql.org/pgsql-sql/2007-07/msg00045.php ...first time
in 4 years tho :P

On Thu, Dec 8, 2011 at 3:09 PM, Safari Code <safaricode(at)gmail(dot)com> wrote:

> You can get the database system identifier from the OS shell as part of
> the control data:
> pg_controldata /Library/PostgreSQL/9.1/data
>
> Here, '/Library/PostgreSQL/9.1/data' is my data directory on os x; replace
> it with your own data directory.
> From there, you can isolate the database system identifier with grep:
> pg_controldata /Library/PostgreSQL/9.1/data | grep "system identifier"
>
> This is not the same as calling a function within a SELECT statement, but
> using the shell command above, one could easily write a function that
> returns the database system identifier as a string in a SQL query.
>
> I hope this solves the problem.
>
> On Thu, Dec 8, 2011 at 4:57 PM, Scott Mead <scottm(at)openscg(dot)com> wrote:
>
>>
>> On Thu, Dec 8, 2011 at 4:27 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>
>>> Joshua D. Drake wrote:
>>> >
>>> > On 12/08/2011 12:57 PM, Bruce Momjian wrote:
>>> > >
>>> > > Chris Redekop wrote:
>>> > >> Is there any way to get the database system identifier via a select
>>> > >> statement? I have a primary/secondary async replication setup, and
>>> I'd
>>> > >> like be able to verify from the client side that the provided
>>> primary and
>>> > >> secondary connection strings do in fact refer to the same data
>>> set...
>>> > >
>>> > > Wow, that is a reasonable thing to want available via SQL, but I
>>> can't
>>> > > see a way to get to it.
>>> > >
>>> > > The only method I can suggest is to write a server-side C function
>>> that
>>> > > calls GetSystemIdentifier().
>>>
>>
>> select inet_server_addr()?
>>
>> --Scott
>>
>>
>>
>>
>>> >
>>> > This seems like something we should have in core, don't you think?
>>>
>>> Yeah, kind of, except this is the first request we ever got for this.
>>> The identifier is passed as part of streaming replication, so maybe it
>>> will be needed more in the future.
>>>
>>> --
>>> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>>> EnterpriseDB http://enterprisedb.com
>>>
>>> + It's impossible for everything to be true. +
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>>
>