Re: Backing up a DB excluding certain tables

[JORGE MALDONADO <jorgemal1960@xxxxxxxxx>]

Hi,

I tried the suggestion in the "Notes for Windows Users" which recommends the following:

psql is built as a “console application”. Since the Windows console windows use a different encoding than the rest of the system, you must take special care when using 8-bit characters within psql. If psql detects a problematic console code page, it will warn you at startup. To change the console code page, two things are necessary:

• Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code page that is appropriate for German; replace it with your value.) If you are using Cygwin, you can put this command in /etc/profile.

• Set the console font to Lucida Console, because the raster font does not work with the ANSI code page.

The first suggestion says that 1252 is the code page for German. However, I used it because that is the code the DB was created with. When I ran the cmd.exe /c chcp1252 command, nothing appeared on screen but it looked like something was done. So I ran the command without the /c argument so the console window remained open. Then I changed the font to Lucida Console and ran the pg_dump command with different combinations in the --exclude-table argument without success.

• --exclude-table *.AspNet*
• --exclude-table '*."AspNet"*'
• --exclude-table '*."AspNet*"'
• --exclude-table'*."AspNet*"'

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

After searching and reading many posts about the same issue, I found a common situation: the encoding of the database. As far as I understood, PostgreSQL uses UTF8 as default. So I opened the psql console which shows the following warning:

WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.

Then, I issued the \l command to display all the databases and their settings with the following result:

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

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

 postgres       | postgres | UTF8         | Spanish_Latin America.1252 | Spanish_Latin America.1252 |

 riopoderoso | postgres | WIN1252 | Spanish_Latin America.1252 | Spanish_Latin America.1252 |

 template0     | postgres | UTF8        | Spanish_Latin America.1252 | Spanish_Latin America.1252 | =c/postgres          + postgres=CTc/postgres

 template1     | postgres | UTF8        | Spanish_Latin America.1252 | Spanish_Latin America.1252 | =c/postgres          + postgres=CTc/postgres

 testdb            | postgres | UTF8        | Spanish_Latin America.1252 | Spanish_Latin America.1252 |

(5 rows)

As shown, riopoderoso is the only DB with WIN1252 encoding. I created a new DB for testing purposes with the name of testdb and UTF8 encoding. I, however, left Collate and Ctype with 1252 for string comparison and manipulation because Spanish is the language that data will be saved. 

testdb has 3 tables:

• table01
• table02
• exclude01
• exclude02
• Exclude03 (upper case E intentionally)

I ran the following command successfully where tables exclude01 and exclude02 were excluded in the result and Exclude03 was included so the case is important. I noticed that no quotes (single or double) were needed.

pg_dump -f c:/temp/respaldo.backup -n testdb -F p -h localhost -p 5433 -U postgres -W -s --exclude-table *.exclude* testdb

In summary, because UTF8 is capable of encoding virtually all characters, including Spanish characters, I suppose that it is valid to use UTF8 for DB encoding and setting Collate and Ctype to 1252 so that data saved in the database is correctly compared and manipulated in my case where Spanish is the data language.

I will very much appreciate your valuable comments.

Respectfully,

Jorge Maldonado

On Mon, May 2, 2022 at 1:18 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 5/2/22 12:24, JORGE MALDONADO wrote:
> Hi,
>
> After a lot of tests and reading about the issue with the "*exclude
> table*" option on *pg_dump*, I found many articles saying that the
> problem has to do with the encoding of the DB. The DB I am testing with
> has a *WIN1252* encoding, so I decided to create a new DB with *UTF8*
> encoding. The following is a list of the actual DBs, being *riopoderoso*
> and *testdb* the ones I tested. As you can see, *riopoderoso* has
> *WIN1252* encoding and *testdb* has *UTF8* encoding. The *pg_dump*
> command worked correctly when testing the *testDB* database. So, this
> proves that encoding is the real issue here. Both DBs have the same
> *Collate* and *Ctype* however, the only difference is the encoding. With
> this in mind, I see that I can set the encoding to *UTF8* and leave
> *Collate* and *Ctype* as shown because, as far as I understand, both of
> them have to do with string comparison (the language used to enter data
> into the DB is Spanish). I will very much appreciate your comments on this.
>
> image.png

Please do not use images for textual information as you end up with the
above in the email archives or in email clients that are text only. The
information is then lost. Copy and paste from the console.

Back to the issue at hand:

1) Did you try the suggestion in the  "Notes for Windows users" for the
riopoderoso database?

2) What was the pg_dump command that you used that worked?

>
> With respect,
> Jorge Maldonado
>
>


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx