Thread: pg_dump seems to be broken in regards to the "--exclude-table-data" option on Windows.

After taking a longer break from my problem, I have now made a fresh, clean, scientifically conducted experiment in order to truly get to the bottom of this annoying problem once and for all.

First, I set cmd.exe to use Unicode, just to be sure:

C:\pg_dump_test>chcp 65001

Active code page: 65001

pg_dump: creating CONSTRAINT "Test schäma.Test täble Test täble_pkey"

= WORKS. The dump was successful. (In spite of weird output chars.)

C:\pg_dump_test>pg_dump --format plain --verbose --file "testdump.txt" --exclude-table-data="Test schäma.Test täble" --host="localhost" --port="5432" --username="postgres" --dbname="test"

pg_dump: last built-in OID is 16383

pg_dump: [archiver (db)] query failed: ERROR:  invalid byte sequence for encoding "UTF8": 0xe4 0x62 0x6c

pg_dump: [archiver (db)] query was: SELECT c.oid

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n

ON n.oid OPERATOR(pg_catalog.=) c.relnamespace

WHERE c.relkind OPERATOR(pg_catalog.=) ANY

(array['r', 'S', 'v', 'm', 'f', 'p'])

  AND c.relname OPERATOR(pg_catalog.~) '^(test täble)$'

  AND n.nspname OPERATOR(pg_catalog.~) '^(test schäma)$'

= FAILED. The dump was aborted with these nonsensical errors.

Finally, I tried the same command again like this:

I looked everywhere for some kind of "client-encoding" option in the pg_dump manual, but there is no such thing. The only thing I can think of is that the client's encoding (that is, pg_dump) is for some reason not set to "UTF8" even though that's the encoding of the "test" database which I'm connecting to.

On Fri, Jul 24, 2020 at 8:14 AM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thursday, July 23, 2020, <tutiluren@tutanota.com> wrote:

After taking a longer break from my problem, I have now made a fresh, clean, scientifically conducted experiment in order to truly get to the bottom of this annoying problem once and for all.

First, I set cmd.exe to use Unicode, just to be sure:

C:\pg_dump_test>chcp 65001

Active code page: 65001

pg_dump: creating CONSTRAINT "Test schäma.Test täble Test täble_pkey"

= WORKS. The dump was successful. (In spite of weird output chars.)

C:\pg_dump_test>pg_dump --format plain --verbose --file "testdump.txt" --exclude-table-data="Test schäma.Test täble" --host="localhost" --port="5432" --username="postgres" --dbname="test"

pg_dump: last built-in OID is 16383

pg_dump: [archiver (db)] query failed: ERROR:  invalid byte sequence for encoding "UTF8": 0xe4 0x62 0x6c

pg_dump: [archiver (db)] query was: SELECT c.oid

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n

ON n.oid OPERATOR(pg_catalog.=) c.relnamespace

WHERE c.relkind OPERATOR(pg_catalog.=) ANY

(array['r', 'S', 'v', 'm', 'f', 'p'])

  AND c.relname OPERATOR(pg_catalog.~) '^(test täble)$'

  AND n.nspname OPERATOR(pg_catalog.~) '^(test schäma)$'

= FAILED. The dump was aborted with these nonsensical errors.

Finally, I tried the same command again like this:

I looked everywhere for some kind of "client-encoding" option in the pg_dump manual, but there is no such thing. The only thing I can think of is that the client's encoding (that is, pg_dump) is for some reason not set to "UTF8" even though that's the encoding of the "test" database which I'm connecting to.

The main thing here is this isn’t properly considered a bug in pg_dump but rather with most likely any client command run in the cmd.exe shell.

The problem is coming from CMD and its UTF8 support, but I guess I am not making a great job in convincing you. I will try to explain it through an example:

1. Start a CMD using "CMD /U", and set the code page to UTF8:

C:\postgres>chcp 65001

Active code page: 65001

2.  Create a database named 'test', an schema named "Ö" and table named "Ä":

C:\postgres>psql -c "create database test" postgres

CREATE DATABASE

C:\postgres>psql -c "\l test" postgres

                                              List of databases

 Name |    Owner     | Encoding |          Collate           |           Ctype            | Access privileges

------+--------------+----------+----------------------------+----------------------------+-------------------

 test | postgres     | UTF8     | English_United States.1252 | English_United States.1252 |

(1 row)

C:\postgres>psql -c "create schema "Ö"" test

CREATE SCHEMA

C:\postgres>psql -c " create table "Ö"."Ä" (c1 char(1))" test

CREATE TABLE

3. Test the table and check your client encoding:

C:\postgres>psql -c "select * from "Ö"."Ä"" test

 c1

----

(0 rows)

C:\postgres>psql -c "show client_encoding" test

 client_encoding

-----------------

 WIN1252

(1 row)

4. Change the client encoding to UTF8 and repeat the test:

C:\postgres>set PGCLIENTENCODING=UTF8

C:\postgres>psql -c "show client_encoding" test

 client_encoding

-----------------

 UTF8

(1 row)

C:\postgres>psql -c "select * from "Ö"."Ä"" test

2020-07-24 11:25:01.710 CEST [1452] ERROR:  invalid byte sequence for encoding "UTF8": 0xd6 0x2e

ERROR:  invalid byte sequence for encoding "UTF8": 0xd6 0x2e

The reason why this happens is because the limited CMD's UTF8 support, 'chcp' only changes the code page of the displayed characters, shell commands, piping, redirection and most commands are still ANSI only.

Does explicitly setting 'PGCLIENTENCODING=WIN1252' on your shell solves the issue?

Regards,

Juan José Santamaría Flecha

1. Thanks for putting in the effort to help.
   
2. Thanks for teaching me about the "cmd.exe /U" option; I never even knew that it existed and was always annoyed by how Unicode characters looked messed up even though Unicode worked (outside of this case).
   
3. As a matter of fact, 'PGCLIENTENCODING=WIN1252' causes it to stop failing and does dump the database. To make 100% sure that this was it, I set 'PGCLIENTENCODING=UTF8' immediately afterwards and repeated the command: failed. Then changed it back to 'PGCLIENTENCODING=WIN1252' once again and repeated: worked. So it's definitely due to this, which makes zero sense since the database *HAS* the encoding UTF8!
   
4. (Lots of too-early negative conclusions by me removed here.) It turns out that, using your =WIN1252 hack, and this specific version of the exclude syntax: --exclude-table-data="\"Test schäma\".\"Test täble\"" ... it works. It dumps correctly and ignores that specific table in that schema.
   
5. If anyone responsible for the documentation for PostgreSQL is reading this, please update it to make clear what syntax one is supposed to use for the confusing --exclude-table-data option!
   
6. Thanks for finding a workaround, but I sure don't like the fact that I have to lie to PG about the encoding to be able to dump my database. Who knows what kind of unintended consequences this might have? Will it dump the DB's contents incorrectly? Will it remove/garble any non-WIN1252-compatible data? Will it corrupt my backups? It feels horribly wrong to set the wrong encoding like this, when *everything* I do/use works in UTF-8...
   

1. If anyone responsible for the documentation for PostgreSQL is reading this, please update it to make clear what syntax one is supposed to use for the confusing --exclude-table-data option!

On 2020-Jul-25, tutiluren@tutanota.com wrote:

> Although I now have a work-around, which *appears* to work, I'm still utterly confused as to *why* it works. To be
clear:why is it that setting the environment variable 'PGCLIENTENCODING' to 'WIN1252' makes the pg_dump succeed when
everything,including the database I'm trying to dump, uses UTF8? The only thing not using UTF8 is the "postgres"
database(which indeed uses 'WIN1252', certainly not out of my active choice), but that's not involved here... My
commandconnects to my testdb, does it not?
 

What doesn't use UTF8 is your terminal.  So when you enter the accented
letter, you're not actually sending the UTF8 character you think you're
sending, but instead a Win1252 character which matches nothing.

When you use "cmd /U", then your terminal *is* UTF8, so the character is
correct and things work.

You can further test this by setting log_statements=all in
postgresql.conf, sending a reload signal to postgres, and then rerunning
the pg_dump command.  When run in the non-UTF8 terminal, the server log
file will show different garbage than when run in the UTF8 terminal.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

tutiluren:

Since at least part of your struggle has been with cmd.exe not handling UTF8 well, I wonder if you have considered
WindowsTerminal, which purports to handle UTF8 better?
 

https://www.microsoft.com/en-us/p/windows-terminal/9n0dx20hk701

I'm not saying it will solve everything, but it might at least remove one layer of confusion.

Graham

At 7/25/2020 06:44 PM, tutiluren@tutanota.com wrote:
>I don't know if anyone is getting or reading my messages, but I just want to make it clear that my problem has 
>*not* been resolved, and I suspect more strongly than ever that pg_dump is at fault.
>
>I don't know what I could possibly add to what I've already posted describing my issue, so I probably shouldn't repeat
myselfany further now, but simply hope that somebody will read my previous posts and be able to help me.
 
>
>It truly looks as if I have to forget that the "--exclude*" options exist at all...

Since at least part of your struggle has been with cmd.exe not handling UTF8 well, I wonder if you have considered Windows Terminal, which purports to handle UTF8 better?

https://www.microsoft.com/en-us/p/windows-terminal/9n0dx20hk701

I'm not saying it will solve everything, but it might at least remove one layer of confusion.

Also, diff-ing two files with mismatched encodings is not going to work as expected.

If you see this error, then PGCLIENTENCODING=1252 will not be a viable workaround

activate the beta UTF8 support of the Windows Regional settings

or access your database from a system with true UTF8 terminal support.

Ten years ago my first reaction would be to download msys and try the command there.  It looks, however, that the development stalled around 2008: http://www.mingw.org/wiki/MSYS

Now I read that a second generation of that is available, but haven't tried it: https://www.msys2.org/

You'll have to quote the special characters differently, but that way of quoting will be familiar to the majority of folks here, I guess.

Oleksandr Shulgin <oleksandr.shulgin@zalando.de> writes:
> Didn't you mention that once you could overcome the runtime errors you
> still have seen some unexpected output from pg_dump?  A script that one can
> download and run on their (Windows) system would help to confirm the
> problem (if it does reproduce on others' systems) and speed up diagnosys
> and a fix, if required.

My understanding of what's happening here (admittedly, I've not been
paying really close attention) is that some component is misprocessing
non-ASCII data as it's being typed into the pg_dump shell command.
That being the case, I'm not sure that a pre-written shell script could
reproduce the issue accurately.  A script to create the initial database
contents would surely be helpful, but the steps after that might need to
be more like "type this into the terminal, and then you will get X instead
of Y".

            regards, tom lane