Re: Invalid byte sequence errors on DB restore

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

Re: Order by and timestamp

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

Re: Order by and timestamp

> 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. >> *continuousl

Re: Order by and timestamp

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

Re: Streaming replication - 11.5

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

Re: Order by and timestamp

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

Formatting output (was: Order by and timestamp)

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 prett

Mixed Locales and Upgrading

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 cl

Re: Invalid byte sequence errors on DB restore

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

Re: psql crash on 9.6.16

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?

Re: Order by and timestamp

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

Re: Order by and timestamp

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

Re: Mixed Locales and Upgrading

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

Re: Streaming replication - 11.5

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. I

Re: psql crash on 9.6.16

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

Re: Order by and timestamp

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 (basicall

Re: Order by and timestamp

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

Re: Order by and timestamp

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

Re: Order by and timestamp

> 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

Re: Order by and timestamp

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

Re: Invalid byte sequence errors on DB restore

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 err

Re: Order by and timestamp

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 yo

Re: Order by and timestamp

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

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

RE: psql crash on 9.6.16

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 package

RE: psql crash on 9.6.16

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 Kr

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

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

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

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 tha

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

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 r

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

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

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

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 firs

Re: psql crash on 9.6.16

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 somew

Re: Order by and timestamp

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

Re: Order by and timestamp

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

Re: Order by and timestamp

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/

Re: Order by and timestamp

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 mac

Re: Order by and timestamp

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, otherw

Re: Invalid byte sequence errors on DB restore

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: invali