Re: Help with plpython3u

2020-06-09 Thread Laurenz Albe
On Tue, 2020-06-09 at 20:26 -0400, PEDRO PABLO SEVERIN HONORATO wrote: > I installed Postgres downloading the file posgresql-12.2-1-windows-x64 from > the website https://www.postgresql.org/download/windows/. > > The python37.dll I copied to the System32 folder was located in > C:\Users\develop

Re: Logical replication stuck in catchup state

2020-06-09 Thread Dan shmidt
Thank you very much for your replies. Regarding the server logs, I didn't find anything but healthy log when the server start which says that it is going to recover from the same point in WAL which was last sent. Regarding bugfixes, I will try to update ASAP - but wouldn't a restart of the serv

RE: Postgres server 12.2 crash with process exited abnormally and possibly corrupted shared memory

2020-06-09 Thread Ishan Joshi
Hi Michael, Thanks for your response. Please find answers for your questions How many rows did these tables have before partitioning? --> We starts test with 0 rows in partition table. Why did you decide to partition? --> These tables are

Re: Planner misestimation for JOIN with VARCHAR

2020-06-09 Thread Sebastian Dressler
> On 9. Jun 2020, at 21:30, Michael Lewis wrote: > >> On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler >> wrote: >> - Add an index on top of the whole PK >> - Add indexes onto other columns trying to help the JOIN >> - Add additional statistics on two related columns >> >> Another idea I h

Re: Help with plpython3u

2020-06-09 Thread PEDRO PABLO SEVERIN HONORATO
Thank you, Adrian. Will read about all this. By the way, the issue reappeared. For some reason, I had to reinstall Anaconda and had to delete the "PYTHONHOME" variable because I could not launch conda. Seems that they both fight for the same variable name? Is there a way to set it to two different

Re: Help with plpython3u

2020-06-09 Thread Adrian Klaver
On 6/9/20 6:17 PM, PEDRO PABLO SEVERIN HONORATO wrote: Thanks Adrian, this actually helped me. I was looking for the "language pack" thing without any success and thanks god that you pointed me in the right direction :) What I don't understand is the following. After installing the language p

Re: Help with plpython3u

2020-06-09 Thread PEDRO PABLO SEVERIN HONORATO
Thanks Adrian, this actually helped me. I was looking for the "language pack" thing without any success and thanks god that you pointed me in the right direction :) What I don't understand is the following. After installing the language pack with stack builder, added a variable named "PYTHONHOME"

Re: Help with plpython3u

2020-06-09 Thread Adrian Klaver
On 6/9/20 5:26 PM, PEDRO PABLO SEVERIN HONORATO wrote: Hi Adrian, I installed Postgres downloading the file posgresql-12.2-1-windows-x64 from the website https://www.postgresql.org/download/windows/. Did you go through the steps here?: https://www.enterprisedb.com/edb-docs/d/postgresql/user-

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Adrian Klaver
On 6/9/20 4:35 PM, Peter wrote: On Tue, Jun 09, 2020 at 12:34:38PM -0700, Adrian Klaver wrote: And that can very well be done properly with an incremental filesystem backup software plus some 20 lines of shellscript. Read the caveats here: https://www.postgresql.org/docs/12/backup-file.ht

Re: Help with plpython3u

2020-06-09 Thread PEDRO PABLO SEVERIN HONORATO
Hi Adrian, I installed Postgres downloading the file posgresql-12.2-1-windows-x64 from the website https://www.postgresql.org/download/windows/. The python37.dll I copied to the System32 folder was located in C:\Users\developer\Anaconda3. After trying this, I installed python 3.7.0 and copied the

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Stephen Frost
Greetings, * Peter (p...@citylink.dinoex.sub.org) wrote: > On Tue, Jun 09, 2020 at 03:42:48PM -0400, Stephen Frost wrote: > ! * Peter (p...@citylink.dinoex.sub.org) wrote: > ! > This professional backup solution also offers support for postgres. > ! > Sadly, it only covers postgres up to Rel.9, an

Re: Help with plpython3u

2020-06-09 Thread Adrian Klaver
On 6/9/20 5:09 PM, PEDRO PABLO SEVERIN HONORATO wrote: Hello! I'm cracking my head while trying to make python language work within postges. I executed "*create extension plpython3u*" but I get this error: "*ERROR: could not load library "C:/Program Files/PostgreSQL/12/lib/plpython3.dll": Th

Help with plpython3u

2020-06-09 Thread PEDRO PABLO SEVERIN HONORATO
Hello! I'm cracking my head while trying to make python language work within postges. I executed "*create extension plpython3u*" but I get this error: "*ERROR: could not load library "C:/Program Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not be found. SQL state: 58P01*" L

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Peter
On Tue, Jun 09, 2020 at 12:34:38PM -0700, Adrian Klaver wrote: ! The backup solution is? https://www.bareos.com/ ! Fine rant below. Go forth and work your wonders. I don't need to, anymore. I did that, for about 20 years - people I used to work for as a consultant (major banks and insurance sho

Re: Planner misestimation for JOIN with VARCHAR

2020-06-09 Thread David Rowley
On Wed, 10 Jun 2020 at 09:05, Michael Lewis wrote: >> >> the join selectivity functions have yet to learn about extended statistics. > > > That is very interesting to me. So, extended statistics would help to > properly estimate the result set coming out of a single table when comparing > each o

Re: Logical replication stuck in catchup state

2020-06-09 Thread Peter Eisentraut
On 2020-06-09 23:30, Dan shmidt wrote: We have a setup in which there are several master nodes replicating to a single slave/backup node. We are using Postgres 11.4. Recently, one of the nodes seems to be stuck and stopped replicating. I did some basic troubleshooting and couldn't find the root

Re: Logical replication stuck in catchup state

2020-06-09 Thread Michael Lewis
I don't know if it would be relevant to this problem, but you are missing almost 1 full year of bug fixes. 11.4 was released on 20 June last year. Upgrading minor versions asap is recommended. I do see this in the release notes from 11.8 last month ( https://www.postgresql.org/docs/release/11.8/)-

Re: Planner misestimation for JOIN with VARCHAR

2020-06-09 Thread Michael Lewis
> > the join selectivity functions have yet to learn about extended statistics. > That is very interesting to me. So, extended statistics would help to properly estimate the result set coming out of a single table when comparing each of those columns to one or many values, but not when joining up

Re: Planner misestimation for JOIN with VARCHAR

2020-06-09 Thread David Rowley
On Wed, 10 Jun 2020 at 07:31, Michael Lewis wrote: > > On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler > wrote: >> >> - Add an index on top of the whole PK >> - Add indexes onto other columns trying to help the JOIN >> - Add additional statistics on two related columns >> >> Another idea I ha

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Adrian Klaver
On 6/9/20 12:02 PM, Peter wrote: On Tue, Jun 09, 2020 at 01:27:20AM -0400, Tom Lane wrote: ! Adrian Klaver writes: ! > On 6/8/20 7:33 PM, Peter wrote: ! >> That "cp" is usually not synchronous. So there is the possibility ! >> that this command terminates successfully, and reports exitcode zero

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Peter
On Tue, Jun 09, 2020 at 01:27:20AM -0400, Tom Lane wrote: ! Adrian Klaver writes: ! > On 6/8/20 7:33 PM, Peter wrote: ! >> That "cp" is usually not synchronous. So there is the possibility ! >> that this command terminates successfully, and reports exitcode zero ! >> back to the Postgres, and then

Re: Postgres server 12.2 crash with process exited abnormally and possibly corrupted shared memory

2020-06-09 Thread Michael Lewis
On Tue, Jun 9, 2020 at 8:35 AM Ishan Joshi wrote: > I have using postgresql server v12.2 on CentOS Linux release 7.3.1611 > (Core). > > > > My application is working fine with non partition tables but recently we > are trying to adopt partition table on few of application tables. > > So we have

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Stephen Frost
Greetings, * Peter (p...@citylink.dinoex.sub.org) wrote: > This professional backup solution also offers support for postgres. > Sadly, it only covers postgres up to Rel.9, and that piece of software > wasn't touched in the last 6 or 7 years. Then it certainly doesn't work with the changes in v12

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Adrian Klaver
On 6/9/20 10:55 AM, Peter wrote: On Mon, Jun 08, 2020 at 09:21:47PM -0700, Adrian Klaver wrote: ! ! On 6/8/20 7:33 PM, Peter wrote: ! > ! > Actually, the affair had some good side: as usual I was checking ! > my own designs first and looking for flaws, and indeed I found one: ! > If you do copy o

Re: Planner misestimation for JOIN with VARCHAR

2020-06-09 Thread Michael Lewis
On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler wrote: > - Add an index on top of the whole PK > - Add indexes onto other columns trying to help the JOIN > - Add additional statistics on two related columns > > Another idea I had was to make use of generated columns and hash the PKs > together

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Peter
On Mon, Jun 08, 2020 at 09:21:47PM -0700, Adrian Klaver wrote: ! ! On 6/8/20 7:33 PM, Peter wrote: ! > ! > Actually, the affair had some good side: as usual I was checking ! > my own designs first and looking for flaws, and indeed I found one: ! > If you do copy out the archive logs not directly

Planner misestimation for JOIN with VARCHAR

2020-06-09 Thread Sebastian Dressler
Helloes, I do have a set of tables which contain user data and users can choose to have columns as constrained VARCHAR, limit is typically 100. While users can also choose from different types, quite often they go the VARCHAR route. Furthermore, they can pick PKs almost freely. As a result, I q

Re: [HELP] query regarding replication

2020-06-09 Thread Jerry Sievers
Andreas Kretschmer writes: > Am 09.06.20 um 10:44 schrieb Praveen Kumar K S: >> Thanks. Will this approach replicate DDL changes ? No. > > sure. > > > Regards, Andreas -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Postgres server 12.2 crash with process exited abnormally and possibly corrupted shared memory

2020-06-09 Thread Ishan Joshi
Hi Team, I have using postgresql server v12.2 on CentOS Linux release 7.3.1611 (Core). My application is working fine with non partition tables but recently we are trying to adopt partition table on few of application tables. So we have created List partition on 6 table. 2 out of 6 tables have

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Stephen Frost
Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 6/9/20 4:15 AM, Stephen Frost wrote: > >* Adrian Klaver (adrian.kla...@aklaver.com) wrote: > >>I use pg_backrest, but it does not look promising for running on BSD: > >>https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Adrian Klaver
On 6/9/20 4:15 AM, Stephen Frost wrote: Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: I use pg_backrest, but it does not look promising for running on BSD: https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html That's an unfortunately ancient post, really, considering

Re: troubleshooting postgresql ldap authentication

2020-06-09 Thread Stephen Frost
Greetings, * Chris Stephens (cstephen...@gmail.com) wrote: > yes, shortly after i sent this out to the list, one of our security > administrators suggested ldapscheme. I just tested and ldapurl works as > well. > > the security admin explained it like this: > > "since we are using port 636 I kn

Re: troubleshooting postgresql ldap authentication

2020-06-09 Thread Chris Stephens
yes, shortly after i sent this out to the list, one of our security administrators suggested ldapscheme. I just tested and ldapurl works as well. the security admin explained it like this: "since we are using port 636 I know that it needs the TLS connection in place before LDAP commands. starttl

Re: Index no longer being used, destroying and recreating it restores use.

2020-06-09 Thread Koen De Groote
Right. In that case, the function I ended up with is this: create or replace function still_needs_backup(bool, bool) returns BOOLEAN as $$ BEGIN PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2; IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE;

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Stephen Frost
Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > I use pg_backrest, but it does not look promising for running on BSD: > https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html That's an unfortunately ancient post, really, considering that pgbackrest has now been fully rewri

Re: [HELP] query regarding replication

2020-06-09 Thread Andreas Kretschmer
Am 09.06.20 um 10:44 schrieb Praveen Kumar K S: Thanks. Will this approach replicate DDL changes ? sure. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: [HELP] query regarding replication

2020-06-09 Thread Praveen Kumar K S
Thanks. Will this approach replicate DDL changes ? On Tue, Jun 9, 2020 at 1:36 PM Andreas Kretschmer wrote: > > > Am 09.06.20 um 09:55 schrieb Praveen Kumar K S: > > Can I achieve master/slave streaming replication by setting WAL_LEVEL > > to logical on master ? Are there any drawbacks of it ? >

Re: [HELP] query regarding replication

2020-06-09 Thread Andreas Kretschmer
Am 09.06.20 um 09:55 schrieb Praveen Kumar K S: Can I achieve master/slave streaming replication by setting WAL_LEVEL to logical on master ? Are there any drawbacks of it ? yes, no problem. the wal's would be a bit larger, that's all. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Suppo

[HELP] query regarding replication

2020-06-09 Thread Praveen Kumar K S
Hello, I had setup one master and hot-slave by setting WAL_LEVEL=replica But I use a debezium plugin and it requires to connect to master with WAL_LEVEL=logical and it is mandatory. Can I achieve master/slave streaming replication by setting WAL_LEVEL to logical on master ? Are there any drawback

Re: Is it possible to use keywords (date units) in a function definition?

2020-06-09 Thread Alistair Johnson
> > I think you need to be more specific as to what "this" means. > > Looking again after Andrian's comment are you trying to write, in the > script file: > > datetime_diff('start time as string'::timestamp, 'end time as > string'::timestamp, HOUR) > > and get PostgreSQL to recognize the value HOUR