Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[tutiluren]

> Well not partial as in incremental. Instead dump only some portion of the 
> schema with or without its associated data.
>
It's funny that you should bring that up, considering how it was one of my 
points... See the point about pg_dump's bug on Windows.


>> I'm saying that PostGIS has a bug due to incorrectly constructed internal 
>> queries which makes it impossible to properly name the schema where PostGIS 
>> is to reside, causing my database to look very ugly when it has to say 
>> "postgis" instead of "PostGIS" for PostGIS's schema. And that was an example 
>> of how sloppy/bad third-party things always are, and is one reason why I 
>> don't like it when I have to rely on "extensions".
>>
>
> If that is the sum of your issues with PostGIS then I really don't have much 
> sympathy.
>
Why does nobody understand that it was an *example* and not some kind of full 
PostGIS review?

> They are extensions so you aren't required to use them and rely on their way 
> of doing things. You have the choice of writing your own code/extension or do 
> without completely.
>
It sure is great to have such choices... I can't take it seriously when people 
say things like this. It's similar to "it's open source so you can easily vet 
it yourself". It's not taking reality into consideration at all.

As for doing without it, that would make it impossible to deal with GPS 
coordinates/maps. So it's not really a choice at all.


>> That would entail building an AI into the code that would deal with
>>  all the possible OS(versions), Postgres(versions), hardware
>>  permutations.
>>
>> I... guess. If "AI" means "a series of ifs". Which is what software... is? I 
>> doubt that people who can make the world's most advanced open source 
>> database cannot check the amount of RAM and see how fast the CPU/disk is.
>>
>
> It is more then that. It would have to take into account the behavior changes 
> that happen in Postgres between major versions. It also would have to account 
> for OS specific parameters and the changes that happen there between OS 
> versions. It also would need to 'know' how the database was going to be used; 
> readonly, heavy writes, etc. Also how the database should play with other 
> programs on the same machine. Add to the mix containers, cloud instances and 
> so on and you are outrunning the ability of 'ifs' to handle it.
>
If it changes that much, it's far, far worse than I even thought, and it sounds 
like it will be pointless to even *try* to learn it as it keeps changing 
between versions/OSes/other stuff.

I can't help but feel as if people just don't want to answer this and other 
concerns I have. As if there's some silent agreement along the lines of 
"securing PG DBAs' jobs".


>> Does your server runs to your satisfaction with the default settings?
>>
>> Right now, yes, but that says very little as I'm the only user of it. I've 
>> had many nightmares in the past, however, where even determining whether the 
>> changes in the config did anything (good or bad) has been impossible. I 
>> fundamentally don't like the idea that the config is so "conservative" 
>> (crippled) with no obvious/easy way to "set a different general mode". If 
>> you honestly think that the numerous performance-related options are easy to 
>> understand, I don't know what to say.
>>
>
> The thing is 'general mode' is going to mean something different to someone 
> running a database in the MB-low GB range vs. high GB vs. TB vs. PB.
>
I don't mean this to sound rude, but it's like talking to a wall... What I mean 
is that there are obviously technical means for software to know whether they 
are exhausting the system they are running on or not, and expecting people to 
understand all these intricate internal parameters is just... bizarre. There 
ought to be some kind of "abstract" setting for those of us who aren't able to 
(or even *wish* to) comprehend all the PG internals, and just want an efficient 
database using (roughly) as much of our machine as we want.

This is not the first time I feel like I'm repeating myself over and over in 
different ways but never getting through. It could be that you are so familiar 
with PG's internals that it all is obvious to you, but it could just as well be 
that you don't want to hear about this.

"There's plenty of guides" and "the information is out there" doesn't help me 
and all the other people who have stuck with the default config and thus a 
massively restricted PG database for all these years. Just because it's easy to 
you doesn't mean it's easy to everyone else. Just dealing with composing 
efficient-enough SQL queries and designing an optimized database structure is 
(way) more than enough work for most of us. I don't have the luxury of some 
hired DBA who sits all day tuning the PG server. Besides, I've already 
explained the privacy issues with that even if I had the money...

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[tutiluren]

Sep 24, 2020, 8:13 AM by tshel...@gmail.com:

>>
>> On 9/23/20 11:51 AM, >> tutilu...@tutanota.com>>  wrote:
>>
> >     Huh? A schema is just a name space, why does it matter how the
> >     extension chooses to define it? I mean you could have number of
> >     permutations of postgis.
> >
> > I'm saying that PostGIS has a bug due to incorrectly constructed
> > internal queries which makes it impossible to properly name the schema
> > where PostGIS is to reside, causing my database to look very ugly when
> > it has to say "postgis" instead of "PostGIS" for PostGIS's schema. And
> > that was an example of how sloppy/bad third-party things always are, and
> > is one reason why I don't like it when I have to rely on "extensions".
>
>
> U?   I have PostGIS installed in my core app schema, in part because at 
> the time I didn't know what I was doing. Better to have been in  public...
>
> You may also want to look at comparable ANSI (standards based) database 
> products (Oracle for example) when it comes to the use of case in naming 
> conventions.  Different products handle things in different ways.
>
> You may want to google around the issue, for example > 
> https://postgis.net/2017/11/07/tip-move-postgis-schema/>  for moving schemas.
>
> You may want to do some research on where PostGIS comes from.  It could never 
> have been developed as a core part of Postgres, so the fact that products 
> like PostGIS are so domain specific. 
> The fact that the Postgesql extension system is so flexible and robust was 
> probably a key factor in the choice it's choice in the development of PostGIS.
>
It's painfully clear that people don't read anymore. I don't know what to say 
other than: "This has nothing to do with what I said."


> My suggestion is 'live with it'.
>
And my point was that third-party extensions always are sloppy/bad in some way, 
and what I described was a perfect example of such a thing. It's insulting 
beyond words to be forced to make an ugly schema called "postgis" just because 
of a BUG in PostGIS, when all my other schemas are named properly and 
PostgreSQL has ZERO problems calling it "PostGIS" -- it's PostGIS that has a 
BUG in it that makes it impossible to use if you name it properly.


> Or, move to a product that suits your use cases / desires better
>
Always the same thing. The slightest criticism, no matter how warranted, always 
results in: "Fine. Go somewhere else. Use something else." Never: "Oh, right. 
Sorry, but we always used lowercase ourselves and therefore didn't consider 
this. In retrospect, it's an embarrassing mistake! We'll fix it in the next 
release. Thanks for pointing that out."


> , But, good luck finding another open source "free" (or any) product with the 
> functionality, robusiness and performance of PostGIS / Postgresql.
>
I didn't say there is, nor do I think so. It wasn't my point at all...

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Ron]

On 9/24/20 6:20 PM, Bruce Momjian wrote:

On Wed, Sep 23, 2020 at 12:28:14AM +0200, tutilu...@tutanota.com wrote:

Sep 21, 2020, 7:53 PM by j...@commandprompt.com:
 See my comment about Google. The information is out there and easy to find.

I guess I'm the worst idiot in the world, then, who can't DuckDuckGo (Google is
evil) it even after 15 years.

Seriously, I didn't type my feedback "for fun". It may be difficult for very
intelligent people to understand (as often is the case, because you operate on
a whole different level), but the performance-related PostgreSQL configuration
options are a *nightmare* to me and many others. I spent *forever* reading
about them and couldn't make any sense of it all. Each time I tried, I would
give up, frustrated and angry, with no real clue what "magic numbers" it
wanted.

It's quite baffling to me how this can be so difficult for you all to
understand. Even if we disregard the sheer intelligence factor, it's clear that
users of PG don't have the same intimate knowledge of PG's internals as the PG
developers, nor could possibly be expected to.

As mentioned, I kept going back to the default configuration over and over
again. Anyone who doesn't is either a genius or pretends/thinks that they
understand it. (Or I'm extremely dumb.)

I think there is a clear dependency that people reading the docs,
particularly for performance purposes, must have an existing knowledge
of a lot of low-level things --- this could be the cause of your
frustration.


And that's a serious problem with the documentation. (Not that I know how to 
fix it in an OSS project.)


--
Angular momentum makes the world go 'round.

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[tutiluren]

(I can't believe I'm replying to this drivel...)

>> >     Huh? A schema is just a name space, why does it matter how the
>> >     extension chooses to define it? I mean you could have number of
>> >     permutations of postgis.
>> >
>> > I'm saying that PostGIS has a bug due to incorrectly constructed
>> > internal queries which makes it impossible to properly name the schema
>> > where PostGIS is to reside, causing my database to look very ugly when
>> > it has to say "postgis" instead of "PostGIS" for PostGIS's schema. And
>> > that was an example of how sloppy/bad third-party things always are, and
>> > is one reason why I don't like it when I have to rely on "extensions".
>>
> All lowercase is good
>
That's your personal opinion -- not some kind of fact, and it definitely goes 
against everything that I believe.

> , as you don't have to remember which bits are capitalized.
>
Yes, so let's remove all upper-case letters, punctuation, grammar and 
everything else as well. Let's just communicate with grunts. That way, we don't 
have to remember all of this unnecessary stuff which provides meaningless 
complexity to this existence...

>   And besides, there are far more important issues to consider as Tony has 
> covered in detail.
>
And yet you focus on it, ignoring everything of importance that I had written...

>   It is definitely not a bug!
>
It is the very *definition* of a bug. The PostgreSQL manual CLEARLY states that 
you are to use double quotes around all identifiers or else it will turn them 
into lowercase. Please at least try to have some minimum knowledge of what you 
are talking about before trying to correct people.

The fact that there are a lot of sloppy, US-centric people who refuse to use 
correctly named identifiers and cannot understand how there can be anything 
besides a-z in an alphabet, doesn't change reality and doesn't make a bug 
"right".

> There are many reasons for going with PostgreSQL and PostGIS, but you are 
> free to use something else if you prefer.
>
"Something else"? If I "prefer"? Why waste my and your own time to send 
something when you clearly didn't read what you are replying to?

The answer, of course, is that you have zero interest in helping but 100% 
interest in making smug insults, like so many other people out there. It's 
incredibly tiresome to constantly have to wade through useless noise from 
horribly ignorant people, while getting further and further away from actually 
getting answers or meaningful feedback on the actual thing you asked.

What's the best practice of Postgres sharding?

[Wang Xu]

Hello,

We have one master/slave nodes running already, it works very well until now we 
need more business data to be stored.
But our running machine almost reached the read/write limit (1600 ops/s).

So what I am thinking is that I add more machines to store the new business 
data.But I don’t want to change my applications that query the data.
In short I want one single query entry, multiple machines to do 
data-persistence job.


What’s the best practice in this situation?


Thanks a lot,
James

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Bruce Momjian]

On Wed, Sep 23, 2020 at 12:28:14AM +0200, tutilu...@tutanota.com wrote:
> Sep 21, 2020, 7:53 PM by j...@commandprompt.com:
> See my comment about Google. The information is out there and easy to 
> find.
> 
> I guess I'm the worst idiot in the world, then, who can't DuckDuckGo (Google 
> is
> evil) it even after 15 years.
> 
> Seriously, I didn't type my feedback "for fun". It may be difficult for very
> intelligent people to understand (as often is the case, because you operate on
> a whole different level), but the performance-related PostgreSQL configuration
> options are a *nightmare* to me and many others. I spent *forever* reading
> about them and couldn't make any sense of it all. Each time I tried, I would
> give up, frustrated and angry, with no real clue what "magic numbers" it
> wanted.
> 
> It's quite baffling to me how this can be so difficult for you all to
> understand. Even if we disregard the sheer intelligence factor, it's clear 
> that
> users of PG don't have the same intimate knowledge of PG's internals as the PG
> developers, nor could possibly be expected to.
> 
> As mentioned, I kept going back to the default configuration over and over
> again. Anyone who doesn't is either a genius or pretends/thinks that they
> understand it. (Or I'm extremely dumb.)

I think there is a clear dependency that people reading the docs,
particularly for performance purposes, must have an existing knowledge
of a lot of low-level things --- this could be the cause of your
frustration.

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

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

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Bruce Momjian]

On Sun, Sep 20, 2020 at 01:15:26PM -0400, Tom Lane wrote:
> Bruce Momjian  writes:
> > On Mon, Sep 14, 2020 at 05:39:57PM -0400, Tom Lane wrote:
> >> On the other hand, the very same thing could be said of database names
> >> and role names, yet we have never worried much about whether those were
> >> encoding-safe when viewed from databases with different encodings, nor
> >> have there been many complaints about the theoretical unsafety.  So maybe
> >> this is just overly anal-retentive and we should drop the restriction,
> >> or at least pass through data that doesn't appear to be invalidly
> >> encoded.
> 
> > I think the issue is that role and database names are controlled by
> > privileged users, while application_name is not.
> 
> That's certainly an argument against a completely laissez-faire approach,
> but if we filtered invalidly-encoded data on the reading side, it seems
> like we would be in good enough shape.

Yes, if we want to filter, sure.  I thought we were not 100% able to
filter, but I guess if it safe, we can do it.

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

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

Re: multiple tables got corrupted

[Bruce Momjian]

On Tue, Sep 15, 2020 at 07:58:39PM +0200, Magnus Hagander wrote:
> Try reading them "row by row" until it breaks. That is, SELECT * FROM ... 
> LIMIT
> 1, then LIMIT 2 etc. For more efficiency use a binary search starting at what
> seems like a reasonable place looking at the size of the table vs the first
> failed block to make it faster, but the principle is the same. Once it fails,
> you've found a corrupt block...

You can also include the invisible 'ctid' column so you can see the
block number of each row, e.g.:

SELECT ctid, relname FROM pg_class LIMIT 2;
  ctid  |   relname
+--
 (0,46) | pg_statistic
 (0,47) | pg_type

The format is page number, item number on page.

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

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

Re: Need explanation on index size

[Peter Geoghegan]

On Thu, Sep 24, 2020 at 6:55 AM Guillaume Luchet  wrote:
> I don’t understand why after the update where I only update a non indexed 
> column the indexes size is growing. Is it something someone can explain ?

If you reduce the table fillfactor then these updates will all be HOT
updates. That will make the table larger initially, but leaving enough
space behind on the same heap pages for successor tuples makes it
possible to use HOT updates.


-- 
Peter Geoghegan

Re: Need explanation on index size

[Chris Sterritt]

On 24/09/2020 15:08, Guillaume Lelarge wrote:

Hi,

Le jeu. 24 sept. 2020 à 15:55, Guillaume Luchet > a écrit :


Hi,

I’m facing of a comportement I don’t understand on indexes, here a
quick example to reproduce my problem


test=# select version();
         version

--
 PostgreSQL 12.4 (Debian 12.4-1.pgdg90+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

test=# create table plop (col_a int, col_b int, col_c int);
CREATE TABLE

test=# create unique index on plop (col_a);
CREATE INDEX

test=# create index on plop(col_b);
CREATE INDEX

test=# insert into plop (col_a, col_b) select generate_series(1,
1), generate_series(1, 1);
INSERT 0 1

test=# SELECT schemaname, tablename,
         pg_size_pretty(SIZE) AS size_pretty,
         pg_size_pretty(total_size) AS total_size_pretty
    FROM (SELECT *, pg_relation_size(quote_ident(schemaname) ||
'.' || quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' ||
quote_ident(tablename)) AS total_size
          FROM pg_tables) AS TABLES where tablename = 'plop';
 schemaname | tablename | size_pretty | total_size_pretty
+---+-+---
 public     | plop      | 360 kB      | 864 kB
(1 row)

test=# update plop set col_c = floor(random() * 10 + 1)::int;
UPDATE 1

test=# SELECT schemaname, tablename,
         pg_size_pretty(SIZE) AS size_pretty,
         pg_size_pretty(total_size) AS total_size_pretty
    FROM (SELECT *, pg_relation_size(quote_ident(schemaname) ||
'.' || quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' ||
quote_ident(tablename)) AS total_size
          FROM pg_tables) AS TABLES where tablename = 'plop';
 schemaname | tablename | size_pretty | total_size_pretty
+---+-+---
 public     | plop      | 792 kB      | 2160 kB
(1 row)

test=# reindex table plop;
REINDEX

test=# SELECT schemaname, tablename,
         pg_size_pretty(SIZE) AS size_pretty,
         pg_size_pretty(total_size) AS total_size_pretty
    FROM (SELECT *, pg_relation_size(quote_ident(schemaname) ||
'.' || quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' ||
quote_ident(tablename)) AS total_size
          FROM pg_tables) AS TABLES where tablename = 'plop';
 schemaname | tablename | size_pretty | total_size_pretty
+---+-+---
 public     | plop      | 792 kB      | 1304 kB
(1 row)

I don’t understand why after the update where I only update a non
indexed column the indexes size is growing. Is it something
someone can explain ?


Every tuple is now on a different location on the table (remember that 
update in PostgreSQL is more something like delete+insert). So even if 
the value of the column doesn't change, its tuple location changes, so 
the index needs to be updated to reflect that change.



--
Guillaume.



If you execute
  vacuum full plop;
you will see the size shrink back as the dead tuples will have been removed.

Chris

Re: Need explanation on index size

[Guillaume Lelarge]

Hi,

Le jeu. 24 sept. 2020 à 15:55, Guillaume Luchet  a
écrit :

> Hi,
>
> I’m facing of a comportement I don’t understand on indexes, here a quick
> example to reproduce my problem
>
>
> test=# select version();
>  version
>
>
> --
>  PostgreSQL 12.4 (Debian 12.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
> by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
> (1 row)
>
> test=# create table plop (col_a int, col_b int, col_c int);
> CREATE TABLE
>
> test=# create unique index on plop (col_a);
> CREATE INDEX
>
> test=# create index on plop(col_b);
> CREATE INDEX
>
> test=# insert into plop (col_a, col_b) select generate_series(1, 1),
> generate_series(1, 1);
> INSERT 0 1
>
> test=# SELECT schemaname, tablename,
>  pg_size_pretty(SIZE) AS size_pretty,
>  pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS SIZE,
> pg_total_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS total_size
>   FROM pg_tables) AS TABLES where tablename = 'plop';
>  schemaname | tablename | size_pretty | total_size_pretty
> +---+-+---
>  public | plop  | 360 kB  | 864 kB
> (1 row)
>
> test=# update plop set col_c = floor(random() * 10 + 1)::int;
> UPDATE 1
>
> test=# SELECT schemaname, tablename,
>  pg_size_pretty(SIZE) AS size_pretty,
>  pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS SIZE,
> pg_total_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS total_size
>   FROM pg_tables) AS TABLES where tablename = 'plop';
>  schemaname | tablename | size_pretty | total_size_pretty
> +---+-+---
>  public | plop  | 792 kB  | 2160 kB
> (1 row)
>
> test=# reindex table plop;
> REINDEX
>
> test=# SELECT schemaname, tablename,
>  pg_size_pretty(SIZE) AS size_pretty,
>  pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS SIZE,
> pg_total_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS total_size
>   FROM pg_tables) AS TABLES where tablename = 'plop';
>  schemaname | tablename | size_pretty | total_size_pretty
> +---+-+---
>  public | plop  | 792 kB  | 1304 kB
> (1 row)
>
> I don’t understand why after the update where I only update a non indexed
> column the indexes size is growing. Is it something someone can explain ?
>
>
Every tuple is now on a different location on the table (remember that
update in PostgreSQL is more something like delete+insert). So even if the
value of the column doesn't change, its tuple location changes, so the
index needs to be updated to reflect that change.


-- 
Guillaume.

Need explanation on index size

[Guillaume Luchet]

Hi,

I’m facing of a comportement I don’t understand on indexes, here a quick 
example to reproduce my problem


test=# select version();
 version
  
--
 PostgreSQL 12.4 (Debian 12.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by 
gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

test=# create table plop (col_a int, col_b int, col_c int);
CREATE TABLE

test=# create unique index on plop (col_a);
CREATE INDEX

test=# create index on plop(col_b);
CREATE INDEX

test=# insert into plop (col_a, col_b) select generate_series(1, 1), 
generate_series(1, 1);
INSERT 0 1

test=# SELECT schemaname, tablename,
 pg_size_pretty(SIZE) AS size_pretty,
 pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' || 
quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' || 
quote_ident(tablename)) AS total_size
  FROM pg_tables) AS TABLES where tablename = 'plop';
 schemaname | tablename | size_pretty | total_size_pretty 
+---+-+---
 public | plop  | 360 kB  | 864 kB
(1 row)

test=# update plop set col_c = floor(random() * 10 + 1)::int;
UPDATE 1

test=# SELECT schemaname, tablename,
 pg_size_pretty(SIZE) AS size_pretty,
 pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' || 
quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' || 
quote_ident(tablename)) AS total_size
  FROM pg_tables) AS TABLES where tablename = 'plop';
 schemaname | tablename | size_pretty | total_size_pretty 
+---+-+---
 public | plop  | 792 kB  | 2160 kB
(1 row)

test=# reindex table plop;
REINDEX

test=# SELECT schemaname, tablename,
 pg_size_pretty(SIZE) AS size_pretty,
 pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' || 
quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' || 
quote_ident(tablename)) AS total_size
  FROM pg_tables) AS TABLES where tablename = 'plop';
 schemaname | tablename | size_pretty | total_size_pretty 
+---+-+---
 public | plop  | 792 kB  | 1304 kB
(1 row)

I don’t understand why after the update where I only update a non indexed 
column the indexes size is growing. Is it something someone can explain ?

Regards,
Guillaume

Re: BUG? Slave don't reconnect to the master

[Олег Самойлов]

Hi, Jehan.

> On 9 Sep 2020, at 18:19, Jehan-Guillaume de Rorthais  wrote:
> 
> On Mon, 7 Sep 2020 23:46:17 +0300
> Олег Самойлов  wrote:
> 
>>> [...]
>> 10:30:55.965 FATAL:  terminating walreceiver process dpue to
>> administrator cmd 10:30:55.966 LOG:  redo done at 0/1600C4B0
>> 10:30:55.966 LOG:  last completed transaction was at log time
>> 10:25:38.76429 10:30:55.968 LOG:  selected new timeline ID: 4
>> 10:30:56.001 LOG:  archive recovery complete
>> 10:30:56.005 LOG:  database system is ready to accept connections
> 
>> The slave with didn't reconnected replication, tuchanka3c. Also I
>> separated logs copied from the old master by a blank line:
>> 
>> [...]
>> 
>> 10:20:25.168 LOG:  database system was interrupted; last known up at
>> 10:20:19 10:20:25.180 LOG:  entering standby mode
>> 10:20:25.181 LOG:  redo starts at 0/1198
>> 10:20:25.183 LOG:  consistent recovery state reached at 0/11000A68
>> 10:20:25.183 LOG:  database system is ready to accept read only
>> connections 10:20:25.193 LOG:  started streaming WAL from primary at
>> 0/1200 on tl 3 10:25:05.370 LOG:  could not send data to client:
>> Connection reset by peer 10:26:38.655 FATAL:  terminating walreceiver
>> due to timeout 10:26:38.655 LOG:  record with incorrect prev-link
>> 0/1200C4B0 at 0/1600C4D8
> 
> This message appear before the effective promotion of tuchanka3b. Do you
> have logs about what happen *after* the promotion?
 
 This is end of the slave log. Nothing. Just absent replication.  
>>> 
>>> This is unusual. Could you log some more details about replication
>>> tryouts to your PostgreSQL logs? Set log_replication_commands and lower
>>> log_min_messages to debug ?  
>> 
>> Sure, this is PostgreSQL logs for the cluster tuchanka3.
>> Tuchanka3a is an old (failed) master.
> 
> According to your logs:
> 
> 20:29:41 tuchanka3a: freeze
> 20:30:39 tuchanka3c: wal receiver timeout (default 60s timeout)
> 20:30:39 tuchanka3c: switched to archives, and error'ed (expected)
> 20:30:39 tuchanka3c: switched to stream again (expected)
> no more news from this new wal receiver 
> 20:34:21 tuchanka3b: promoted
> 
> I'm not sure where your floating IP is located at 20:30:39, but I suppose it
> is still on tuchanka3a as the wal receiver don't hit any connection error and
> tuchanka3b is not promoted yet.

I think so.

> 
> So at this point, I suppose the wal receiver is stuck in libpqrcv_connect
> waiting for frozen tuchanka3a to answer, with no connection timeout. You might
> track tcp sockets on tuchanka3a to confirm this.

I don't know how to do this.

> 
> To avoid such a wait, try to add eg. connect_timeout=2 to your 
> primary_conninfo
> parameter. See:
> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

Nope, this was not enough. But I went further and I added tcp keepalive options 
too. So now paf file, for instance in tuchanka3c, is:

# recovery.conf for krogan3, pgsqlms pacemaker module
primary_conninfo = 'host=krogan3 user=replicant application_name=tuchanka3c 
connect_timeout=5 keepalives=1 keepalives_idle=1 keepalives_interval=3 
keepalives_count=3'
recovery_target_timeline = 'latest'
standby_mode = 'on'

And now the problem with PostgreSQL-STOP is solved. But I surprised, why this 
was needed? I though that wal_receiver_timeout must be enough for this case.

I need some more time to check this solution with other tests.

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Gavin Flower]

On 24/09/2020 18:13, Tony Shelver wrote:



On 9/23/20 11:51 AM, tutilu...@tutanota.com
 wrote:

>     Huh? A schema is just a name space, why does it matter how the
>     extension chooses to define it? I mean you could have number of
>     permutations of postgis.
>
> I'm saying that PostGIS has a bug due to incorrectly constructed
> internal queries which makes it impossible to properly name the schema
> where PostGIS is to reside, causing my database to look very ugly when
> it has to say "postgis" instead of "PostGIS" for PostGIS's schema. And
> that was an example of how sloppy/bad third-party things always are, and
> is one reason why I don't like it when I have to rely on "extensions".


All lowercase is good, as you don't have to remember which bits are 
capitalized.  And besides, there are far more important issues to 
consider as Tony has covered in detail.  It is definitely not a bug!


[..]

Oracle's equivalent is probably the closest.  Pity that installing 
Oracle and their products as a whole is a nightmare, and rather 
wallet-draining...


Have a look at GIS / Mapping projects around the world, a majority are 
implemented on PostGIS.  Openstreetmap is probably the biggest (think 
open source version of Google Maps), and it moved to PostGIS from 
MySQL several years ago.
We did a lot of research into PostGIS, as GIS / tracking is a core 
part of our business.

We didn't find a product that could compare on

  * Maturity
  * Functionality
  * Performance
  * Cost
  * Documentation
  * Support (huge community)


I remember going to free seminars promoting the Oracle database over 25 
years ago, and the only thing I can remembers now is the pie charts 
saying how much revenue Oracle had versus all the others.  Never  how 
many transactions, size of databases, number of users, nor any really 
useful metric -- just how much Oracle was being paid!


To be honest, I've heard many bad things about Oracle, and rarely 
anything good.


There are many reasons for going with PostgreSQL and PostGIS, but you 
are free to use something else if you prefer.



Cheers,
Gavin

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Tony Shelver]

>
>
> On 9/23/20 11:51 AM, tutilu...@tutanota.com wrote:
>
> Huh? A schema is just a name space, why does it matter how the
> extension chooses to define it? I mean you could have number of
> permutations of postgis.
>
> I'm saying that PostGIS has a bug due to incorrectly constructed
> internal queries which makes it impossible to properly name the schema
> where PostGIS is to reside, causing my database to look very ugly when
> it has to say "postgis" instead of "PostGIS" for PostGIS's schema. And
> that was an example of how sloppy/bad third-party things always are, and
> is one reason why I don't like it when I have to rely on "extensions".


U?   I have PostGIS installed in my core app schema, in part because at
the time I didn't know what I was doing. Better to have been in  public...

You may also want to look at comparable ANSI (standards based) database
products (Oracle for example) when it comes to the use of case in naming
conventions.  Different products handle things in different ways.

You may want to google around the issue, for example
https://postgis.net/2017/11/07/tip-move-postgis-schema/ for moving schemas.

You may want to do some research on where PostGIS comes from.  It could
never have been developed as a core part of Postgres, so the fact that
products like PostGIS are so domain specific.
The fact that the Postgesql extension system is so flexible and robust was
probably a key factor in the choice it's choice in the development of
PostGIS.

Effectively, you may lose a bit, but you gain a whole lot more.  My
suggestion is 'live with it'.

Or, move to a product that suits your use cases / desires better, But, good
luck finding another open source "free" (or any) product with the
functionality, robusiness and performance of PostGIS / Postgresql.
We tried the MS SQLServer equivalent, the install and use there is way
nastier than PostGIS.  Both the use and the performance sucked in
comparison for our use cases.
MySQL's equivalent is nowhere near as functional, robust, as well
documented or as widely used.

Oracle's equivalent is probably the closest.  Pity that installing Oracle
and their products as a whole is a nightmare, and rather wallet-draining...

Have a look at GIS / Mapping projects around the world, a majority are
implemented on PostGIS.  Openstreetmap is  probably the biggest (think open
source version of Google Maps), and it moved to PostGIS from MySQL several
years ago.
We did a lot of research into PostGIS, as GIS / tracking is a core part of
our business.
We didn't find a product that could compare on

   - Maturity
   - Functionality
   - Performance
   - Cost
   - Documentation
   - Support (huge community)