Re: Error: checkpoint occurs too frequently

On Sat, 2020-11-14 at 04:19 +0530, Atul Kumar wrote: > I am getting the error notification as "PostgreSQL: Required > checkpoints occurs too frequently". > > But I am could not find any reason of it. Lots of data modification activity that triggers checkpoints too frequently. You should do exact

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

Hi, On Fri, 2020-11-13 at 14:19 -0500, Jeremy Wilson wrote: > > If that shows up two different "proj" libraries, then you have that > > same problem. > > Just wanted to reply that this was indeed an issue.  After removing > the proj RPMs and reinstalling, the upgrade seems to be running now. (r

RE: New "function tables" in V13 documentation

>From: David G. Johnston >>On Fri, Nov 13, 2020 at 12:20 PM Kevin Brannen >>wrote: >>Designing pages to the smallest media just frustrates those users on larger >>media (cue the many examples on the web where the left/right margins are so >>wide half of your scree

RE: Range partitioning and overlap

De: Tom Lane Enviado: sexta-feira, 13 de novembro de 2020 17:58 Para: Edson Richter Cc: David G. Johnston ; pgsql-general Assunto: Re: Range partitioning and overlap Edson Richter writes: > Further on the documentation: "When creating a range partition, the lower > bound specified with FROM

Re: Range partitioning and overlap

Edson Richter writes: > Further on the documentation: "When creating a range partition, the lower > bound specified with FROM is an inclusive bound, whereas the upper bound > specified with TO is an exclusive bound." > I'm pretty sure I cannot find this statement in PostgreSQL 13 documentation

Restoring database from false update

Hi! Yesterday I updated the version of posgtresql from 12.0 to 12.5-1. Today I found that for some reason (*) there was bug in package update script which caused the postgresql to think that it should be updated from 12 to 13. I followed instructions to update postgresql (**). What I did: 1) co

Re: New "function tables" in V13 documentation

On 11/13/20 11:30 AM, David G. Johnston wrote: On Fri, Nov 13, 2020 at 12:20 PM Kevin Brannen > wrote: Go to the string funcs/ops page in v13, and try to quickly find the ones that return an "int" (because your goal is to find the position of something in a

RE: Range partitioning and overlap

De: David G. Johnston Enviado: sexta-feira, 13 de novembro de 2020 17:32 Para: Edson Richter Cc: pgsql-general Assunto: Re: Range partitioning and overlap On Fri, Nov 13, 2020 at 1:29 PM Edson Richter mailto:edsonrich...@hotmail.com>> wrote: "Range Partitioning The table is partitioned into

Re: New "function tables" in V13 documentation

On 11/13/20 11:20 AM, Kevin Brannen wrote: From: David G. Johnston On Mon, Nov 9, 2020 at 1:41 PM Tom Lane wrote: Alvaro Herrera writes: On 2020-Nov-08, Adrian Klaver wrote: Yeah, I would agree with the mobile first design comment

Re: Range partitioning and overlap

On Fri, Nov 13, 2020 at 1:29 PM Edson Richter wrote: > "Range Partitioning > > The table is partitioned into “ranges” defined by a key column or set of > columns, with no overlap between the ranges of values assigned to different > partitions. For example, one might partition by date ranges, or b

Range partitioning and overlap

Hi, Using PostgreSQL 13.1 - I need your guidance about corretly implementing partition by timestamp ranges. Looking at documentation ( https://www.postgresql.org/docs/13/ddl-partitioning.html ) there a statement saying explicit "Range Partitioning The table is partitioned into “ranges” defin

Re: New "function tables" in V13 documentation

On Fri, Nov 13, 2020 at 12:20 PM Kevin Brannen wrote: > Go to the string funcs/ops page in v13, and try to quickly find the ones > that return an "int" (because your goal is to find the position of > something in a string so you know the return value will have to be an > "int"). > That is not so

RE: New "function tables" in V13 documentation

>From: David G. Johnston >On Mon, Nov 9, 2020 at 1:41 PM Tom Lane wrote: >Alvaro Herrera writes: >> On 2020-Nov-08, Adrian Klaver wrote: >>> Yeah, I would agree with the mobile first design comments. Then again that >>> plague is hittin

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 1:10 PM, Magnus Hagander wrote: > > The problem is that postgis, through gdal, ended up being linked to two > different versions of proj at the same time. > > You can check it by doing: > ldd /usr/pgsql-13/lib/postgis_raster-3.so | grep proj > > If that shows up two di

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 9:12 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 12:06 PM, Adrian Klaver wrote: Hmm. You can still connect if you use?: /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start Same result. bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile

Re: conflict with recovery when delay is gone

On Fri, Nov 13, 2020 at 7:37 PM Laurenz Albe wrote: > On Fri, 2020-11-13 at 15:24 +0200, Radoslav Nedyalkov wrote: > > On a very busy master-standby setup which runs typical olap processing - > > long living , massive writes statements, we're getting on the standby: > > > > ERROR: canceling st

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On Fri, Nov 13, 2020 at 7:10 PM Magnus Hagander wrote: > dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_12 > postgis30_12-devel postgis30_12-utils postgis30_12-client postgis30_12-docs > > On Fri, Nov 13, 2020 at 7:01 PM Tom Lane wrote: > > > > Bruce Momjian writes: > > > O

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_12 postgis30_12-devel postgis30_12-utils postgis30_12-client postgis30_12-docs On Fri, Nov 13, 2020 at 7:01 PM Tom Lane wrote: > > Bruce Momjian writes: > > On Fri, Nov 13, 2020 at 12:06:34PM -0500, Jeremy Wilson wrote: > >> Not

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

Bruce Momjian writes: > On Fri, Nov 13, 2020 at 12:06:34PM -0500, Jeremy Wilson wrote: >> Not sure what you mean by this - I’ve installed the postgis packages for 9.5 >> and 13 and the extensions are installed and working in 9.5, but I’m not >> doing anything but initdb and then pg_upgrade for 1

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On Fri, Nov 13, 2020 at 12:06:34PM -0500, Jeremy Wilson wrote: > > > > On Nov 13, 2020, at 12:00 PM, Tom Lane wrote: > > > > Are you by any chance trying to preload any of the postgis-related > > extensions? If so, try not doing that. > > Not sure what you mean by this - I’ve installed the po

Re: conflict with recovery when delay is gone

On Fri, 2020-11-13 at 15:24 +0200, Radoslav Nedyalkov wrote: > On a very busy master-standby setup which runs typical olap processing - > long living , massive writes statements, we're getting on the standby: > > ERROR: canceling statement due to conflict with recovery > FATAL: terminating co

Re: PostgreSQL equivalent to Oracles ANYDATASET

## Dirk Mika (dirk.m...@mikatiming.de): > SELECT * FROM TABLE(series_pkg.get_results(1)); > > The purpose of this function is to provide a DATASET, which has > different columns in the result depending on the passed parameter. > > Is there any way to achieve something similar in PostreSQL? test

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 12:06 PM, Adrian Klaver wrote: > > Hmm. You can still connect if you use?: > > /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start Same result. bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start waiting for server to start..

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 8:40 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 11:39 AM, Adrian Klaver wrote: This does not show trying to connect to a database. It would help to list the commands run and then the corresponding log portions. bash-4.4$ "/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.lo

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 12:00 PM, Tom Lane wrote: > > Are you by any chance trying to preload any of the postgis-related > extensions? If so, try not doing that. Not sure what you mean by this - I’ve installed the postgis packages for 9.5 and 13 and the extensions are installed and working in

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

Jeremy Wilson writes: > I did a completely fresh initdb to get clean logs: > ... > free(): invalid pointer > 2020-11-13 11:30:05.292 EST [205647] LOG: server process (PID 205659) was > terminated by signal 6: Aborted This is highly significant. It suggests that you're getting bit by the postg

Re: Problem with psprintf and intmax_t (%jd)

Jan Behrens writes: > I'm facing a problem with psprintf and the %jd format string. I used > the following C-code: > PG_RETURN_CSTRING(psprintf("%d@%jd", (int)1, (intmax_t)2)); > While this worked fine in past, I recently get (with PostgreSQL 13): > ERROR: vsnprintf failed: Invalid argument wi

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 11:39 AM, Adrian Klaver wrote: > > This does not show trying to connect to a database. It would help to list the > commands run and then the corresponding log portions. bash-4.4$ "/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/13/data" -o "-

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 8:37 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 11:35 AM, Adrian Klaver wrote: On 11/13/20 8:31 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 11:26 AM, Adrian Klaver wrote: What shows up in the log file in "log" directory when you try to connect to a database? I did a compl

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 11:35 AM, Adrian Klaver wrote: > > On 11/13/20 8:31 AM, Jeremy Wilson wrote: >>> On Nov 13, 2020, at 11:26 AM, Adrian Klaver >>> wrote: >>> >>> What shows up in the log file in "log" directory when you try to connect to >>> a database? >> I did a completely fresh init

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 8:31 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 11:26 AM, Adrian Klaver wrote: What shows up in the log file in "log" directory when you try to connect to a database? I did a completely fresh initdb to get clean logs: Was the below from starting using the pg_upgrade versio

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 8:27 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 11:26 AM, Adrian Klaver wrote: What shows up in the log file in "log" directory when you try to connect to a database? 2020-11-13 10:14:35.821 EST [204797] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 11:26 AM, Adrian Klaver wrote: > > What shows up in the log file in "log" directory when you try to connect to a > database? I did a completely fresh initdb to get clean logs: bash-4.4$ cat 13/data/log/postgresql-Fri.log 2020-11-13 11:29:44.744 EST [205647] LOG: start

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 11:26 AM, Adrian Klaver wrote: > > What shows up in the log file in "log" directory when you try to connect to a > database? 2020-11-13 10:14:35.821 EST [204797] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 8:19 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 11:06 AM, Adrian Klaver wrote: When you manually run the pg_upgrade pg_ctl script the server starts but you cannot connect to any database in it correct? Yes. What does pg_upgrade_server.log show when you do above?

Problem with psprintf and intmax_t (%jd)

Dear all, I'm facing a problem with psprintf and the %jd format string. I used the following C-code: PG_RETURN_CSTRING(psprintf("%d@%jd", (int)1, (intmax_t)2)); While this worked fine in past, I recently get (with PostgreSQL 13): ERROR: vsnprintf failed: Invalid argument with format string "%d

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 11:06 AM, Adrian Klaver wrote: > > When you manually run the pg_upgrade pg_ctl script the server starts but you > cannot connect to any database in it correct? Yes. > What does pg_upgrade_server.log show when you do above? -

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 10:46 AM, Tom Lane wrote: > > Hmph. We know that 9.5 -> 13 pg_upgrade works in simple scenarios, > because the buildfarm tests that every day. So there has to be > something out of the ordinary about your setup. Any unusual > extensions, pg_hba.conf configuration, etc?

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 7:28 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 10:23 AM, Tom Lane wrote: Unless ... could it be that there is another PG server active on the machine, whose cluster lacks a "template1" database? Seems unlikely, but you might try confirming with "ps auxww | grep post" or the l

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

Jeremy Wilson writes: > [ no soap on either of my theories ] Hmph. We know that 9.5 -> 13 pg_upgrade works in simple scenarios, because the buildfarm tests that every day. So there has to be something out of the ordinary about your setup. Any unusual extensions, pg_hba.conf configuration, etc?

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 10:23 AM, Tom Lane wrote: > > Unless ... could it be that there is another PG server active on the > machine, whose cluster lacks a "template1" database? Seems unlikely, > but you might try confirming with "ps auxww | grep post" or the like. This is a test environment s

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

Jeremy Wilson writes: >> On Nov 13, 2020, at 10:09 AM, Adrian Klaver >> wrote: >> In your previous post you had --socketdir=/var/run/postgresql/. Did you >> change that or is it missing? > Sorry, here it is with the socket directory specified. An incorrect socket-directory setting would lead

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 10:09 AM, Adrian Klaver wrote: > > In your previous post you had --socketdir=/var/run/postgresql/. Did you > change that or is it missing? Sorry, here it is with the socket directory specified. bash-4.4$ rm -r 13/data/* bash-4.4$ bash-4.4$ /usr/pgsql-13/bin/initdb -D /

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 7:02 AM, Jeremy Wilson wrote: On Nov 13, 2020, at 9:58 AM, Adrian Klaver wrote: To me it seems the initdb for the 13 instance did not complete successfully. Have you tried clearing /var/lib/pgsql/13/data and doing the init over again? If you do try it monitor the output careful

Re: "invalid record length" after restoring pg_basebackup

Dennis Jacobfeuerborn writes: > All of this works fine and the logs report that the db reaches a > consistent recovery state but as last entry it reports an "invalid > record length": This looks quite normal to me. If you'd pulled the power plug on the primary system at the time you made this ba

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

> On Nov 13, 2020, at 9:58 AM, Adrian Klaver wrote: > > To me it seems the initdb for the 13 instance did not complete successfully. > Have you tried clearing /var/lib/pgsql/13/data and doing the init over again? > If you do try it monitor the output carefully. here’s the complete process:

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 6:32 AM, Jeremy Wilson wrote: I’m running CentOS 8 on an EC2 instance and attempting to upgrade a 9.5 database to 13 using pg_upgrade. Both are running on the same box and pass initial tests but it fails during the later part of the process. --- bash-4.4$ /usr/pgsql-13/bin/pg_upg

Re: Failed Login Attempts in PostgreSQL

You can use fail2ban for example. See for example this thread here https://www.postgresql.org/message-id/flat/61463e206b7c4c0ca17b03a59e890b78%40lmco.com, and the config on https://github.com/rc9000/postgres-fail2ban-lockout. (probably needs some small adaptations, but as a base it should work

Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

I’m running CentOS 8 on an EC2 instance and attempting to upgrade a 9.5 database to 13 using pg_upgrade. Both are running on the same box and pass initial tests but it fails during the later part of the process. --- bash-4.4$ /usr/pgsql-13/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin --new-

conflict with recovery when delay is gone

Hi Forum,all On a very busy master-standby setup which runs typical olap processing - long living , massive writes statements, we're getting on the standby: ERROR: canceling statement due to conflict with recovery FATAL: terminating connection due to conflict with recovery The weird thing is

"invalid record length" after restoring pg_basebackup

Hi, I've run into a strange issue after restoring a backup that I created using pg_basebackup on a standby instance. The command I use to create the backup is this: pg_basebackup -v --write-recovery-conf -h$BACKUP_HOST -p5432 -U$BACKUP_USER --format tar --wal-method stream --compress=2 -D "$BACKUP

Re: Failed Login Attempts in PostgreSQL

On Fri, Nov 13, 2020 at 11:03 AM Jagmohan Kaintura wrote: > > Hi Team, > I was looking for a workaround on how we can configure Failed Login attempts > feature of Oracle in PostgreSQL. > The Only requirement is End user shouldn't be allowed to Login after an "n" > number of unsuccessful attempts

Failed Login Attempts in PostgreSQL

Hi Team, I was looking for a workaround on how we can configure Failed Login attempts feature of Oracle in PostgreSQL. The Only requirement is End user shouldn't be allowed to Login after an "n" number of unsuccessful attempts. Users have the ability to perform all operations on the underlying tab

Re: Is it possible to write a generic UPSERT?

On 12.11.20 18:34, Michael Lewis wrote: > On Thu, Nov 12, 2020 at 6:58 AM Mario Emmenlauer > wrote: > > I can see how "ON CONFLICT" is very powerful. But that power seems > often a burden for us. We would prefer something that is less manual > effort for th