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

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.