Re: replication topography

Ouch. I'm sure others will have ideas to add but here are a few to start
with. I would start by cataloging the instances. I hope you at least have a
list of the servers - if not you may need to do network scans with nmap and
even then may not locate all of them.

On each instance I'd use some combination of the following to get the info
you want (as user postgres for many of them or some rows will be hidden or
tables prohibited):

Version:
select version()

Databases:
select * from pg_database;

Extensions (may help determine if Slony or similar is installed):
select * from pg_extension ;

Settings. Choose which ones you want but hba and config file locations as
well as listen address and port would be good. Others like
"archive_command" and "archive_mode" may shed light on whether or not
WAL-shipping is in use.
select * from pg_settings;

Replication status (to see if and where server is sending streaming
replication data):
select * from pg_stat_replication ;

You may need to investigate whether pg_bouncer or other pooling is in use.

Good luck,
Steve

On Tue, Feb 26, 2019 at 2:41 PM Julie Nishimura <juliezain(at)hotmail(dot)com>
wrote:

> Hello everybody,
> I've inherited big complicated postgresql kingdon with multiple servers in
> it, some of them are replicated, some of them are not, and there is Bunch
> of them.
>
> What would you be your suggestion, how should I start my investigation
> what is master, what is replicas, is there any slony/streaming replication.
> etc?
>
> Some of them stand alone instances, but some are mirrored. And of course,
> developers want to know answers NOW:)
>
> The versions are mainly 8.3 and 9.4. So, pg_is_in_recovery shows either
> function doe not exist (on 8.3) or "f" for 9.4 (which can mean it is
> master, right)?
>
> Any advises are greatly appreciated.
>
> Thank you,
> Julie
>