Thread: Streaming replication stacked.
Hi All, I use streaming replication on systems. But I have an problem. I need your support. The system sometimes stacks Postgres process. So, postgresql data isn't syncing. I checkd process that it was stacked "waiting" status on slave server. e.g.) postgres: startup process recovering 0000000100000xxxxxxxxxx waiting And I think Master Server completed to transfer wal files but Slave server couldn't read wal files. In addition once about 10 hours later , suddenly waiting process started . Below is the replication status. ----------------------------------------- state | streaming sent_location | A3C/10D51570 write_location | A3C/10D51570 flush_location | A3C/10D51570 replay_location | A3C/10D4A4B8 sync_priority | 0 sync_state | async ----------------------------------------- Replay_location was stopping and Other location was active. And There is System Version. ----------------------------------------- - PostgreSQL Version : 9.4 - OS : CentOS6.4 - Replicaion Type : async ----------------------------------------- And Below is the Postgres configration. ----------------------------------------- ++ Master : wal_level = hot_standby wal_buffers = 16MB checkpoint_segments = 32 checkpoint_completion_target = 0.7 archive_mode = on archive_command = 'cp %p /var/lib/pgsql/9.4/data/archive_log/%f' max_wal_senders = 3 wal_keep_segments = 32 wal_sender_timeout = 20s hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 wal_receiver_status_interval = 5s hot_standby_feedback = on ++ Slave : wal_level = hot_standby wal_buffers = 16MB checkpoint_segments = 32 checkpoint_completion_target = 0.7 archive_mode = on archive_command = 'cp %p /var/lib/pgsql/9.4/data/archive_log/%f' max_wal_senders = 3 wal_keep_segments = 32 wal_sender_timeout = 20s hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 wal_receiver_status_interval = 5s hot_standby_feedback = on ----------------------------------------- Do you have any solution? Thanks in advance for any help. Yoji Tsukada.
y-tsukada@xseed.co.jp <y-tsukada@xseed.co.jp> wrote: > ----------------------------------------- > > Do you have any solution? please show us your recovery.conf. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Hi Yoji,
Please check is there any network issues ,between master and slave server and also share the slave server pg log output.
-Are you shipping wals to standby server using any script other than archive_command which is mentioned master config?.
On Mon, Jan 4, 2016 at 5:58 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
y-tsukada@xseed.co.jp <y-tsukada@xseed.co.jp> wrote:
> -----------------------------------------
>
> Do you have any solution?
please show us your recovery.conf.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi, Andreas Thank you for reply. Below is the recovery.conf. ---------------------------------------------- standby_mode = on primary_conninfo = 'host=172.16.xxx.xxx user=xxxxxx' trigger_file = '/tmp/trigger_file0' ---------------------------------------------- Best regards. Yoji -- View this message in context: http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880287.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi Chiru , Thank you for your information. I send wals to standby server using "wal sender" processes. And I had checked logs but It was nothing. Best regards. Yoji -- View this message in context: http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880288.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Yoji <y-tsukada@xseed.co.jp> wrote: > Hi, Andreas > > Thank you for reply. > > Below is the recovery.conf. > > ---------------------------------------------- > standby_mode = on > primary_conninfo = 'host=172.16.xxx.xxx user=xxxxxx' > trigger_file = '/tmp/trigger_file0' > ---------------------------------------------- Seems okay for me, can't find any errors. (neihter here nor in the other config you shown) (you have only 1 slave, right?) Do you have long running transactions on the slave? What happens if you restart the slave? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Mon, Jan 4, 2016 at 9:08 PM, <y-tsukada@xseed.co.jp> wrote: > And There is System Version. > ----------------------------------------- > - PostgreSQL Version : 9.4 > - OS : CentOS6.4 > - Replicaion Type : async > ----------------------------------------- You are using a version of 9.4 older than 9.4.5, right? Like 9.4.4? -- Michael
Hi, Andreas Thank you for replying. You're right, I have only 1 slave. And I need running transactions on slave. Once I restarted postgres service on slave and then process began to move. Best regards. Yoji -- View this message in context: http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880326.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi Michael, Thank you for replying. I'm using version of 9.4.3. And I have seen same behavior on version of 9.3.4. Best regards. Yoji -- View this message in context: http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880329.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Yoji <y-tsukada@xseed.co.jp> wrote: > Hi, Andreas > > Thank you for replying. > > You're right, I have only 1 slave. > > And I need running transactions on slave. > Once I restarted postgres service on slave and then process began to move. ok, i think it's clear now: because of running transactions on the standby (and hot_standby_feedback on) the master has to wait for the slave and can't replay all from the master. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer <akretschmer@spamfence.net> wrote: > ok, i think it's clear now: because of running transactions on the > standby (and hot_standby_feedback on) the master has to wait for the > slave and can't replay all from the master. Mhh. Maybe i'm wrong, can't reproduce that. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Tue, Jan 5, 2016 at 5:13 PM, Yoji <y-tsukada@xseed.co.jp> wrote: > I'm using version of 9.4.3. You should update to 9.4.5 as soon as possible. > And I have seen same behavior on version of 9.3.4. As Andreas has already outlined, as hot_standby_feedback is enabled the master has to wait for the slave and the slave cannot replay from the master as transactions are running on the hot standby. -- Michael
Michael Paquier <michael.paquier@gmail.com> wrote: > As Andreas has already outlined, as hot_standby_feedback is enabled > the master has to wait for the slave and the slave cannot replay from > the master as transactions are running on the hot standby. Oh, thanks for the confirmation ;-) (wasn't really sure) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Hi Andreas and Michael, Thank you for your information! Let me know, which should I choose update to 9.4.5 or hot_standby_feedback off? Best regards. Yoji -- View this message in context: http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880487.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Jan 6, 2016 at 9:27 AM, Yoji <y-tsukada@xseed.co.jp> wrote: > Hi Andreas and Michael, > > Thank you for your information! > > Let me know, which should I choose update to 9.4.5 Updating to 9.4.5 is highly recommended, and a separate issue. > or hot_standby_feedback off? Switching hot_standby_feedback to off depends on if you can afford the fact that the application querying the standby may fail because of recovery conflicts. There is no magic solution here. -- Michael
Hi Michael , Thank you for your information!! I understand. I'll consider to upgrade to 9.4.5. I'm grateful for all your support. Yoji -- View this message in context: http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880550.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.