After upgrade pg12 -> pg14 import time increased
Hello, starting with an explanation first... i have some sort of 'cold backup' system: it rsyncs a postgres dump (size: ~1.5TB) from the main system (created with 'pg_dump $dbname > db.sql) to the backup system (10TB hdds in a raid10), and then automatically imports that dump (psql $dbname < $db.sql) into postgres. So the backup system stores/provides the database of each day (cleanup of older stuff done manually). copy+import normally finished within 16-18 hours. About 4 weeks ago both systems were running with postgres12 on debian buster; then the switch to postgres14 was done, first on main system, later on the backup system (both with pg_upgrade, no problems here). But after that switch the import time on the backup system increased, now taking more than 24 hours. A dist-upgrade to debian bullseye was done, but no improvement. I was checking release notes (releases 13+14), but, somehow, nothing catched my eye. hardware of backup system: 128G ram, raid10 (ext4, 4 disks) for postgresql, raid10 (ext4, 6 disks) for sql backup files shared_buffers = 32768MB work_mem = 16384MB maintenance_work_mem = 16384MB effective_cache_size = 16GB no read/write operations on the backup system. any idea what could've changed? more information needed? thx, Hubert
Re: \set \e and newline
On Thu, 2022-02-24 at 18:16 +0100, Wim Bertels wrote: > it seems that \set does not interpret an 'enter' interactively the same > as an 'enter' in a short script made with \e > > * case 1: > postgres=# \set x 1 > postgres=# select :x; > ?column? > -- > 1 Ok. > * case 2: > postgres=# \e > > -- enter the following the editor (the same code as above) > > \set x 1 > select :x; > > -- save and quit > > postgres=# > > -- no output This was interpreted as if you had entered it in a single line: \set x 1 select :x So "x" is now "1select1;". > -- curiosly: again \e > > postgres=#\e > > -- shows select 1; in the editor in v14 That was the last executed SQL statement. > -- shows nothing in the editor in v13 (or recursive the content being > cut off) I don't quite understand what you mean, but the behavior of \e changed (got more sane) in v14. > variation of case 2: > postgres=# \e > > -- enter the following the editor (the same code as above) > > \set x 1 > select :x; > > -- save and quit > > postgres=# select :x; > select1select1 > > 1 Now "x" is "1select1select1;;", so you are running select 1select1select1;;; Here "select1select1" is interpreted as alias, so you get that column heading. You can use "\echo :x" to see the value of a variable. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: alter table to multi partitions
On Thu, 2022-02-24 at 12:48 +, Catalin Maftei wrote: > on a project I have large tables, with more than 2-5.000.000 rows, and the > system is starting to be slow, > This tables have many dependencies functions, procedures and views. > > I want to partition this tables. > > I think for this scenario for table Orders: > step1: create new table with same structure -> Orders_Part > step 2: create all the partitions&triggers on new table Orders_Part > step 3: copy/moved data from Orders to Orders_Part > step 4: rename Orders to Orders_Old > If i want to drop Orders, it's necessary to drop all dependencies (and that's > not what I want) > step 5:rename Orders_Part to Orders > here is the problem - when rename Orders to Orders_old, postgres > automatically change Orders to Orders_old in all dependencies (views, > functions, sp) > > is there any postgres setting I can use to not automatically change the in > all the depandencies? No, that is not possible. Note that the dependencies don't change at all. Rather, the dependencies are on the object itself, not the name of the object. So if you change the name, the dependencies don't change. You'll have to drop and re-create foreign keys, views and the like. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Transactions starting with Cursor with Hold are not closing in database.
Hi Team, We are facing an issue with few of the transactions which are running from SAP end. Below is the case statement. Issue -: We are seeing that , the session which are generated from SAP report end to PostgreSQL Database, it starts with the "Open Cursor " Cursor Name " with Hold - Select * from abc' (Screenshot Attached) The problem here is that , when any report runs from SAP end , and if it takes time to run into the database and cancelled from Application end , it keeps running into the database till its end or indefinitely. Suppose they cancel one report and runs that report again , there is another session which generated and previous one also run at the same time. Even after cancelling the report from application end. There is one parameter which we are suspecting that at ODBC level , there is option for Fetch/Declare we are using , so that can be an issue but we are not sure. Please suggest if there is any parameter, we need to look in the conf file in order to resolve this issue as it acquires a locks on the table and prevent it from modifying from other sessions or users. Thanks and Regards, Mukesh Kumar
