Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Sat, 2010-05-01 at 09:05 -0500, Jaime Casanova wrote: maybe we should be using the tables that exists in the regression database or adding hs_setup_primary in installcheck to prepare the regression database to run standbycheck in the standby server This can definitely use some improvement though not yet. I've documented what's there a little better and fixed up the test results. Thanks for your input in this area. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Mon, 2010-04-26 at 02:45 -0500, Jaime Casanova wrote: On Mon, Apr 26, 2010 at 2:32 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: How many of the tests in the regular regression suite do anything useful when run against a standby server? They all have to set up a bunch of objects before they run queries, so you just get a lot of errors complaining that you can't do X in standby mode, followed by errors about missing objects. That doesn't sound very useful. granted. what i'm looking for is a way of continually see that the standby will return consistent values and yes, i want to be sure that we disallow everything that we need to... maybe just a new set of tests? maybe i just should make the hs_* tests use regression's database tables intead of the ones it is using? The existing standbycheck does statically test for all the things that should be allowed and all the things that should be disallowed. If its missing some, please say. Standbycheck doesn't test dynamic behaviour, which is more than a regression test suite is supposed to do. Yes, dynamic tests needed also. The most important thing now is that there are some tests in CVS and they cover the documented static behaviours. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Sat, May 1, 2010 at 7:22 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, 2010-04-26 at 02:45 -0500, Jaime Casanova wrote: On Mon, Apr 26, 2010 at 2:32 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: How many of the tests in the regular regression suite do anything useful when run against a standby server? They all have to set up a bunch of objects before they run queries, so you just get a lot of errors complaining that you can't do X in standby mode, followed by errors about missing objects. That doesn't sound very useful. granted. what i'm looking for is a way of continually see that the standby will return consistent values and yes, i want to be sure that we disallow everything that we need to... maybe just a new set of tests? maybe i just should make the hs_* tests use regression's database tables intead of the ones it is using? The existing standbycheck does statically test for all the things that should be allowed and all the things that should be disallowed. If its missing some, please say. maybe we should be using the tables that exists in the regression database or adding hs_setup_primary in installcheck to prepare the regression database to run standbycheck in the standby server -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Sat, 2010-05-01 at 09:05 -0500, Jaime Casanova wrote: maybe we should be using the tables that exists in the regression database or adding hs_setup_primary in installcheck to prepare the regression database to run standbycheck in the standby server That's part of the procedure already. We need something better for the future, though not sure if there's any small tweaks worth making for 9.0 right now. Let's start making wider plans for next release. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
Simon Riggs si...@2ndquadrant.com writes: On Sat, 2010-05-01 at 09:05 -0500, Jaime Casanova wrote: maybe we should be using the tables that exists in the regression database or adding hs_setup_primary in installcheck to prepare the regression database to run standbycheck in the standby server That's part of the procedure already. Where is this test procedure documented? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Sat, 2010-05-01 at 12:37 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Sat, 2010-05-01 at 09:05 -0500, Jaime Casanova wrote: maybe we should be using the tables that exists in the regression database or adding hs_setup_primary in installcheck to prepare the regression database to run standbycheck in the standby server That's part of the procedure already. Where is this test procedure documented? In src/test/regress/standby_schedule hs_standby_check.sql throws warning if not setup correctly. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
Simon Riggs si...@2ndquadrant.com writes: On Sat, 2010-05-01 at 12:37 -0400, Tom Lane wrote: Where is this test procedure documented? In src/test/regress/standby_schedule That's a good way to ensure nobody knows it's there :-( If you want users to run this, document it in cookbook fashion in doc/src/sgml/regress.sgml regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Sat, 2010-05-01 at 13:12 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Sat, 2010-05-01 at 12:37 -0400, Tom Lane wrote: Where is this test procedure documented? In src/test/regress/standby_schedule That's a good way to ensure nobody knows it's there :-( If you want users to run this, document it in cookbook fashion in doc/src/sgml/regress.sgml OK -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Wed, Apr 14, 2010 at 9:16 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Apr 10, 2010 at 12:23 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: i think make standbycheck needs a little more work, why it isn't accesible from top of source dir? what i want to do. i started to make this, this weekend 3) it should execute the existing set of tests (the ones installcheck execute) but with a new set of expected results, that way we can be sure that what should be disallowed is disallowed and that the database is returning consistent values. i've thought about having expected/normal (or expected/primary) and expected/standby and check actual results against the appropiate one depending if we use installcheck and standbycheck the real question here is how pg_regress.c should know that it should compare against expected/primary or expected/standby? i mean, could i add an --standby option (my preferred) to pg_regress.c or should i try to guess it from current options and/or asking to the server? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
Jaime Casanova wrote: On Wed, Apr 14, 2010 at 9:16 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: 3) it should execute the existing set of tests (the ones installcheck execute) but with a new set of expected results, that way we can be sure that what should be disallowed is disallowed and that the database is returning consistent values. i've thought about having expected/normal (or expected/primary) and expected/standby and check actual results against the appropiate one depending if we use installcheck and standbycheck the real question here is how pg_regress.c should know that it should compare against expected/primary or expected/standby? i mean, could i add an --standby option (my preferred) to pg_regress.c or should i try to guess it from current options and/or asking to the server? How many of the tests in the regular regression suite do anything useful when run against a standby server? They all have to set up a bunch of objects before they run queries, so you just get a lot of errors complaining that you can't do X in standby mode, followed by errors about missing objects. That doesn't sound very useful. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Mon, Apr 26, 2010 at 2:32 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: How many of the tests in the regular regression suite do anything useful when run against a standby server? They all have to set up a bunch of objects before they run queries, so you just get a lot of errors complaining that you can't do X in standby mode, followed by errors about missing objects. That doesn't sound very useful. granted. what i'm looking for is a way of continually see that the standby will return consistent values and yes, i want to be sure that we disallow everything that we need to... maybe just a new set of tests? maybe i just should make the hs_* tests use regression's database tables intead of the ones it is using? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hash indexes and HS was:(Re: [HACKERS] testing hot standby)
On Tue, 2010-04-13 at 10:41 -0500, Jaime Casanova wrote: On Mon, Apr 12, 2010 at 1:23 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: another point, what happened with this: http://archives.postgresql.org/message-id/1229549172.4793.105.ca...@ebony.2ndquadrant? Obviously we still have the problem with hash indexes, and in that thread Tom advice was just to document the issue and while that could be fine at least we should be emitting better messages, consider this one that i got on the standby server (where 4658650 is the oid of a hash index): mic=# explain analyze select * from tt1 where col1 = 5000; ERROR: could not read block 0 in file base/21958/4658650: read only 0 of 8192 bytes The issue is clearly documented and follows result of discussion. If we allow scans on hash indexes, there isn't any good way to catch an ERROR at this point. I'm in favour of applying the patch that would give a good error message, but not everybody agrees, AFAIK. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Sat, Apr 10, 2010 at 12:23 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: i think make standbycheck needs a little more work, why it isn't accesible from top of source dir? what i want to do. 1) make standbycheck should be accesible from top source dirs 2) it should use an existing regression database on an already configured standby server (no need of the hs_primary_setup.sql) 3) it should execute the existing set of tests (the ones installcheck execute) but with a new set of expected results, that way we can be sure that what should be disallowed is disallowed and that the database is returning consistent values. i've thought about having expected/normal (or expected/primary) and expected/standby and check actual results against the appropiate one depending if we use installcheck and standbycheck comments? i will start this the weekend... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Mon, Apr 12, 2010 at 8:32 PM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Apr 12, 2010 at 11:27 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: 1. start the primary 2. pg_start_backup() 3. copy $PGDATA from the primary to the standby 4. pg_stop_backup(); 5. create the recovery.conf and start the standby execute some WAL-logged action (i've seen this happen even with no WAL-logged action if i wait for a while before shutdown servers) 6. shutdown (smart mode) the standby shutdown (smart) the primary start the primary again 7. start the standby again -- cannot accept connnections until new WAL has been created I was not able to reproduce the problem using the above scenario. But when I did one more restart of the primary and standby, I was able to observe the problem. If this is the same as you encountered, it would be the can't start hot standby from a shutdown checkpoint issue that Heikki pointed out. So it's very helpful to check whether the posted patch fixes your problem or not. http://archives.postgresql.org/pgsql-hackers/2010-04/msg00407.php this patch seems to fix the problem for me... i will read it on the morning and the thread where it is, something that seems strange to me is that the patch touch twophase.c and twophase.h, why? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] hash indexes and HS was:(Re: [HACKERS] testing hot standby)
On Mon, Apr 12, 2010 at 1:23 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: another point, what happened with this: http://archives.postgresql.org/message-id/1229549172.4793.105.ca...@ebony.2ndquadrant? Obviously we still have the problem with hash indexes, and in that thread Tom advice was just to document the issue and while that could be fine at least we should be emitting better messages, consider this one that i got on the standby server (where 4658650 is the oid of a hash index): mic=# explain analyze select * from tt1 where col1 = 5000; ERROR: could not read block 0 in file base/21958/4658650: read only 0 of 8192 bytes -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
Jaime Casanova wrote: On Mon, Apr 12, 2010 at 8:32 PM, Fujii Masao masao.fu...@gmail.com wrote: But when I did one more restart of the primary and standby, I was able to observe the problem. If this is the same as you encountered, it would be the can't start hot standby from a shutdown checkpoint issue that Heikki pointed out. So it's very helpful to check whether the posted patch fixes your problem or not. http://archives.postgresql.org/pgsql-hackers/2010-04/msg00407.php this patch seems to fix the problem for me... Committed, thanks for the testing. i will read it on the morning and the thread where it is, something that seems strange to me is that the patch touch twophase.c and twophase.h, why? When you start hot standby from an online checkpoint, the XIDs of any two-phase transactions are included in the running-xacts record, just like any other in-progress transactions. At a shutdown checkpoint, we know that no regular transactions are in-progress, but there can be transactions in prepared state, which need to be considered as in-progress in the standby, but there's no information about them in the shutdown record. So we scan pg_twophase to discover them. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Tue, Apr 13, 2010 at 11:06 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Jaime Casanova wrote: i will read it on the morning and the thread where it is, something that seems strange to me is that the patch touch twophase.c and twophase.h, why? When you start hot standby from an online checkpoint, the XIDs of any two-phase transactions are included in the running-xacts record, just like any other in-progress transactions. At a shutdown checkpoint, we know that no regular transactions are in-progress, but there can be transactions in prepared state, which need to be considered as in-progress in the standby, but there's no information about them in the shutdown record. So we scan pg_twophase to discover them. ah! that makes sense... thanks -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Sat, Apr 10, 2010 at 5:39 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: i'm startint to try Hot Standby Streaming Replication, so i started a replication: Great! but, my main concern is why it was asking for 00010006? is this normal? The standby server tries to replay all of the available WAL files in the archive and pg_xlog directory at first. Then, when an invalid record is found or no more WAL file is available, it starts streaming replication and tries to read the missing WAL files from the primary. In your testing, an invalid record was found in 00010006, then the standby was waiting for it to be shipped from the primary. is this standby's way of saying i'm working but i have nothing to do? Since SR is a record-based log-shipping, the standby can receive the WAL records from the primary before the WAL file has been filled up. So, in your case, I guess the standby was receiving the WAL records which belong to 00010006 from the primary. when that happens after a standby restart, is normal that i have to wait until the file is created before it can accept connections? No. All the WAL records for the standby to accept connections should be shipped before any additional WAL records are written. Didn't the standby accept connections before executing pgbench? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: but, my main concern is why it was asking for 00010006? is this normal? is this standby's way of saying i'm working but i have nothing to do? when that happens after a standby restart, is normal that i have to wait until the file is created before it can accept connections? ok, i see this again in a new env. seems like this happen when i shutdown standby and primary (in that order) after making some WAL-logged action on the primary an then start again primary and standby (in that order)... it doesn't occur always but it does occur too often, still i'm not sure what is the key factor that triggers this standby waits for a file that doesn't exist to reach a consistent state (last time i wait for an hour after i force a WAL-logged action), here is an extract of the message on standby's log: postg...@casanova1:/usr/local/pgsql/9.0slave$ cat data/pg_log/postgresql-2010-04-12_000947.log LOG: database system was interrupted while in recovery at log time 2010-04-11 20:44:09 GMT HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: entering standby mode LOG: restored log file 0001000E0014 from archive LOG: redo starts at E/1488 LOG: consistent recovery state reached at E/1500 cp: no se puede efectuar `stat' sobre «/usr/local/pgsql/wal_archive/0001000E0015»: No existe el fichero o el directorio LOG: unexpected pageaddr D/EE00 in log file 14, segment 21, offset 0 cp: no se puede efectuar `stat' sobre «/usr/local/pgsql/wal_archive/0001000E0015»: No existe el fichero o el directorio LOG: streaming replication successfully connected to primary another point, what happened with this: http://archives.postgresql.org/message-id/1229549172.4793.105.ca...@ebony.2ndquadrant? Obviously we still have the problem with hash indexes, and in that thread Tom advice was just to document the issue and while that could be fine at least we should be emitting better messages, consider this one that i got on the standby server (where 4658650 is the oid of a hash index): mic=# explain analyze select * from tt1 where col1 = 5000; ERROR: could not read block 0 in file base/21958/4658650: read only 0 of 8192 bytes -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Mon, Apr 12, 2010 at 1:21 AM, Fujii Masao masao.fu...@gmail.com wrote: Didn't the standby accept connections before executing pgbench? nop, and last time i try it was in that state for an hour (without accepting connections)... after that i execute on the primary: CREATE TABLE tt2 AS SELECT generate_series(1, 100) as i After that, the standby start accepting connections -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Mon, Apr 12, 2010 at 3:29 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Apr 12, 2010 at 1:21 AM, Fujii Masao masao.fu...@gmail.com wrote: Didn't the standby accept connections before executing pgbench? nop, and last time i try it was in that state for an hour (without accepting connections)... after that i execute on the primary: CREATE TABLE tt2 AS SELECT generate_series(1, 100) as i After that, the standby start accepting connections OK. Your reproduction scenario is the following? If not, could you show me the complete scenario? 1. start the primary 2. pg_start_backup() 3. copy $PGDATA from the primary to the standby 4. pg_stop_backup(); 5. create the recovery.conf and start the standby 6. shutdown (smart mode) the standby 7. start the standby again -- cannot accept connnections until new WAL has been created Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
Jaime Casanova wrote: On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: but, my main concern is why it was asking for 00010006? is this normal? is this standby's way of saying i'm working but i have nothing to do? Yes. when that happens after a standby restart, is normal that i have to wait until the file is created before it can accept connections? ok, i see this again in a new env. seems like this happen when i shutdown standby and primary (in that order) after making some WAL-logged action on the primary an then start again primary and standby (in that order)... it doesn't occur always but it does occur too often, still i'm not sure what is the key factor that triggers this Perhaps you're being bitten by the can't start hot standby from a shutdown checkpoint issue I've complained for a long time. There's a pending patch for that, see http://archives.postgresql.org/pgsql-hackers/2010-04/msg00222.php. If that's what's hitting you, the way to reproduce is: 1. shut down primary 2. shut down standby 3. start standby 4. start primary. The standby will get hung until it receives an online checkpoint record from the primary. Connecting to the primary and issuing a manual CHECKPOINT helps. That's not the order of shutdowns you described, though... -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Mon, Apr 12, 2010 at 1:48 AM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Apr 12, 2010 at 3:29 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Apr 12, 2010 at 1:21 AM, Fujii Masao masao.fu...@gmail.com wrote: Didn't the standby accept connections before executing pgbench? nop, and last time i try it was in that state for an hour (without accepting connections)... after that i execute on the primary: CREATE TABLE tt2 AS SELECT generate_series(1, 100) as i After that, the standby start accepting connections OK. Your reproduction scenario is the following? If not, could you show me the complete scenario? 1. start the primary 2. pg_start_backup() 3. copy $PGDATA from the primary to the standby 4. pg_stop_backup(); 5. create the recovery.conf and start the standby execute some WAL-logged action (i've seen this happen even with no WAL-logged action if i wait for a while before shutdown servers) 6. shutdown (smart mode) the standby shutdown (smart) the primary start the primary again 7. start the standby again -- cannot accept connnections until new WAL has been created a manual CHECKPOINT on the primary does help -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Mon, Apr 12, 2010 at 9:27 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Apr 12, 2010 at 1:48 AM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Apr 12, 2010 at 3:29 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Apr 12, 2010 at 1:21 AM, Fujii Masao masao.fu...@gmail.com wrote: Didn't the standby accept connections before executing pgbench? nop, and last time i try it was in that state for an hour (without accepting connections)... after that i execute on the primary: CREATE TABLE tt2 AS SELECT generate_series(1, 100) as i After that, the standby start accepting connections OK. Your reproduction scenario is the following? If not, could you show me the complete scenario? 1. start the primary 2. pg_start_backup() 3. copy $PGDATA from the primary to the standby 4. pg_stop_backup(); 5. create the recovery.conf and start the standby execute some WAL-logged action (i've seen this happen even with no WAL-logged action if i wait for a while before shutdown servers) 6. shutdown (smart mode) the standby shutdown (smart) the primary start the primary again 7. start the standby again i guess, this is because the primary is in recovery when the standby tries to connect to it, and it should wait until the primary is ready but seems like the primary is failing to advertise itself and the standby doesn't recheck the condition... could be? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Mon, Apr 12, 2010 at 11:27 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: 1. start the primary 2. pg_start_backup() 3. copy $PGDATA from the primary to the standby 4. pg_stop_backup(); 5. create the recovery.conf and start the standby execute some WAL-logged action (i've seen this happen even with no WAL-logged action if i wait for a while before shutdown servers) 6. shutdown (smart mode) the standby shutdown (smart) the primary start the primary again 7. start the standby again -- cannot accept connnections until new WAL has been created I was not able to reproduce the problem using the above scenario. But when I did one more restart of the primary and standby, I was able to observe the problem. If this is the same as you encountered, it would be the can't start hot standby from a shutdown checkpoint issue that Heikki pointed out. So it's very helpful to check whether the posted patch fixes your problem or not. http://archives.postgresql.org/pgsql-hackers/2010-04/msg00407.php Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing hot standby
On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova
jcasa...@systemguards.com.ec wrote:
Hi,
i'm startint to try Hot Standby Streaming Replication, so i started
a replication:
i think make standbycheck needs a little more work, why it isn't
accesible from top of source dir?
For now, to excercise it i have to do (on the standby server):
- cd src/test/regress
- psql -h ip_primary -f sql/hs_primary_setup.sql
- make standbycheck (and 2 tests fail for differences in the messages,
patch to fix attached)
just an idea, can't we use the info about primary_conninfo to know how
to execute the script hs_primary_setup.sql on the primary?
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
Index: src/test/regress/expected/hs_standby_disallowed.out
===
RCS file: /home/postgres/pg_repo/pgsql/src/test/regress/expected/hs_standby_disallowed.out,v
retrieving revision 1.1
diff -c -r1.1 hs_standby_disallowed.out
*** src/test/regress/expected/hs_standby_disallowed.out 19 Dec 2009 01:32:45 - 1.1
--- src/test/regress/expected/hs_standby_disallowed.out 10 Apr 2010 04:26:48 -
***
*** 11,50
WARNING: there is no transaction in progress
-- SELECT
select * from hs1 FOR SHARE;
! ERROR: transaction is read-only
select * from hs1 FOR UPDATE;
! ERROR: transaction is read-only
-- DML
BEGIN;
insert into hs1 values (37);
! ERROR: transaction is read-only
ROLLBACK;
BEGIN;
delete from hs1 where col1 = 1;
! ERROR: transaction is read-only
ROLLBACK;
BEGIN;
update hs1 set col1 = NULL where col1 0;
! ERROR: transaction is read-only
ROLLBACK;
BEGIN;
truncate hs3;
! ERROR: transaction is read-only
ROLLBACK;
-- DDL
create temporary table hstemp1 (col1 integer);
! ERROR: transaction is read-only
BEGIN;
drop table hs2;
! ERROR: transaction is read-only
ROLLBACK;
BEGIN;
create table hs4 (col1 integer);
! ERROR: transaction is read-only
ROLLBACK;
-- Sequences
SELECT nextval('hsseq');
! ERROR: cannot be executed during recovery
-- Two-phase commit transaction stuff
BEGIN;
SELECT count(*) FROM hs1;
--- 11,50
WARNING: there is no transaction in progress
-- SELECT
select * from hs1 FOR SHARE;
! ERROR: cannot execute SELECT FOR SHARE in a read-only transaction
select * from hs1 FOR UPDATE;
! ERROR: cannot execute SELECT FOR UPDATE in a read-only transaction
-- DML
BEGIN;
insert into hs1 values (37);
! ERROR: cannot execute INSERT in a read-only transaction
ROLLBACK;
BEGIN;
delete from hs1 where col1 = 1;
! ERROR: cannot execute DELETE in a read-only transaction
ROLLBACK;
BEGIN;
update hs1 set col1 = NULL where col1 0;
! ERROR: cannot execute UPDATE in a read-only transaction
ROLLBACK;
BEGIN;
truncate hs3;
! ERROR: cannot execute TRUNCATE TABLE in a read-only transaction
ROLLBACK;
-- DDL
create temporary table hstemp1 (col1 integer);
! ERROR: cannot execute CREATE TABLE in a read-only transaction
BEGIN;
drop table hs2;
! ERROR: cannot execute DROP TABLE in a read-only transaction
ROLLBACK;
BEGIN;
create table hs4 (col1 integer);
! ERROR: cannot execute CREATE TABLE in a read-only transaction
ROLLBACK;
-- Sequences
SELECT nextval('hsseq');
! ERROR: cannot execute nextval() in a read-only transaction
-- Two-phase commit transaction stuff
BEGIN;
SELECT count(*) FROM hs1;
***
*** 54,60
(1 row)
PREPARE TRANSACTION 'foobar';
! ERROR: cannot be executed during recovery
ROLLBACK;
BEGIN;
SELECT count(*) FROM hs1;
--- 54,60
(1 row)
PREPARE TRANSACTION 'foobar';
! ERROR: cannot execute PREPARE TRANSACTION during recovery
ROLLBACK;
BEGIN;
SELECT count(*) FROM hs1;
***
*** 64,70
(1 row)
COMMIT PREPARED 'foobar';
! ERROR: cannot be executed during recovery
ROLLBACK;
BEGIN;
SELECT count(*) FROM hs1;
--- 64,70
(1 row)
COMMIT PREPARED 'foobar';
! ERROR: COMMIT PREPARED cannot run inside a transaction block
ROLLBACK;
BEGIN;
SELECT count(*) FROM hs1;
***
*** 74,80
(1 row)
PREPARE TRANSACTION 'foobar';
! ERROR: cannot be executed during recovery
ROLLBACK PREPARED 'foobar';
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
--- 74,80
(1 row)
PREPARE TRANSACTION 'foobar';
! ERROR: cannot execute PREPARE TRANSACTION during recovery
ROLLBACK PREPARED 'foobar';
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
***
*** 86,137
(1 row)
ROLLBACK PREPARED 'foobar';
! ERROR: cannot be executed during recovery
ROLLBACK;
-- Locks
BEGIN;
LOCK hs1;
! ERROR: cannot be executed during recovery
COMMIT;
BEGIN;
LOCK hs1 IN SHARE UPDATE EXCLUSIVE MODE;
! ERROR: cannot be executed during recovery
