Backing up a DB excluding certain tables

[ 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

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

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

With respect,
Jorge Maldonado

On Tue, Apr 26, 2022 at 6:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

On Wed, Apr 27, 2022 at 3:46 PM JORGE MALDONADO <jorgemal1960@gmail.com>
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
<pgsql-general@lists.postgresql.org>* list or also include individual
emails like yours and Adrian's.

Most of us prefer (or at least I don't see many complaints - I do have a
preference for being included specifically) if the individual responders
are kept too (reply-all) but in any case the list needs to be addressed.

The additional convention is to inline post (with trimming) as opposed to
top-posting like you did here. As a secondary option, bottom-post.

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*

That doesn't make any sense.

Using a clean testing database are you able to reproduce the problem?

What version of PostgreSQL and from where?

If you substitute in ? for various characters in the name (particularly the
table name portion) can you make it work? Is there some kind of encoding
difference so the name you are typing in pg_dump and the name stored in the
database, while looking the same, are actually different? Copy-and-paste
the name from the pg_dump file back into the command line.

David J.

On Wed, Apr 27, 2022 at 4:16 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wed, Apr 27, 2022 at 3:46 PM JORGE MALDONADO <jorgemal1960@gmail.com>
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
<pgsql-general@lists.postgresql.org>* list or also include individual
emails like yours and Adrian's.

Most of us prefer (or at least I don't see many complaints - I do have a
preference for being included specifically) if the individual responders
are kept too (reply-all) but in any case the list needs to be addressed.

The additional convention is to inline post (with trimming) as opposed to
top-posting like you did here. As a secondary option, bottom-post.

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*

That doesn't make any sense.

Using a clean testing database are you able to reproduce the problem?

What version of PostgreSQL and from where?

If you substitute in ? for various characters in the name (particularly
the table name portion) can you make it work? Is there some kind of
encoding difference so the name you are typing in pg_dump and the name
stored in the database, while looking the same, are actually different?
Copy-and-paste the name from the pg_dump file back into the command line.

Also, which shell are you using? Maybe it doesn't like single quotes for
arguments/options, only double-quotes, so the single quotes are becoming
part of the argument input?

David J.

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&gt; 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

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

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.

The command is issued using the command prompt in Windows 10.
[image: image.png]

The pg_dump command is included in a batch file which basically does the
following. These are the only lines in the batch file:

[image: image.png]
After all the tests I have performed, it looks that there is a good chance
that the problem has to do with the shell, the command prompt in this case.
Can I run the *pg_dump* command in *psql*? I rarely use psql.

Regards,
Jorge Maldonado

On Wed, Apr 27, 2022 at 6:19 PM Adrian Klaver <adrian.klaver@aklaver.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: image.png]

Regards,
Jorge Maldonado

On Thu, Apr 28, 2022 at 10:13 AM Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com

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: image.png]

With respect,
Jorge Maldonado

On Thu, Apr 28, 2022 at 11:49 AM JORGE MALDONADO <jorgemal1960@gmail.com>
wrote:

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

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

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

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

C:\Users\JorgeMal>cd "C:\Program Files\PostgreSQL\14\bin"

C:\Program Files\PostgreSQL\14\bin>pg_dump -E win1252 -f
c:/temp/respaldo.backup -n riopoderoso -F p -h localhost -p 5433 -U
postgres -W -s --exclude-table *.AspNet* riopoderoso
Password:

C:\Program Files\PostgreSQL\14\bin>chcp
Active code page: 1252

C:\Program Files\PostgreSQL\14\bin>pg_dump -E win1252 -f
c:/temp/respaldo.backup -n riopoderoso -F p -h localhost -p 5433 -U
postgres -W -s --exclude-table '*.AspNet*' riopoderoso
Password:

C:\Program Files\PostgreSQL\14\bin>pg_dump -E win1252 -f
c:/temp/respaldo.backup -n riopoderoso -F p -h localhost -p 5433 -U
postgres -W -s --exclude-table '*."AspNet"*' riopoderoso
Password:

C:\Program Files\PostgreSQL\14\bin>pg_dump -f c:/temp/respaldo.backup -n
riopoderoso -F p -h localhost -p 5433 -U postgres -W -s --exclude-table
'*."AspNet"*' riopoderoso
Password:

C:\Program Files\PostgreSQL\14\bin>pg_dump -f c:/temp/respaldo.backup -n
riopoderoso -F p -h localhost -p 5433 -U postgres -W -s --exclude-table
'*."AspNet"'* riopoderoso
Password:

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

Regards,
Jorge Maldonado

On Tue, May 3, 2022 at 9:47 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Hello,

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 dumped file 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 originally created with UTF8 encoding.
The problem persists in such a case too.

Regards,
Jorge Maldonado

On Tue, May 3, 2022 at 11:12 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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 dumped file 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 originally created 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.