Thread: question on streaming replication
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
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 masteragain, 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 isnot 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 tostandby 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 14/06/18 07:28, Atul Kumar 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 >
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 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_segmentsor by using replication slots. Please use google, there are tons of docs about that all. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company
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_segmentsor by using replication slots. Please use google, there are tons of docs about that all. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company