How to convert postgresql dump after Powershell encoding?

Question

I use on Windows Server 2019 scheduled job as

powershell & 'C:\Program Files\PostgreSQL\12.3-7.1C\bin\pg_dump.exe' basename > bn.bak

And after that I tried to import bn.bak on other base, but Postgre said me that

"invalid byte sequence for encoding "UTF8": 0xff"

File Util gave

Little-endian UTF-16 Unicode text, with CRLF line terminators

So, I understand that Powershell 5.1 use ">" as Out-File and use default Encoding UTF-16LE. But how can I import that file on my base? I tried this ways:

iconv -f UTF-16LE -t UTF-8 bn.bak -o conv1.bak
get Powershell 7.0 and use "Get-Content .\bn.bak -Encoding utf8NoBom"

enter image description here

But all this ways gave me same thing - all latin symbols and numbers shows normal, all Cyrillic symbols shows cracked.

Can this dump still be saved? :)

jsch · Accepted Answer · 2022-03-15 14:04:28Z

1

If you can control how the dump file is created you can use the -f argument of pgdump to write to a file instead of using >. And control the encoding with the -E option.

Solution found here: https://dba.stackexchange.com/questions/281119/postgresql-pg-dump-e-encoding-option-not-working

Postgres manual pgdump https://www.postgresql.org/docs/current/app-pgdump.html

EDIT: added some extra links/info on pg_dump

answered Mar 15, 2022 at 14:04

jsch

1264 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clueless_captain · Accepted Answer · 2021-07-05 13:46:54Z

0

Faced this same problem all day; there's a simple trick.

Open the file in notepad++ go to encoding and change it to UTF8 and save. Filesize should be halved now. This worked for me.

I tried to make an UTF8 export, with this command:

.\pg_dump.exe -U postgres -h remotehost -d dbName --encoding utf8 > "C:\Users\me\Documents\project\Migration\backup.sql"

but that didn't work as intended for me.

answered Jul 5, 2021 at 13:46

Clueless_captain

4403 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to convert postgresql dump after Powershell encoding?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related