Re: Backing up a DB excluding certain tables - Mailing list pgsql-general

From JORGE MALDONADO
Subject Re: Backing up a DB excluding certain tables
Date
Msg-id CAAY=A7_7n+N3+7Wpn_wBLYHJ+s5n9ZycogiUU_7xiV8sChMv4Q@mail.gmail.com
Whole thread Raw
In response to Re: Backing up a DB excluding certain tables  (JORGE MALDONADO <jorgemal1960@gmail.com>)
Responses Re: Backing up a DB excluding certain tables
List pgsql-general
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

With respect,
Jorge Maldonado



On Thu, Apr 28, 2022 at 11:49 AM JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
Yes, I get a warning when running psql as follows. I will search for help in Google and PostgreSQL documentation. The warning suggests seeing psql reference page "Notes for Windows users". I will do that. I had not noticed the warning. Thank you.

image.png

Regards,
Jorge Maldonado

On Thu, Apr 28, 2022 at 10:13 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/28/22 09:57, JORGE MALDONADO wrote:
> Good day,
>
> Here is the output to commands suggested by *Adrian Klaver*. Encoding is
> the same in both client and server. Also, there are 7 tables I want to
> exclude.
> image.png
> The version of source DB is 11, and target version is 14.
>
> Regarding the following comment:
>
>     *FYI, -n riopoderoso and the riopoderoso in
>     'riopoderoso."AspNetRoleClaims"' are redundant.*
>
>
> Should I remove the schema name so the exclude switch is *-T "AspNet"*
> instead of *-T 'riopoderoso."AspNet"'?*

As Tom Lane pointed out earlier -n riopoderoso is going to restrict the
dump to objects in the riopoderoso schema so schema qualifying the table
name is not needed.

>
> I have tried -T 'riopoderoso."Asp*"',  -T 'riopoderoso."Asp"*', -T
> '"Asp"*', -T "Asp"*' and several other combinations unsuccessfully.
> Also, I have escaped double quotes, single quotes and both at the same
> time using the *^* character as documented in several sources.

I don't use Windows much anymore and not all for Postgres so I can't
comment on this. Someone who does will need to offer guidance.


> Can I run the *pg_dump* command in *psql*? I rarely use psql.

No you can't run pg_dump in psql. Speaking of psql, what happens when
you log in with it? Do you get a code page warning?


>
> Regards,
> Jorge Maldonado
>

--
Adrian Klaver
adrian.klaver@aklaver.com
Attachment

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Backing up a DB excluding certain tables
Next
From: Tom Lane
Date:
Subject: Re: External psql editor