Re: [GENERAL] dropdb breaks replication?
Em 31/10/2012 20:47, Greg Williamson escreveu: Edson -- I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? Sorry for the slow response -- as others have indicated, the drop db is probably not the problem. We have one system that drops a several-gig database hourly and the replication has never failed. We see issues on the master with dead file handles but the replication itself is rock solid. Greg Our application should (almost) never delete databases, but just in case I'll keep an eye open, and manually sync the replication if needed. It is not a major issue, was more a matter of curiosity. Also, John pointed that xlog in PostgreSQL is not the same as the concept I had from Oracle days. Thanks, Greg (and everyone). Edson -- 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] dropdb breaks replication?
Edson -- >I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. >They are replicated asynchronously. > >Yesterday, I've dropped a database of 20Gb, and then replication has broken, >requiring me to manually synchronize both servers again. > >It is expected that dropdb (or, perhaps, createdb) break existing replication >between servers? > Sorry for the slow response -- as others have indicated, the drop db is probably not the problem. We have one system that drops a several-gig database hourly and the replication has never failed. We see issues on the master with dead file handles but the replication itself is rock solid. Greg -- 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] dropdb breaks replication?
On 10/31/12 11:34 AM, Edson Richter wrote: Sorry for the portguese text. Yes, your assumption is correct: WAL segment has been excluded before being able to replicate. I keep 80 WAL segments, but I was wondering if a drop database is being logged: it's just so fast, I thought it wasn't logged. And what is the purpose to log (and replicate) the database drop, if you will not be able to recover it - IMHO, dropdb should be replicated as "database deactivation" or something more or like that... WAL is not a 'redo' log like Oracle uses. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] dropdb breaks replication?
Em 31/10/2012 16:34, Tom Lane escreveu: Lonni J Friedman writes: On Wed, Oct 31, 2012 at 11:01 AM, Edson Richter wrote: May the cause not having enough segments (currently 80) for dropdb command? Is dropdb logged in transaction log page-by-page excluded? I can't read portugese(?), but i think the gist of the error is that the WAL segment was already removed before the slave could consume it. I'm guessing that you aren't keeping enough of them, and dropping the database generated a huge volume which flushed out the old ones before they could get consumed by your slave. dropdb generates one, not very large, WAL record saying "go rm -rf this directory". So sheer WAL volume is not the correct explanation. It's possible though that the slave spent long enough executing the rm -rf to fall behind the master. Your assumption is right: the slave server is a slow mono processor, low memory, cloud computer, and would have taken very long time to delete everything. In any case, it should have been able to catch up automatically if WAL archiving was configured properly. I don't use WAL archiving - both servers are miles away from each other, and don't have anything except PostgreSQL async replication over VPN connecting them. Edson regards, tom lane -- 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] dropdb breaks replication?
Em 31/10/2012 16:09, Lonni J Friedman escreveu: On Wed, Oct 31, 2012 at 11:01 AM, Edson Richter wrote: Em 31/10/2012 15:39, Lonni J Friedman escreveu: On Wed, Oct 31, 2012 at 10:32 AM, Edson Richter wrote: I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? How did you determine that replication was broken, and how did you manually synchronize the servers? Are you certain that replication was working prior to dropping the database? I'm sure replication was running. I usually keep two windows open in both servers, running In master: watch -n 2 "ps aux | egrep sender" In slave: watch -n 2 "ps aux | egrep receiver" At the point the dropdb command has been executed, both disappeared from my "radar". Also, in the log there is the following error: LOG: replicação em fluxo conectou-se com sucesso ao servidor principal FATAL: não pôde receber dados do fluxo do WAL: FATAL: segmento do WAL solicitado 0001000100BE já foi removido May the cause not having enough segments (currently 80) for dropdb command? Is dropdb logged in transaction log page-by-page excluded? I can't read portugese(?), but i think the gist of the error is that the WAL segment was already removed before the slave could consume it. I'm guessing that you aren't keeping enough of them, and dropping the database generated a huge volume which flushed out the old ones before they could get consumed by your slave. Sorry for the portguese text. Yes, your assumption is correct: WAL segment has been excluded before being able to replicate. I keep 80 WAL segments, but I was wondering if a drop database is being logged: it's just so fast, I thought it wasn't logged. And what is the purpose to log (and replicate) the database drop, if you will not be able to recover it - IMHO, dropdb should be replicated as "database deactivation" or something more or like that... Edson -- 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] dropdb breaks replication?
Lonni J Friedman writes: > On Wed, Oct 31, 2012 at 11:01 AM, Edson Richter > wrote: >> May the cause not having enough segments (currently 80) for dropdb command? >> Is dropdb logged in transaction log page-by-page excluded? > I can't read portugese(?), but i think the gist of the error is that > the WAL segment was already removed before the slave could consume it. > I'm guessing that you aren't keeping enough of them, and dropping the > database generated a huge volume which flushed out the old ones before > they could get consumed by your slave. dropdb generates one, not very large, WAL record saying "go rm -rf this directory". So sheer WAL volume is not the correct explanation. It's possible though that the slave spent long enough executing the rm -rf to fall behind the master. In any case, it should have been able to catch up automatically if WAL archiving was configured properly. regards, tom lane -- 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] dropdb breaks replication?
On Wed, Oct 31, 2012 at 11:01 AM, Edson Richter wrote: > Em 31/10/2012 15:39, Lonni J Friedman escreveu: >> >> On Wed, Oct 31, 2012 at 10:32 AM, Edson Richter >> wrote: >>> >>> I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. >>> They are replicated asynchronously. >>> >>> Yesterday, I've dropped a database of 20Gb, and then replication has >>> broken, >>> requiring me to manually synchronize both servers again. >>> >>> It is expected that dropdb (or, perhaps, createdb) break existing >>> replication between servers? >> >> How did you determine that replication was broken, and how did you >> manually synchronize the servers? Are you certain that replication >> was working prior to dropping the database? >> >> > I'm sure replication was running. > I usually keep two windows open in both servers, running > > In master: > > watch -n 2 "ps aux | egrep sender" > > In slave: > > watch -n 2 "ps aux | egrep receiver" > > > At the point the dropdb command has been executed, both disappeared from my > "radar". > Also, in the log there is the following error: > > LOG: replicação em fluxo conectou-se com sucesso ao servidor principal > FATAL: não pôde receber dados do fluxo do WAL: FATAL: segmento do WAL > solicitado 0001000100BE já foi removido > > > May the cause not having enough segments (currently 80) for dropdb command? > Is dropdb logged in transaction log page-by-page excluded? I can't read portugese(?), but i think the gist of the error is that the WAL segment was already removed before the slave could consume it. I'm guessing that you aren't keeping enough of them, and dropping the database generated a huge volume which flushed out the old ones before they could get consumed by your slave. -- 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] dropdb breaks replication?
Em 31/10/2012 15:39, Lonni J Friedman escreveu: On Wed, Oct 31, 2012 at 10:32 AM, Edson Richter wrote: I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? How did you determine that replication was broken, and how did you manually synchronize the servers? Are you certain that replication was working prior to dropping the database? I'm sure replication was running. I usually keep two windows open in both servers, running In master: watch -n 2 "ps aux | egrep sender" In slave: watch -n 2 "ps aux | egrep receiver" At the point the dropdb command has been executed, both disappeared from my "radar". Also, in the log there is the following error: LOG: replicação em fluxo conectou-se com sucesso ao servidor principal FATAL: não pôde receber dados do fluxo do WAL: FATAL: segmento do WAL solicitado 0001000100BE já foi removido May the cause not having enough segments (currently 80) for dropdb command? Is dropdb logged in transaction log page-by-page excluded? Thanks, Edson -- 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] dropdb breaks replication?
On Wed, Oct 31, 2012 at 10:32 AM, Edson Richter wrote: > I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. > They are replicated asynchronously. > > Yesterday, I've dropped a database of 20Gb, and then replication has broken, > requiring me to manually synchronize both servers again. > > It is expected that dropdb (or, perhaps, createdb) break existing > replication between servers? How did you determine that replication was broken, and how did you manually synchronize the servers? Are you certain that replication was working prior to dropping the database? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dropdb breaks replication?
I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? Thanks, Edson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
