Re: pg_basebackup
On Mon, Dec 23, 2019 at 4:13 AM Kyotaro Horiguchi wrote: > Hello. > > At Mon, 23 Dec 2019 03:38:12 +, Daulat Ram > wrote in > > thanks Adrian, what about the > > postmaster.opts file, this file was also skipped in backup. > > The file is overwritten at startup so there's no point in having it in > a backup. Thus, it is deliberately excluded from a backup. > That is true and sensible, but it does seem to be contradicted by the documentation for 9.5. In later versions, they added "except certain temporary files managed by PostgreSQL". That addition seems to just be a correction of the docs, not something to reflect a change in actual behavior, at least in regards to postmaster.opts. Cheers, Jeff
Re: Are my autovacuum settings too aggressive for this table?
> > > > Live tuples = 19,766,480 > Analyze scale factor = 0.001 > Analyze thresh = 5000 > Thresh + live_tuples * factor = 24,766 > > So an autovacuum analyze should trigger around 24K tuples modified, is > this to little or too much? This seems too much to me. Was there a specific problem occurring that spurred this change? If many of the tuple modifications are occurring on a certain subset of the data which changes the distribution in an important way, then maybe this would be justified. (But maybe partitioning between active and largely inactive would be a better solution) > Same goes for autvacuum vacuum, is it too aggressive? > > > #-- > # AUTOVACUUM > > #-- > autovacuum_naptime = 1h > autovacuum_vacuum_threshold = 1 > autovacuum_analyze_threshold = 5000 > autovacuum_vacuum_scale_factor = 0.002 > autovacuum_analyze_scale_factor = 0.001 > > The analyze settings don't seem defensible to me, but maybe you can make a case for them by referring to problems that were showing up in particular queries. The naptime seems particularly indefensible. If you think you overdid it with some changes, then you should back off those changes. Not randomly whack around other settings in an attempt to compensate for the first ones, without having some identifiable theory which supports this. If you do have such a theory, you haven't told us what it might be. If index-only-scans (and hence rel_allvisible) are particularly important to you, then the autovac settings might make sense. However, this is unlikely to be true at the global level, but rather on a table by table basis. But, I don't see the point in setting autovacuum_vacuum_scale_factor = 0.002. If it needs a drastic decrease (to pair with a well-considered increase in autovacuum_vacuum_threshold) why would 99% be the correct decrease, rather than 100%? A nice thing about exact 0 is it stands out as being intentional, as opposed to a typo or a mistranslation between percentage and fraction. Cheers, Jeff
replace single char for string using regexp_replace
I have to replace ; , " and ' chars in an string putting its correspondent ascii value I have a Replace(Replace(Replace(Replace($$Text,with;On'It"$$,',','chr(59)'),';','chr(44)'),'"','chr(34)'),,'chr(39)') It works but I would like to call just one Replace. I tried this but didn´t work select regexp_replace($$Text,with;On'It"$$,$$,|;|"|'$$,$$chr(59)|chr(44)|chr(34)|chr(39)$$,'g'); , -> chr(59) ; -> chr(44) " -> chr(34) ' -> chr (39) -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: replace single char for string using regexp_replace
PegoraroF10 writes: > I have to replace ; , " and ' chars in an string putting its correspondent > ascii value > I have a > Replace(Replace(Replace(Replace($$Text,with;On'It"$$,',','chr(59)'),';','chr(44)'),'"','chr(34)'),,'chr(39)') > It works but I would like to call just one Replace. Use translate(), perhaps? https://www.postgresql.org/docs/current/functions-string.html regards, tom lane
Re: testing in ESQL/C if a CUSROR "foo" is open?
El día viernes, diciembre 27, 2019 a las 06:55:22a. m. +0100, Matthias Apitz escribió: > > Hello, > > Is there any way (without looking up the name in table 'pg_cursors') to see > if a > cursor "foo" is still open? > I found this hint about 'pg_cursors' in stackoverflow: https://stackoverflow.com/questions/51146931/postgres-finding-an-open-cursor-and-closing-it But did no see how to code this in ESQL/C, something like: EXEC SQL IF EXISTS(SELECT * FROM pg_cursors WHERE name = :cur) THEN CLOSE :cur; Which does not work. And, what concerns me as well is how expensive such test is before closing any cursor. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
