Thread: Log Shipping
It is my understanding that if PostgeSQL has log shipping enabled, if for whatever reason it cannot ship the file the master server will hold it. But for how long?
Secondly, I have 2 servers I ship log files to using the following script:
#!/usr/local/bin/bash
# Slave 1
rsync -a $1 pgi@192.168.1.105:archive/$2 < /dev/null;
# Slave 2
rsync -a $1 pg@192.168.1.93:archive/$2 < /dev/null;
In this case if Slave 1 is up but Slave 2 is down. It will ship the log file to Slave 1 but not Slave 2 and move one. Thereby Slave 2 will now be out of sync, correct?
To allow both slaves to remain in sync when one is restarted I would need to modify my script to return an error or false to PostgreSQL, this way it will hold the WAL files until both Slaves are online. Correct?
Thanks,
-Joseph
Joseph Kregloh wrote: > It is my understanding that if PostgeSQL has log shipping enabled, if for > whatever reason it cannot ship the file the master server will hold it. But > for how long? Forever (which means it dies because of running out of space in the partition containing pg_xlog). > Secondly, I have 2 servers I ship log files to using the following script: > > #!/usr/local/bin/bash > > # Slave 1 > rsync -a $1 pgi@192.168.1.105:archive/$2 < /dev/null; > > # Slave 2 > rsync -a $1 pg@192.168.1.93:archive/$2 < /dev/null; > > In this case if Slave 1 is up but Slave 2 is down. It will ship the log > file to Slave 1 but not Slave 2 and move one. Thereby Slave 2 will now be > out of sync, correct? You could cause the script to return failure if either of these copies fail, and return success if once both replicas have the file (considering that one replica might already have the file from a previous run of your script); that way, the master will retain the file until both replicas have it, and remove the file once both replicas have it. Of course, you want to avoid copying the file again to the replica that already had the file, without getting confused by a partially written file. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, May 31, 2016 at 4:12 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Joseph Kregloh wrote:
> It is my understanding that if PostgeSQL has log shipping enabled, if for
> whatever reason it cannot ship the file the master server will hold it. But
> for how long?
Forever (which means it dies because of running out of space in the
partition containing pg_xlog).
> Secondly, I have 2 servers I ship log files to using the following script:
>
> #!/usr/local/bin/bash
>
> # Slave 1
> rsync -a $1 pgi@192.168.1.105:archive/$2 < /dev/null;
>
> # Slave 2
> rsync -a $1 pg@192.168.1.93:archive/$2 < /dev/null;
>
> In this case if Slave 1 is up but Slave 2 is down. It will ship the log
> file to Slave 1 but not Slave 2 and move one. Thereby Slave 2 will now be
> out of sync, correct?
You could cause the script to return failure if either of these copies
fail, and return success if once both replicas have the file
(considering that one replica might already have the file from a
previous run of your script); that way, the master will retain the file
until both replicas have it, and remove the file once both replicas have
it. Of course, you want to avoid copying the file again to the replica
that already had the file, without getting confused by a partially
written file.
Excellent, thanks for the reply.
-Joseph
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services