Re: PG12 autovac issues

[Michael Paquier]

On Fri, Mar 27, 2020 at 05:10:03PM -0500, Justin King wrote:
> Sounds great.  I will email you directly with a link!

Thanks.  From the logs, the infinite loop on which autovacuum jobs are
stuck is clear.  We have a repetitive number of anti-wraparound and
non-aggressive jobs happening for 7 shared relations every 5 seconds
or so and autovacuum is not able to do something else:
pg_auth_members
pg_authid
pg_database
pg_replication_origin
pg_shseclabel
pg_subscription
pg_tablespace

However I have found a correlation between all the tests I have done
on my own laptop, all the tests Julien and I have done in our internal
company buildfarm, and your logs.  We have 10 shared catalogs in the
system and the problem does not happen with the three following ones,
unable to trigger an anti-wraparound non-aggressive job:
pg_db_role_setting
pg_shdepend
pg_shdescription

Not sure yet what that is, but it is hard to believe that it is just a
coincidence.

> This is encouraging. As I mentioned, we have a workaround in place for
> the moment, but don't hesitate if you need anything else from me.
> Thanks for jumping in on the thread, it was nice validation to know
> that I wasn't the only one seeing the issue!

Yes.  We have been able to confirm that 2aa6e33 is a direct cause of
your problem.
--
Michael


signature.asc
Description: PGP signature

Re: PG12 autovac issues

[Michael Paquier]

On Fri, Mar 27, 2020 at 08:23:03PM +0100, Julien Rouhaud wrote:
> FTR we reached the 200M transaxtion earlier, and I can see multiple logs of 
> the
> form "automatic vacuum to prevent wraparound", so non-aggressive 
> antiwraparound
> autovacuum, all on shared relations.

Thanks Julien for sharing the information with the community.  Note
that three shared relations did not run such jobs though.

> As those vacuum weren't skipped, autovacuum didn't get stuck in a loop on 
> those
> and continue its work normally.  This happened ~ 4h ago, didn't ocurred again
> while the 200M threshold was reached again multiple time.

As we have confirmed that 2aa6e33 is actually the origin of the
problem, I am planning to revert it at the beginning of next week, and
then dig more into the origin of those anti-wraparound and
non-aggressive jobs happening for a portion of the shared relations.
Andres has started a thread about the problem on -hackers here:
https://www.postgresql.org/message-id/20200323235036.6pje6usrjjx22...@alap3.anarazel.de

And I'll follow up there with anything new I find.  Please let me know
if there are any objections with the revert though, this will address
the problem reported by Justin.
--
Michael


signature.asc
Description: PGP signature

Re: \COPY to accept non UTF-8 chars in CHAR columns

[Matthias Apitz]

El día sábado, marzo 28, 2020 a las 09:40:30a. m. +1300, Thomas Munro escribió:

> On Sat, Mar 28, 2020 at 4:46 AM Tom Lane  wrote:
> > Matthias Apitz  writes:
> > > In short, it there a way to let \COPY accept such broken ISO bytes, just
> > > complaining about, but not stopping the insert of the row?
> >
> > No.  We don't particularly believe in the utility of invalid data.
> >
> > If you don't actually care about what encoding your data is in,
> > you could use SQL_ASCII as the database "encoding" and thereby
> > disable all UTF8-specific behavior.  Otherwise, maybe this conversion
> > is a good time to clean up the mess?
> 
> Something like this approach might be useful for fixing the CSV file:
> 
> https://codereview.stackexchange.com/questions/185821/convert-a-mix-of-latin-1-and-utf-8-to-proper-utf-8
> 
> I haven't tested that program but it looks like the right sort of
> approach; I remember writing similar logic to untangle the strange
> mixtures of Latin 1, Windows 1252, and UTF-8  that late 90s browsers
> used to send.  That sort of approach can't fix every theoretical
> problem (some valid Latin1 sequences are also valid UTF-8 sequences)
> but it's doable with text in European languages.

Thomas,

This is a very good finding, thanks for this.

I played around a bit with the original code, and tested some
modifications to fit better in our problem... It works fine:

- it translates any ISO char, for example 0xfc (German Umlaut ü in ISO), into 
the
  correct UTF-8 coding 0xc3bc:

  perl -e 'print pack("H*", "4040fc4040")' | ./convert2properUTF8 | hexdump -C
    40 40 c3 bc 40 40 |@@..@@|
  0006

- it translates a situation where 0xc3bc (German Umlaut ü in UTF-8
  coding) was broken into two columns, one terminating in 0xc3 and the 2nd
  row starting with 0xbc; this would give:

  perl -e 'print pack("H*", "c3")' | ./convert2properUTF8 | hexdump -C
    c3 83 |..|
  0002
  perl -e 'print pack("H*", "bc40")' | ./convert2properUTF8 | hexdump -C
    c2 bc 40  |..@|
  0003

  i.e. 0xc3 is translated to 0xc383 and the 2nd half, the 0xbc to
  0xc2bc, both translations have nothing to do with the original split 0xc3bc, 
and
  perhaps in this case it would be better to spill out a blank 0x40 for
  each of the bytes which formed the 0xc3bc.

But this we will discuss here and align the code to our use cases.

Thanks again

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

PostegreSQL 9.2 to 9.6

[Lucas Possamai]

Hi guys.

We need to upgrade (asap) our PostgreSQL 9.2 cluster. We run our cluster on
AWS EC2 instances, and want to upgrade to RDS 9.6. Once in RDS, it makes
easier for us to major upgrade to 11.

Since this upgrade must be done with minimal downtime, my original idea was
to use Bucardo .

Does anybody have any better idea? please share.
Just wanna make sure I'm going on the right path.

Cluster size is 2TB.

Cheers
Lucas

Re: \COPY to accept non UTF-8 chars in CHAR columns

[Andrew Gierth]

> "Matthias" == Matthias Apitz  writes:

 Matthias>   i.e. 0xc3 is translated to 0xc383 and the 2nd half, the
 Matthias>   0xbc to 0xc2bc, both translations have nothing to do with
 Matthias>   the original split 0xc3bc, and perhaps in this case it
 Matthias>   would be better to spill out a blank 0x40 for each of the
 Matthias>   bytes which formed the 0xc3bc.

If the only malformed sequences are there as a result of splitting up
valid sequences, then you could do something like convert all invalid
sequences to (sequences of) noncharacters, then once the data is
imported, fix it up by adjusting how the data is split and regenerating
the correct sequence (assuming your application allows this).

For example you could encode an arbitrary byte xy as a sequence of two
codepoints U+FDDx U+FDEy (the range FDD0-FDEF are all defined as
noncharacters).

-- 
Andrew (irc:RhodiumToad)

Postgres 12 backup in 32 bit windows client

[Andrus]

Hi!

Postgres 12 is installed in Debian 10 server.
Client computer is running 32 bit windows.

pg_dump.exe which can downloaded as part of Postgres 12 windows installation 
is only 64 bit version.


How to make backup from Postgres 12  in 32 bit Microsoft windows computer ?
psqlodbc is used for data access.
Maybe there is 32 -bit version of pg_dump.exe.

Andrus. 

Re: Postgres 12 backup in 32 bit windows client

[Adrian Klaver]

On 3/28/20 8:18 AM, Andrus wrote:

Hi!

Postgres 12 is installed in Debian 10 server.
Client computer is running 32 bit windows.

pg_dump.exe which can downloaded as part of Postgres 12 windows 
installation is only 64 bit version.


How to make backup from Postgres 12  in 32 bit Microsoft windows computer ?
psqlodbc is used for data access.
Maybe there is 32 -bit version of pg_dump.exe.


The newest I can see is for 10.12:

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

and you would not want to use that to dump a version 12 database.

What are you going to do with this backup?

Do you have shell access to the Debian machine in order to do the backup 
there?




Andrus.





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

Re: PostegreSQL 9.2 to 9.6

[Enrico Pirozzi]

Hi Lucas I've made it,  from 9.2 to 12.2 using slony and it worked very
well :)

Enrico

Il sab 28 mar 2020, 13:29 Lucas Possamai  ha scritto:

> Hi guys.
>
> We need to upgrade (asap) our PostgreSQL 9.2 cluster. We run our cluster
> on AWS EC2 instances, and want to upgrade to RDS 9.6. Once in RDS, it makes
> easier for us to major upgrade to 11.
>
> Since this upgrade must be done with minimal downtime, my original idea
> was to use Bucardo .
>
> Does anybody have any better idea? please share.
> Just wanna make sure I'm going on the right path.
>
> Cluster size is 2TB.
>
> Cheers
> Lucas
>

Re: Postgres 12 backup in 32 bit windows client

[Andrus]

Hi!


What are you going to do with this backup?


If server disk crashes, it can used to restore data.

Do you have shell access to the Debian machine in order to do the backup 
there?


I have but my application user in 32bit windows does not have.
I want to allow 32 bit windows user to make backup from 5432 port directly 
to her computer C: drive.


If Postgres 12 32bit pg_dump is not available maybe it is possible to create 
stored procedure which invokes pg_dump in server and returns .backup file as 
bytea value to client:


select run('pg_dump mydatabase')

Andrus.


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

Re: Postgres 12 backup in 32 bit windows client

[Ron]

On 3/28/20 12:57 PM, Andrus wrote:

Hi!


What are you going to do with this backup?


If server disk crashes, it can used to restore data.

Do you have shell access to the Debian machine in order to do the backup 
there?


I have but my application user in 32bit windows does not have.
I want to allow 32 bit windows user to make backup from 5432 port directly 
to her computer C: drive.


If Postgres 12 32bit pg_dump is not available maybe it is possible to 
create stored procedure which invokes pg_dump in server and returns 
.backup file as bytea value to client:


select run('pg_dump mydatabase')


What about you make the backup on the db server with cron and then have her 
copy the file to her computer using WinSCP?


--
Angular momentum makes the world go 'round.

Re: PG12 autovac issues

[Andres Freund]

Hi,

On 2020-03-28 17:47:19 +0900, Michael Paquier wrote:
> On Fri, Mar 27, 2020 at 05:10:03PM -0500, Justin King wrote:
> > This is encouraging. As I mentioned, we have a workaround in place for
> > the moment, but don't hesitate if you need anything else from me.
> > Thanks for jumping in on the thread, it was nice validation to know
> > that I wasn't the only one seeing the issue!
> 
> Yes.  We have been able to confirm that 2aa6e33 is a direct cause of
> your problem.

I assume you're still trying to track the actual cause of the problem
further?

Greetings,

Andres Freund

could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

[Andrus]

Hi!

Postgres 12 database dump is created in Debian 10 using pg_dump .

Trying to restore it in Windows 10  using

pg_restore --clean --create --if-exists --dbname=postgres --jobs=8 --no-password 
mydb.backup


produces strange message

pg_restore: WARNING:  could not determine encoding for locale "et_EE.UTF-8": 
codeset is "CPUTF-8"


How to fix this ?

Debian and Windows computer have same settings:

Latest Postgres 12  is used
OS and database locales are  Estonian
Database encoding is UTF-8

Andrus. 

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

[Adrian Klaver]

On 3/28/20 2:39 PM, Andrus wrote:

Hi!

Postgres 12 database dump is created in Debian 10 using pg_dump .

Trying to restore it in Windows 10  using

pg_restore --clean --create --if-exists --dbname=postgres --jobs=8 
--no-password mydb.backup


produces strange message

pg_restore: WARNING:  could not determine encoding for locale 
"et_EE.UTF-8": codeset is "CPUTF-8"


In the Debian Postgres instance in psql what does \l show for the databases?

In the Windows 10 command prompt what does systeminfo show?




How to fix this ?

Debian and Windows computer have same settings:

Latest Postgres 12  is used
OS and database locales are  Estonian
Database encoding is UTF-8

Andrus.





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

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

[Andrus]

Hi!

In the Debian Postgres instance in psql what does \l show for the 
databases?


#psql namm postgres
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.

namm=# \l namm
 List of databases
Name |   Owner| Encoding |   Collate   |Ctype| Access 
privileges

--++--+-+-+---
namm | namm_owner | UTF8 | et_EE.UTF-8 | et_EE.UTF-8 | =Tc/namm_owner 
+
 ||  | | | 
namm_owner=CTc/namm_owner

(1 row)


In the Windows 10 command prompt what does systeminfo show?


Host Name: SERVER2
OS Name:   Microsoft Windows 10 Pro
OS Version:10.0.18363 N/A Build 18363
OS Manufacturer:   Microsoft Corporation
OS Configuration:  Standalone Workstation
OS Build Type: Multiprocessor Free
Registered Owner:  Windows User
Registered Organization:
Product ID:00330-70008-16217-AAOEM
Original Install Date: 05.09.2019, 9:16:41
System Boot Time:  28.03.2020, 11:05:23
System Manufacturer:   Gigabyte Technology Co., Ltd.
System Model:  Q270M-D3H
System Type:   x64-based PC
Processor(s):  1 Processor(s) Installed.
  [01]: Intel64 Family 6 Model 158 Stepping 9 
GenuineIntel ~3601 Mhz

BIOS Version:  American Megatrends Inc. F1, 09.01.2017
Windows Directory: C:\WINDOWS
System Directory:  C:\WINDOWS\system32
Boot Device:   \Device\HarddiskVolume3
System Locale: et;Eesti
Input Locale:  et;Eesti
Time Zone: (UTC+02:00) Helsingi, Kiiev, Riia, Sofia, 
Tallinn, Vilnius

Total Physical Memory: 16 286 MB
Available Physical Memory: 12 032 MB
Virtual Memory: Max Size:  18 718 MB
Virtual Memory: Available: 14 867 MB
Virtual Memory: In Use:3 851 MB
Page File Location(s): C:\pagefile.sys
Domain:WORKGROUP
Logon Server:  \\SERVER2
Hotfix(s): 18 Hotfix(s) Installed.
  [01]: KB4534132
  [02]: KB4497165
  [03]: KB4498523
  [04]: KB4503308
  [05]: KB4515383
  [06]: KB4515530
  [07]: KB4516115
  [08]: KB4517245
  [09]: KB4520390
  [10]: KB4521863
  [11]: KB4524244
  [12]: KB4524569
  [13]: KB4528759
  [14]: KB4532441
  [15]: KB4537759
  [16]: KB4538674
  [17]: KB4541338
  [18]: KB4551762
Network Card(s):   2 NIC(s) Installed.
  [01]: TAP-Windows Adapter V9
Connection Name: Ethernet 4
Status:  Media disconnected
  [02]: Intel(R) Ethernet Connection (2) I219-LM
Connection Name: Ethernet 3
DHCP Enabled:Yes
DHCP Server: 192.168.91.1
IP address(es)
[01]: 192.168.91.154
[02]: fe80::94d:b1c:3945:bc8a
[03]: 
2001:7d0:4c83:4c80:257f:b077:e1f7:21e1

[04]: 2001:7d0:4c83:4c80:94d:b1c:3945:bc8a
Hyper-V Requirements:  VM Monitor Mode Extensions: Yes
  Virtualization Enabled In Firmware: Yes
  Second Level Address Translation: Yes
  Data Execution Prevention Available: Yes

Andrus.

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

[Adrian Klaver]

On 3/28/20 3:31 PM, Andrus wrote:

Hi!

In the Debian Postgres instance in psql what does \l show for the 
databases?


#psql namm postgres
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.

namm=# \l namm
  List of databases
Name |   Owner    | Encoding |   Collate   |    Ctype    | Access 
privileges
--++--+-+-+--- 


namm | namm_owner | UTF8 | et_EE.UTF-8 | et_EE.UTF-8 | =Tc/namm_owner +
  |    |  | | | 
namm_owner=CTc/namm_owner

(1 row)


In the Windows 10 command prompt what does systeminfo show?





System Locale: et;Eesti
Input Locale:  et;Eesti


Hmm, I was expecting to see et_EE though I will admit to not truly 
understanding how Windows does locales.


I should have asked earlier, in the Postgres instance on Windows what 
does \l show for template0?




Andrus.




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

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

[Tom Lane]

"Andrus"  writes:
> Postgres 12 database dump is created in Debian 10 using pg_dump .
> Trying to restore it in Windows 10  using
> pg_restore --clean --create --if-exists --dbname=postgres --jobs=8 
> --no-password 
> mydb.backup
> produces strange message
> pg_restore: WARNING:  could not determine encoding for locale "et_EE.UTF-8": 
> codeset is "CPUTF-8"

> How to fix this ?

Generally speaking, locale names from Unix systems won't work at all on
Windows.  You need to create the database manually with whatever seems
to be the closest Windows locale match, and then restore its contents
without using --create.

(I do agree that that message isn't the most helpful thing.  It looks
like chklocale.c is overoptimistically assuming that what it's handed
is valid, even if GetLocaleInfoEx says it isn't.)

regards, tom lane

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

[Tom Lane]

I wrote:
> (I do agree that that message isn't the most helpful thing.  It looks
> like chklocale.c is overoptimistically assuming that what it's handed
> is valid, even if GetLocaleInfoEx says it isn't.)

After further digging, that optimism isn't *completely* without
foundation; it seems to be based on the fact that we know setlocale()
thought the locale string was OK.  Which is interesting --- apparently
Microsoft is more willing to accept Unix-style locale names than
I thought.  But they couldn't be bothered to make GetLocaleInfoEx()
and setlocale() take the same set of strings ...

regards, tom lane