Re: [GENERAL] Streaming replication stacked.

[Yoji]

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

Re: [GENERAL] Streaming replication stacked.

[Yoji]

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

Re: [GENERAL] Streaming replication stacked.

[Andreas Kretschmer]

Yoji  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°


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

Re: [GENERAL] Streaming replication stacked.

[Andreas Kretschmer]

Andreas Kretschmer  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°


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

Re: [GENERAL] Streaming replication stacked.

[Yoji]

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

Re: [GENERAL] Streaming replication stacked.

[Yoji]

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

Re: [GENERAL] Streaming replication stacked.

[Michael Paquier]

On Tue, Jan 5, 2016 at 5:13 PM, Yoji  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

Re: [GENERAL] Streaming replication stacked.

[Andreas Kretschmer]

Michael Paquier  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°


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

Re: [GENERAL] Streaming replication stacked.

[Michael Paquier]

On Wed, Jan 6, 2016 at 9:27 AM, Yoji  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

[GENERAL] Streaming replication stacked.

[y-tsukada]

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 00010xx 
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.


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

Re: [GENERAL] Streaming replication stacked.

[Yoji]

Hi, Andreas 

Thank you for reply.

Below is the recovery.conf.

--
standby_mode = on
primary_conninfo = 'host=172.16.xxx.xxx user=xx'
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

Re: [GENERAL] Streaming replication stacked.

[Yoji]

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

Re: [GENERAL] Streaming replication stacked.

[chiru r]

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 <
akretsch...@spamfence.net> wrote:

> y-tsuk...@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
>

Re: [GENERAL] Streaming replication stacked.

[Andreas Kretschmer]

y-tsuk...@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

Re: [GENERAL] Streaming replication stacked.

[Michael Paquier]

On Mon, Jan 4, 2016 at 9:08 PM,   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

Re: [GENERAL] Streaming replication stacked.

[Andreas Kretschmer]

Yoji  wrote:

> Hi, Andreas 
> 
> Thank you for reply.
> 
> Below is the recovery.conf.
> 
> --
> standby_mode = on
> primary_conninfo = 'host=172.16.xxx.xxx user=xx'
> 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°


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