Thread: Re: Backing up a DB excluding certain tables

On 4/26/22 12:15 PM, JORGE MALDONADO wrote:
> Hi,
> 
> I am using *pg_dump* to backup a DB but I would like to exclude some 
> tables so I added the *-T* switch with a pattern like this: *aspnet*.**. 
> The backup command  includes the *-s* switch to consider the schema only 
> because I do not need the data. However, even with the *-T* switch, the 
> tables with the above pattern are created and I also want to exclude 
> their creation. Is this possible? Or maybe I am not setting the pattern 
> correctly. This is the command I am issuing:
> 
> pg_dump -E win1252 -f c:/temp/respaldo.backup -F p -n riopoderoso -h 
> localhost -p 5432 -U postgres -W -T aspnet*.* -s riopoderoso

Try:

-T 'aspnet*.*'

Per:

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

"When using wildcards, be careful to quote the pattern if needed to 
prevent the shell from expanding the wildcards; see Examples below."

And Examples is:

https://www.postgresql.org/docs/current/app-pgdump.html#PG-DUMP-EXAMPLES
> 
> Respectfully,
> Jorge Maldonado


-- 
Adrian Klaver
adrian.klaver@aklaver.com

JORGE MALDONADO <jorgemal1960@gmail.com> writes:
> I am using *pg_dump* to backup a DB but I would like to exclude some tables
> so I added the *-T* switch with a pattern like this: *aspnet*.**. The
> backup command  includes the *-s* switch to consider the schema only
> because I do not need the data. However, even with the *-T* switch, the
> tables with the above pattern are created and I also want to exclude their
> creation. Is this possible? Or maybe I am not setting the pattern
> correctly. This is the command I am issuing:

> pg_dump -E win1252 -f c:/temp/respaldo.backup -F p -n riopoderoso -h
> localhost -p 5432 -U postgres -W -T aspnet*.* -s riopoderoso

"-n riopoderoso" is already restricting the dump to objects in
schema riopoderoso, so it doesn't look to me like that -T switch
is going to change anything.

I suspect the problem is somewhere else in your process.  Maybe
you are restoring the backup into a database that isn't empty,
but already contains the aspnet*.* tables?

            regards, tom lane

[ please keep the list cc'd ]

JORGE MALDONADO <jorgemal1960@gmail.com> writes:
> As for the answer by *Tom Lane*, I am not restoring the DB but only getting
> the backup in plain format. I see that tables that contain "AspNet" in
> their name are part of the resulting dumped file. For example, the
> following is part of the resulting backup plain file:

> CREATE TABLE riopoderoso."AspNetRoleClaims" (
>     "Id" integer NOT NULL,
>     "RoleId" character varying(450) NOT NULL,
>     "ClaimType" text,
>     "ClaimValue" text
> );

Ah.  Now that you actually showed us what you're doing, there are
two problems:

1. "aspnet*.*" is trying to match a *schema* name starting with "aspnet",
not a *table* name.  What you want is more like "*.aspnet*", or possibly
"riopoderoso.aspnet*".  (You can't just write "aspnet*", because
riopoderoso isn't going to be in pg_dump's search path, and that pattern
would only match tables in the search path.)

2. You're not accounting for case.  Per the discussion of patterns
in the psql reference manual, to match an upper-case name you'd need
to spell it with the correct casing and then put double quotes around
it.

Actually there's a third problem, which is to get the shell to not strip
the double quotes from the pattern before handing it to pg_dump.

For me, a dump command like

pg_dump -n riopoderoso -T '*."AspNet"*' ...

does what you want.  However, I gather you're doing this on Windows,
and I'm not sure whether shell command quoting rules are the same there.
You might need something weird like backslashing the double quotes.

            regards, tom lane

On 4/27/22 15:45, JORGE MALDONADO wrote:
> Is this the correct way to answer when you say that I must *keep the 
> list cc'd*? I am not sure if I only have to reply to the 
> *pgsql-general@lists.postgresql.org 
> <mailto:pgsql-general@lists.postgresql.org>* list or also include 
> individual emails like yours and Adrian's.
> 
> I have tried many ways to make this pg_dump command work without 
> success. I just do not know what is going on. The last test I performed 
> was as follows which only excludes 1 table in the *riopoderoso* schema 
> but it did not work. Such a table is included in the resulting backup 
> plain format file.
> 
> *pg_dump -E win1252 -f c:/temp/respaldo.backup -n riopoderoso -F p -h 
> localhost -p 5432 -U postgres -W -s -T 'riopoderoso."AspNetRoleClaims"' 
> riopoderoso*

Using the command line client psql what does:

show client_encoding ;
show server_encoding ;

return?

Also in psql what does:

\dt riopoderoso."Asp"*

return?


FYI, -n riopoderoso and the riopoderoso in 
'riopoderoso."AspNetRoleClaims"' are redundant.

> 
> If there are any additional comments please let me know. I will keep 
> trying. It is important to exclude these tables because they are created 
> and managed by other means. Such tables are part of the authentication 
> feature included in ASP.NET <http://ASP.NET> Core.
> 
> With respect,
> Jorge Maldonado
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

JORGE MALDONADO <jorgemal1960@gmail.com> writes:
>> *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"'?*

No.  As I explained upthread, you'd better use either
    *."AspNet"*
or
    riopoderoso."AspNet"*
because otherwise the pattern won't match tables that aren't in
pg_dump's restricted search_path.

I continue to think that your problem boils down to one of getting
the quoting around the double-quoted pattern correct.  I'm not a
Windows user so I don't know much about shell quoting rules there.

            regards, tom lane

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@aklaver.com

On 5/3/22 09:10, JORGE MALDONADO wrote:
> Hi,
> 
> I tried the suggestion in the "*Notes for Windows Users*" which 
> recommends the following:
> 

> 
> 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 

If you do in a console window:

chcp
cmd.exe /c chcp 1252 --It should be this not chcp1252
chcp

You should see the value returned by chcp change from 437 to 1252 with 
the window staying open. chcp without an argument will only display the 
current code page number.

Not sure what '...so the console window remained open.' means?
Did running cmd.exe close the console window?
If so how did you get to the console(command prompt)?



> 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*"'
> 
>
------------------------------------------------------------------------------------------------------------------------------------------------------------

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 5/3/22 10:26 AM, JORGE MALDONADO wrote:
> I included the space in chcp 1252, it was a typo in the last message I sent.
> This is what I just did as per your suggestion in a command prompt with 
> Lucida Console font.
> 
> C:\Users\JorgeMal>chcp
> Active code page: 437
> 
> C:\Users\JorgeMal>cmd.exe /c chcp 1252
> Active code page: 1252
> 
> C:\Users\JorgeMal>chcp
> Active code page: 1252
> 

> 
> The result always included tables with *AspNet* in the name.

I am at a loss for an answer. I just don't use Windows enough to know 
where to go from here.

> 
> Regards,
> Jorge Maldonado
> 
> 

> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Jorge:

On Wed, 4 May 2022 at 18:12, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> I just found the solution (or 1 solution).
> It seems that the problem resides in tables with names containing characters other than lowercase letters.
> I want to exclude the following tables from the backup:

> AspNetRoleClaims
> AspNetRoles
> AspNetUserClaims
> AspNetUserLogins
> AspNetUserRoles
> AspNetUserTokens
> AspNetUsers
> __EFMigrationsHistory
>
> One pg_dump command that worked correctly is as follows:
>
> pg_dump -f c:/temp/respaldo.backup -n riopoderoso -F p -h localhost -p 5433 -U postgres -W -s -T *.?sp?et* -T
*.*igrations?istory riopoderoso
 
>
> As you can see, uppercase letters and underscore characters were avoided in both -T arguments. In this way, the
dumpedfile was generated successfully without the files listed above.
 
> It was not even necessary to issue the command cmd.exe /c chcp 1252 in the command prompt before running pg_dump.
> I also tried different combinations of single quotes and double quotes but none worked.

> This behavior of characters other than lowercase letters in table names is present no matter if the database is
originallycreated with UTF8 encoding. The problem persists in such a case too.
 

When I initially saw your question talking about chcp and similar
stuff I skipped it, not having used windows for more than a decade.

The problem you are seeing may be due more to your shell/OS combo than
to other things. In unix, where psql is easier to work with,
to execute a program the OS passes it the arguments vector properly
separated. The different shells are responsible for building these
arguments,
process wildcards and other things an have very well documented
quoting rules to let the user generate exactly what he wants passed to
the
programs. This is why in unix we have to quote * whenever it appears
in a table name and similar stuff.

IIRC In MSDOS mode, whith windows inherited, the shell does some
substitutiton to the command line and then passes whole to the
process, which,
if it is a C program, is then responsible of reparsing it,
reprocessing quotes, expand wildcards and call main. Quoting is poorly
documented and
processing may vary for each program using different CRTs ( that is C
runtime, not cathode ray tube ).

Now for the thing. I'm going to use braces for quoting... If you need
to send a table name with uppercase letters to postgres related
programs you
normally need to insure it receives it in argument with double-quotes,
i.e. {"AspNetRoles"}. In unix this easy, in the bash shell I use the
easy way is to surround the
double quotes with single quotes, {'"AspNetRoles"'} ( you may need a
non proportional font to read that), or just escape the quotes
{\"AspNetRoles\"}
or several other variations. But in windows the quoting rules are
difficult to master, and I'm not sure if you can do that easily ( back
in the days I had
a program which dumped the arguments it received to check what the
shell was doing to my command line ).

At the end of https://www.postgresql.org/docs/14/app-pgdump.html there
is a sample double quoting, but I do not know if this is for a windows
shell. It would
work with unix shells, but is a little convoluted, so may be it is the
thing to try ( try it with an schema only dump redirected to dev/null
( I do not remember
it is windows name, I think it was NUL: )).

An I remember pg_dump had a catalog mode, but it seems to have
dissapeared in recent versions ( you gave it a switch, it wrote a
commented list
of IDs which you could edit ( normally avoiding reorders ) and feed
back with another switch to make partial backups, it was really useful
for selective
backups of complex stuff ).

Francisco Olarte.

Replying to self...

On Wed, 4 May 2022 at 19:19, Francisco Olarte <folarte@peoplecall.com> wrote:

> An I remember pg_dump had a catalog mode, but it seems to have
> dissapeared in recent versions ( you gave it a switch, it wrote a
> commented list
> of IDs which you could edit ( normally avoiding reorders ) and feed
> back with another switch to make partial backups, it was really useful
> for selective
> backups of complex stuff ).

Got it wrong, after consulting some old notes it is pg_restore which
has the -l/-L switches, what we did, was full-dump a db for upgrading,
write TOC, edit TOC to restore only schema on some tables, restore it
faster, go live,  then use rest of the TOC to restore the missing
tables ( they where huge logs, just affected reports which could wait
).

Francisco Olarte.