Re: better architecture? - Mailing list pgsql-general

On 11/20/14, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 11/20/2014 08:00 AM, zach cruise wrote:
>> combining replies for the list:
>>
>>
>> On 11/19/14, Charles Zaffery <charlesz@focusschoolsoftware.com> wrote:
>>> 2 and 3 can be covered by this:
>>> http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster
>> does something similar exist for windows?
>>
>>
>> On 11/20/14, Michael Paquier <michael.paquier@gmail.com> wrote:
>>> On Thu, Nov 20, 2014 at 10:58 AM, zach cruise <zachc1980@gmail.com>
>>> wrote:
>>>> 2. what happens if master-slave are rebooted at different times?
>>> What do you mean by that? If replication is impacted?
>> eg if i were to reboot the vmware running the master in the evening,
>> and reboot the vmware running the slave in the night, how would they
>> sync up?
>
> Well it would depend on your setup and the load on the master. Assuming
> streaming replication. Simple explanation:
yes streaming replication.
>
> 1) If the master is down and slave is up then the slave will stall at
> whatever the last WAL was sent. When the master comes back up it will
> catch up as new WALs are generated.
>
> 2) If the slave is down and the master is up, the master will keep on
> creating WALs. The issue is that WALs are recycled over time, so given a
> significant load on the master and extended downtime for the slave it is
> possible that when the slave comes back up a WAL it needs is no longer
> available and it will start throwing errors. One way to tune this is
> modify wal_keep_segments (integer):
>
> http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html
while the WAL archive directory has to be shared with both master and
slave, should the WAL archive directory be independent of them ie
should it not go down with either of them? if it has to go down with
one, it seems it'd best for the WAL archive directory to go down with
slave?


>> On 11/20/14, Adrian Klaver wrote:
>>> What replication method are you using?
>>> The built in methods, Slony. Bucardo, etc?
>> built in
>>
>>> The production users cannot enter or update records?
>> they can't. slave is read-only.
>>
>>> If you have replication set up master -> slave, how can there be a
>>> difference between the two?
>> there isn't. both contain dev and prod databases. users connect to the
>> dev databases from the dev web server, and to the prod databases from
>> the prod web server.
>
> Crossed wires on my part, I was reading databases and thinking database
> clusters.
>
>>
>>> Not sure where the mssql databases into this?
>> our corporate partners use them. when i need to query against them, i
>> import.
>>
>>>> 2. what happens if master-slave are rebooted at different times?
>>>>
>>>> 3. i also need to auto-promote slave to master if master fails (without
>>>> using repmgr or postgres-r or even postgres-xl). how?
>>> Answers for 2 & 3 are dependent on answers to the above questions.
>
> For failover see:
>
> http://www.postgresql.org/docs/9.3/interactive/warm-standby-failover.html
>
> "PostgreSQL does not provide the system software required to identify a
> failure on the primary and notify the standby database server. Many such
> tools exist and are well integrated with the operating system facilities
> required for successful failover, such as IP address migration."
>
> So if you are looking for auto-promote you will need to look at third
> party tools or writing your own script.
while i can always use "pg_ctl promote", any recommendations for windows?