AnswerBun.com

Why does pg_dumpall use the wrong encoding in Ubuntu 16.04 / Postgres 9.5?

Database Administrators Asked by Thomas R. on September 5, 2020

I cannot run a "pg_upgradecluster" because there are problems with pg_dumpall. I have set the encoding to UTF8 everywhere on the server but pg_dumpall always uses

"SET client_encoding = ‘LATIN1’;"

. Where does this "LATIN1" come from?

postgres=# show server_encoding; show client_encoding;


server_encoding
-----------------
 UTF8
(1 row)

 client_encoding
-----------------
 UTF8
(1 row)

[email protected]:~$ locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

[email protected]:~$ env
SHELL=/bin/bash
TERM=xterm
USER=postgres
MAIL=/var/mail/postgres
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin
PWD=/var/lib/postgresql
LANG=en_US.UTF-8
SHLVL=1
HOME=/var/lib/postgresql
LOGNAME=postgres
XDG_DATA_DIRS=/usr/local/share:/usr/share:/var/lib/snapd/desktop
_=/usr/bin/env

[email protected]:~$ cat /etc/default/locale
#  File generated by update-locale
LANG=en_US.UTF-8
#LANGUAGE="en_US:en"

Because of the wrong encoding I get errors:

pg_dump: Dumping the contents of table "TBLDATEN" failed:
PQgetResult() failed. pg_dump: Error message from server: ERROR:
character with byte sequence 0xe2 0x80 0xa6 in encoding "UTF8" has no
equivalent in encoding "LATIN1"

I have the same problem with pg_dump but then I can use

pg_dump -E UTF8

to set the correct encoding.


[email protected]:~$ psql -l | grep LATIN
[email protected]:~$ psql -l
                                         List of databases
         Name         |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
----------------------+-------------+----------+-------------+-------------+------------------------
 Liste1               | cpl_user    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste2               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste3               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste4               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste5               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste6               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
...

Add your own answers!

Related Questions

SqlPackage does not pick up variables from profile

3  Asked on December 24, 2021 by thx-1138

 

This simple mysql query is taking 2-3 seconds

2  Asked on December 24, 2021 by mikkergp

     

Powershell Invoke-Sqlcmd Replacing £ with?

1  Asked on December 24, 2021 by dennis-finke

   

Upgrade Postgres 9.6 to 12

0  Asked on December 24, 2021 by doug-p

 

Stored procedure for adding cascade

1  Asked on December 22, 2021 by user73639

     

MySQL Optimization Guidance Needed

3  Asked on December 22, 2021 by enyceexdanny

   

Sybase IQ cache database result

1  Asked on December 21, 2021 by daria

   

Help Outputing MySQL General Log to Named Pipe

2  Asked on December 21, 2021 by widdlewabbit

 

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP