date:20201113

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 
> page about partitioning. May be this statement is in another page?

It's in the CREATE TABLE reference page.  Seems like it would be a good
idea to have it also in ddl.sgml's discussion of partitioning, though.

regards, tom lane

Restoring database from false update

2020-11-13 Thread Maksim Fomin

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) copied postgresql folder to backup destination (mv)
2) created new directories (mkdir) and changed permissions (chown)
3) executed 'initdb' inside new folder

Then I tried to upgrade the database via 'pg_upgrade' but realised that I 
cannot do that because in my distro binaries of previous package version are 
gone, so I cannot supply arguement to the '-b' parameter. At this point I 
realized that the upgrade step should not be necessary for transition from 12 
to 12.5. I found the issue in bugzilla (*) and updated postgresql from 12.5-1 
12.5-3. Later, I stopped the service and moved backup folder to the usual 
place. For some reason psql shows that there are no relations found in the 
database, although the database is listed. My next step was to copy data from 
file-system level backup (about 4-5 days ago) but the result was the same.

How I can restore the database?

* https://bugs.archlinux.org/task/68601
** https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL

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 string so you know the return value will have to
be an "int").


That is not something I considered...I figured people would look for a 
name that seems to reflect "position" or "in_string".  I've never felt 
the need to search based upon return type.


Which is an indication that for changes of this scope it would be 
prudent to create a mock up and have end users see and comment on before 
rolling them out.




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 screen is wasted instead of letting
the text flow and resize).]


It is just as bad it is so wide that one has to move their head instead 
of just moving their eyes.  If anything our tables could probably be 
improved by enforcing a maximum width to the content area.


David J.




--
Adrian Klaver
adrian.kla...@aklaver.com

RE: Range partitioning and overlap

2020-11-13 Thread Edson Richter


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 “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 by ranges of 
identifiers for particular business objects."

Is there a misinterpretation from my side, or examples are inconsistent with 
the paragraph above?

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."

David J.

I'm pretty sure I cannot find this statement in PostgreSQL 13 documentation 
page about partitioning. May be this statement is in another page?
Would you mind to share the URL with this statement?

Thanks,

Edson

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 comments. Then again that
plague is hitting most sites these days. My 2 cents is it is a step
backwards. You can cover more ground quickly and digest it faster in the old
format.



If you have suggestion on how to improve the new format, I'm sure we can
discuss that.  It seems pretty clear to me that we're not going back to
the old format.



I think there's no question that the new format is better in any case
where a function needs more than a couple words of documentation.
I could see the argument for adopting a more compact format for tables
that contain no such functions.  I think you might find that the set of
such tables is nigh empty, though; even section 9.3 (mathematical
functions) has a lot of functions that need a sentence or two.  We used
to either omit important details for such functions or stick them in
footnotes, and neither of those options is very nice.



My observation is that the new format reduces one's ability to quickly skim the 
table to find out what is present since there is considerable extra information 
in one's eyes during that process that needs to be skimmed over.



I'm slow to the party, but I'm going to go with the new format is horrible. I agree with David that 
you can't quickly scan down the new table to find things like the return type (for example) which 
is something I do frequently. 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"). The new version makes that 
hard while the old version is easy. In fact, I couldn't even find the return type at first when I 
looked because there is no label (the power of tables with headers was removed).

The description and example also run together under the new format, which sort of comes 
across as a "wall of text". If I really zoom in, I can see they're different 
fonts, but at my normal zoom level they look pretty much the same at first glance.


+1



This makes me want to stay on v12.x for as long as possible -- really.


Second the motion.



If the maintainers are dead-set on maintaining the new format despite it 
drawbacks (and after reading all the other comments about the positives I'm 
going to say I don't see any positives**), then it'd be extremely helpful to do 
some CSS magic to make them easier to read. Personally, I'd like to see there 
be a setting for if media is HTML that it show the old table format, but if 
not, here's some ideas to make the new format more palatable:

* The return type needs to stand out a LOT more, bold would be a great start, 
adding color would help too.

* Make the description and example look much more different, again color or 
perhaps color banding (background a light gray or something on the example).

* Make the example code and example results more different, the simple "->" 
between them gets lost easily to my eye so it's harder to read as is.

* Can you add a few more classes to identify the parts betters in the tables? For example, I see 
the CSS class "returnvalue" on the example result, but there is nothing on the example 
code itself identifying it as such. If you did this better labeling then perhaps those of us who 
are complaining could attempt to overcome some of our objections by restyling the tables with 
colors & fonts & such. Not a full solution as that would require manipulating the DOM, 
but even small changes with color could bring large relief.

[** I think it was Bruce who pointed out the old table format was troublesome 
in the PDF format. I concede that and anything else for when you're constrained 
to paper. But I suspect most users look at these docs on a computer monitor 
with HTML so those size constraints aren't an issue there. 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 
screen is wasted instead of letting the text flow and resize).]

Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.




--
Adrian

Re: Range partitioning and overlap

2020-11-13 Thread David G. Johnston

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 by ranges
> of identifiers for particular business objects."
> Is there a misinterpretation from my side, or examples are inconsistent
> with the paragraph above?
>

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."

David J.

Range partitioning and overlap

2020-11-13 Thread Edson Richter

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” 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 by ranges of 
identifiers for particular business objects."


But afterwards, looking into examples, the ranges overlaps:


CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
TABLESPACE fasttablespace;

Is there a misinterpretation from my side, or examples are inconsistent with 
the paragraph above?

Thanks,

Edson

Re: New "function tables" in V13 documentation

2020-11-13 Thread David G. Johnston

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 something I considered...I figured people would look for a name
that seems to reflect "position" or "in_string".  I've never felt the need
to search based upon return type.

> 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 screen is wasted instead of letting the text flow
> and resize).]
>

It is just as bad it is so wide that one has to move their head instead of
just moving their eyes.  If anything our tables could probably be improved
by enforcing a maximum width to the content area.

David J.

RE: New "function tables" in V13 documentation

2020-11-13 Thread Kevin Brannen

>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 hitting most sites these days. My 2 cents is it is a step
>>> backwards. You can cover more ground quickly and digest it faster in the old
>>> format.

>> If you have suggestion on how to improve the new format, I'm sure we can
>> discuss that.  It seems pretty clear to me that we're not going back to
>> the old format.

>>I think there's no question that the new format is better in any case
>>where a function needs more than a couple words of documentation.
>>I could see the argument for adopting a more compact format for tables
>>that contain no such functions.  I think you might find that the set of
>>such tables is nigh empty, though; even section 9.3 (mathematical
>>functions) has a lot of functions that need a sentence or two.  We used
>>to either omit important details for such functions or stick them in
>>footnotes, and neither of those options is very nice.

>My observation is that the new format reduces one's ability to quickly skim 
>the table to find out what is present since there is considerable extra 
>information in one's eyes during that process that needs to be skimmed over.

I'm slow to the party, but I'm going to go with the new format is horrible. I 
agree with David that you can't quickly scan down the new table to find things 
like the return type (for example) which is something I do frequently. 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"). The new version makes that 
hard while the old version is easy. In fact, I couldn't even find the return 
type at first when I looked because there is no label (the power of tables with 
headers was removed).

The description and example also run together under the new format, which sort 
of comes across as a "wall of text". If I really zoom in, I can see they're 
different fonts, but at my normal zoom level they look pretty much the same at 
first glance.

This makes me want to stay on v12.x for as long as possible -- really.

If the maintainers are dead-set on maintaining the new format despite it 
drawbacks (and after reading all the other comments about the positives I'm 
going to say I don't see any positives**), then it'd be extremely helpful to do 
some CSS magic to make them easier to read. Personally, I'd like to see there 
be a setting for if media is HTML that it show the old table format, but if 
not, here's some ideas to make the new format more palatable:

* The return type needs to stand out a LOT more, bold would be a great start, 
adding color would help too.

* Make the description and example look much more different, again color or 
perhaps color banding (background a light gray or something on the example).

* Make the example code and example results more different, the simple "->" 
between them gets lost easily to my eye so it's harder to read as is.

* Can you add a few more classes to identify the parts betters in the tables? 
For example, I see the CSS class "returnvalue" on the example result, but there 
is nothing on the example code itself identifying it as such. If you did this 
better labeling then perhaps those of us who are complaining could attempt to 
overcome some of our objections by restyling the tables with colors & fonts & 
such. Not a full solution as that would require manipulating the DOM, but even 
small changes with color could bring large relief.

[** I think it was Bruce who pointed out the old table format was troublesome 
in the PDF format. I concede that and anything else for when you're constrained 
to paper. But I suspect most users look at these docs on a computer monitor 
with HTML so those size constraints aren't an issue there. 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 
screen is wasted instead of letting the text flow and resize).]

Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

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 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.

On a side note, you need to enable the PowerTools repo on CentOS to install 
PostGIS, otherwise it complains about the gdal libraries being missing.

If I run into another failure I’ll update the list - thanks!

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 start
waiting for server to start done
server started
bash-4.4$ psql postgres
psql: error: could not connect to server: FATAL:  database "postgres" does not 
exist



In this post:

https://www.postgresql.org/message-id/793E4164-90E9-41F0-B74C-129E1DB76408%40clover.co

it worked. Though in that case pg_upgrade was not run before you ran:

/usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start

Can you start with clean initdb and then run above command to see if you 
can connect. Then run:


/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/lib/pgsql/13/data" -o "-p 5432 -b -c synchronous_commit=off -c 
fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c 
listen_addresses='' -c unix_socket_permissions=0700 -c 
unix_socket_directories='/var/lib/pgsql'" start


and see if you can connect. Leave pg_upgrade out of the mix to see if it 
is indeed the issue.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: conflict with recovery when delay is gone

2020-11-13 Thread Radoslav Nedyalkov

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 statement due to conflict with recovery
> >  FATAL:  terminating connection due to conflict with recovery
> >
> > The weird thing is that cancellations happen usually after standby has
> experienced
> > some huge delay(2h), still not at the allowed maximum(3h). Even recently
> run statements
> > got cancelled when the delay is already at zero.
> >
> > Sometimes the situation got relaxed after an hour or so.
> > Restarting the server instantly helps.
> >
> > It is pg11.8, centos7, hugepages, shared_buffers 196G from 748G.
> >
> > What phenomenon could we be facing?
>
> Hard to say.  Perhaps an unusual kind of replication conflict?
>
> What is in "pg_stat_database_conflicts" on the standby server?
>

db01=# select * from pg_stat_database_conflicts;
 datid |  datname  | confl_tablespace | confl_lock | confl_snapshot |
confl_bufferpin | confl_deadlock
---+---+--+++-+
 13877 | template0 |0 |  0 |  0 |
0 |  0
 16400 | template1 |0 |  0 |  0 |
0 |  0
 16402 | postgres  |0 |  0 |  0 |
0 |  0
 16401 | db01  |0 |  0 | 51 |
0 |  0
(4 rows)

On a freshly restarted standby we've just got similar behaviour after a 2
hours delay and a slow catch-up.
confl_snapshots is 51 and we have exactly the same number cancelled
statements.

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

2020-11-13 Thread Magnus Hagander

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:
> > > 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 13.
> >
> > > I think he is asking about shared_preload_libraries,
> > > local_preload_libraries, and session_preload_libraries.
> >
> > Yeah, but if Jeremy isn't touching the new cluster's config between
> > initdb and pg_upgrade, those won't be set.
> >
> > I'm kind of baffled at this point.  It seems pretty likely that this
> > is related to the v13 postgis problems we've heard a few reports of,
> > but the symptoms are a lot different.
> >
> > Best advice I can give is to go inquire on the postgis mailing lists
> > as to whether they've figured out the "free(): invalid pointer"
> > issue.  (I assume that dropping postgis from the source DB is not
> > an option...)
>
> This is not actually a PostGIS problem, it's a problem with our yum
> repository packaging.
>
> 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 different "proj" libraries, then you have that same
> problem.
>
> In this case, uninstall the OS supplied "proj" library. If that removes
> postgis through dependency, let it and then install it with:
>
> dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_12
>
>
... and that should of course be postgis30_13 if you're on PostgreSQL 13...

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/

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

2020-11-13 Thread Magnus Hagander

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 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 13.
>
> > I think he is asking about shared_preload_libraries,
> > local_preload_libraries, and session_preload_libraries.
>
> Yeah, but if Jeremy isn't touching the new cluster's config between
> initdb and pg_upgrade, those won't be set.
>
> I'm kind of baffled at this point.  It seems pretty likely that this
> is related to the v13 postgis problems we've heard a few reports of,
> but the symptoms are a lot different.
>
> Best advice I can give is to go inquire on the postgis mailing lists
> as to whether they've figured out the "free(): invalid pointer"
> issue.  (I assume that dropping postgis from the source DB is not
> an option...)

This is not actually a PostGIS problem, it's a problem with our yum
repository packaging.

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 different "proj" libraries, then you have that same
problem.

In this case, uninstall the OS supplied "proj" library. If that removes
postgis through dependency, let it and then install it with:

dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_12

as a workaround.

*If* the root cause is the same one, that is...

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

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 13.

> I think he is asking about shared_preload_libraries,
> local_preload_libraries, and session_preload_libraries.

Yeah, but if Jeremy isn't touching the new cluster's config between
initdb and pg_upgrade, those won't be set.

I'm kind of baffled at this point.  It seems pretty likely that this
is related to the v13 postgis problems we've heard a few reports of,
but the symptoms are a lot different.

Best advice I can give is to go inquire on the postgis mailing lists
as to whether they've figured out the "free(): invalid pointer"
issue.  (I assume that dropping postgis from the source DB is not
an option...)

regards, tom lane

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

2020-11-13 Thread Bruce Momjian

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 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 13.

I think he is asking about shared_preload_libraries,
local_preload_libraries, and session_preload_libraries.  Also, try
running this query and show us what is not the default:

SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: conflict with recovery when delay is gone

2020-11-13 Thread Laurenz Albe

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 connection due to conflict with recovery
> 
> The weird thing is that cancellations happen usually after standby has 
> experienced 
> some huge delay(2h), still not at the allowed maximum(3h). Even recently run 
> statements
> got cancelled when the delay is already at zero.
> 
> Sometimes the situation got relaxed after an hour or so.
> Restarting the server instantly helps.
> 
> It is pg11.8, centos7, hugepages, shared_buffers 196G from 748G.
> 
> What phenomenon could we be facing?

Hard to say.  Perhaps an unusual kind of replication conflict?

What is in "pg_stat_database_conflicts" on the standby server?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: PostgreSQL equivalent to Oracles ANYDATASET

2020-11-13 Thread Christoph Moench-Tegeder

## 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?

testing=# CREATE OR REPLACE FUNCTION public.rr(p INTEGER)
 RETURNS SETOF RECORD
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF p = 1 THEN
RETURN NEXT ('k1'::TEXT, 'v1'::TEXT);
RETURN NEXT ('k2'::TEXT, 'v2'::TEXT);
  ELSE
RETURN NEXT (23::INTEGER, 42::INTEGER, 'abc'::TEXT);
RETURN NEXT (42::INTEGER, 23::INTEGER, 'xyz'::TEXT);
  END IF;
  RETURN;
END;
$function$;
CREATE FUNCTION

testing=# SELECT * FROM rr(2) f(a INTEGER, b INTEGER, c TEXT);
 a  | b  |  c
++-
 23 | 42 | abc
 42 | 23 | xyz
(2 rows)

testing=# SELECT * FROM rr(1) f(x TEXT, y TEXT);
 x  | y
+
 k1 | v1
 k2 | v2
(2 rows)

Regards,
Christoph

-- 
Spare Space

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 done
server started
bash-4.4$ psql postgres
psql: error: could not connect to server: FATAL:  database "postgres" does not 
exist

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.log" -D 
"/var/lib/pgsql/13/data" -o "-p 5432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off 
-c vacuum_defer_cleanup_age=0  -c listen_addresses='' -c unix_socket_permissions=0700 -c 
unix_socket_directories='/var/run/postgresql'" start
waiting for server to start done
server started
bash-4.4$ psql postgres
psql: error: could not connect to server: FATAL:  database "postgres" does not 
exist

---

2020-11-13 11:39:38.378 EST [205747] LOG:  starting PostgreSQL 13.0 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 11:39:38.378 EST [205747] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 11:39:38.382 EST [205749] LOG:  database system was shut down at 
2020-11-13 11:37:34 EST
2020-11-13 11:39:38.388 EST [205747] LOG:  database system is ready to accept 
connections
2020-11-13 11:39:45.750 EST [205756] FATAL:  database "postgres" does not exist




Hmm. You can still connect if you use?:

/usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start

If so that would to me point to one of the options in the -o string. I 
would try dropping/changing them one at a time to see what happens. I 
would say start with the items in the  "-c synchronous_commit=off -c 
fsync=off -c full_page_writes=off" section.


--
Adrian Klaver
adrian.kla...@aklaver.com

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 9.5, but I’m not doing 
anything but initdb and then pg_upgrade for 13.

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
postgis crash-in-atexit problem that a couple of people have reported
(and which, unfortunately, we don't yet know the exact cause of).
I now suspect that something similar is happening earlier in the process
and preventing template1 from getting created, or maybe it's created but
then we can't access it.

Are you by any chance trying to preload any of the postgis-related
extensions?  If so, try not doing that.

regards, tom lane

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 with format string "%d@%jd"

Before PG 12, this would have worked (on many platforms) if your
local libc's printf understood the "j" modifier.  Since v12, we use
src/port/snprintf.c on every platform, and it doesn't know "j".

By and large, we do not use the  types in Postgres, and are
unlikely to start doing so.  So this omission doesn't particularly
concern me.  "ll" with a cast to "long long" is indeed the recommended
practice if you want to print a 64-bit value.

regards, tom lane

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 "-p 5432 -b -c synchronous_commit=off -c fsync=off 
-c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' 
-c unix_socket_permissions=0700 -c 
unix_socket_directories='/var/run/postgresql'" start
waiting for server to start done
server started
bash-4.4$ psql postgres
psql: error: could not connect to server: FATAL:  database "postgres" does not 
exist

---

2020-11-13 11:39:38.378 EST [205747] LOG:  starting PostgreSQL 13.0 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 11:39:38.378 EST [205747] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 11:39:38.382 EST [205749] LOG:  database system was shut down at 
2020-11-13 11:37:34 EST
2020-11-13 11:39:38.388 EST [205747] LOG:  database system is ready to accept 
connections
2020-11-13 11:39:45.750 EST [205756] FATAL:  database "postgres" does not exist

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 completely fresh initdb to get clean logs:


Was the below from starting using the pg_upgrade version of pg_ctl start?


No, strictly from running pg_upgrade.

Here’s the additional logs from manually running it:

2020-11-13 11:36:35.976 EST [205733] LOG:  starting PostgreSQL 13.0 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 11:36:35.976 EST [205733] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 11:36:35.981 EST [205735] LOG:  database system was shut down at 
2020-11-13 11:30:06 EST
2020-11-13 11:36:35.989 EST [205733] LOG:  database system is ready to accept 
connections




This does not show trying to connect to a database. It would help to 
list the commands run and then the corresponding log portions.



--
Adrian Klaver
adrian.kla...@aklaver.com

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 initdb to get clean logs:
> 
> Was the below from starting using the pg_upgrade version of pg_ctl start?

No, strictly from running pg_upgrade.

Here’s the additional logs from manually running it:

2020-11-13 11:36:35.976 EST [205733] LOG:  starting PostgreSQL 13.0 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 11:36:35.976 EST [205733] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 11:36:35.981 EST [205735] LOG:  database system was shut down at 
2020-11-13 11:30:06 EST
2020-11-13 11:36:35.989 EST [205733] LOG:  database system is ready to accept 
connections

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 version of pg_ctl start?



bash-4.4$ cat 13/data/log/postgresql-Fri.log
2020-11-13 11:29:44.744 EST [205647] LOG:  starting PostgreSQL 13.0 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 11:29:44.744 EST [205647] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 11:29:44.748 EST [205649] LOG:  database system was shut down at 
2020-11-13 11:29:19 EST
2020-11-13 11:29:44.755 EST [205647] LOG:  database system is ready to accept 
connections
free(): invalid pointer
2020-11-13 11:29:45.771 EST [205647] LOG:  received smart shutdown request
2020-11-13 11:30:05.292 EST [205647] LOG:  server process (PID 205659) was 
terminated by signal 6: Aborted
2020-11-13 11:30:05.292 EST [205647] LOG:  terminating any other active server 
processes
2020-11-13 11:30:05.295 EST [205647] LOG:  abnormal database system shutdown
2020-11-13 11:30:05.306 EST [205647] LOG:  database system is shut down
2020-11-13 11:30:06.136 EST [205704] LOG:  starting PostgreSQL 13.0 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 11:30:06.136 EST [205704] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 11:30:06.141 EST [205706] LOG:  database system was shut down at 
2020-11-13 11:30:05 EST
2020-11-13 11:30:06.148 EST [205704] LOG:  database system is ready to accept 
connections
2020-11-13 11:30:06.195 EST [205712] FATAL:  database "template1" does not exist
2020-11-13 11:30:06.200 EST [205704] LOG:  received fast shutdown request
2020-11-13 11:30:06.201 EST [205704] LOG:  aborting any active transactions
2020-11-13 11:30:06.204 EST [205704] LOG:  background worker "logical replication 
launcher" (PID 205711) exited with exit code 1
2020-11-13 11:30:06.204 EST [205707] LOG:  shutting down
2020-11-13 11:30:06.219 EST [205704] LOG:  database system is shut down




--
Adrian Klaver
adrian.kla...@aklaver.com

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 (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 10:14:35.822 EST [204797] LOG:  listening on IPv4 address 
"127.0.0.1", port 5432
2020-11-13 10:14:35.824 EST [204797] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 10:14:35.828 EST [204797] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"


I'm pretty sure the above, two sockets, should not happen. Have to think 
on how that could come to pass.



2020-11-13 10:14:35.833 EST [204799] LOG:  database system was shut down at 
2020-11-13 10:12:15 EST
2020-11-13 10:14:35.845 EST [204797] LOG:  database system is ready to accept 
connections
2020-11-13 10:14:45.416 EST [204797] LOG:  received fast shutdown request
2020-11-13 10:14:45.418 EST [204797] LOG:  aborting any active transactions
2020-11-13 10:14:45.421 EST [204797] LOG:  background worker "logical replication 
launcher" (PID 204805) exited with exit code 1
2020-11-13 10:14:45.421 EST [204800] LOG:  shutting down
2020-11-13 10:14:45.442 EST [204797] LOG:  database system is shut down




--
Adrian Klaver
adrian.kla...@aklaver.com

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:  starting PostgreSQL 13.0 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 11:29:44.744 EST [205647] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 11:29:44.748 EST [205649] LOG:  database system was shut down at 
2020-11-13 11:29:19 EST
2020-11-13 11:29:44.755 EST [205647] LOG:  database system is ready to accept 
connections
free(): invalid pointer
2020-11-13 11:29:45.771 EST [205647] LOG:  received smart shutdown request
2020-11-13 11:30:05.292 EST [205647] LOG:  server process (PID 205659) was 
terminated by signal 6: Aborted
2020-11-13 11:30:05.292 EST [205647] LOG:  terminating any other active server 
processes
2020-11-13 11:30:05.295 EST [205647] LOG:  abnormal database system shutdown
2020-11-13 11:30:05.306 EST [205647] LOG:  database system is shut down
2020-11-13 11:30:06.136 EST [205704] LOG:  starting PostgreSQL 13.0 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 
64-bit
2020-11-13 11:30:06.136 EST [205704] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 11:30:06.141 EST [205706] LOG:  database system was shut down at 
2020-11-13 11:30:05 EST
2020-11-13 11:30:06.148 EST [205704] LOG:  database system is ready to accept 
connections
2020-11-13 11:30:06.195 EST [205712] FATAL:  database "template1" does not exist
2020-11-13 11:30:06.200 EST [205704] LOG:  received fast shutdown request
2020-11-13 11:30:06.201 EST [205704] LOG:  aborting any active transactions
2020-11-13 11:30:06.204 EST [205704] LOG:  background worker "logical 
replication launcher" (PID 205711) exited with exit code 1
2020-11-13 11:30:06.204 EST [205707] LOG:  shutting down
2020-11-13 11:30:06.219 EST [205704] LOG:  database system is shut down

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-5), 
64-bit
2020-11-13 10:14:35.822 EST [204797] LOG:  listening on IPv4 address 
"127.0.0.1", port 5432
2020-11-13 10:14:35.824 EST [204797] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"
2020-11-13 10:14:35.828 EST [204797] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2020-11-13 10:14:35.833 EST [204799] LOG:  database system was shut down at 
2020-11-13 10:12:15 EST
2020-11-13 10:14:35.845 EST [204797] LOG:  database system is ready to accept 
connections
2020-11-13 10:14:45.416 EST [204797] LOG:  received fast shutdown request
2020-11-13 10:14:45.418 EST [204797] LOG:  aborting any active transactions
2020-11-13 10:14:45.421 EST [204797] LOG:  background worker "logical 
replication launcher" (PID 204805) exited with exit code 1
2020-11-13 10:14:45.421 EST [204800] LOG:  shutting down
2020-11-13 10:14:45.442 EST [204797] LOG:  database system is shut down

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?


-
   pg_upgrade run on Fri Nov 13 10:11:45 2020
-





command: "/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/13/data" -o "-p 5432 
-b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' -c 
unix_socket_permissions=0700 -c unix_socket_directories='/var/run/postgresql'" start >> "pg_upgrade_server.log" 
2>&1
waiting for server to start2020-11-13 10:12:15.902 EST [204782] LOG:  
redirecting log output to logging collector process
2020-11-13 10:12:15.902 EST [204782] HINT:  Future log output will appear in directory 
"log".
  done
server started


What shows up in the log file in "log" directory when you try to connect 
to a database?





command: "/usr/pgsql-13/bin/pg_ctl" -w -D "/var/lib/pgsql/13/data" -o "" -m fast stop >> 
"pg_upgrade_server.log" 2>&1
waiting for server to shut down done
server stopped




--
Adrian Klaver
adrian.kla...@aklaver.com

Problem with psprintf and intmax_t (%jd)

2020-11-13 Thread Jan Behrens

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@%jd"

I was not able to figure out what's the problem. Apparently this error
is thrown when vsnprintf(buf, len, fmt, args) in src/common/psprintf.c
returns a negative value. However, calling snprintf(buf, len, "%d@%jd",
(int)1, (intmax_t)2)) in a separate test program works fine on my
system.

I found a workaround. The following call works fine:

PG_RETURN_CSTRING(psprintf("%d@%lld", (int)1, (long long)2));

I could live with the workaround, but it is a bit scary, as I don't
really understand why "%jd" fails while "%lld" does not. Does anyone
know why, and/or is it possible for anyone else to reproduce the
problem?

Kind regards,
Jan Behrens

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?

-
  pg_upgrade run on Fri Nov 13 10:11:45 2020
-

command: "/usr/pgsql-9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/lib/pgsql/9.5/data" -o "-p 54320 -b  -c listen_addresses='' -c 
unix_socket_permissions=0700 -c unix_socket_directories='/var/run/postgresql'" 
start >> "pg_upgrade_server.log" 2>&1
waiting for server to start2020-11-13 10:11:46 EST [204620]: [1-1] 
user=,db=,app=,client= LOG:  ending log output to stderr
2020-11-13 10:11:46 EST [204620]: [2-1] user=,db=,app=,client= HINT:  Future 
log output will go to log destination "syslog".
 done
server started


command: "/usr/pgsql-9.5/bin/pg_ctl" -w -D "/var/lib/pgsql/9.5/data" -o "" -m 
smart stop >> "pg_upgrade_server.log" 2>&1
waiting for server to shut down done
server stopped


command: "/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/lib/pgsql/13/data" -o "-p 5432 -b -c synchronous_commit=off -c fsync=off 
-c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' 
-c unix_socket_permissions=0700 -c 
unix_socket_directories='/var/run/postgresql'" start >> "pg_upgrade_server.log" 
2>&1
waiting for server to start2020-11-13 10:11:54.944 EST [204724] LOG:  
redirecting log output to logging collector process
2020-11-13 10:11:54.944 EST [204724] HINT:  Future log output will appear in 
directory "log".
 done
server started


command: "/usr/pgsql-13/bin/pg_ctl" -w -D "/var/lib/pgsql/13/data" -o "" -m 
smart stop >> "pg_upgrade_server.log" 2>&1
waiting for server to shut down... done
server stopped


command: "/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/lib/pgsql/13/data" -o "-p 5432 -b -c synchronous_commit=off -c fsync=off 
-c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' 
-c unix_socket_permissions=0700 -c 
unix_socket_directories='/var/run/postgresql'" start >> "pg_upgrade_server.log" 
2>&1
waiting for server to start2020-11-13 10:12:15.902 EST [204782] LOG:  
redirecting log output to logging collector process
2020-11-13 10:12:15.902 EST [204782] HINT:  Future log output will appear in 
directory "log".
 done
server started


command: "/usr/pgsql-13/bin/pg_ctl" -w -D "/var/lib/pgsql/13/data" -o "" -m 
fast stop >> "pg_upgrade_server.log" 2>&1
waiting for server to shut down done
server stopped

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?

We have postgis installed, here’s a list of most of the extensions:

master=# \dx
  List of installed extensions
Name| Version |   Schema   | 
Description
+-++-
 btree_gin  | 1.0 | public | support for indexing common 
datatypes in GIN
 cube   | 1.0 | public | data type for multidimensional 
cubes
 dblink | 1.1 | public | connect to other PostgreSQL 
databases from within a database
 earthdistance  | 1.0 | public | calculate great-circle distances 
on the surface of the Earth
 pageinspect| 1.3 | public | inspect the contents of database 
pages at a low level
 pg_buffercache | 1.1 | public | examine the shared buffer cache
 pg_stat_statements | 1.3 | public | track execution statistics of all 
SQL statements executed
 pg_trgm| 1.1 | public | text similarity measurement and 
index searching based on trigrams
 plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural language
 postgis| 3.0.2   | public | PostGIS geometry, geography, and 
raster spatial types and functions
 postgis_raster | 3.0.2   | public | PostGIS raster types and functions
 postgres_fdw   | 1.0 | public | foreign-data wrapper for remote 
PostgreSQL servers
 unaccent   | 1.0 | public | text search dictionary that 
removes accents
(13 rows)

I did have to upgrade all the postgis from 2.2 to 3.0 which went smoothly.

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 like.


This is a test environment so only the two databases are on it and neither is 
running.  pg_upgrade checks if either is running.


When you manually run the pg_upgrade pg_ctl script the server starts but 
you cannot connect to any database in it correct?


What does pg_upgrade_server.log show when you do above?




I also wonder if the cluster you're trying to upgrade from has a
"template1" database.  Not sure if anyone has ever tested whether
pg_upgrade can cope with the lack of one.



It’s there:

$ psql -p 54320 template1
psql (13.0, server 9.5.23)
Type "help" for help.

template1=#






--
Adrian Klaver
adrian.kla...@aklaver.com

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?

regards, tom lane

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 so only the two databases are on it and neither is 
running.  pg_upgrade checks if either is running.

> I also wonder if the cluster you're trying to upgrade from has a
> "template1" database.  Not sure if anyone has ever tested whether
> pg_upgrade can cope with the lack of one.


It’s there:

$ psql -p 54320 template1
psql (13.0, server 9.5.23)
Type "help" for help.

template1=#

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 to something like
"could not connect to server: No such file or directory", not to a
specific complaint about a database not being present.

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.

I also wonder if the cluster you're trying to upgrade from has a
"template1" database.  Not sure if anyone has ever tested whether
pg_upgrade can cope with the lack of one.

regards, tom lane

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 /var/lib/pgsql/13/data/ 
--locale=en_US.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/13/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Toronto
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start

bash-4.4$
bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start
waiting for server to start done
server started
bash-4.4$ psql template1
psql (13.0)
Type "help" for help.
template1=# \q
bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile stop
waiting for server to shut down done
server stopped
bash-4.4$ /usr/pgsql-13/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin 
--new-bindir /usr/pgsql-13/bin --old-datadir /var/lib/pgsql/9.5/data 
--new-datadir /var/lib/pgsql/13/data --old-port 54320 --new-port 5432 
--socketdir /var/run/postgresql
Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for tables WITH OIDS   ok
Checking for invalid "sql_identifier" user columns  ok
Checking for invalid "unknown" user columns ok
Checking for roles starting with "pg_"  ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user  ok
Checking for prepared transactions  ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows in the new clusterok
Deleting files from new pg_xact ok
Copying old pg_clog to new server   ok
Setting next transaction ID and epoch for new cluster   ok
Deleting files from new pg_multixact/offsetsok
Copying old pg_multixact/offsets to new server  ok
Deleting files from new pg_multixact/membersok
Copying old pg_multixact/members to new server  ok
Setting next multixact ID and offset for new clusterok
Resetting WAL archives  ok

connection to database failed: FATAL:  database "template1" does not exist

could not connect to target postmaster started with the command:
"/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/lib/pgsql/13/data" -o "-p 5432 -b -c synchronous_commit=off -c fsync=off 
-c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' 
-c unix_socket_permissions=0700 -c 
unix_socket_directories='/var/run/postgresql'" start
Failure, exiting

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 carefully.

here’s the complete process:

bash-4.4$ /usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/
--locale=en_US.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/13/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Toronto
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start

bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start
waiting for server to start done
server started
bash-4.4$ psql postgres
psql (13.0)
Type "help" for help.

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \q

Well that blows the initdb theory out of the water.

bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile stop
waiting for server to shut down done
server stopped
bash-4.4$ /usr/pgsql-13/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin
--new-bindir /usr/pgsql-13/bin --old-datadir /var/lib/pgsql/9.5/data
--new-datadir /var/lib/pgsql/13/data --old-port=54320 --new-port=5432

In your previous post you had --socketdir=/var/run/postgresql/. Did you
change that or is it missing?

connection to database failed: FATAL: database "template1" does not exist

could not connect to target postmaster started with the command:
"/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/13/data" -o
"-p 5432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0 -c
listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/pgsql'" start

Where is Postgres putting its sockets?

Failure, exiting

--
Adrian Klaver
adrian.kla...@aklaver.com

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 backup, you would likely see
the same message at the end of its crash recovery.  Some sort of
corrupt-WAL-entry report is expected at the end of WAL replay anytime
you didn't have a clean shutdown.

regards, tom lane

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:

bash-4.4$ /usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ 
--locale=en_US.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/13/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Toronto
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start

bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start
waiting for server to start done
server started
bash-4.4$ psql postgres
psql (13.0)
Type "help" for help.

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \q
bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile stop
waiting for server to shut down done
server stopped
bash-4.4$ /usr/pgsql-13/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin 
--new-bindir /usr/pgsql-13/bin --old-datadir /var/lib/pgsql/9.5/data 
--new-datadir /var/lib/pgsql/13/data --old-port=54320 --new-port=5432
Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for tables WITH OIDS   ok
Checking for invalid "sql_identifier" user columns  ok
Checking for invalid "unknown" user columns ok
Checking for roles starting with "pg_"  ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user  ok
Checking for prepared transactions  ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows in the new clusterok
Deleting files from new pg_xact ok
Copying old pg_clog to new server   ok
Setting next transaction ID and epoch for new cluster   ok
Deleting files from new pg_multixact/offsetsok
Copying old pg_multixact/offsets to new server  ok
Deleting files from new pg_multixact/membersok
Copying old pg_multixact/members to new server  ok
Setting next multixact ID and offset for new clusterok
Resetting WAL archives  ok

connection to database failed: FATAL:  database "template1" does not exist

could not connect to target postmaster started with the command:
"/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/lib/pgsql/13/data" -o "-p 5432 -b -c synchronous_commit=off -c fsync=off 
-c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' 
-c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/pgsql'" 
start
Failure, exiting

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_upgrade --old-bindir /usr/pgsql-9.5/bin 
--new-bindir /usr/pgsql-13/bin --old-datadir /var/lib/pgsql/9.5/data 
--new-datadir /var/lib/pgsql/13/data --old-port=54320 --new-port=5432 
--socketdir=/var/run/postgresql/
Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for tables WITH OIDS   ok
Checking for invalid "sql_identifier" user columns  ok
Checking for invalid "unknown" user columns ok
Checking for roles starting with "pg_"  ok
Creating dump of global objects ok
Creating dump of database schemas
 ok

connection to database failed: FATAL:  database "template1" does not exist

could not connect to target postmaster started with the command:
"/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/13/data" -o 
"-p 5432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c 
listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/run/postgresql'" start
Failure, exiting

---

When I manually run that command it works, although when I attempt to connect 
to the ‘postgres’ database on it, it complains it doesn’t exist.


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.




I can’t use the dump/restore method to upgrade this server as the data in 
question is fairly massive and in my testing it took 45 hours to complete.  Any 
help appreciated.







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Failed Login Attempts in PostgreSQL

2020-11-13 Thread Wolff, Ken L



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).



--

Magnus Hagander

Me: https://www.hagander.net/

Work: https://www.redpill-linpro.com/





Having  been down this road myself, these are the options I eventually 
identified.  Each obviously has its benefits and drawbacks:

  *   Change the Postgres source code and deploy a new version.  Believe there 
are examples of how to do this in Git.
  *   Disable/disallow local accounts and rely on LDAP.  Be aware passwords 
would be passed in clear text across the network unless your DCs require SSL.
  *   Disable/disallow local accounts and rely on PKI certificates.  I don’t 
know that this would necessarily limit failed login attempts but is definitely 
much more secure.
  *   Procure a vendor-supported version of PostgreSQL which offers this 
functionality.
  *   Fail2ban, as Magnus observed.
  *   Leverage something like Splunk monitoring to identify failed logins and 
then reach back into the database to lock accounts when appropriate.



Hope this is of some help.





Ken

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