question on streaming replication

Hi Atul,

Please do not cross-post over mailing lists.

As per your problem:

on a streaming replication setup, all changes applied to master are propagated to standby(s).

If standby is stopped or cannot temporary reach master, then it will pick up changes when started or when can reach master again, given that the initial state was in sync with the master.

What stated here above is always true, but there are cases in which too many changes are pushed to master and standby is not able to pick them up.

In order for standby to pick the missing data, is fundamental that the WAL files containing the changes are available to standby or in alternative wal_keep_segments is 'large enough'.

A good starting point to debug the situation are the logfiles of standby server, together with pg_stat_replication table.

Also handy to run on master:

SELECT pg_current_xlog_location() and on standby: select pg_last_xlog_receive_location() to understand if it is picking up. Documentation is always a good starting point to understand what is going on. If you did not already, have a look here: https://www.postgresql.org/docs/9.6/static/runtime-config-replication.html Hope it helps, Fabio

On Thu, Jun 14, 2018 at 10:58 AM, Atul Kumar <akumar14871@gmail.com> wrote:

Hi,

I have postgres edb 9.6 version, i have below query to solve it out.

This is not the right place to ask queries on edb versions. You need
to check with your vendor about the right place to ask questions.
Here, you can ask the questions about PostgreSQL.

i have configured streaming replication having master and slave node
on same server just to test it.

All worked fine but when i made slave service stop, and create some
test databases in master, after then i made slave service start, slave
didn't pick the changes.

I think you need to ensure that replica is connected to master server
and then probably check logs to confirm what exactly happened.

The replication was on async state.

Then after doing some search on google i tried to make it sync state
but even making changes in postgresql.conf file I am neither getting
sync state nor getting any changes on slave server.

After making changes in postgresql.conf, you might need to use
pg_reload_conf or restart the server depending on the setting you
have changed to make that setting effective.

Please suggest the needful.

As mentioned above, I suggest asking only PostgreSQL related questions
on these mailing lists.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On 14 June 2018 07:28:53 CEST, Atul Kumar <akumar14871@gmail.com> wrote:

Hi,

I have postgres edb 9.6 version, i have below query to solve it out.

i have configured streaming replication having master and slave node
on same server just to test it.

All worked fine but when i made slave service stop, and create some
test databases in master, after then i made slave service start, slave
didn't pick the changes.

The replication was on async state.

Then after doing some search on google i tried to make it sync state
but even making changes in postgresql.conf file I am neither getting
sync state nor getting any changes on slave server.

Please suggest the needful.

Regards,
Atul

Sync replication isn't usefull with only one standby.

I think, during the stop of the standby the master has overwitten needed wal's. You can prevent that by increasing wal_keep_segments or by using replication slots. Please use google, there are tons of docs about that all.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company