Re: Order by and timestamp

On 3/16/20 11:56 AM, Björn Lundin wrote:
>
>
>
>>> Ooh - terrible sorry.
>>> The output from first post describing the database schema
>>> Was actually from my production machine - a raspberry pi.
>>> The pi hold a db on an usb-disk, which is pg_dump()ed every night and
>>> imported to ibm2 history db (the bad one)
>>> The schema is identical to the one with trouble - which is a history
>>> database
>>> Intended for testing
>>
>> To be clear the RPI version of the database sorts correctly?
>
>
> Yes, but as I replied to Tom, it only contains a days worth of data,
> then pg_dump()ed and truncated.
> Tas data is imported to
> * the faulty one (ibm2/debian/9.4)
> * the correct one (tp/ubuntu/pg 10.6)

Per Tom's comment, what are the encodings?

Also I would point out that the problem occurs on the machine you are
dumping/restoring backwards 9.6 --> 9.4. Not sure if that is relevant or
not, but worth looking at.

How is the dump/restore done(plain text, custom format, etc) and what
are the command strings?

Also what versions of pg_dump/pg_restore are you using on the dump and
restore sides for the various Postgres versions?

More below.

>
>
>>
>>> I did not realize that would matter when posting - did the post away
>>> from home,
>>
>>
>> Yes, it would be have been nice to know at the outset there where
>> multiple instances involved.
>
> Hmm did not realize that. It’s hard to know when to leave out
> ’insignificant details’ and when not to.
> (Ie when the details turn out to be significant)
> I saw a machine - with its current data - sort in a for me strange way.
> Then it struck me that I have another (semi-retired) machine with
> basically the same data,
> Enetered the same way, with the same import files, that works
>
> So in a sense many instances, but not really.

Yes really, otherwise you would not be seeing a difference. Sorry, pet
peeve of mine, when people say these two things are not doing the same
thing but then say they are the same thing.

> I mean, the pg_dump does copy-commands.

It also does a certain amount of setup at the beginning of the file.

> I could have inserted that by hand.
>
> --
> Björn Lundin
> b(dot)f(dot)lundin(at)gmail(dot)com <mailto:b(dot)f(dot)lundin(at)gmail(dot)com>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com