Re: Invalid byte sequence errors on DB restore

[Laurenz Albe]

On Sun, 2020-03-15 at 23:18 -0500, Samuel Smith wrote:
> My current DB backup routine is just to call pg_dump and pipe to gzip. 
> We recently started to get a failure to restore (which is basically just 
> using psql -f on the pg_dump file) with the following errors:
> 
> invalid byte sequence for encoding "UTF8": 0xa0
>   and
> invalid byte sequence for encoding "UTF8": 0xd7 0x20
> 
> 
> This is on a pg 9.2.24 instance. Any tips to troubleshoot?

Simple.  Fix the offending string and upgrade.

Failure to enforce correct encoding is a bug in PostgreSQL, and a
number of such bugs have been fixed over the decades, so you might
be happier with a less ancient version.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Order by and timestamp

[Björn Lundin]

> 
> (1) Suggest using "pastebin.com " for this kind of 
> data. It may not
>look very pretty -- or readable at all -- on the viewer's end
>depending on their settings (see below for example).

Ok, sorry about that.  

https://pastebin.com/2XANMcF6 
Odds rows market with ** at the end of the rows

> 
> (3) "Old database" might mean anyting. Provide the PG version 
>it was created in and the one you are using along with the
>result of "\d+" in the current database. 

  version   
  

 PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 
4.9.2-10) 4.9.2, 64-bit
(1 rad)

bnl@ibm2:~$ uname -a
Linux ibm2 4.9.0-11-amd64 #1 SMP Debian 4.9.189-3+deb9u1 (2019-09-20) x86_64 
GNU/Linux


bnl=> \d+
Lista med relationer
 Schema |  Namn  |   Typ   | Ägare |  Storlek   |Beskrivning
 
++-+---++
 public | abets  | tabell  | bnl   | 62 MB  | Bets
 public | abets_found| tabell  | bnl   | 702 MB | 
 public | adiff  | tabell  | bnl   | 0 bytes| Price diff between 1 
and 2
 public | aevents| tabell  | bnl   | 968 kB | collected runners
 public | amarkets   | tabell  | bnl   | 15 MB  | collected runners
 public | aprices| tabell  | bnl   | 122 MB | runners odds
 public | apriceshistory | tabell  | bnl   | 95 GB  | runners odds during 
race
 public | arunners   | tabell  | bnl   | 124 MB | collected runners
 public | bet_id_serial  | sekvens | bnl   | 8192 bytes | 
(9 rader)


> (4) Classic causes of this are a botched index. Depending on the 
>size you might just want to either drop and re-add the 
>indexes or export and reload the table (e.g., \copy to ...
>+ truncate + \copy from ...). The point there would be 
>fully rebuilding the table and index structure. 

But there is no index on startts
I’ll try that later on. 

> (6) Don't gamble on horses, play the stock market instead: It 
>sounds fancier and you can loose much more money much more
>quickly...  er... yeah.


I can guarantee you that you can loose on horses in any rate you prefer. :-)

> What this looks like on my end. Feel free to try and make sense
> of it yourself.

Ok - point taken.

--
Björn Lundin
b.f.lun...@gmail.com

Re: Order by and timestamp

[Björn Lundin]

> 16 mars 2020 kl. 01:37 skrev Adrian Klaver :
> 
> On 3/15/20 2:33 PM, Björn Lundin wrote:
>> Hi!
>> I have an old database that behaves a bit strange.
>> I keeps horse races in UK/IE.
>> I have a program that continuously* adds record into a market table ,
>> described as below.
>> *continuously means ’after each race’ which is ca 12:00 --> 23:00.
>> I then did ’select * from AMARKETS order by STARTTS’
> 
> Is amarkets in more then one schema?

Yes but the table is empty in other schema (’dry’) - and has less idexes
It is also present in imports - but empty there as well 

bnl@ibm2:~$ psql -l
Tidtagning är på.
AUTOCOMMIT off
Lista med databaser
   Namn|  Ägare   | Kodning | Jämförelse  |Ctype|  
Åtkomsträttigheter   
---+--+-+-+-+---
 bnl   | bnl  | UTF8| sv_SE.UTF-8 | sv_SE.UTF-8 | 
 dry   | bnl  | UTF8| sv_SE.UTF-8 | sv_SE.UTF-8 | 
 imports   | bnl  | UTF8| sv_SE.UTF-8 | sv_SE.UTF-8 | 
 postgres  | postgres | UTF8| sv_SE.UTF-8 | sv_SE.UTF-8 | 
 template0 | postgres | UTF8| sv_SE.UTF-8 | sv_SE.UTF-8 | =c/postgres   
   +
   |  | | | | 
postgres=CTc/postgres
 template1 | postgres | UTF8| sv_SE.UTF-8 | sv_SE.UTF-8 | =c/postgres   
   +
   |  | | | | 
postgres=CTc/postgres
(6 rader)

bnl@ibm2:~$ \c dry
-bash: c: kommandot finns inte
bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> \c dry
psql (9.6.15, server 9.4.15)
Du är nu uppkopplad mot databasen "dry" som användare "bnl".
dry=> \d amarkets
   Tabell "public.amarkets"
  Kolumn  |  Typ   |   Modifierare  
  
--++--
 marketid | character varying(11)  | inte null default ' 
'::character varying
 marketname   | character varying(50)  | inte null default ' 
'::character varying
 startts  | timestamp(3) without time zone | inte null
 eventid  | character varying(11)  | inte null default ' 
'::character varying
 markettype   | character varying(25)  | inte null default ' 
'::character varying
 status   | character varying(50)  | inte null default ' 
'::character varying
 betdelay | integer| inte null default 1
 numwinners   | integer| inte null default 1
 numrunners   | integer| inte null default 1
 numactiverunners | integer| inte null default 1
 totalmatched | numeric(15,2)  | inte null default 0.0
 totalavailable   | numeric(15,2)  | inte null default 0.0
 ixxlupd  | character varying(15)  | inte null default ' 
'::character varying
 ixxluts  | timestamp(3) without time zone | inte null
Index:
"amarketsp1" PRIMARY KEY, btree (marketid)

dry=> select count('a') from amarkets;
 count 
---
 0
(1 rad)

Tid: 2,059 ms
dry=> 

dry=> \c imports
psql (9.6.15, server 9.4.15)
Du är nu uppkopplad mot databasen "imports" som användare "bnl".
imports=> \d
 Lista med relationer
 Schema |Namn|  Typ   | Ägare 
+++---
 public | abets  | tabell | bnl
 public | aevents| tabell | bnl
 public | aevents_tmp| tabell | bnl
 public | amarkets   | tabell | bnl
 public | amarkets_tmp   | tabell | bnl
 public | aprices| tabell | bnl
 public | aprices_tmp| tabell | bnl
 public | apriceshistory | tabell | bnl
 public | apriceshistory_tmp | tabell | bnl
 public | arunners   | tabell | bnl
 public | arunners_tmp   | tabell | bnl
(11 rader)

imports=> select count('a') from amarkets;
 count 
---
 0
(1 rad)




> If so what is search_path?

bnl=> show search_path;
  search_path   

 "$user",public
(1 rad)


> I could not replicate the below.
> 
> What does below show?:
> 
> select '2016-09-30 13:00:00'::timestamp at time zone 'UTC’;

bnl=> select '2016-09-30 13:00:00'::timestamp at time zone 'UTC';
timezone

 2016-09-30 15:00:00+02


> select '2016-10-01 15:35:00'::timestamp at time zone ’UTC’
bnl=> select '2016-10-01 15:35:00'::timestamp at time zone 'UTC';
timezone

 2016-10-01 17:35:00+02


--
Björn Lundin
b.f.lun...@gmail.com

Re: Order by and timestamp

[Björn Lundin]

> 16 mars 2020 kl. 01:41 skrev Tom Lane :
> 
> Adrian Klaver  writes:
>> On 3/15/20 2:33 PM, Björn Lundin wrote:
>>> I then did ’select * from AMARKETS order by STARTTS’
> 
>> Is amarkets in more then one schema?
> 
> Yeah, it's hard to think of any explanation other than "the query used a
> corrupt index on startts to produce the ordering".  But your \d doesn't
> show any index on startts.  So maybe there's more than one amarkets
> table?

Yes - in other schemas - described in reply to Adrain
But the schema_path does not point to them
And those two other tables are empty


> Another possibly-useful bit of evidence is to see what EXPLAIN shows as
> the query plan for this query.

bnl=> explain select * from amarkets order by startts;
  QUERY PLAN   
---
 Sort  (cost=10702.57..10939.29 rows=94691 width=106)
   Sort Key: startts
   ->  Seq Scan on amarkets  (cost=0.00..2875.91 rows=94691 width=106)
(3 rader)

--
Björn Lundin
b.f.lun...@gmail.com

Re: Streaming replication - 11.5

[Nicola Contu]

I was able to make pg_basebackup working using --max-rate=128M

Still don't understand why. I guess it is related to the encryption and
slowness of the disk..
Do you have any idea?

Il giorno ven 13 mar 2020 alle ore 16:15 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 3/13/20 4:11 AM, Nicola Contu wrote:
> > So in the logs I now see this :
> >
> > 2020-03-13 11:03:42 GMT [10.150.20.22(45294)] [27804]: [1-1]
> > db=[unknown],user=replicator LOG:  terminating walsender process due to
> > replication timeout
>
> Yeah that's been showing up the log snippets you have been posting.
>
> To figure this out you will need to:
>
> 1) Make a list of what changed since the last time replication worked
> consistently.
>
> 2) Monitor the changed components, start logging or increase logging.
>
> 3) Monitor the chain of replication as whole, to catch changes that you
> do not know about. Since you seem to be operating across data centers
> that would include verifying the network.
>
> >
> > So I tried increasing the wal_sender_timeout to 300s but it did not help
> >
> > Il giorno gio 12 mar 2020 alle ore 15:56 Nicola Contu
> > mailto:nicola.co...@gmail.com>> ha scritto:
> >
> > The encryption is at os level. So the drives are encrypted with a
> > password where the db saves data
> >
> > Il gio 12 mar 2020, 15:51 Adrian Klaver  > > ha scritto:
> >
> > On 3/12/20 4:31 AM, Nicola Contu wrote:
> >  > The replicator is ok and the replicated as well.
> >  > %Cpu(s):  0.2 us,  1.0 sy,  0.0 ni, 94.8 id,  4.0 wa,  0.0
> > hi,  0.0 si,
> >  >   0.0 st
> >  >
> >  > CPU is really low on both.
> >  >
> >  > I am running pg_basebackup again everytime.
> >  > Any other suggestions?
> >  >
> >
> > I have to believe their is a connection between changing to
> > encrypting
> > the disks and your issues. Not sure what, but to help how is the
> > encryption being done and what program is being used?
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Order by and timestamp

[Björn Lundin]

>> Yeah, it's hard to think of any explanation other than "the query used a
>> corrupt index on startts to produce the ordering".  But your \d doesn't
>> show any index on startts.  So maybe there's more than one amarkets
>> table?


I realize that I have (basically) the same dataset on another machine.

bnl=# select version();
version 
   
---
 PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
(1 row)

bnl@tp:~$ uname -a
Linux tp 4.15.0-39-generic #42-Ubuntu SMP Tue Oct 23 15:48:01 UTC 2018 x86_64 
x86_64 x86_64 GNU/Linux


It misses som later record (from 2020) but otherwise contains the same data, 
and same definition
It is also the only user-database on the system

bnl=# \d amarkets
  Table "public.amarkets"
  Column  |  Type  | Collation | Nullable | 
   Default 
--++---+--+
 marketid | character varying(11)  |   | not null | ' 
'::character varying
 marketname   | character varying(50)  |   | not null | ' 
'::character varying
 startts  | timestamp(3) without time zone |   | not null | 
 eventid  | character varying(11)  |   | not null | ' 
'::character varying
 markettype   | character varying(25)  |   | not null | ' 
'::character varying
 status   | character varying(50)  |   | not null | ' 
'::character varying
 betdelay | integer|   | not null | 1
 numwinners   | integer|   | not null | 1
 numrunners   | integer|   | not null | 1
 numactiverunners | integer|   | not null | 1
 totalmatched | numeric(15,2)  |   | not null | 0.0
 totalavailable   | numeric(15,2)  |   | not null | 0.0
 ixxlupd  | character varying(15)  |   | not null | ' 
'::character varying
 ixxluts  | timestamp(3) without time zone |   | not null | 
Indexes:
"amarketsp1" PRIMARY KEY, btree (marketid)
"amarketsi2" btree (eventid)
"amarketsi3" btree (markettype)
"amarketsi4" btree (status)
"amarketsi5" btree (numwinners)
"amarketsi6" btree (ixxluts)


This gets it correctly.

So it points to something on the first machine.
Recreating indexes is a possibility, but (to me) a bit unintuitive since there 
are no index on startts
I’ll do that tomorrow.


--
Björn Lundin
b.f.lun...@gmail.com

Formatting output (was: Order by and timestamp)

[Peter J. Holzer]

On 2020-03-15 16:48:35 -0500, Steven Lembark wrote:
> On Sun, 15 Mar 2020 22:33:35 +0100:wq
> Björn Lundin  wrote:
> 
> > And to my surprise i get a result like this  (note the order of
> > column STARTTS)
> 
> (1) Suggest using "pastebin.com" for this kind of data. It may not
> look very pretty -- or readable at all -- on the viewer's end
> depending on their settings (see below for example).
[...]
> >   marketid   |marketname|   startts   |
> > eventid ….
> > ….
> > 
> >  1.127253880 | To Be Placed | 2016-09-29 16:10:00 |
> > 27951325 | 1.127275624 | 1m4f Hcap| 2016-09-30
> > 16:20:00 | 27953169 | 1.127275625 | To Be Placed |

This is weird. The output is correctly formatted in the text/plain part
and the HTML part looks reasonable, too: Every line is in a div of its
own, so it shouldn't be jumbled together like that (Out of curiosity:
What mail program did use to read this?). The only obvious problem I see
is the use of the "Menlo" font. Non-Mac Users won't have that and won't
know that they should substitute a monospace font, so the alignment will
be off.

As far as pasted output on this list goes, Björn's message looked
actually quite good.

Some problems I see frequently and find annoying:

* Wide output (especially explain plans) with wrapped lines. I find
  these almost impossible to read, so I have to save the mail to a file
  and manually undo the line breaks to read it. I rarely bother to do
  that.

* ASCII graphics which only line up in a certain proportional font

* text/plain messages with very long lines which really should be
  paragraphs.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Mixed Locales and Upgrading

[Don Seiler]

Good morning,

I have a few clusters that need to be upgraded from PG 9.6 or 10 to 12.
Normally I'd just plan to pg_upgrade the lot and be good to go. However
I've found that quite a few (including our biggest/busiest database) have
mixed locales. In the case of the biggest/busiest database, the cluster was
created with locale en_US (NOT en_US.UTF-8), and so the databases have
encoding LATIN1.

However this database has encoding UTF8 while still having ctype and
collation of en_US. I've since found that when this was last upgraded, they
ran "update pg_database set encoding = pg_char_to_encoding('UTF8') where
datname = 'test';" to change the encoding. In my testing, pg_upgrade breaks
when trying to restore this since UTF8 isn't supported in en_US for the CREATE
DATABASE command used during pg_restore:

command: "/usr/lib/postgresql/12/bin/pg_restore" --host /var/lib/postgresql
--port 50432 --username postgres --create --exit-on-error --verbose
--dbname template1 "pg_upgrade_dump_16385.custom" >>
"pg_upgrade_dump_16385.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "test"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2164; 1262 16385 DATABASE test postgres
pg_restore: error: could not execute query: ERROR:  encoding "UTF8" does
not match locale "en_US"
DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".
Command was: CREATE DATABASE "test" WITH TEMPLATE = template0 ENCODING =
'UTF8' LC_COLLATE = 'en_US' LC_CTYPE = 'en_US';

Is there a way around this while still using pg_upgrade? My understanding
is that a full dump and restore into a new DB with everything set to
en_US.UTF-8 is the only to convert these (if I have to dump/restore, I
wouldn't want to keep the mixed environment). Even with parallel jobs, I
imagine that's a bit of downtime but I'll have to wait until I can get a
copy of prod data to test with to be sure.

Is logical replication an option here? Either maintaining the mixed
environment or converting everything to en_US.UTF-8? I'm relatively new in
this shop but I'm told they didn't mean to use en_US and there's no reason
they wouldn't want to just use the standard/default UTF-8.

Thanks,
Don.

-- 
Don Seiler
www.seiler.us

Re: Invalid byte sequence errors on DB restore

[Adrian Klaver]

On 3/15/20 9:18 PM, Samuel Smith wrote:
My current DB backup routine is just to call pg_dump and pipe to gzip. 
We recently started to get a failure to restore (which is basically just 
using psql -f on the pg_dump file) with the following errors:


invalid byte sequence for encoding "UTF8": 0xa0
  and
invalid byte sequence for encoding "UTF8": 0xd7 0x20


This is on a pg 9.2.24 instance. Any tips to troubleshoot?


What are the locale and encodings set to for the instance and databases 
in it?




Regards,
Samuel Smith





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: psql crash on 9.6.16

[Adrian Klaver]

On 3/16/20 7:48 AM, Scot Kreienkamp wrote:
I was using psql to connect to the local database for a simple update 
this morning and it crashed.  Here’s what was printed out after the 
crash.  I was not able to re-create it.


I don't have an answer, just some questions:

1) OS and version of same?

2) Where you using the 9.6 version of psql?

3) What was the connection string that you supplied to psql?



rms =# update userlogin set firstname='namechanged', 
lastname='namechanged' where userloginid='1598113';


UPDATE 1

rms =# select * from userlogin where userloginid='1598113';

*** Error in `psql': free(): invalid pointer: 0x01bc64e0 ***

=== Backtrace: =

/lib64/libc.so.6(+0x816b9)[0x7fa5e4b106b9]

/lib64/libreadline.so.6(rl_do_undo+0x1bd)[0x7fa5e5188abd]

/lib64/libreadline.so.6(rl_revert_line+0x15)[0x7fa5e5188c65]

/lib64/libreadline.so.6(readline_internal_teardown+0x66)[0x7fa5e5174286]

/lib64/libreadline.so.6(readline+0x82)[0x7fa5e51750e2]

psql[0x40f9e5]

psql[0x410412]

psql[0x405ac1]

/lib64/libc.so.6(__libc_start_main+0xf5)[0x7fa5e4ab1545]

psql[0x405c2a]

=== Memory map: 

0040-0048 r-xp  fd:00 134383438  
/usr/pgsql-9.6/bin/psql


0067f000-0068 r--p 0007f000 fd:00 134383438  
/usr/pgsql-9.6/bin/psql


0068-00681000 rw-p 0008 fd:00 134383438  
/usr/pgsql-9.6/bin/psql


00681000-00682000 rw-p  00:00 0

01b97000-01bd9000 rw-p  00:00 0  
[heap]


7fa5d400-7fa5d4021000 rw-p  00:00 0

7fa5d4021000-7fa5d800 ---p  00:00 0

7fa5da0f-7fa5da105000 r-xp  fd:00 67546789   
/usr/lib64/libgcc_s-4.8.5-20150702.so.1


7fa5da105000-7fa5da304000 ---p 00015000 fd:00 67546789   
/usr/lib64/libgcc_s-4.8.5-20150702.so.1


7fa5da304000-7fa5da305000 r--p 00014000 fd:00 67546789   
/usr/lib64/libgcc_s-4.8.5-20150702.so.1


7fa5da305000-7fa5da306000 rw-p 00015000 fd:00 67546789   
/usr/lib64/libgcc_s-4.8.5-20150702.so.1


7fa5da306000-7fa5da312000 r-xp  fd:00 67259454   
/usr/lib64/libnss_files-2.17.so


7fa5da312000-7fa5da511000 ---p c000 fd:00 67259454   
/usr/lib64/libnss_files-2.17.so


7fa5da511000-7fa5da512000 r--p b000 fd:00 67259454   
/usr/lib64/libnss_files-2.17.so


7fa5da512000-7fa5da513000 rw-p c000 fd:00 67259454   
/usr/lib64/libnss_files-2.17.so


7fa5da513000-7fa5da519000 rw-p  00:00 0

7fa5da519000-7fa5e0a43000 r--p  fd:00 201551822  
/usr/lib/locale/locale-archive


7fa5e0a43000-7fa5e0a45000 r-xp  fd:00 67252476   
/usr/lib64/libfreebl3.so


7fa5e0a45000-7fa5e0c44000 ---p 2000 fd:00 67252476   
/usr/lib64/libfreebl3.so


7fa5e0c44000-7fa5e0c45000 r--p 1000 fd:00 67252476   
/usr/lib64/libfreebl3.so


7fa5e0c45000-7fa5e0c46000 rw-p 2000 fd:00 67252476   
/usr/lib64/libfreebl3.so


7fa5e0c46000-7fa5e0ca6000 r-xp  fd:00 67252693   
/usr/lib64/libpcre.so.1.2.0


7fa5e0ca6000-7fa5e0ea6000 ---p 0006 fd:00 67252693   
/usr/lib64/libpcre.so.1.2.0


7fa5e0ea6000-7fa5e0ea7000 r--p 0006 fd:00 67252693
    /usr/lib64/libpcre.so.1.2.0


7fa5e0ea7000-7fa5e0ea8000 rw-p 00061000 fd:00 67252693   
/usr/lib64/libpcre.so.1.2.0


7fa5e0ea8000-7fa5e0eaf000 r-xp  fd:00 69021578   
/usr/lib64/librt-2.17.so


7fa5e0eaf000-7fa5e10ae000 ---p 7000 fd:00 69021578   
/usr/lib64/librt-2.17.so


7fa5e10ae000-7fa5e10af000 r--p 6000 fd:00 69021578   
/usr/lib64/librt-2.17.so


7fa5e10af000-7fa5e10b rw-p 7000 fd:00 69021578   
/usr/lib64/librt-2.17.so


7fa5e10b-7fa5e10b8000 r-xp  fd:00 67242996   
/usr/lib64/libcrypt-2.17.so


7fa5e10b8000-7fa5e12b7000 ---p 8000 fd:00 67242996   
/usr/lib64/libcrypt-2.17.so


7fa5e12b7000-7fa5e12b8000 r--p 7000 fd:00 67242996   
/usr/lib64/libcrypt-2.17.so


7fa5e12b8000-7fa5e12b9000 rw-p 8000 fd:00 67242996   
/usr/lib64/libcrypt-2.17.so


7fa5e12b9000-7fa5e12e7000 rw-p  00:00 0

7fa5e12e7000-7fa5e130b000 r-xp  fd:00 67252633   
/usr/lib64/libselinux.so.1


7fa5e130b000-7fa5e150a000 ---p 00024000 fd:00 67252633   
/usr/lib64/libselinux.so.1


7fa5e150a000-7fa5e150b000 r--p 00023000 fd:00 67252633   
/usr/lib64/libselinux.so.1


7fa5e150b000-7fa5e150c000 rw-p 00024000 fd:00 67252633   
/usr/lib64/libselinux.so.1


7fa5e150c000-7fa5e150e000 rw-p  00:00 0

7fa5e150e000-7fa5e1548000 r-xp  fd:00 67243008   
/usr/lib64/libnspr4.so


7fa5e1548000-7fa5e1747000 ---p 0003a000 

Re: Order by and timestamp

[Adrian Klaver]

On 3/16/20 1:49 AM, Björn Lundin wrote:



16 mars 2020 kl. 01:37 skrev Adrian Klaver >:


On 3/15/20 2:33 PM, Björn Lundin wrote:

Hi!
I have an old database that behaves a bit strange.
I keeps horse races in UK/IE.
I have a program that continuously* adds record into a market table ,
described as below.
*continuously means ’after each race’ which is ca 12:00 --> 23:00.
I then did ’select * from AMARKETS order by STARTTS’


Is amarkets in more then one schema?


Yes but the table is empty in other schema (’dry’) - and has less idexes
It is also present in imports - but empty there as well


Actually the below indicates it is in other databases. A schema would be 
a namespace within a database, see here:


https://www.postgresql.org/docs/12/sql-createschema.html

In your original example the 'public' in public.amarkets.
So just to be complete \dn in psql will show you the schemas in a 
database. Given the search_path("$user",public) shown below I suspect 
you have only a public schema. $user matches a schema named for the 
current user and generally is not there.



The times returned below match, so I am at a loss for an explanation at 
the moment.




bnl@ibm2:~$ psql -l
Tidtagning är på.
AUTOCOMMIT off
                                 Lista med databaser
    Namn    |  Ägare   | Kodning | Jämförelse  |    Ctype    |  
Åtkomsträttigheter

---+--+-+-+-+---
  bnl       | bnl      | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
  dry       | bnl      | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
  imports   | bnl      | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
  postgres  | postgres | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
  template0 | postgres | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 | 
=c/postgres          +
            |          |         |             |             | 
postgres=CTc/postgres
  template1 | postgres | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 | 
=c/postgres          +
            |          |         |             |             | 


If so what is search_path?


bnl=> show search_path;
   search_path

  "$user",public
(1 rad)



I could not replicate the below.

What does below show?:

select '2016-09-30 13:00:00'::timestamp at time zone 'UTC’;


bnl=> select '2016-09-30 13:00:00'::timestamp at time zone 'UTC';
         timezone

  2016-09-30 15:00:00+02



select '2016-10-01 15:35:00'::timestamp at time zone ’UTC’

bnl=> select '2016-10-01 15:35:00'::timestamp at time zone 'UTC';
         timezone

  2016-10-01 17:35:00+02


--
Björn Lundin
b.f.lun...@gmail.com 







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Order by and timestamp

[Adrian Klaver]

On 3/16/20 1:51 AM, Björn Lundin wrote:



16 mars 2020 kl. 01:41 skrev Tom Lane >:


Adrian Klaver > writes:

On 3/15/20 2:33 PM, Björn Lundin wrote:

I then did ’select * from AMARKETS order by STARTTS’



Is amarkets in more then one schema?


Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering".  But your \d doesn't
show any index on startts.  So maybe there's more than one amarkets
table?


Yes - in other schemas - described in reply to Adrain
But the schema_path does not point to them
And those two other tables are empty



Another possibly-useful bit of evidence is to see what EXPLAIN shows as
the query plan for this query.


bnl=> explain select * from amarkets order by startts;


Can you run as:

explain analyze select * from amarkets order by startts;


                               QUERY PLAN
---
  Sort  (cost=10702.57..10939.29 rows=94691 width=106)
    Sort Key: startts
    ->  Seq Scan on amarkets  (cost=0.00..2875.91 rows=94691 width=106)
(3 rader)

--
Björn Lundin
b.f.lun...@gmail.com 







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Mixed Locales and Upgrading

[Tom Lane]

Don Seiler  writes:
> However this database has encoding UTF8 while still having ctype and
> collation of en_US. I've since found that when this was last upgraded, they
> ran "update pg_database set encoding = pg_char_to_encoding('UTF8') where
> datname = 'test';" to change the encoding.

Egad.

> In my testing, pg_upgrade breaks
> when trying to restore this since UTF8 isn't supported in en_US for the CREATE
> DATABASE command used during pg_restore:

Well, in principle you could likewise manually update pg_database's
datcollate and datctype columns to say "en_US.utf8".  However, there's
a much bigger problem here --- what steps if any did this cowboy take
to ensure that the data inside the database was valid UTF8?

I don't think you should use pg_upgrade here at all.  A dump/restore
is really the only way to make sure that you have validly encoded data.

However, if it's only one database out of a bunch, you could do something
like

* pg_dump that one database;
* drop said database;
* pg_upgrade everything else;
* restore that one database from dump.

regards, tom lane

Re: Streaming replication - 11.5

[Adrian Klaver]

On 3/16/20 2:57 AM, Nicola Contu wrote:

I was able to make pg_basebackup working using --max-rate=128M

Still don't understand why. I guess it is related to the encryption and 
slowness of the disk..

Do you have any idea?


I think your explanation fits. Encryption/decryption have overhead.



Il giorno ven 13 mar 2020 alle ore 16:15 Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> ha scritto:


On 3/13/20 4:11 AM, Nicola Contu wrote:
 > So in the logs I now see this :
 >
 > 2020-03-13 11:03:42 GMT [10.150.20.22(45294)] [27804]: [1-1]
 > db=[unknown],user=replicator LOG:  terminating walsender process
due to
 > replication timeout

Yeah that's been showing up the log snippets you have been posting.

To figure this out you will need to:

1) Make a list of what changed since the last time replication worked
consistently.

2) Monitor the changed components, start logging or increase logging.

3) Monitor the chain of replication as whole, to catch changes that you
do not know about. Since you seem to be operating across data centers
that would include verifying the network.

 >
 > So I tried increasing the wal_sender_timeout to 300s but it did
not help
 >
 > Il giorno gio 12 mar 2020 alle ore 15:56 Nicola Contu
 > mailto:nicola.co...@gmail.com>
>> ha
scritto:
 >
 >     The encryption is at os level. So the drives are encrypted with a
 >     password where the db saves data
 >
 >     Il gio 12 mar 2020, 15:51 Adrian Klaver
mailto:adrian.kla...@aklaver.com>
 >     >> ha scritto:
 >
 >         On 3/12/20 4:31 AM, Nicola Contu wrote:
 >          > The replicator is ok and the replicated as well.
 >          > %Cpu(s):  0.2 us,  1.0 sy,  0.0 ni, 94.8 id,  4.0 wa,  0.0
 >         hi,  0.0 si,
 >          >   0.0 st
 >          >
 >          > CPU is really low on both.
 >          >
 >          > I am running pg_basebackup again everytime.
 >          > Any other suggestions?
 >          >
 >
 >         I have to believe their is a connection between changing to
 >         encrypting
 >         the disks and your issues. Not sure what, but to help how
is the
 >         encryption being done and what program is being used?
 >
 >
 >         --
 >         Adrian Klaver
 > adrian.kla...@aklaver.com 
>
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: psql crash on 9.6.16

[Tom Lane]

Scot Kreienkamp  writes:
> I was using psql to connect to the local database for a simple update this 
> morning and it crashed.  Here's what was printed out after the crash.  I was 
> not able to re-create it.

Hm, the backtrace says the problem is somewhere inside libreadline,
which means it has nothing to do with any SQL-level concepts, but
rather with editing your input text.  Did you do anything unusual
while typing that line of input --- say, recall it from history,
or edit it in a way that you don't usually do, or even mistype
some control character you don't usually type?

Sadly, the visible evidence here doesn't tell us much of anything
about what provoked the crash :-(

FWIW, there's a pretty good chance that this isn't psql's fault
per se, but a libreadline bug.  You might check to see if you can
get a newer readline version installed.

regards, tom lane

Re: Order by and timestamp

[Adrian Klaver]

On 3/16/20 3:03 AM, Björn Lundin wrote:




Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering".  But your \d doesn't
show any index on startts.  So maybe there's more than one amarkets
table?



I realize that I have (basically) the same dataset on another machine.


Which brings me back to your first post where you had:

Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

Then you said the database was:

 version 



 PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 
4.9.2-10) 4.9.2, 64-bit

(1 rad)

Which seemed to be confirmed by:

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.


That leaves me wondering how you got to the output in the first post?

In other words different psql version and no server version listed which 
indicates the server is 9.6.




bnl=# select version();
                                                             version
---
  PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit

(1 row)

*bnl@tp*:*~*$ uname -a
Linux tp 4.15.0-39-generic #42-Ubuntu SMP Tue Oct 23 15:48:01 UTC 2018 
x86_64 x86_64 x86_64 GNU/Linux



It misses som later record (from 2020) but otherwise contains the same 
data, and same definition

It is also the only user-database on the system

bnl=# \d amarkets
                                       Table "public.amarkets"
       Column      |              Type              | Collation | 
Nullable |        Default

--++---+--+
  marketid         | character varying(11)          |           | not 
null | ' '::character varying
  marketname       | character varying(50)          |           | not 
null | ' '::character varying
  startts          | timestamp(3) without time zone |           | not 
null |
  eventid          | character varying(11)          |           | not 
null | ' '::character varying
  markettype       | character varying(25)          |           | not 
null | ' '::character varying
  status           | character varying(50)          |           | not 
null | ' '::character varying
  betdelay         | integer                        |           | not 
null | 1
  numwinners       | integer                        |           | not 
null | 1
  numrunners       | integer                        |           | not 
null | 1
  numactiverunners | integer                        |           | not 
null | 1
  totalmatched     | numeric(15,2)                  |           | not 
null | 0.0
  totalavailable   | numeric(15,2)                  |           | not 
null | 0.0
  ixxlupd          | character varying(15)          |           | not 
null | ' '::character varying
  ixxluts          | timestamp(3) without time zone |           | not 
null |

Indexes:
     "amarketsp1" PRIMARY KEY, btree (marketid)
     "amarketsi2" btree (eventid)
     "amarketsi3" btree (markettype)
     "amarketsi4" btree (status)
     "amarketsi5" btree (numwinners)
     "amarketsi6" btree (ixxluts)


This gets it correctly.

So it points to something on the first machine.
Recreating indexes is a possibility, but (to me) a bit unintuitive since 
there are no index on startts

I’ll do that tomorrow.


--
Björn Lundin
b.f.lun...@gmail.com 







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Order by and timestamp

[Björn Lundin]

>>> 
>>> Is amarkets in more then one schema?
>> Yes but the table is empty in other schema (’dry’) - and has less idexes
>> It is also present in imports - but empty there as well
> 
> Actually the below indicates it is in other databases. A schema would be a 
> namespace within a database, see here:
> 
> https://www.postgresql.org/docs/12/sql-createschema.html
> 
> In your original example the 'public' in public.amarkets.
> So just to be complete \dn in psql will show you the schemas in a database. 
> Given the search_path("$user",public) shown below I suspect you have only a 
> public schema. $user matches a schema named for the current user and 
> generally is not there.

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> \dn
 Lista med scheman
  Namn  |  Ägare   
+--
 public | postgres
(1 rad)


Yes only 1 schema


Hmm to be clear, I have the problem on a machine 
Called ibm running debian
In a 9.4 database called bnl

This also have database dry and import which both contain the same table (with 
only PK - no index) - but both those are empty.

Then I said I have the same dataset on another another box
Called tp, running ubuntu
With a 10.6 database called bnl
Which works

I’ll reply to the other mail separately 

thanks for replying


--
Björn Lundin
b.f.lun...@gmail.com

Re: Order by and timestamp

[Björn Lundin]

> 16 mars 2020 kl. 16:27 skrev Adrian Klaver :
> 
> On 3/16/20 1:51 AM, Björn Lundin wrote:
>>> 16 mars 2020 kl. 01:41 skrev Tom Lane >> >:
>>> 
>>> Adrian Klaver >> > writes:
 On 3/15/20 2:33 PM, Björn Lundin wrote:
> I then did ’select * from AMARKETS order by STARTTS’
>>> 
 Is amarkets in more then one schema?
>>> 
>>> Yeah, it's hard to think of any explanation other than "the query used a
>>> corrupt index on startts to produce the ordering".  But your \d doesn't
>>> show any index on startts.  So maybe there's more than one amarkets
>>> table?
>> Yes - in other schemas - described in reply to Adrain
>> But the schema_path does not point to them
>> And those two other tables are empty
>>> Another possibly-useful bit of evidence is to see what EXPLAIN shows as
>>> the query plan for this query.
>> bnl=> explain select * from amarkets order by startts;
> 
> Can you run as:
> 
> explain analyze select * from amarkets order by startts;

Yes, below the first plan

> 
>>   QUERY PLAN
>> ---
>>  Sort  (cost=10702.57..10939.29 rows=94691 width=106)
>>Sort Key: startts
>>->  Seq Scan on amarkets  (cost=0.00..2875.91 rows=94691 width=106)
>> (3 rader)
>> —


bnl=> explain analyze select * from amarkets order by startts;
  QUERY PLAN
   
---
 Sort  (cost=10702.57..10939.29 rows=94691 width=106) (actual 
time=404.555..422.174 rows=97835 loops=1)
   Sort Key: startts
   Sort Method: quicksort  Memory: 24329kB
   ->  Seq Scan on amarkets  (cost=0.00..2875.91 rows=94691 width=106) (actual 
time=4.586..351.739 rows=97835 loops=1)
 Planning time: 74.707 ms
 Execution time: 434.785 ms
(6 rader)

Tid: 527,142 ms
bnl=> 




--
Björn Lundin
b.f.lun...@gmail.com

Re: Order by and timestamp

[Björn Lundin]

> 16 mars 2020 kl. 16:46 skrev Adrian Klaver :
> 
> On 3/16/20 3:03 AM, Björn Lundin wrote:
 Yeah, it's hard to think of any explanation other than "the query used a
 corrupt index on startts to produce the ordering".  But your \d doesn't
 show any index on startts.  So maybe there's more than one amarkets
 table?
>> I realize that I have (basically) the same dataset on another machine.
> 
> Which brings me back to your first post where you had:
> 
> Timing is on.
> AUTOCOMMIT off
> psql (9.6.10)
> Type "help" for help.
> 
> Then you said the database was:
> 
> version 
> 
> PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 
> 4.9.2-10) 4.9.2, 64-bit
> (1 rad)
> 
> Which seemed to be confirmed by:
> 
> bnl@ibm2:~$ psql
> Tidtagning är på.
> AUTOCOMMIT off
> psql (9.6.15, server 9.4.15)
> Skriv "help" för hjälp.
> 
> 
> That leaves me wondering how you got to the output in the first post?


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

I did not realize that would matter when posting - did the post away from home,
I can reach the prod machine but not the history machine (ibm2) from outside.
 
So - from the pi - first post

bnl=# \q
bnl@pibetbot:~ $ psql
Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

bnl=# \d amarkets
   Table "public.amarkets"
  Column  |  Type  |Modifiers   
 
--++-
 marketid | character varying(11)  | not null default ' 
'::character varying
 marketname   | character varying(50)  | not null default ' 
'::character varying
 startts  | timestamp(3) without time zone | not null
 eventid  | character varying(11)  | not null default ' 
'::character varying
 markettype   | character varying(25)  | not null default ' 
'::character varying
 status   | character varying(50)  | not null default ' 
'::character varying
 betdelay | integer| not null default 1
 numwinners   | integer| not null default 1
 numrunners   | integer| not null default 1
 numactiverunners | integer| not null default 1
 totalmatched | numeric(15,2)  | not null default 0.0
 totalavailable   | numeric(15,2)  | not null default 0.0
 ixxlupd  | character varying(15)  | not null default ' 
'::character varying
 ixxluts  | timestamp(3) without time zone | not null
Indexes:
"amarketsp1" PRIMARY KEY, btree (marketid)
"amarketsi2" btree (eventid)
"amarketsi3" btree (markettype)
"amarketsi4" btree (status)
"amarketsi5" btree (numwinners)
"amarketsi6" btree (ixxluts)

bnl=# \q
bnl@pibetbot:~ $ logout
Connection to 192.168.1.7 closed.

From the machine (ibm2) with bad sort order


imac:~ bnl$ ssh 192.168.1.20
bnl@192.168.1.20's password: 
Linux ibm2 4.9.0-11-amd64 #1 SMP Debian 4.9.189-3+deb9u1 (2019-09-20) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
No mail.
Last login: Mon Mar 16 16:54:56 2020 from 192.168.1.174
bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> \d amarkets
   Tabell "public.amarkets"
  Kolumn  |  Typ   |   Modifierare  
  
--++--
 marketid | character varying(11)  | inte null default ' 
'::character varying
 marketname   | character varying(50)  | inte null default ' 
'::character varying
 startts  | timestamp(3) without time zone | inte null
 eventid  | character varying(11)  | inte null default ' 
'::character varying
 markettype   | character varying(25)  | inte null default ' 
'::character varying
 status   | character varying(50)  | inte null default ' 
'::character varying
 betdelay | integer| inte null default 1
 numwinners   | integer| inte null default 1
 numrunne

Re: Order by and timestamp

[Tom Lane]

=?utf-8?Q?Bj=C3=B6rn_Lundin?=  writes:
> 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

Hmmm ... schema is evidently the same, but locale seemingly not.
Is it possible this is a locale problem?  I did not look closely
at the original data, but I'm suddenly wondering if it could be
explained by misinterpreting the date field order (month-day vs
day-month).  Check the datestyle settings on both machines.

regards, tom lane

Re: Invalid byte sequence errors on DB restore

[Samuel Smith]

On 3/16/20 2:49 AM, Laurenz Albe wrote:

On Sun, 2020-03-15 at 23:18 -0500, Samuel Smith wrote:

My current DB backup routine is just to call pg_dump and pipe to gzip.
We recently started to get a failure to restore (which is basically just
using psql -f on the pg_dump file) with the following errors:

invalid byte sequence for encoding "UTF8": 0xa0
   and
invalid byte sequence for encoding "UTF8": 0xd7 0x20


This is on a pg 9.2.24 instance. Any tips to troubleshoot?


Simple.  Fix the offending string and upgrade.

Failure to enforce correct encoding is a bug in PostgreSQL, and a
number of such bugs have been fixed over the decades, so you might
be happier with a less ancient version.

Yours,
Laurenz Albe





Yes I thought I had seen some info on bug fixes in newer versions. I 
will pass this on to the system admins/stakeholders.


Thanks,
Samuel Smith

Re: Order by and timestamp

[Adrian Klaver]

On 3/16/20 9:15 AM, Björn Lundin wrote:



16 mars 2020 kl. 16:46 skrev Adrian Klaver >:


On 3/16/20 3:03 AM, Björn Lundin wrote:
Yeah, it's hard to think of any explanation other than "the query 
used a

corrupt index on startts to produce the ordering".  But your \d doesn't
show any index on startts.  So maybe there's more than one amarkets
table?

I realize that I have (basically) the same dataset on another machine.


Which brings me back to your first post where you had:

Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

Then you said the database was:

version

PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 
4.9.2-10) 4.9.2, 64-bit

(1 rad)

Which seemed to be confirmed by:

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.


That leaves me wondering how you got to the output in the first post?


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?



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.


I can reach the prod machine but not the history machine (ibm2) from 
outside.

So - from the pi - first post




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Order by and timestamp

[Björn Lundin]

>> 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)


> 
>> 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.
I mean, the pg_dump does copy-commands.
I could have inserted that by hand.

--
Björn Lundin
b.f.lun...@gmail.com

Re: Order by and timestamp

[Adrian Klaver]

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.f.lun...@gmail.com 







--
Adrian Klaver
adrian.kla...@aklaver.com

RE: psql crash on 9.6.16

[Scot Kreienkamp]

I don't have an answer, just some questions:   
No problem, wasn't looking for an answer, just trying to help by reporting it.  


1) OS and version of same?   
RHEL7.7

2) Where you using the 9.6 version of psql?   
Yes, 9.6.16 client and server, installed via RPM from the official repo 
packages.

3) What was the connection string that you supplied to psql?  
No connection string, so connected via socket.  Psql -d rms.   



Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162 | Office: 734-384-6403 | Fax:  | 
Mobile: 7349151444 | E-mail: scot.kreienk...@la-z-boy.com

?
?

This message is intended only for the individual or entity to which it is 
addressed. It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws. If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information. If you have 
received this communication in error, please notify us immediately by e-mail or 
by telephone at the above number. Thank you. 

RE: psql crash on 9.6.16

[Scot Kreienkamp]

Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162 | Office: 734-384-6403 | Fax:  | 
Mobile: 7349151444 | E-mail: scot.kreienk...@la-z-boy.com

?
?
-Original Message-
From: Tom Lane  
Sent: Monday, March 16, 2020 11:40 AM
To: Scot Kreienkamp 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: psql crash on 9.6.16

ATTENTION:   This email was sent to La-Z-Boy from an external source. Be 
vigilant when opening attachments or clicking links.

Scot Kreienkamp  writes:
> I was using psql to connect to the local database for a simple update this 
> morning and it crashed.  Here's what was printed out after the crash.  I was 
> not able to re-create it.

Hm, the backtrace says the problem is somewhere inside libreadline,
which means it has nothing to do with any SQL-level concepts, but
rather with editing your input text.  Did you do anything unusual
while typing that line of input --- say, recall it from history,
or edit it in a way that you don't usually do, or even mistype
some control character you don't usually type?

Sadly, the visible evidence here doesn't tell us much of anything
about what provoked the crash :-(

FWIW, there's a pretty good chance that this isn't psql's fault
per se, but a libreadline bug.  You might check to see if you can
get a newer readline version installed.

regards, tom lane

[Scot Kreienkamp] 
Nothing unusual that I can think of.  I typed it in by hand, the update 
succeeded, and I was able to recall it and run it again from history with no 
crash.  

This message is intended only for the individual or entity to which it is 
addressed. It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws. If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information. If you have 
received this communication in error, please notify us immediately by e-mail or 
by telephone at the above number. Thank you. 

Re: unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

[Andres Freund]

Hi,

On 2020-03-15 19:23:49 +0100, Karsten Hilbert wrote:
> > /home/marc# pg_dump -p 5432 --username=gm-dbo --dbname=gnumed_v22 
> > --compress=0 --no-sync --format=custom --file=/dev/null
> > pg_dump: Ausgabe des Inhalts der Tabelle »doc_obj« fehlgeschlagen: 
> > PQgetResult() fehlgeschlagen.
> > pg_dump: Fehlermeldung vom Server: ERROR:  unexpected chunk number 2 
> > (expected 0) for toast value 99027 in pg_toast_18536
> > pg_dump: Die Anweisung war: COPY blobs.doc_obj (pk, fk_doc, seq_idx, 
> > comment, fk_intended_reviewer, data, filename) TO stdout;
> 
>   (to note: column "data" is of type BYTEA)
> 
> We have been able to identify the row (there may be more)
> in blobs.doc_obj which leads to the above error.
> 
>   blobs.doc_obj.pk -> 82224
> 
> We have ruled out (?) below-PG hardware problems by a
> successful run of:
> 
>   cp -rv —preserve=all /var/lib/postgresql/9.6  /tmp/

FWIW, I don't think that rules out hardware problems at all. In plenty
cases of corruption you can just end up with corrupted on-disk data
(swapped blocks, zeroed blocks, randomly different values ...).

But obviously it is not at all guaranteed that is the case. Could you
describe the "history" of the database? Replication set up, failovers,
etc?


> Now, what else can we try to address the problem short of
> doing the
> 
>   pg_dump --exclude-table-data=blobs.doc_obj
> 
>   judicious use of COPY-FROM-with-subselect from blobs.doc_obj
> 
>   restore
> 
> dance ?

A plpgsql function that returns the rows one-by-one and catches the
exception is probably your best bet.

It could roughly look something like:

CREATE OR REPLACE FUNCTION salvage(p_tblname regclass)
RETURNS SETOF text
LANGUAGE plpgsql AS
$$
DECLARE
v_row record;
BEGIN
FOR v_row IN EXECUTE 'SELECT * FROM '||p_tblname::text LOOP
   BEGIN
   -- this forces detoasting
   RETURN NEXT v_row::text;
   EXCEPTION WHEN internal_error OR data_corrupted OR index_corrupted THEN
   -- add pkey or something else
   RAISE NOTICE 'failed to return data';
   END;
END LOOP;
END
$$

should work. You can call it like
SELECT (salvaged_rec.rec).* FROM (SELECT salvaged_text::salvage_me FROM 
salvage('salvage_me') AS salvaged_text) AS salvaged_rec(rec)

Greetings,

Andres Freund

Re: unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

[Andres Freund]

Hi,

On 2020-03-15 20:11:18 -0400, Tom Lane wrote:
> Unfortunately, it seems like you can't do that either, short of
> hacking up the backend or writing some custom C code, because the
> executor won't let you open a toast table as result relation :-(.
> I wonder if we should change it to allow that when
> allow_system_table_mods is true?  This isn't the first time we've
> seen people need to be able to do surgery on a toast table.

I'd be mildly in favor. But it's considerably more than just the
executor check that'd need to change. We don't the right thing for toast
relations in plenty places right now, because we just check for
RELKIND_RELATION - which will break junkvars etc.

Greetings,

Andres Freund

Re: unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

[Andres Freund]

Hi,

On 2020-03-16 12:44:53 -0700, Andres Freund wrote:
> On 2020-03-15 20:11:18 -0400, Tom Lane wrote:
> > Unfortunately, it seems like you can't do that either, short of
> > hacking up the backend or writing some custom C code, because the
> > executor won't let you open a toast table as result relation :-(.
> > I wonder if we should change it to allow that when
> > allow_system_table_mods is true?  This isn't the first time we've
> > seen people need to be able to do surgery on a toast table.
> 
> I'd be mildly in favor. But it's considerably more than just the
> executor check that'd need to change. We don't the right thing for toast
> relations in plenty places right now, because we just check for
> RELKIND_RELATION - which will break junkvars etc.

Hm, and I wonder if there could be problems with
HeapTupleSatisfiesToast() too? It doesn't really forsee much DML being
done.

Greetings,

Andres Freund

Re: unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

[Tom Lane]

Andres Freund  writes:
> On 2020-03-16 12:44:53 -0700, Andres Freund wrote:
>> On 2020-03-15 20:11:18 -0400, Tom Lane wrote:
>>> I wonder if we should change it to allow that when
>>> allow_system_table_mods is true?  This isn't the first time we've
>>> seen people need to be able to do surgery on a toast table.

>> I'd be mildly in favor. But it's considerably more than just the
>> executor check that'd need to change. We don't the right thing for toast
>> relations in plenty places right now, because we just check for
>> RELKIND_RELATION - which will break junkvars etc.

> Hm, and I wonder if there could be problems with
> HeapTupleSatisfiesToast() too? It doesn't really forsee much DML being
> done.

We've always allowed people to select from toast tables, so if there
are planner or executor problems with that, I'd think they'd mostly be
bugs that need fixed anyway.  Your point about HeapTupleSatisfiesToast
is better though.

Actually though ... now that I look at the code, I don't understand
why tuple deletion would provoke any errors.  toast_delete_datum()
is not picky about whether it finds consecutive chunk_seq values,
or indeed any matching rows at all.  So now I think that Karsten's
problem traces to writing the query in such a way that something
thinks it needs to fetch the pre-update or pre-delete toasted value.
Or maybe the query as such is fine, and the problem stems from something
like a trigger or logical replication that's trying to fetch the old
value?

regards, tom lane

Re: unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

[Andres Freund]

Hi, 

On March 16, 2020 1:22:18 PM PDT, Tom Lane  wrote:
>Andres Freund  writes:
>> On 2020-03-16 12:44:53 -0700, Andres Freund wrote:
>>> On 2020-03-15 20:11:18 -0400, Tom Lane wrote:
 I wonder if we should change it to allow that when
 allow_system_table_mods is true?  This isn't the first time we've
 seen people need to be able to do surgery on a toast table.
>
>>> I'd be mildly in favor. But it's considerably more than just the
>>> executor check that'd need to change. We don't the right thing for
>toast
>>> relations in plenty places right now, because we just check for
>>> RELKIND_RELATION - which will break junkvars etc.
>
>> Hm, and I wonder if there could be problems with
>> HeapTupleSatisfiesToast() too? It doesn't really forsee much DML
>being
>> done.
>
>We've always allowed people to select from toast tables, so if there
>are planner or executor problems with that, I'd think they'd mostly be
>bugs that need fixed anyway.  Your point about HeapTupleSatisfiesToast
>is better though.

The logic to add/extract junkvars for updated/deleted tables, as well as other 
parts of the modification code paths, weren't exposed so far though.

I've tried allowing updates/deletes before (at least deletes are needed to e.g 
handle duplicate values), I'm fairly confident that the junkvar issue is real.

Andres
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: psql crash on 9.6.16

[Adrian Klaver]

On 3/16/20 12:32 PM, Scot Kreienkamp wrote:



Scot Kreienkamp  writes:

I was using psql to connect to the local database for a simple update this 
morning and it crashed.  Here's what was printed out after the crash.  I was 
not able to re-create it.


Hm, the backtrace says the problem is somewhere inside libreadline,
which means it has nothing to do with any SQL-level concepts, but
rather with editing your input text.  Did you do anything unusual
while typing that line of input --- say, recall it from history,
or edit it in a way that you don't usually do, or even mistype
some control character you don't usually type?

Sadly, the visible evidence here doesn't tell us much of anything
about what provoked the crash :-(

FWIW, there's a pretty good chance that this isn't psql's fault
per se, but a libreadline bug.  You might check to see if you can
get a newer readline version installed.

regards, tom lane

[Scot Kreienkamp]
Nothing unusual that I can think of.  I typed it in by hand, the update 
succeeded, and I was able to recall it and run it again from history with no 
crash.



How did you get to the SELECT statement that ultimately crashed the session?


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Order by and timestamp

[Björn Lundin]

> 16 mars 2020 kl. 17:40 skrev Tom Lane :
> 
> =?utf-8?Q?Bj=C3=B6rn_Lundin?=  writes:
>> 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
> 
> Hmmm ... schema is evidently the same, but locale seemingly not.
> Is it possible this is a locale problem?  I did not look closely
> at the original data, but I'm suddenly wondering if it could be
> explained by misinterpreting the date field order (month-day vs
> day-month).  Check the datestyle settings on both machines.
> 
>   regards, tom lane


But most of the output is correct. Like 95+% 
I found the bad ones by chance



The pi - keeping only data for 1 day then table exported and truncated every 
night 
(So there is not more than say max 60 records any given day)
Was only involved because I use it to describe the table

bnl@pibetbot:~ $ locale
LANG=en_GB.UTF-8
LANGUAGE=
LC_CTYPE="en_GB.UTF-8"
LC_NUMERIC="en_GB.UTF-8"
LC_TIME="en_GB.UTF-8"
LC_COLLATE="en_GB.UTF-8"
LC_MONETARY="en_GB.UTF-8"
LC_MESSAGES="en_GB.UTF-8"
LC_PAPER="en_GB.UTF-8"
LC_NAME="en_GB.UTF-8"
LC_ADDRESS="en_GB.UTF-8"
LC_TELEPHONE="en_GB.UTF-8"
LC_MEASUREMENT="en_GB.UTF-8"
LC_IDENTIFICATION="en_GB.UTF-8"
LC_ALL=

bnl@pibetbot:~ $ psql
Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

bnl=# show datestyle;
 DateStyle 
---
 ISO, DMY



The faulty machine 
Ibm2 - linux debian pg-9.4
bnl@ibm2:~$ locale
LANG=sv_SE.UTF-8
LANGUAGE=
LC_CTYPE="sv_SE.UTF-8"
LC_NUMERIC="sv_SE.UTF-8"
LC_TIME="sv_SE.UTF-8"
LC_COLLATE="sv_SE.UTF-8"
LC_MONETARY="sv_SE.UTF-8"
LC_MESSAGES="sv_SE.UTF-8"
LC_PAPER="sv_SE.UTF-8"
LC_NAME="sv_SE.UTF-8"
LC_ADDRESS="sv_SE.UTF-8"
LC_TELEPHONE="sv_SE.UTF-8"
LC_MEASUREMENT="sv_SE.UTF-8"
LC_IDENTIFICATION="sv_SE.UTF-8"
LC_ALL=

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> show datestyle;
 DateStyle 
---
 ISO, YMD


The machine briefly mentioned with basically same dataset as faulty machine
Linux Ubuntu with pg-10.6 
bnl@tp:~$ locale
LANG=sv_SE.UTF-8
LANGUAGE=
LC_CTYPE="sv_SE.UTF-8"
LC_NUMERIC="sv_SE.UTF-8"
LC_TIME="sv_SE.UTF-8"
LC_COLLATE="sv_SE.UTF-8"
LC_MONETARY="sv_SE.UTF-8"
LC_MESSAGES="sv_SE.UTF-8"
LC_PAPER="sv_SE.UTF-8"
LC_NAME="sv_SE.UTF-8"
LC_ADDRESS="sv_SE.UTF-8"
LC_TELEPHONE="sv_SE.UTF-8"
LC_MEASUREMENT="sv_SE.UTF-8"
LC_IDENTIFICATION="sv_SE.UTF-8"
LC_ALL=

bnl@tp:~$ psql
Timing is on.
AUTOCOMMIT off
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

bnl=# show datestyle;
 DateStyle 
---
 ISO, YMD



Both faulty (ibm2) and correct(tp) are populated with the same pg_dump()- files 
that r-pi produces every nigth

And for completeness - b info from the pi

bnl=# select version();
   version  
  
--
 PostgreSQL 9.6.10 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 
6.3.0-18+rpi1) 6.3.0 20170516, 32-bit
(1 row)

bnl@pibetbot:~ $ uname -a
Linux pibetbot 4.14.79-v7+ #1159 SMP Sun Nov 4 17:50:20 GMT 2018 armv7l 
GNU/Linux

--
Björn Lundin
b.f.lun...@gmail.com

Re: Order by and timestamp

[Björn Lundin]

> 16 mars 2020 kl. 20:26 skrev Adrian Klaver :
> 
> Per Tom's comment, what are the encodings?
Just sent reply to his mail with 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?

I pasted 2 days at pastebin

(with date marking added)
https://pastebin.com/4E24JLEF 



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

Hmm, now that is tricky,
The prod has - as I briefly mentioned - been on the AWS
So I used its pg_dump. But I don’t recall version
In my notes I can see that we started with an ubuntu 12.04 image

But - I always use the pg_dump that belongs to the source database
And psql that belongs to the target database
So insert is 
bnl@ibm2:~/db$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

While pg_dump may have varied through the years
The dump at pastebin gave me no clue of version that created it

Lately (the lsat 2 years or so) it has ben the pg_dump on the pi
bnl@pibetbot:~ $ pg_dump --version
pg_dump (PostgreSQL) 9.6.10

But not for that data sep/oct 2016


> 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 stand corrected

--
Björn Lundin
b.f.lun...@gmail.com

Re: Order by and timestamp

[Adrian Klaver]

On 3/16/20 2:50 PM, Björn Lundin wrote:



16 mars 2020 kl. 20:26 skrev Adrian Klaver >:


Per Tom's comment, what are the encodings?

Just sent reply to his mail with 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?


I pasted 2 days at pastebin

(with date marking added)
https://pastebin.com/4E24JLEF



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


Hmm, now that is tricky,
The prod has - as I briefly mentioned - been on the AWS
So I used its pg_dump. But I don’t recall version
In my notes I can see that we started with an ubuntu 12.04 image

But - I always use the pg_dump that belongs to the source database
And psql that belongs to the target database
So insert is
bnl@ibm2:~/db$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.


Except you are using psql 9.6.15 against a 9.4.15 server.

What happens if you use psql(9.4.15) to do sort query against 9.4.15 server?



While pg_dump may have varied through the years
The dump at pastebin gave me no clue of version that created it

Lately (the lsat 2 years or so) it has ben the pg_dump on the pi
*bnl@pibetbot*:*~ $*pg_dump --version
pg_dump (PostgreSQL) 9.6.10

But not for that data sep/oct 2016


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 stand corrected

--
Björn Lundin
b.f.lun...@gmail.com 







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Order by and timestamp

[Adrian Klaver]

On 3/16/20 2:28 PM, Björn Lundin wrote:



16 mars 2020 kl. 17:40 skrev Tom Lane >:


=?utf-8?Q?Bj=C3=B6rn_Lundin?= > writes:

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


Hmmm ... schema is evidently the same, but locale seemingly not.
Is it possible this is a locale problem?  I did not look closely
at the original data, but I'm suddenly wondering if it could be
explained by misinterpreting the date field order (month-day vs
day-month).  Check the datestyle settings on both machines.

regards, tom lane



But most of the output is correct. Like 95+%
I found the bad ones by chance


A quick look at the dump data in your other post indicates the data 
values themselves are not bad, just that sorting is not correct.


I do not have a 9.4 instance available, so I cannot test below.





The pi - keeping only data for 1 day then table exported and truncated 
every night

(So there is not more than say max 60 records any given day)
Was only involved because I use it to describe the table

*bnl@pibetbot*:*~ $*locale
LANG=en_GB.UTF-8
LANGUAGE=
LC_CTYPE="en_GB.UTF-8"
LC_NUMERIC="en_GB.UTF-8"
LC_TIME="en_GB.UTF-8"
LC_COLLATE="en_GB.UTF-8"
LC_MONETARY="en_GB.UTF-8"
LC_MESSAGES="en_GB.UTF-8"
LC_PAPER="en_GB.UTF-8"
LC_NAME="en_GB.UTF-8"
LC_ADDRESS="en_GB.UTF-8"
LC_TELEPHONE="en_GB.UTF-8"
LC_MEASUREMENT="en_GB.UTF-8"
LC_IDENTIFICATION="en_GB.UTF-8"
LC_ALL=

*bnl@pibetbot*:*~ $*psql
Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

bnl=# show datestyle;
  DateStyle
---
  ISO, DMY



The faulty machine
Ibm2 - linux debian pg-9.4
bnl@ibm2:~$ locale
LANG=sv_SE.UTF-8
LANGUAGE=
LC_CTYPE="sv_SE.UTF-8"
LC_NUMERIC="sv_SE.UTF-8"
LC_TIME="sv_SE.UTF-8"
LC_COLLATE="sv_SE.UTF-8"
LC_MONETARY="sv_SE.UTF-8"
LC_MESSAGES="sv_SE.UTF-8"
LC_PAPER="sv_SE.UTF-8"
LC_NAME="sv_SE.UTF-8"
LC_ADDRESS="sv_SE.UTF-8"
LC_TELEPHONE="sv_SE.UTF-8"
LC_MEASUREMENT="sv_SE.UTF-8"
LC_IDENTIFICATION="sv_SE.UTF-8"
LC_ALL=

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> show datestyle;
  DateStyle
---
  ISO, YMD


The machine briefly mentioned with basically same dataset as faulty machine
Linux Ubuntu with pg-10.6
*bnl@tp*:*~*$ locale
LANG=sv_SE.UTF-8
LANGUAGE=
LC_CTYPE="sv_SE.UTF-8"
LC_NUMERIC="sv_SE.UTF-8"
LC_TIME="sv_SE.UTF-8"
LC_COLLATE="sv_SE.UTF-8"
LC_MONETARY="sv_SE.UTF-8"
LC_MESSAGES="sv_SE.UTF-8"
LC_PAPER="sv_SE.UTF-8"
LC_NAME="sv_SE.UTF-8"
LC_ADDRESS="sv_SE.UTF-8"
LC_TELEPHONE="sv_SE.UTF-8"
LC_MEASUREMENT="sv_SE.UTF-8"
LC_IDENTIFICATION="sv_SE.UTF-8"
LC_ALL=

*bnl@tp*:*~*$ psql
Timing is on.
AUTOCOMMIT off
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

bnl=# show datestyle;
  DateStyle
---
  ISO, YMD



Both faulty (ibm2) and correct(tp) are populated with the same 
pg_dump()- files that r-pi produces every nigth


And for completeness - b info from the pi

bnl=# select version();
                                                        version
--
  PostgreSQL 9.6.10 on armv7l-unknown-linux-gnueabihf, compiled by gcc 
(Raspbian 6.3.0-18+rpi1) 6.3.0 20170516, 32-bit

(1 row)

*bnl@pibetbot*:*~ $*uname -a
Linux pibetbot 4.14.79-v7+ #1159 SMP Sun Nov 4 17:50:20 GMT 2018 armv7l 
GNU/Linux


--
Björn Lundin
b.f.lun...@gmail.com 







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Order by and timestamp

[Adrian Klaver]

On 3/16/20 2:50 PM, Björn Lundin wrote:



But not for that data sep/oct 2016


Had a thought, what if on the ibm2 machine you do:

UPDATE amarkets SET  startts = '2016-09-30 13:00:00' WHERE
 marketid = 1.127278857;

And then rerun:

select * from amarkets order by startts;





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 stand corrected

--
Björn Lundin
b.f.lun...@gmail.com 







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Invalid byte sequence errors on DB restore

[Samuel Smith]

On 3/16/20 9:33 AM, Adrian Klaver wrote:

On 3/15/20 9:18 PM, Samuel Smith wrote:
My current DB backup routine is just to call pg_dump and pipe to gzip. 
We recently started to get a failure to restore (which is basically 
just using psql -f on the pg_dump file) with the following errors:


invalid byte sequence for encoding "UTF8": 0xa0
  and
invalid byte sequence for encoding "UTF8": 0xd7 0x20


This is on a pg 9.2.24 instance. Any tips to troubleshoot?


What are the locale and encodings set to for the instance and databases 
in it?




Regards,
Samuel Smith








The server is in UTF8. The file made with pg_dump used 'SQL_ASCII', but 
setting it to UTF8 (via SET client_encoding ) did not help either. 
Having the pg_dump encoding set to 'latin1' seems to allow the file 
created it by it to be loaded via psql -f and everything seems to work. 
Is there any bad side to setting the encoding on pg_dump to latin1?



For the record, the problem characters are:
https://www.htmlsymbols.xyz/unicode/U+00D7
and
https://www.htmlsymbols.xyz/unicode/U+00A0

But those characters were in many places and not all were issues. They 
only fail depending on the characters that precede it which makes it 
complicated.


Thanks,
Samuel Smith