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 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*
Re: Backing up a DB excluding certain tables
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
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
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
Meaning of "constant" not honored when the variable is used as the actual for a proc's OUT formal paameter
I just did this using PG 14.2:
create procedure p(a out int)
language plpgsql
as $body$
begin
a := 42;
end;
$body$;
do $body$
declare
a constant int := 0;
begin
call p(a);
raise info '%', a::text;
end;
$body$;
The DO block runs without error and reports "INFO: 42". This is an unambiguous
semantic error because "a" is declared "constant".
Is this a known issue?
Replace "a" with the literal "37" in this test:
do $body$
begin
call p(37);
raise info '%', a::text;
end;
$body$;
This causes the expected runtime error 42601:
procedure parameter "a" is an output parameter but corresponding argument is
not writable.
Bt.w., error 42601 is mapped to the name "syntax_error" in PL/pgSQL. I'd say
that this is its own distinct bug. The syntax is fine. It's a semantic error.
Notice that the test can be trivially transcribed to Oracle Database's PL/SQL
as this SQL*Plus script:
create procedure p(a out integer)
authid definer
as
begin
a := 42;
end;
/
declare
a /*constant*/ int := 0;
begin
p(a);
DBMS_Output.put_line('a: '||to_char(a));
end;
/
When "constant" is commented out (as presented), the anonymous block runs
without error and outputs "a: 42". But when "constant" is uncommented, the
attempt causes this error:
PLS-00363: expression 'A' cannot be used as an assignment target
This is the proper report of what clearly is a semantic error. PG should do the
same.
B.t.w., this happens to be a compilation error in ORCL and not a run-time
error. But that's an entirely different story and reflects the fundamentally
different compilation and execution models for anonymous blocks, user-defined
functions, and user-defined procedures between ORCL and PG.
Re: Meaning of "constant" not honored when the variable is used as the actual for a proc's OUT formal paameter
Bryn Llewellyn writes: > The DO block runs without error and reports "INFO: 42". This is an > unambiguous semantic error because "a" is declared "constant". > Is this a known issue? It is, see https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=ccd10a9bf regards, tom lane
Re: Meaning of "constant" not honored when the variable is used as the actual for a proc's OUT formal paameter
> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> The DO block runs without error and reports "INFO: 42". This is an >> unambiguous semantic error because "a" is declared "constant"… Is this a >> known issue? > > It is, see > https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=ccd10a9bf Thanks for the instant response, Tom. It's uncanny that you spotted this on Saturday 30-Apr and I spotted it just three days later on Tuesday 3-May. One of us must be psychic.
Logical replication 'possible' problem
Hi, I'm in the process of doing the initial syncing of a subscriber with a publisher. There is only one table that is still in a 'dumping' state. It is quite a large table and in previous executions it took several hours. I'm not sure if it encountered a problem and stopped or if it is still going. Looking at the replication slots on the publisher I see this: b2bcreditonline=> select slot_name, active, active_pid from pg_replication_slots; slot_name | active | active_pid ++ b2bcreditonline_prod_b_master | t | 21511 b2bcreditonline_prod_b_shard | t | 21703 pg_67491625_sync_60067_7093664237039303581 | f | (3 rows) I assume the pg_ slot is the one created for the initial copy but I'm not sure if having a false active state is normal/ok. If it is, great. If not, how do I determine the problem and go about fixing it? Thanks, Steve
Re: Logical replication 'possible' problem
Sorry, I should have added the publisher is on 13.1 and the subscriber 14.2. Both are AWS RDS instances. I checked the log files for the publisher and subscriber and couldn't see any logical replication errors. The publisher is a busy DB though so if there are any errors there, I may have missed them. Thanks. On Wed, May 4, 2022 at 1:50 PM Steve Baldwin wrote: > Hi, > > I'm in the process of doing the initial syncing of a subscriber with a > publisher. > > There is only one table that is still in a 'dumping' state. It is quite a > large table and in previous executions it took several hours. > > I'm not sure if it encountered a problem and stopped or if it is still > going. > > Looking at the replication slots on the publisher I see this: > > b2bcreditonline=> select slot_name, active, active_pid from > pg_replication_slots; > slot_name | active | active_pid > ++ > b2bcreditonline_prod_b_master | t | 21511 > b2bcreditonline_prod_b_shard | t | 21703 > pg_67491625_sync_60067_7093664237039303581 | f | > (3 rows) > > I assume the pg_ slot is the one created for the initial copy but I'm > not sure if having a false active state is normal/ok. > > If it is, great. If not, how do I determine the problem and go about > fixing it? > > Thanks, > > Steve >
Re: Logical replication 'possible' problem
The logical replication dump of the table I thought was 'stuck' eventually completed after 6+ hours. I guess the replication slot showing active as false is to be expected. I never noticed it before. So there never was an issue - apart from my ignorance. Sorry for the noise. Cheers, Steve On Wed, May 4, 2022 at 1:54 PM Steve Baldwin wrote: > Sorry, I should have added the publisher is on 13.1 and the subscriber > 14.2. Both are AWS RDS instances. I checked the log files for the publisher > and subscriber and couldn't see any logical replication errors. The > publisher is a busy DB though so if there are any errors there, I may have > missed them. > > Thanks. > > On Wed, May 4, 2022 at 1:50 PM Steve Baldwin > wrote: > >> Hi, >> >> I'm in the process of doing the initial syncing of a subscriber with a >> publisher. >> >> There is only one table that is still in a 'dumping' state. It is quite a >> large table and in previous executions it took several hours. >> >> I'm not sure if it encountered a problem and stopped or if it is still >> going. >> >> Looking at the replication slots on the publisher I see this: >> >> b2bcreditonline=> select slot_name, active, active_pid from >> pg_replication_slots; >> slot_name | active | active_pid >> ++ >> b2bcreditonline_prod_b_master | t | 21511 >> b2bcreditonline_prod_b_shard | t | 21703 >> pg_67491625_sync_60067_7093664237039303581 | f | >> (3 rows) >> >> I assume the pg_ slot is the one created for the initial copy but I'm >> not sure if having a false active state is normal/ok. >> >> If it is, great. If not, how do I determine the problem and go about >> fixing it? >> >> Thanks, >> >> Steve >> >
