Re: Backing up a DB excluding certain tables

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(dot)klaver(at)aklaver(dot)com>
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(dot)klaver(at)aklaver(dot)com
>