Re: [HACKERS] Replication vs. float timestamps is a disaster

[Omar Kilani]

Hi,

I know I'm 7 months late to this, but only just read the beta 4 release notes.

Is there anything people using float datetimes can do that isn't a
pg_dumpall | pg_restore to do a less painful update?

We have several TB of data still using float datetimes and I'm trying
to figure out how we can move to 10 (currently on 9.6.x) without
massive amounts of $ in duplicated hardware or downtime.

I did attempt a pg_dumpall | pg_restore at one point but for whatever
reason we had data in tables that integer datetimes fails on (I forget
the exact crash, but the datetime values were either too small or too
large to fit into the integer datetimes field -- I can retry this if
it would be helpful).

Thanks.

Regards,
Omar

On Mon, Feb 27, 2017 at 5:13 PM, Andres Freund  wrote:
> On 2017-02-27 17:00:23 -0800, Joshua D. Drake wrote:
>> On 02/22/2017 02:45 PM, Tom Lane wrote:
>> > Andres Freund  writes:
>> > > On 2017-02-22 08:43:28 -0500, Tom Lane wrote:
>> > > > (To be concrete, I'm suggesting dropping --disable-integer-datetimes
>> > > > in HEAD, and just agreeing that in the back branches, use of 
>> > > > replication
>> > > > protocol with float-timestamp servers is not supported and we're not
>> > > > going to bother looking for related bugs there.  Given the lack of 
>> > > > field
>> > > > complaints, I do not believe anyone cares.)
>> >
>> > What I *am* willing to spend time on is removing float-timestamp code
>> > in HEAD.  I've not yet heard anybody speak against doing that (or at
>> > least, nothing I interpreted as a vote against it).  If I've not heard
>> > any complaints by tomorrow, I'll get started on that.
>>
>> Rip it out!
>
> Already happened: 
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b6aa17e0ae367afdcea07118e016111af4fa6bc3
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


-- 
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] Streaming replication bug in 9.3.2, WAL contains references to invalid pages

[Omar Kilani]

We had the same issues running 9.2.4:

[2013-10-15 00:23:01 GMT/0/15396] WARNING:  page 8789807 of relation
base/16429/2349631976 is uninitialized
[2013-10-15 00:23:01 GMT/0/15396] CONTEXT:  xlog redo vacuum: rel
1663/16429/2349631976; blk 8858544, lastBlockVacuumed 0
[2013-10-15 00:23:01 GMT/0/15396] PANIC:  WAL contains references to
invalid pages
[2013-10-15 00:23:01 GMT/0/15396] CONTEXT:  xlog redo vacuum: rel
1663/16429/2349631976; blk 8858544, lastBlockVacuumed 0
[2013-10-15 00:23:11 GMT/0/15393] LOG:  startup process (PID 15396)
was terminated by signal 6: Aborted
[2013-10-15 00:23:11 GMT/0/15393] LOG:  terminating any other active
server processes

Also on an index. I ended up manually patching the heap files at that
block location to fix the problem. It happened again about 2 weeks
after that, then never again. It hit all connected secondaries.

On Fri, Jan 3, 2014 at 12:50 PM, Sergey Konoplev gray...@gmail.com wrote:
 On Thu, Jan 2, 2014 at 11:59 AM, Christophe Pettus x...@thebuild.com wrote:
 In both cases, the indicated relation was a primary key index.  In one case, 
 rebuilding the primary key index caused the problem to go away permanently 
 (to date).  In the second case, the problem returned even after a full dump 
 / restore of the master database (that is, after a dump / restore of the 
 master, and reimaging the secondary, the problem returned at the same 
 primary key index, although of course with a different OID value).

 It looks like this has been experienced on 9.2.6, as well:

 
 http://www.postgresql.org/message-id/flat/CAL_0b1s4QCkFy_55kk_8XWcJPs7wsgVWf8vn4=jxe6v4r7h...@mail.gmail.com

 This problem worries me a lot too. If someone is interested I still
 have a file system copy of the buggy cluster including WAL.

 --
 Kind regards,
 Sergey Konoplev
 PostgreSQL Consultant and DBA

 http://www.linkedin.com/in/grayhemp
 +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
 gray...@gmail.com


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] How to detect invisible rows caused by the relfrozenxid bug?

[Omar Kilani]

Hi,

How would one go about detecting whether they've lost rows due to the
relfrozenxid?

Unfortunately running 'SELECT txid_current()  2^31' on our DB returns
false, and I'm a little bit worried, since we've been seeing some
WeirdStuff(tm) lately.

We're only 200M txids or so past 2^31.

Thanks!

Regards,
Omar


-- 
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] How to detect invisible rows caused by the relfrozenxid bug?

[Omar Kilani]

Hi Robert,

Sounds good. Is it safe to upgrade to 9.2.6 (we're on 9.2.5) in the
mean time, or should we just leave things untouched?

We (unknowingly) got hit by the slave replication bug 2 months ago,
too. That was fun. :)

Omar

On Thu, Dec 5, 2013 at 2:48 PM, Robert Treat r...@xzilla.net wrote:
 On Thu, Dec 5, 2013 at 4:14 PM, Omar Kilani omar.kil...@gmail.com wrote:
 Hi,

 How would one go about detecting whether they've lost rows due to the
 relfrozenxid?

 Unfortunately running 'SELECT txid_current()  2^31' on our DB returns
 false, and I'm a little bit worried, since we've been seeing some
 WeirdStuff(tm) lately.

 We're only 200M txids or so past 2^31.


 We've been working on coming up with a way to determine this, and I
 think we're pretty close, so if you can hang tight for a bit,
 hopefully we can post something.

 That said, if anyone else has come up with a method, I'd be interested
 in looking at it.


 Robert Treat
 play: xzilla.net
 work: omniti.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers