Backing up a DB excluding certain tables
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 Respectfully, Jorge Maldonado
Re: Backing up a DB excluding certain tables
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 * 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 wrote: > [ please keep the list cc'd ] > > JORGE MALDONADO 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 >
Re: Backing up a DB excluding certain tables
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 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.kla...@aklaver.com >
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 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 worke
Re: Backing up a DB excluding certain tables
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 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.kla...@aklaver.com >
Re: Backing up a DB excluding certain tables
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 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.kla...@aklaver.com >
WIN1252 vs UTF8 database encoding
Hi, I am currently working with a PostgreSQL database created with *WIN1252 *encoding because the data will be in Spanish language. So, both *Collation* and *Character type* are set to *Spanish_Mexico.1252* After reading a bit more about encoding, it seems to me that I can use *UTF8* encoding so characters for all languages are available and keep *Collation* and *Character type* as *Spanish_Mexico.1252*. I guess that using *UTF8* as the encoding method will keep databases more general. I think that using *WIN1252* for encoding is restrictive. Does it make sense? Regards, Jorge Maldonado
npgsql versus dotConnect data provider
Hi, I am an ASP.NET Core developer and I have been using Devart dotConnect for PostgreSQL for some time. I know about npgsql data provider but I have never used it. Lately, I have been considering using npgsql instead of the Devart product. Is this a good choice? Is this a good decision? I have googled on performance comparison but have not found very much information. Best regards, Jorge Maldonado
