Thread: replication topography
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
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.
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.
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):
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@hotmail.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
On 2/26/19 2:40 PM, Julie Nishimura 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? In addition to what Steve posted: 1) Do a search on the machines for the file PG_VERSION. That will locate the the PGDATA directory for each instance. 2) Are you dealing with the same OS across the servers? Can you use the package manager to ferret out the Postgres servers? > > Some of them stand alone instances, but some are mirrored. And of > course, developers want to know answers NOW:) Well either: 1) They don't how things where setup in the past and still managed to get development done, so the need to know NOW is just panicking. 2) They do know some or all of the layout and can contribute to the solution. > > 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 -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Feb 27, 2019 at 12:59 PM Julie Nishimura <juliezain@hotmail.com> wrote:
Hello Steve,Thanks a lot for your info yesterday, it was very useful. If I run this command on some of the servers and the results look like this, what would it tell you?select * from pg_extension ;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
postgres_fdw | 10 | 2200 | t | 1.0 | |postgres=# select * from pg_extension ;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
(1 row)I understood it is slony, the rest I need to find from hba file?
I am not experienced with Slony - others on this list will be better able to help there. Somehow I was thinking it created an extension but I don't think that's the case. You may find some info in the monitoring section of the slony docs (http://www.slony.info/documentation/1.2/monitoring.html), in particular looking for the existence of sl_* tables/views.
The postgres_fdw is the Foreign Data Wrapper extension (https://www.postgresql.org/docs/current/postgres-fdw.html) and plpgsql is the PL/pgSQL procedural language (https://www.postgresql.org/docs/current/plpgsql.html).
(Be sure to reply-all so others on the list can help and future viewers can find solutions.)
Cheers,
Steve