oracle to postgresql conversion tool
Hi, there are at least 5 tools I found on the PG list but could you recommend well tested, free one ? we need to migrate production 30GB oracle 11 db to postgres 9 and are looking for best approach. Of course if there is no free/open solution any commercial & recommended tool is always welcome. Thank you Marcin
why postgres process takes so much memory during day time
Hi all, is there any way to figure out why particular postgres process takes more and more memory during the day? This process performes order data queries coming from order management system - exchange system. Normally it's about 0,5mln messages per day but after aprox 4h postgres process occupies : PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 11920 pgsql 20 0 17,6g 9,7g 566572 S 2,6 10,4 2:40.47 postgres Perhaps dev guys (our team) should change something inside our system or it's postgresql conf matter? Postgres engine is 9.6.7. Any hint on this please? Thx Marcin
some questions regarding replication issues and timeline/history files
Hello, I've created synchronous replication between Primary and Secondary server and put pacemaker + PAF over. Client is doing some stress tests (switching nodes, disabling particular node, etc) and it's second time when this kind of problem occoures : 2020-12-18 14:03:46.658 CET [unknown] [28787]ERROR: requested starting point F/A200 on timeline 39 is not in this server's history 2020-12-18 14:03:46.658 CET [unknown] [28787]DETAIL: This server's history forked from timeline 39 at F/A1023338. questions: 1. what does this mean ? How this can happen ? Does this mean that for some "point in time" both servers were primary ? 2. in xxx.history files I find the following rows: 43 F/A6A0 no recovery target specified 44 F/A7A0 no recovery target specified 45 F/A8A0 no recovery target specified again: what does this mean ? 3. general question: can anyone suggest deep explanation of timelines and history files to understand how this postgresql nature works ? Many thx Marcin
Re: pg_upgrade from 12 to 13 failes with plpython2
| plperlu_validator | $libdir/plperl | plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | (6 rows) postgres=# \c template1 psql (12.4 (Ubuntu 12.4-1), server 12.5) You are now connected to database "template1" as user "pgsql". template1=# select * from pg_pltemplate ; tmplname | tmpltrusted | tmpldbacreate | tmplhandler | tmplinline | tmplvalidator | tmpllibrary | tmplacl +-+---++--+-+---+- plpgsql | t | t | plpgsql_call_handler | plpgsql_inline_handler | plpgsql_validator | $libdir/plpgsql | pltcl | t | t | pltcl_call_handler | | | $libdir/pltcl | pltclu | f | f | pltclu_call_handler | | | $libdir/pltcl | plperl | t | t | plperl_call_handler | plperl_inline_handler | plperl_validator | $libdir/plperl | plperlu | f | f | plperlu_call_handler | plperlu_inline_handler | plperlu_validator | $libdir/plperl | plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | (6 rows) template1=# Od: "Magnus Hagander" Do: "Marcin Giedz" DW: "Adrian Klaver" , "Tom Lane" , "Devrim Gündüz" , "pgsql-general" Wysłane: środa, 18 listopad 2020 10:36:10 Temat: Re: pg_upgrade from 12 to 13 failes with plpython2 On Wed, Nov 18, 2020 at 8:11 AM Marcin Giedz wrote: > > but my question still remains the same - what causes pg_upgrade failure - are > functions the reason? what I did was to delete these 2 rows from > pg_pltemplate as I thought this may help: > > postgres=# delete from pg_pltemplate where tmplname = 'plpython2u'; > DELETE 1 > postgres=# delete from pg_pltemplate where tmplname = 'plpythonu'; > DELETE 1 > > > but pg_upgrade still complains about plpython2: > > cat loadable_libraries.txt > could not load library "$libdir/plpython2": ERROR: could not access file > "$libdir/plpython2": No such file or directory > In database: alaxx > In database: template1 It's not the template that's interesting, it's the language itself you need to drop. Log into each database and try to do that, and you will get something like this if you still have functions using it: postgres=# DROP LANGUAGE plpython2u; ERROR: cannot drop language plpython2u because other objects depend on it DETAIL: function testfunc() depends on language plpython2u HINT: Use DROP ... CASCADE to drop the dependent objects too. If you have no functions using it, it will just go away, and once you have dropped it in both databases you should be good to go. And of course, if there are functions depending on it, you should rebuild those on plpython3u before you drop plpython2u (or drop the functions if they're not in use). -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/ -- Pozdrawiam Marcin Giedz Wiceprezes Zarządu ARISE Sp. z o.o. mob. +48 606 673 977 mail: marcin.gi...@arise.pl Al. Solidarności 117 00-140 Warszawa tel. +48 (22) 440 56 20 fax +48 (22) 440 56 22 http://www.arise.pl Grupa ARISE: ARISE Sp. z o.o. (podmiot dominujący), ARISE Services S.A., Al. Solidarności 117, 00-140 Warszawa, Sąd Rejonowy dla m. st. Warszawy w Warszawie XII Wydział Gospodarczy Krajowego Rejestru Sądowego, KRS 316860, REGON 141595449, NIP 5272590610, kapitał zakładowy wpłacony 250,000.00 zł.
Re: pg_upgrade from 12 to 13 failes with plpython2
so look at this: marcin@carbon2:~$ psql -U pgsql -h 192.168.89.64 postgres psql (12.4 (Ubuntu 12.4-1), server 12.5) Type "help" for help. postgres=# drop extension plpython; ERROR: extension "plpython" does not exist postgres=# drop extension plpythonu; ERROR: extension "plpythonu" does not exist postgres=# drop extension plpython2u; ERROR: extension "plpython2u" does not exist postgres=# \q marcin@carbon2:~$ psql -U pgsql -h 192.168.89.64 template1 psql (12.4 (Ubuntu 12.4-1), server 12.5) Type "help" for help. template1=# drop extension plpython2u; ERROR: extension "plpython2u" does not exist template1=# drop extension plpythonu; ERROR: extension "plpythonu" does not exist template1=# drop extension plpython; ERROR: extension "plpython" does not exist template1=# \q marcin@carbon2:~$ psql -U pgsql -h 192.168.89.64 argosrm psql (12.4 (Ubuntu 12.4-1), server 12.5) Type "help" for help. argosrm=# drop extension plpython; ERROR: extension "plpython" does not exist argosrm=# drop extension plpythonu; ERROR: extension "plpythonu" does not exist argosrm=# drop extension plpython2u; ERROR: extension "plpython2u" does not exist Od: "Tom Lane" Do: "Marcin Giedz" DW: "Laurenz Albe" , "Magnus Hagander" , "Adrian Klaver" , "Devrim Gündüz" , "pgsql-general" Wysłane: środa, 18 listopad 2020 19:08:25 Temat: Re: pg_upgrade from 12 to 13 failes with plpython2 Marcin Giedz writes: > all DBs checked and no plpython(2u) is found except for plpython3u I think you also need to make sure you've dropped the plpythonu and plpython2u extensions in every database. regards, tom lane
Re: pg_upgrade from 12 to 13 failes with plpython2
Hi, not the case I believe : postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---+---+--+-+-+--- argosrm | pgsql | UTF8 | pl_PL.UTF-8 | pl_PL.UTF-8 | postgres | pgsql | UTF8 | pl_PL.UTF-8 | pl_PL.UTF-8 | template0 | pgsql | UTF8 | pl_PL.UTF-8 | pl_PL.UTF-8 | =c/pgsql + | | | | | pgsql=CTc/pgsql template1 | pgsql | UTF8 | pl_PL.UTF-8 | pl_PL.UTF-8 | =c/pgsql + | | | | | pgsql=CTc/pgsql (4 rows) all DBs checked and no plpython(2u) is found except for plpython3u ... cat loadable_libraries.txt could not load library "$libdir/plpython2": ERROR: could not access file "$libdir/plpython2": No such file or directory In database: argosrm In database: template1 Thx M. Od: "Laurenz Albe" Do: "Marcin Giedz" , "Magnus Hagander" DW: "Adrian Klaver" , "Tom Lane" , "Devrim Gündüz" , "pgsql-general" Wysłane: środa, 18 listopad 2020 12:58:45 Temat: Re: pg_upgrade from 12 to 13 failes with plpython2 On Wed, 2020-11-18 at 11:05 +0100, Marcin Giedz wrote: > right, I had one function relaying on plpython2u so I changed it... but the > again pg_upgrade claims error with python: > > cat loadable_libraries.txt > could not load library "$libdir/plpython2": ERROR: could not access file > "$libdir/plpython2": No such file or directory > In database: argosrm > In database: template1 The problematic function is perhaps in another database. Look everywhere. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_upgrade from 12 to 13 failes with plpython2
[ please don't top-post, it makes conversations unreadable ] Marcin Giedz writes: > so look at this: > postgres=# drop extension plpython; > ERROR: extension "plpython" does not exist > postgres=# drop extension plpythonu; > ERROR: extension "plpythonu" does not exist > postgres=# drop extension plpython2u; > ERROR: extension "plpython2u" does not exist Well, the pg_upgrade failure clearly shows that you've got some functions referencing plpython2. Maybe they're "loose" instead of being bound into an extension --- that's quite possible if this database has been brought forward from some pre-9.1 state. Try looking in each database with select * from pg_proc where probin like '%python2%'; regards, tom lane sorry for top-posting not a day-to-day habits in our env ;) anyway got this from your query: oid | proname | pronamespace | proowner | prolang | procost | prorows | provariadic | prosupport | prokind | prosecdef | proleakproof | proisstrict | proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults | protrftypes | prosrc | probin | proconfig | proacl ---+-+--+--+-+-+-+-++-+---+--+-+---+-+-+--+-++-++-+-++-+-+---+---+ 16402 | plpython_call_handler | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | f | f | v | u | 0 | 0 | 2280 | | | | | | | plpython_call_handler | $libdir/plpython2 | | 16403 | plpython_inline_handler | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | t | f | v | u | 1 | 0 | 2278 | 2281 | | | | | | plpython_inline_handler | $libdir/plpython2 | | 16404 | plpython_validator | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | t | f | v | u | 1 | 0 | 2278 | 26 | | | | | | plpython_validator | $libdir/plpython2 | | (3 rows) (END) sounds nothing for me I'm afraid but I hope gives a hint for you ;) Many thx M.
Re: pg_upgrade from 12 to 13 failes with plpython2
> anyway got this from your query: > 16402 | plpython_call_handler | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | f > | f | v | u | 0 | 0 | 2280 | | | | | | | plpython_call_handler | > $libdir/plpython2 | | > 16403 | plpython_inline_handler | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | > t | f | v | u | 1 | 0 | 2278 | 2281 | | | | | | plpython_inline_handler | > $libdir/plpython2 | | > 16404 | plpython_validator | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | t | f > | v | u | 1 | 0 | 2278 | 26 | | | | | | plpython_validator | > $libdir/plpython2 | | Uh-huh, so there you have it. These must be leftovers from some pre-extension incarnation of plpython that was never cleaned up properly. Try DROP FUNCTION pg_catalog.plpython_call_handler(); DROP FUNCTION pg_catalog.plpython_inline_handler(internal); DROP FUNCTION pg_catalog.plpython_validator(oid); It'll be interesting to see if there are any dependencies. regards, tom lane - BINGO! after drops all went smooth and easy Many thx Tom! M.
pg_upgrade from 12 to 13 failes with plpython2
Hi all, trying to performe upgrade from 12 to 13 installed from Centos8 repo gives such error: cat loadable_libraries.txt could not load library "$libdir/plpython2": ERROR: could not access file "$libdir/plpython2": No such file or directory digging around: 1. drop extension plpythonu; ERROR: extension "plpythonu" does not exist 2. postgres=# select * from pg_pltemplate ; tmplname | tmpltrusted | tmpldbacreate | tmplhandler | tmplinline | tmplvalidator | tmpllibrary | tmplacl +-+---++--+-+---+- plpgsql | t | t | plpgsql_call_handler | plpgsql_inline_handler | plpgsql_validator | $libdir/plpgsql | pltcl | t | t | pltcl_call_handler | | | $libdir/pltcl | pltclu | f | f | pltclu_call_handler | | | $libdir/pltcl | plperl | t | t | plperl_call_handler | plperl_inline_handler | plperl_validator | $libdir/plperl | plperlu | f | f | plperlu_call_handler | plperlu_inline_handler | plperlu_validator | $libdir/plperl | plpythonu | f | f | plpython_call_handler | plpython_inline_handler | plpython_validator | $libdir/plpython2 | plpython2u | f | f | plpython2_call_handler | plpython2_inline_handler | plpython2_validator | $libdir/plpython2 | plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | (8 rows) 3. postgres=# select * from pg_language ; oid | lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl ---+--+--+-+--+---+---+--+ 12 | internal | 10 | f | f | 0 | 0 | 2246 | 13 | c | 10 | f | f | 0 | 0 | 2247 | 14 | sql | 10 | f | t | 0 | 0 | 2248 | 14177 | plpgsql | 10 | t | t | 14174 | 14175 | 14176 | (4 rows) 4. postgres=# select DISTINCT l.lanname as function_language postgres-# from pg_proc p postgres-# left join pg_language l on p.prolang = l.oid; function_language --- c sql internal (3 rows) 5. postgres=# SELECT oid::regprocedure postgres-# FROM pg_catalog.pg_proc postgres-# WHERE probin = '$libdir/plpython2'; oid - (0 rows) Question: where is plpython2 comming from during pg_upgrade? Is this pg_upgrade bug or something else? Thx Marcin
Re: pg_upgrade from 12 to 13 failes with plpython2
but my question still remains the same - what causes pg_upgrade failure - are functions the reason? what I did was to delete these 2 rows from pg_pltemplate as I thought this may help: postgres=# delete from pg_pltemplate where tmplname = 'plpython2u'; DELETE 1 postgres=# delete from pg_pltemplate where tmplname = 'plpythonu'; DELETE 1 but pg_upgrade still complains about plpython2: cat loadable_libraries.txt could not load library "$libdir/plpython2": ERROR: could not access file "$libdir/plpython2": No such file or directory In database: alaxx In database: template1 marcin@carbon2:~$ psql -U pgsql -h 192.168.89.64 alaxx psql (12.4 (Ubuntu 12.4-1)) Type "help" for help. argosrm=# select * from pg_pltemplate ; tmplname | tmpltrusted | tmpldbacreate | tmplhandler | tmplinline | tmplvalidator | tmpllibrary | tmplacl +-+---++--+-+---+- plpgsql | t | t | plpgsql_call_handler | plpgsql_inline_handler | plpgsql_validator | $libdir/plpgsql | pltcl | t | t | pltcl_call_handler | | | $libdir/pltcl | pltclu | f | f | pltclu_call_handler | | | $libdir/pltcl | plperl | t | t | plperl_call_handler | plperl_inline_handler | plperl_validator | $libdir/plperl | plperlu | f | f | plperlu_call_handler | plperlu_inline_handler | plperlu_validator | $libdir/plperl | plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | (6 rows) argosrm=# \q marcin@carbon2:~$ psql -U pgsql -h 192.168.89.64 template1 psql (12.4 (Ubuntu 12.4-1)) Type "help" for help. template1=# select * from pg_pltemplate ; tmplname | tmpltrusted | tmpldbacreate | tmplhandler | tmplinline | tmplvalidator | tmpllibrary | tmplacl +-+---++--+-+---+- plpgsql | t | t | plpgsql_call_handler | plpgsql_inline_handler | plpgsql_validator | $libdir/plpgsql | pltcl | t | t | pltcl_call_handler | | | $libdir/pltcl | pltclu | f | f | pltclu_call_handler | | | $libdir/pltcl | plperl | t | t | plperl_call_handler | plperl_inline_handler | plperl_validator | $libdir/plperl | plperlu | f | f | plperlu_call_handler | plperlu_inline_handler | plperlu_validator | $libdir/plperl | plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | (6 rows) what can I do next ? Thx Marcin Od: "Adrian Klaver" Do: "Devrim Gündüz" , "Tom Lane" DW: "Marcin Giedz" , "pgsql-general" Wysłane: wtorek, 17 listopad 2020 23:30:44 Temat: Re: pg_upgrade from 12 to 13 failes with plpython2 On 11/17/20 2:17 PM, Devrim Gündüz wrote: > > Hi, > > On Tue, 2020-11-17 at 16:23 -0500, Tom Lane wrote: >> You're confusing what the source code can do (which is what the >> manual documents) versus what individual packagers choose to support. >> The packagers frequently don't have a lot of choice in the matter; >> once their platform drops python2, they can't support plpython2. > First let me say: 1) I don't use Python 2 anymore. 2) I have converted my plpython(2)u functions to plpythonu3u 3) I don't use RH family distros. > Well, CentOS 8 "supports" PY2, however given that Christoph also > dropped PY2 support in Debian packages as of PostgreSQL 12 [1], and > given that *Python 2 is unsupported* anyway, I just wanted to drop > support at this point. Read that sentence again and see the inherent disconnect between the beginning and end. As a packager you are in charge of how the packaging is done. Still announcing a change that effectively nullifies the documentation would to me be something that should be announced somewhere else than a list that I'm guessing 99% of the users don't read. > > FWIW, older PostgreSQL major versions still have PY 2 support. This is > for PostgreSQL 13 only. > > [1] : > https://www.postgresql.org/message-id/20200522080114.GA449430%40msg.df7cb.de > > Regards, > -- Adrian Klaver adrian.kla...@aklaver.com -- Pozdrawiam Marcin Giedz Wiceprezes Zarządu ARISE Sp. z o.o. mob. +48 606 673 977 mail: marcin.gi...@arise.pl Al. Solidarności 117 00-140 Warszawa tel. +48 (22) 440 56 20 fax +48 (22) 440 56 22 http://www.arise.pl Grupa ARISE: ARISE Sp. z o.o. (podmiot dominujący), ARISE Services S.A., Al. Solidarności 117, 00-140 Warszawa, Sąd Rejonowy dla m. st. Warszawy w Warszawie XII Wydział Gospodarczy Krajowego Rejestru Sądowego, KRS 316860, REGON 141595449, NIP 5272590610, kapitał zakładowy wpłacony 250,000.00 zł.
restore with pg engine upgrade in a middle
Hi, the case is: one day we did full backup of database version 12 using standard procedure of continuous backup so: pg_start_backup/rsync/pg_stop_backup snip of WAL files archive directory: ver 12 PG Jul 28 04:37 000103340004 Jul 28 04:41 000103340005 Jul 28 05:16 000103340006 Jul 28 05:16 000103340006.0028.backup Jul 28 05:16 000103340007 Jul 28 05:18 000103340008 ver 13 PG Jul 28 05:18 000103340068 Jul 28 05:18 000103340069 once backup was finished, we migrated PG engine from 12 to 13 - as you can see in WAL filename structure. @ circa 5:18 all went OK however we have forgotten to adjust full backup script to newer version of PG so till mid of Nov we didn't have any full backup of our database except for WAL files and now we need to restore database with target time set to end of Sep. in this case how should I proceed with db restore including pg version switch from 12 to 13 ? Thx Marcin
Re: Performance degradation after upgrading from 9.5 to 14
how about this: jit = off ? Marcin On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh wrote: > 1) How did you upgrade? pg_dump or pg_upgrade? > > I use pg_ugrade with kink option. > > 2) Did you run ANALYZE to collect statistics after the upgrade? > > > Yes. I ran vacuumdb-analyze in stages after the upgrade > > 3) Did you transfer the configuration, or did you just create a new > cluster with the default values? > > I transfer the configuration > > 4) What exactly is slower? Queries? Inserts? > > queries > > 5) Can you quantify the impact? Is it 2x slower? 100x slower? > > it's more than 5 times slower than before. Very high load averages > > On Wed, Apr 17, 2024 at 1:25 PM Tomas Vondra < > tomas.von...@enterprisedb.com> wrote: > >> On 4/17/24 19:13, Johnathan Tiamoh wrote: >> > Hello, >> > >> > >> > I performed an upgrade from postgresql-9.5 to postgresql-14 and the >> > performance has degraded drastically. >> > >> > Please, is they any advice on getting performance back ? >> > >> >> There's very little practical advice we can provide based on this >> report, because it's missing any useful details. There's a number of >> things that might have caused this, but we'd have to speculate. >> >> For example: >> >> 1) How did you upgrade? pg_dump or pg_upgrade? >> >> 2) Did you run ANALYZE to collect statistics after the upgrade? >> >> 3) Did you transfer the configuration, or did you just create a new >> cluster with the default values? >> >> 4) What exactly is slower? Queries? Inserts? >> >> 5) Can you quantify the impact? Is it 2x slower? 100x slower? >> >> >> regards >> >> >> -- >> Tomas Vondra >> EnterpriseDB: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> >
