Streaming replication stacked.

Started by Yojiover 10 years ago16 messagesgeneral

Jump to latest

Yoji

y-tsukada@xseed.co.jp

over 10 years ago

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.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Andreas Kretschmer

akretschmer@spamfence.net

over 10 years ago

In reply to: Yoji (#1)

Re: Streaming replication stacked.

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

chiru r

chirupg@gmail.com

over 10 years ago

In reply to: Andreas Kretschmer (#2)

Re: Streaming replication stacked.

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:

Show quoted text

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

Yoji

y-tsukada@xseed.co.jp

over 10 years ago

In reply to: Andreas Kretschmer (#2)

Re: Streaming replication stacked.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yoji

y-tsukada@xseed.co.jp

over 10 years ago

In reply to: chiru r (#3)

Re: Streaming replication stacked.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Andreas Kretschmer

akretschmer@spamfence.net

over 10 years ago

In reply to: Yoji (#4)

Re: Streaming replication stacked.

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?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Michael Paquier

michael@paquier.xyz

over 10 years ago

In reply to: Yoji (#1)

Re: Streaming replication stacked.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yoji

y-tsukada@xseed.co.jp

over 10 years ago

In reply to: Andreas Kretschmer (#6)

Re: Streaming replication stacked.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yoji

y-tsukada@xseed.co.jp

over 10 years ago

In reply to: Michael Paquier (#7)

Re: Streaming replication stacked.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#10

Andreas Kretschmer

akretschmer@spamfence.net

over 10 years ago

In reply to: Yoji (#8)

Re: Streaming replication stacked.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#11

Andreas Kretschmer

akretschmer@spamfence.net

over 10 years ago

In reply to: Andreas Kretschmer (#10)

Re: Streaming replication stacked.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#12

Michael Paquier

michael@paquier.xyz

over 10 years ago

In reply to: Yoji (#9)

Re: Streaming replication stacked.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#13

Andreas Kretschmer

akretschmer@spamfence.net

over 10 years ago

In reply to: Michael Paquier (#12)

Re: Streaming replication stacked.

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)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#14

Yoji

y-tsukada@xseed.co.jp

over 10 years ago

In reply to: Andreas Kretschmer (#13)

Re: Streaming replication stacked.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#15

Michael Paquier

michael@paquier.xyz

over 10 years ago

In reply to: Yoji (#14)

Re: Streaming replication stacked.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#16

Yoji

y-tsukada@xseed.co.jp

over 10 years ago

In reply to: Michael Paquier (#15)

Re: Streaming replication stacked.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general