Re: [HACKERS] Broken hint bits (freeze)

On Thu, Jun 29, 2017 at 6:57 AM, Bruce Momjian wrote: > On Sat, Jun 24, 2017 at 09:24:21AM +0530, Amit Kapila wrote: >> > I was not clear. I was not saying there can be only one extra WAL file. >> > I am saying the "Latest checkpoint location" should be one WAL file >> >

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 30, 2017 at 6:26 AM, Bruce Momjian wrote: > On Wed, Jun 28, 2017 at 10:11:35PM -0400, Bruce Momjian wrote: >> On Fri, Jun 23, 2017 at 06:17:47PM +0300, Sergey Burladyan wrote: >> > PS: >> > I successfully upgraded last night from 9.2 to 9.4 and find other issue :-)

Re: [HACKERS] Broken hint bits (freeze)

On Wed, Jun 28, 2017 at 10:11:35PM -0400, Bruce Momjian wrote: > On Fri, Jun 23, 2017 at 06:17:47PM +0300, Sergey Burladyan wrote: > > PS: > > I successfully upgraded last night from 9.2 to 9.4 and find other issue :-) > > > > It is about hash index and promote: > > 1. create master > > 2. create

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 23, 2017 at 06:17:47PM +0300, Sergey Burladyan wrote: > PS: > I successfully upgraded last night from 9.2 to 9.4 and find other issue :-) > > It is about hash index and promote: > 1. create master > 2. create standby from it > 3. create unlogged table and hash index like: > create

Re: [HACKERS] Broken hint bits (freeze)

On Sat, Jun 24, 2017 at 09:24:21AM +0530, Amit Kapila wrote: > > I was not clear. I was not saying there can be only one extra WAL file. > > I am saying the "Latest checkpoint location" should be one WAL file > > farther on the master. I think the big problem is that we need a full > > replay of

Re: [HACKERS] Broken hint bits (freeze)

On Sat, Jun 24, 2017 at 09:19:10AM +0530, Amit Kapila wrote: > > I successfully upgraded last night from 9.2 to 9.4 and find other issue :-) > > > > It is about hash index and promote: > > 1. create master > > 2. create standby from it > > 3. create unlogged table and hash index like: > > create

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 23, 2017 at 8:18 PM, Bruce Momjian wrote: > On Fri, Jun 23, 2017 at 08:10:17AM +0530, Amit Kapila wrote: >> On Wed, Jun 21, 2017 at 10:03 PM, Bruce Momjian wrote: >> > On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: >> >> On Tue, Jun

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 23, 2017 at 8:47 PM, Sergey Burladyan wrote: > Bruce Momjian writes: > >> On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: >> > On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila >> > wrote: >> > > Hmm. I think

Re: [HACKERS] Broken hint bits (freeze)

Sergey Burladyan writes: > 1. create master > 2. create standby from it > 3. create unlogged table and hash index like: > create unlogged table test (id int primary key, v text); > create index on test using hash (id); > 3. stop master > 4. promote standby > > now, if you

Re: [HACKERS] Broken hint bits (freeze)

Bruce Momjian writes: > On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: > > On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila > > wrote: > > > Hmm. I think we need something that works with lesser effort because > > > not all users will be as

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 23, 2017 at 08:10:17AM +0530, Amit Kapila wrote: > On Wed, Jun 21, 2017 at 10:03 PM, Bruce Momjian wrote: > > On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: > >> On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila > >> wrote: > >> >

Re: [HACKERS] Broken hint bits (freeze)

On Wed, Jun 21, 2017 at 10:03 PM, Bruce Momjian wrote: > On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: >> On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila wrote: >> > Hmm. I think we need something that works with lesser effort because >> >

Re: [HACKERS] Broken hint bits (freeze)

On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: > On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila wrote: > > Hmm. I think we need something that works with lesser effort because > > not all users will be as knowledgeable as you are, so if they make any > >

Re: [HACKERS] Broken hint bits (freeze)

On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila wrote: > On Tue, Jun 20, 2017 at 7:05 PM, Sergey Burladyan wrote: >> Amit Kapila writes: >> >>> On Tue, Jun 20, 2017 at 3:40 PM, Sergey Burladyan >>> wrote:

Re: [HACKERS] Broken hint bits (freeze)

Sorry, this email from June 16 didn't make it to the lists for some odd reason so I am reposting it now. I will apply a patch based on this email shortly. What is really odd is that I replied to this email already but the original wasn't posted. I think it was something about my email reader.

Re: [HACKERS] Broken hint bits (freeze)

Bruce Momjian writes: > On Tue, Jun 20, 2017 at 06:42:58PM +0300, Sergey Burladyan wrote: > > If file at standby in old data directory is different from same file at > > master, but it have same size, it will be hardlinked into new data > > directory at standby and does not

Re: [HACKERS] Broken hint bits (freeze)

Amit Kapila writes: > > I am not sure about rsync, in my production server I have for example > > 111 GB in pg_xlog and if I run rsync for pg_xlog it must send ~ 40GB > > of new WALs I think. > > > > Isn't the difference between old and new is just the last WAL segment >

Re: [HACKERS] Broken hint bits (freeze)

On Tue, Jun 20, 2017 at 06:42:58PM +0300, Sergey Burladyan wrote: > Bruce Momjian writes: > > > On Tue, Jun 20, 2017 at 01:10:26PM +0300, Sergey Burladyan wrote: > > > Only if missing/changed files changed in size, because rsync run with > > > --size-only it does not copy

Re: [HACKERS] Broken hint bits (freeze)

Bruce Momjian writes: > On Tue, Jun 20, 2017 at 01:10:26PM +0300, Sergey Burladyan wrote: > > Bruce Momjian writes: > > > > Uh, as I understand it the rsync is going to copy the missing WAL file > > > > from the new master to the standby, right, and I think

Re: [HACKERS] Broken hint bits (freeze)

On Tue, Jun 20, 2017 at 01:10:26PM +0300, Sergey Burladyan wrote: > Bruce Momjian writes: > > > Uh, as I understand it the rsync is going to copy the missing WAL file > > > from the new master to the standby, right, and I think pg_controldata > > > too, so it should be fine.

Re: [HACKERS] Broken hint bits (freeze)

On Tue, Jun 20, 2017 at 7:05 PM, Sergey Burladyan wrote: > Amit Kapila writes: > >> On Tue, Jun 20, 2017 at 3:40 PM, Sergey Burladyan >> wrote: > I use pg 9.2 and "skipping restartpoint, already performed at" is from >

Re: [HACKERS] Broken hint bits (freeze)

Amit Kapila writes: > On Tue, Jun 20, 2017 at 3:40 PM, Sergey Burladyan wrote: > > Bruce Momjian writes: > > > >> On Mon, Jun 19, 2017 at 10:59:19PM -0400, Bruce Momjian wrote: > >> > On Tue, Jun 20, 2017 at 03:50:29AM +0300,

Re: [HACKERS] Broken hint bits (freeze)

On Tue, Jun 20, 2017 at 3:40 PM, Sergey Burladyan wrote: > Bruce Momjian writes: > >> On Mon, Jun 19, 2017 at 10:59:19PM -0400, Bruce Momjian wrote: >> > On Tue, Jun 20, 2017 at 03:50:29AM +0300, Sergey Burladyan wrote: >> > > 20 июн. 2017 г. 1:21

Re: [HACKERS] Broken hint bits (freeze)

Bruce Momjian writes: > On Mon, Jun 19, 2017 at 10:59:19PM -0400, Bruce Momjian wrote: > > On Tue, Jun 20, 2017 at 03:50:29AM +0300, Sergey Burladyan wrote: > > > 20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" > > > написал:  > > > > > > > > > We

Re: [HACKERS] Broken hint bits (freeze)

On Mon, Jun 19, 2017 at 10:59:19PM -0400, Bruce Momjian wrote: > On Tue, Jun 20, 2017 at 03:50:29AM +0300, Sergey Burladyan wrote: > > 20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" > > написал:  > > > > > > We are saying that Log-Shipping should match "Latest

Re: [HACKERS] Broken hint bits (freeze)

On Tue, Jun 20, 2017 at 03:50:29AM +0300, Sergey Burladyan wrote: > 20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" написал:  > > > We are saying that Log-Shipping should match "Latest checkpoint > location", but the WAL for that will not be sent to the standby, so

Re: [HACKERS] Broken hint bits (freeze)

20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" написал: We are saying that Log-Shipping should match "Latest checkpoint location", but the WAL for that will not be sent to the standby, so it will not match, but that is OK since the only thing in the non-shipped WAL file is

Re: [HACKERS] Broken hint bits (freeze)

On Sat, Jun 17, 2017 at 08:34:47AM +0530, Amit Kapila wrote: > On Fri, Jun 16, 2017 at 11:03 PM, Sergey Burladyan > wrote: > >> > Yeah, we have ensured that all the transactions before shutdown > >> > checkpoint got archived. It is done in commit > >> >

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 16, 2017 at 11:03 PM, Sergey Burladyan wrote: > Bruce Momjian writes: > >> On Fri, Jun 16, 2017 at 08:10:13PM +0530, Amit Kapila wrote: >> > On Fri, Jun 16, 2017 at 7:03 AM, Sergey Burladyan >> > wrote: >> > > Bruce

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 16, 2017 at 04:44:46PM -0400, Bruce Momjian wrote: > Yes, that is _exactly_ the right place to look. Only in PG 10 do we > restart the new cluster to invalidate hash indexes. In previous > releases we didn't do the restart. > > That didn't matter with the old rsync instructions, but

Re: [HACKERS] Broken hint bits (freeze)

Bruce Momjian writes: > On Fri, Jun 16, 2017 at 04:33:16AM +0300, Sergey Burladyan wrote: > > Bruce Momjian writes: > > > ! > > > ! Also, if upgrading standby servers, change wal_level > > > ! to replica in the postgresql.conf file on > > > !

Re: [HACKERS] Broken hint bits (freeze)

Bruce Momjian writes: > On Fri, Jun 16, 2017 at 08:10:13PM +0530, Amit Kapila wrote: > > On Fri, Jun 16, 2017 at 7:03 AM, Sergey Burladyan > > wrote: > > > Bruce Momjian writes: > > > > > >> ! against the old primary and standby

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 16, 2017 at 08:10:13PM +0530, Amit Kapila wrote: > On Fri, Jun 16, 2017 at 7:03 AM, Sergey Burladyan wrote: > > Bruce Momjian writes: > > > >> ! against the old primary and standby clusters. Verify that the > >> ! Latest checkpoint

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 16, 2017 at 04:33:16AM +0300, Sergey Burladyan wrote: > Bruce Momjian writes: > > ! > > ! Also, if upgrading standby servers, change wal_level > > ! to replica in the postgresql.conf file on > > ! the new cluster. > > > > > > I am

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 16, 2017 at 7:03 AM, Sergey Burladyan wrote: > Bruce Momjian writes: > >> ! against the old primary and standby clusters. Verify that the >> ! Latest checkpoint location values match in all clusters. > > For "Log-Shipping only"

Re: [HACKERS] Broken hint bits (freeze)

Bruce Momjian writes: > ! against the old primary and standby clusters. Verify that the > ! Latest checkpoint location values match in all clusters. For "Log-Shipping only" standby server this cannot be satisfied, because last WAL from master (with shutdown

Re: [HACKERS] Broken hint bits (freeze)

On Thu, Jun 15, 2017 at 03:00:18PM +0530, Amit Kapila wrote: > On Wed, Jun 14, 2017 at 8:44 PM, Bruce Momjian wrote: > > On Wed, Jun 14, 2017 at 07:45:17PM +0530, Amit Kapila wrote: > >> > Now, it seems we later added a doc section early on that talks about > >> > "Verify

Re: [HACKERS] Broken hint bits (freeze)

On Wed, Jun 14, 2017 at 8:44 PM, Bruce Momjian wrote: > On Wed, Jun 14, 2017 at 07:45:17PM +0530, Amit Kapila wrote: >> > Now, it seems we later added a doc section early on that talks about >> > "Verify standby servers" so I have moved the wal_level section into that >> >

Re: [HACKERS] Broken hint bits (freeze)

On Wed, Jun 14, 2017 at 07:45:17PM +0530, Amit Kapila wrote: > > Now, it seems we later added a doc section early on that talks about > > "Verify standby servers" so I have moved the wal_level section into that > > block, which should be safe. There is now no need to start/stop the new > > server

Re: [HACKERS] Broken hint bits (freeze)

On Wed, Jun 14, 2017 at 1:01 AM, Bruce Momjian wrote: > On Mon, Jun 12, 2017 at 06:31:11PM +0300, Vladimir Borodin wrote: >> What about the following sequence? >> >> 1. Run pg_upgrade on master, >> 2. Start it in single-user mode and stop (to get right wal_level in >>

Re: [HACKERS] Broken hint bits (freeze)

On Mon, Jun 12, 2017 at 06:31:11PM +0300, Vladimir Borodin wrote: > What about the following sequence? > > 1. Run pg_upgrade on master, > 2. Start it in single-user mode and stop (to get right wal_level in > pg_control), > 3. Copy pg_control somewhere, > 4. Start master, run analyze and stop. >

Re: [HACKERS] Broken hint bits (freeze)

On Mon, Jun 12, 2017 at 9:01 PM, Vladimir Borodin wrote: > > 12 июня 2017 г., в 13:19, Amit Kapila написал(а): > > On Sun, Jun 11, 2017 at 11:59 PM, Vladimir Borodin wrote: > > > 8 июня 2017 г., в 17:03, Amit Kapila

Re: [HACKERS] Broken hint bits (freeze)

> 12 июня 2017 г., в 13:19, Amit Kapila написал(а): > > On Sun, Jun 11, 2017 at 11:59 PM, Vladimir Borodin > wrote: >> >> 8 июня 2017 г., в 17:03, Amit Kapila написал(а): >> >> On Thu, Jun 8, 2017

Re: [HACKERS] Broken hint bits (freeze)

On Sun, Jun 11, 2017 at 11:59 PM, Vladimir Borodin wrote: > > 8 июня 2017 г., в 17:03, Amit Kapila написал(а): > > On Thu, Jun 8, 2017 at 6:49 PM, Dmitriy Sarafannikov > wrote: > > > Why didn't rsync made the copies on master

Re: [HACKERS] Broken hint bits (freeze)

> 8 июня 2017 г., в 17:03, Amit Kapila написал(а): > > On Thu, Jun 8, 2017 at 6:49 PM, Dmitriy Sarafannikov > wrote: >> >>> Why didn't rsync made the copies on master and replica same? >> >> Because rsync was running with —size-only flag. >>

Re: [HACKERS] Broken hint bits (freeze)

On Thu, Jun 8, 2017 at 6:49 PM, Dmitriy Sarafannikov wrote: > >> Why didn't rsync made the copies on master and replica same? > > Because rsync was running with —size-only flag. > IIUC the situation, the new WAL and updated pg_control file has been copied, but not

Re: [HACKERS] Broken hint bits (freeze)

> Why didn't rsync made the copies on master and replica same? Because rsync was running with —size-only flag. > I haven't looked in detail, but it sounds slightly risky proposition > to manipulate the tuples by writing C functions of the form you have > in your code. I would have preferred

Re: [HACKERS] Broken hint bits (freeze)

On Fri, Jun 2, 2017 at 4:20 PM, Dmitriy Sarafannikov wrote: > Thanks for all. > > We found the source of the problem. It was mistake in upgrade to 9.6. > > We upgrade replica with rsync as it is in the documentation: > rsync --verbose --relative --archive --hard-links

Re: [HACKERS] Broken hint bits (freeze)

Vladimir Borodin writes: > > 6 июня 2017 г., в 23:30, Sergey Burladyan написал(а): > > > > Dmitriy Sarafannikov writes: > > > >> Starting and stopping master after running pg_upgrade but before rsync to > >> collect statistics

Re: [HACKERS] Broken hint bits (freeze)

> 6 июня 2017 г., в 23:30, Sergey Burladyan написал(а): > > Dmitriy Sarafannikov writes: > >> Starting and stopping master after running pg_upgrade but before rsync to >> collect statistics >> was a bad idea. > > But, starting and stopping

Re: [HACKERS] Broken hint bits (freeze)

Dmitriy Sarafannikov writes: > Starting and stopping master after running pg_upgrade but before rsync to > collect statistics > was a bad idea. But, starting and stopping master after running pg_upgrade is *required* by documentation:

Re: [HACKERS] Broken hint bits (freeze)

Thanks for all. We found the source of the problem. It was mistake in upgrade to 9.6. We upgrade replica with rsync as it is in the documentation: rsync --verbose --relative --archive --hard-links --size-only old_pgdata new_pgdata remote_dir We must provide 100% read-only availability of our

Re: [HACKERS] Broken hint bits (freeze)

On Sat, May 27, 2017 at 10:18 PM, Vladimir Borodin wrote: > > 26 мая 2017 г., в 21:39, Amit Kapila написал(а): > > I think you somehow need > to ensure before switchover that all the WAL is replicated to ensure > this is not a setup problem. > > >

Re: [HACKERS] Broken hint bits (freeze)

On Sat, May 27, 2017 at 12:56 PM, Andres Freund wrote: > On 2017-05-27 19:48:24 +0300, Vladimir Borodin wrote: >> Well, actually clean shutdown of master with exit code 0 from `pg_ctl >> stop -m fast` guarantees that all WAL has been replicated to standby. > > It does not. It

Re: [HACKERS] Broken hint bits (freeze)

> 27 мая 2017 г., в 19:56, Andres Freund написал(а): > > On 2017-05-27 19:48:24 +0300, Vladimir Borodin wrote: >> Well, actually clean shutdown of master with exit code 0 from `pg_ctl >> stop -m fast` guarantees that all WAL has been replicated to standby. > > It does not.

Re: [HACKERS] Broken hint bits (freeze)

On 2017-05-27 19:48:24 +0300, Vladimir Borodin wrote: > Well, actually clean shutdown of master with exit code 0 from `pg_ctl > stop -m fast` guarantees that all WAL has been replicated to standby. It does not. It makes it likely, but the connection to the standby could be not up just then, you

Re: [HACKERS] Broken hint bits (freeze)

> 26 мая 2017 г., в 21:39, Amit Kapila написал(а): > >> And LSN on replica is greater that LSN on master (838D/C4A0D280 > >> 8092/6A26DD08) >> How can this be possible? >> > > Yeah, I think this is quite suspicious. This seems to indicate that > not all WAL records

Re: [HACKERS] Broken hint bits (freeze)

> 26 мая 2017 г., в 23:04, Michael Paquier > написал(а): > > On Fri, May 26, 2017 at 2:39 PM, Amit Kapila wrote: >> Yeah, I think this is quite suspicious. This seems to indicate that >> not all WAL records are replicated before the

Re: [HACKERS] Broken hint bits (freeze)

On Fri, May 26, 2017 at 2:39 PM, Amit Kapila wrote: > Yeah, I think this is quite suspicious. This seems to indicate that > not all WAL records are replicated before the switchover. What is the > value of "synchronous_commit" you are using? I think you somehow need >

Re: [HACKERS] Broken hint bits (freeze)

On Tue, May 23, 2017 at 10:50 PM, Dmitriy Sarafannikov wrote: > Hi hackers, > > We have some problems on our production with hint bits and frozen tuples. > More and more following errors began to appear on master after switchover: > ERROR: 58P01: could not access status

Re: [HACKERS] Broken hint bits (freeze)

> 24 мая 2017 г., в 15:44, Robert Haas написал(а): > > On Wed, May 24, 2017 at 7:27 AM, Dmitriy Sarafannikov > wrote: >> It seems like replica did not replayed corresponding WAL records. >> Any thoughts? > > heap_xlog_freeze_page() is a pretty

Re: [HACKERS] Broken hint bits (freeze)

On Wed, May 24, 2017 at 7:27 AM, Dmitriy Sarafannikov wrote: > It seems like replica did not replayed corresponding WAL records. > Any thoughts? heap_xlog_freeze_page() is a pretty simple function. It's not impossible that it could have a bug that causes it to

Re: [HACKERS] Broken hint bits (freeze)

We found that this problem appears also on shards with enabled checksums. This shard has 1st timeline, which means there was no switchover after upgrade to 9.6. xdb11f(master)=# select pg_current_xlog_location(), pg_xlogfile_name(pg_current_xlog_location()); pg_current_xlog_location |

[HACKERS] Broken hint bits (freeze)

Hi hackers, We have some problems on our production with hint bits and frozen tuples. More and more following errors began to appear on master after switchover: ERROR: 58P01: could not access status of transaction 1952523525 DETAIL: Could not open file "pg_clog/0746": No such file or directory.