Re: Time zone offset in to_char()

[michael]

> Am 11.01.2024 um 16:06 schrieb Alban Hertroijs :
> 
> Hi all,
> 
> I'm basically looking for a one-liner to convert a timestamptz (or a 
> timestamp w/o time zone if that turns out to be more convenient) to a string 
> format equal to what MS uses for their datetimeoffset type. I got almost 
> there with to_char(ts, '-MM-DD HH24:MI:SS.US0 TZH:TZM'). 
> Unfortunately(?), the server lives at time zone UTC, while we need to convert 
> to both UTC and Europe/Amsterdam zones. The above always gives me +00 for the 
> TZH output, while it should be +01 now and +02 in the summer...

have you tried to use the proper time zone before you pass it to the to_char() 
function?

mkrueger=# select to_char(now(), '-MM-DD HH24:MI:SS.US0 TZH:TZM');
  to_char

 2024-01-11 16:24:21.9154740 +01:00
(1 row)

Time: 12,351 ms
mkrueger=# select to_char(now() at time zone 'UTC', '-MM-DD HH24:MI:SS.US0 
TZH:TZM');
  to_char

 2024-01-11 15:24:38.1619810 +00:00
(1 row)

Time: 2,095 ms
mkrueger=# select to_char(now() at time zone 'Europe/Amsterdam', '-MM-DD 
HH24:MI:SS.US0 TZH:TZM');
  to_char
----
 2024-01-11 16:24:52.8736860 +00:00
(1 row)


At least it seems to do what you need.

Regards,
Michael

> 
> I'm dealing with a data virtualisation system (TIBCO TDV) here that connects 
> different types of data-sources, among which is an MS SQL database with said 
> type. The virtualisation software uses PostgreSQL (14.10 on Ubuntu Linux 
> 22.04) for caching data. TDV doesn't understand this datetimeoffset type and 
> treats it internally as a VARCHAR(34) - hence the string output - which is 
> obviously kind of hard to work with for aggregations and such.
> 
> However, in TDV we can create a translation between TDV functions that accept 
> a timestamp type and a time zone name with a translation to native PostgreSQL 
> functions, operands and whatnot. That's what I'm looking for.
> It currently have this:
> ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
> ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
> 
> In the above, I worked around the issue using a couple of user-defined 
> functions in PG. That should give a reasonable idea of the desired 
> functionality, but it's not an ideal solution to my problem:
> 1). The first function has as a drawback that it changes the time zone for 
> the entire transaction (not sufficiently isolated to my tastes), while
> 2). The second function has the benefit that it doesn't leak the time zone 
> change, but has as drawback that the time zone is now hardcoded into the 
> function definition, while
> 3). Both functions need to be created in the caching database before we can 
> use them, while we have several environments where they would apply (DEV, 
> pre-PROD, PROD).
> 
> /* Based this one on a stackoverflow post */
> create or replace function ciscache.ToDatetimeOffset(ts_ timestamptz, tz_ 
> text)
> returns varchar(34)
> language plpgsql
> as $$
> begin
>   perform set_config('timezone', tz_, true /* local */);
>   return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
> end;
> $$;
> 
> create or replace function ciscache.ToDatetimeOffsetNL(ts_ timestamptz)
> returns varchar(34)
> language plpgsql
> set timezone to 'Europe/Amsterdam'
> as $$
> begin
>   return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
> end;
> $$;
> 
> Is there a way to do this without functions, or if not, at least without 
> having to hard-code the time zone or leaking the time zone change to other 
> calls within the same transaction?
> 
> Any suggestions much appreciated.
>  
> Groet,
>  
> Alban Hertroijs
> Data engineer ∙ NieuweStroom
> aanwezig ma t/m vr, di tot 13:30 uur
>  
>  
> www.nieuwestroom.nl <http://www.nieuwestroom.nl/>
> Kijk gratis terug: webinar Dynamische energie is de toekomst 
> <https://nieuwe-oogst.webinargeek.com/webinar/replay/8_AX8F0UtBvv0LoSu8_mJb9olc5c94P8uaBKt8H0TG0/>
> 
> PS We hebben een nieuwe huisstijl en website! Met ons 10 jarige bestaan, 
> trokken we een nieuwe jas aan.

Re: Parallel Query - Can it be used within functions?

[Michael Krüger]

ah, and for completeness the simple python function I use for the test:

create or replace function reports.generic_query_python(_sql text)
  RETURNS SETOF record
  LANGUAGE 'plpythonu'
  PARALLEL SAFE
  COST 100
  VOLATILE
  ROWS 5000
AS $BODY$
return plpy.execute( _sql )
$BODY$;


Michael Krüger  schrieb am Mi., 28. Feb. 2018 um
09:05 Uhr:

> Ok, to close this thread. The problem is, that plpgsql function do seem to
> return data using a cursor. That fact is disabling parallel execution. So
> if we instead hand over the SQL to a function with e.g. a python body, then
> parallel execution is happening, because the data is first assembled in
> memory before it is returned, without using a cursor:
>
> mkrueger=# explain analyze select * from reports.generic_query_python($$
> select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where
> mediatrunkid in
> (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028)
> group by 1 $$) as foo (mediatrunkid bigint, count numeric);
> LOG:  0: duration: 35.158 ms  plan:
> Query Text:  select mediatrunkid,count(*)::numeric from
> reports.mediatrunkkpi where mediatrunkid in
> (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028)
> group by 1
> Partial HashAggregate  (cost=56693.72..56696.72 rows=300 width=16) (actual
> time=35.144..35.149 rows=17 loops=1)
>   Group Key: mediatrunkid
>   Buffers: shared hit=1641
>   ->  Parallel Bitmap Heap Scan on mediatrunkkpi  (cost=4525.01..56279.28
> rows=82889 width=8) (actual time=2.350..24.584 rows=63794 loops=1)
> Recheck Cond: (mediatrunkid = ANY
> ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
> Heap Blocks: exact=1641
> Buffers: shared hit=1641
> ->  Bitmap Index Scan on idx_mediatrunkkpi_trunk
> (cost=0.00..4475.27 rows=198933 width=0) (never executed)
>   Index Cond: (mediatrunkid = ANY
> ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
> LOCATION:  explain_ExecutorEnd, auto_explain.c:359
> LOG:  0: duration: 35.165 ms  plan:
> Query Text:  select mediatrunkid,count(*)::numeric from
> reports.mediatrunkkpi where mediatrunkid in
> (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028)
> group by 1
> Partial HashAggregate  (cost=56693.72..56696.72 rows=300 width=16) (actual
> time=35.152..35.157 rows=17 loops=1)
>   Group Key: mediatrunkid
>   Buffers: shared hit=1630
>   ->  Parallel Bitmap Heap Scan on mediatrunkkpi  (cost=4525.01..56279.28
> rows=82889 width=8) (actual time=2.364..24.702 rows=63272 loops=1)
> Recheck Cond: (mediatrunkid = ANY
> ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
> Heap Blocks: exact=1630
> Buffers: shared hit=1630
> ->  Bitmap Index Scan on idx_mediatrunkkpi_trunk
> (cost=0.00..4475.27 rows=198933 width=0) (never executed)
>   Index Cond: (mediatrunkid = ANY
> ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
> LOCATION:  explain_ExecutorEnd, auto_explain.c:359
> LOG:  0: duration: 47.855 ms  plan:
> Query Text:  select mediatrunkid,count(*)::numeric from
> reports.mediatrunkkpi where mediatrunkid in
> (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028)
> group by 1
> Finalize GroupAggregate  (cost=57784.41..57792.66 rows=300 width=40)
> (actual time=45.331..45.344 rows=17 loops=1)
>   Group Key: mediatrunkid
>   Buffers: shared hit=2735
>   ->  Sort  (cost=57784.41..57785.91 rows=600 width=16) (actual
> time=45.322..45.325 rows=51 loops=1)
> Sort Key: mediatrunkid
> Sort Method: quicksort  Memory: 27kB
> Buffers: shared hit=2735
> ->  Gather  (cost=57693.72..57756.72 rows=600 width=16) (actual
> time=45.270..45.295 rows=51 loops=1)
>   Workers Planned: 2
>   Workers Launched: 2
>   Buffers: shared hit=2735
>   ->  Partial HashAggregate  (cost=56693.72..56696.72 rows=300
> width=16) (actual time=38.387..38.391 rows=17 loops=3)
> Group 

Re: Parallel Query - Can it be used within functions?

[Michael Krüger]

1241 loops=1)
Index Cond: (mediatrunkid = ANY
('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Buffers: shared hit=823
LOCATION:  explain_ExecutorEnd, auto_explain.c:359
LOG:  0: duration: 49.924 ms  plan:
Query Text: explain analyze select * from reports.generic_query_python($$
select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where
mediatrunkid in
(2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028)
group by 1 $$) as foo (mediatrunkid bigint, count numeric);
Function Scan on generic_query_python foo  (cost=0.25..50.25 rows=5000
width=40) (actual time=49.920..49.922 rows=17 loops=1)
  Buffers: shared hit=6388
LOCATION:  explain_ExecutorEnd, auto_explain.c:359
  QUERY PLAN
---
 Function Scan on generic_query_python foo  (cost=0.25..50.25 rows=5000
width=40) (actual time=49.920..49.922 rows=17 loops=1)
 Planning time: 0.029 ms
 Execution time: 49.977 ms
(3 rows)





Michael Krüger  schrieb am Fr., 16. Feb. 2018 um
11:42 Uhr:

> Dear all,
>
> still same behavior with Postgres 10.2 ...
>
> Just as a reminder that the issue still exists.
>
> Regards,
> Michael
>
> Andreas Kretschmer  schrieb am Di., 6. Feb. 2018
> um 08:35 Uhr:
>
>> Hi,
>>
>>
>> Am 06.02.2018 um 08:24 schrieb Michael Krüger:
>> > create or replace function reports.generic_query(_sql text)
>> >   RETURNS SETOF record
>> >   LANGUAGE 'plpgsql'
>> >   PARALLEL SAFE
>> >   COST 100
>>
>> there is an other parameter, parallel_setup_cost, with default = 1000. I
>> think, you should set this parameter too.
>>
>> Please keep me informed, it is interessting me.
>>
>>
>> Regards, Andreas
>>
>> --
>> 2ndQuadrant - The PostgreSQL Support Company.
>> www.2ndQuadrant.com
>>
>>
>>

Re: Best options for new PG instance

[Michael Paquier]

On Mon, Mar 05, 2018 at 09:51:53AM -0800, Steve Atkins wrote:
> I've been running postgresql instances on ESXi VMs for years with no
> issues. I've not benchmarked them, but performance has been good
> enough despite their running on fairly wimpy hardware. Performance
> relative to bare metal is probably going to be dominated by disk IO,
> and depending on how you're hosting VMs that can be anywhere between
> pretty good and terrible - in a large corporation I'd expect it to be
> pretty good. Just don't skimp on RAM - having your hot data in the
> filesystem cache is always good and can make high latency storage
> tolerable.

One thing to be very careful about is the backup strategy of your
PostgreSQL instances.  I would recommend primarily using PostgreSQL
in-core tools like pg_basebackup to do the work and make sure that
things are consistent.  Users tend to rely a lot on VM snapshots,
particularly quiesced snapshots without memory footprint, but those
could be the cause of data corruption if not using appropriate
pre-freeze and post-thaw scripts in charge of freezing the partitions
while the snapshot is taken (use different partitions for the data
folder, pg_wal and logs as well!), so this would require extra work from
your side.  I am talking about VMware technology here, still you can
find a lot of so-told-useful VM-level backup technologies.  Be careful
with those as well when it comes to database backups.  You can think
that your backups taken are safe, until you see a corruption which has
been hidden for weeks.
--
Michael


signature.asc
Description: PGP signature

Re: Resync second slave to new master

[Michael Paquier]

On Tue, Mar 06, 2018 at 04:45:10AM +, Dylan Luong wrote:
> After a failover (promote) to the Slave1,  is it easily resync the
> Slave2 to the new master (old slave1)? Do we need to do  full rebuild
> of the Slave2 from new master everytime we failover to Slave1 from
> Master? Can we use pg_rewind on Slave2 to resyn it with new master
> (old slave1)? 

After promoting slave 1, it could be possible that some records have
slipped to slave 2 from the primary.  In this case, a rewind would be
recommended.  You should be careful that your slave 2 has not received
WAL to a position newer than where WAL has forked because of the
promotion.  If that happened, then a rewind would be necessary before
replugging slave 2 to the newly-promoted server.  Be very careful with
your failover flow here.  pg_rewind also would not run if it finds that
the target server does not need a rewind, so you could stop the slave 2,
and run pg_rewind unconditionally to keep things simple.
--
Michael


signature.asc
Description: PGP signature

Re: Resync second slave to new master

[Michael Paquier]

On Tue, Mar 06, 2018 at 06:00:40AM +, Dylan Luong wrote:
> So everytime after promoting Slave to become master (either manually
> or automatic), just stop Slave2 and run pg_rewind on slave2 against
> the new maser (old slave1). And when old master server is available
> again, use pg_rewind on that serve as well against new master to
> return to original configuration.

Yes.  That's exactly the idea.  Running pg_rewind on the old master will
be necessary anyway because you need to stop it cleanly once, which will
cause it to generate WAL records at least for the shutdown checkpoint,
while doing it on slave 2 may be optional, still safer to do.
--
Michael


signature.asc
Description: PGP signature

Re: Resync second slave to new master

[Michael Paquier]

On Thu, Mar 08, 2018 at 10:48:29AM +0300, Yavuz Selim Sertoğlu wrote:
> If not set, could you add recovery.conf file
> recovery_target_timeline='latest'
> parameter?
> https://www.postgresql.org/docs/devel/static/recovery-target-settings.html

Yes, that's visibly the issue here.
--
Michael


signature.asc
Description: PGP signature

Re: wiki Disk Usage, table size: ERROR: could not open relation with OID 0

[Michael Paquier]

On Wed, Mar 14, 2018 at 04:17:54PM +0100, Hans Schou wrote:
> I got the message
>   ERROR: could not open relation with OID 0
> when running the "General Table Size Information" from
> https://wiki.postgresql.org/wiki/Disk_Usage

I cannot see this failure on latest HEAD on a database running the
regression database.  This is an elog() message by the way, which is
something to report internal errors, so users should never be able to
face it.

> But I guess it was supposed to give size of all tables.
> 
> I'm running version 9.1.9 so it should be working according to the
> wiki.

You should update and upgrade.  9.1 has fallen out of community support
1 year and a half ago, and 9.1.9 is utterly outdated.
--
Michael


signature.asc
Description: PGP signature

Re: changing my mail address

[Michael Paquier]

On Sun, Mar 18, 2018 at 09:38:22AM -0400, Stephen Frost wrote:
> These days, each email from the mailing list includes a link which can
> be used to unsubscribe from that list without having any account.

Yes. Ron, if you take the time to look at a raw email, just look for the
field value of List-Unsubscribe and you are good to go.  I don't know
much how email clients parse that, but gmail actually shows that as a
small button you can click on if I recall correctly.
--
Michael


signature.asc
Description: PGP signature

Re: FDW Foreign Table Access: strange LOG message

[Michael Paquier]

On Thu, Mar 22, 2018 at 06:55:12PM -0400, Tom Lane wrote:
> Anyway, it's clearly not very nice that postgres_fdw makes no attempt
> to do a graceful shutdown of its remote connection.  I don't know that
> this rises to the level of a bug, exactly, but if somebody wanted to
> send in a patch it'd probably get accepted.

I won't qualify that as a bug, this is mainly noise.  Still I agree that
it would be cleaner to make more efforts in finishing the connections
when the session goes down.  You would roughly just need to register an
on_shmem_exit callback which loops through all the connections to finish
them.  Now would we want to slow down the session shutdown just for
that?  I am less sure particularly if there is lag between the remote
and the local servers.
--
Michael


signature.asc
Description: PGP signature

Re: pg_stat_statements: password in command is not obfuscated

[Michael Paquier]

On Sat, Mar 24, 2018 at 12:17:30PM +1300, David Rowley wrote:
> If it is, then it's not a bug in pg_stat_statements. log_statement =
> 'ddl' would have kept a record of the same thing.
> 
> Perhaps the best fix would be a documentation improvement to mention
> the fact and that it's best not to use plain text passwords in
> CREATE/ALTER ROLE. Passwords can be md5 encrypted.

Yeah, this is bad practice.  That's one of the reasons why storage of
plain text passwords has been removed in Postgres 10 still they can be
passed via command, and also why PQencryptPasswordConn and
PQencryptPassword are useful.  Using psql's \password is a good habit to
have.
--
Michael


signature.asc
Description: PGP signature

Re: PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13

[Michael Paquier]

On Mon, Mar 26, 2018 at 01:49:30PM -0700, David G. Johnston wrote:
> You should probably ask this question on the VMWare forums since its their
> software that would or would not have the performance improvements.
> PostgreSQL will benefit from any memory and disk-related virtualization
> enhancements that they made.  I would have to assume that the answer is a
> favorable yes.

(VMware human here).

Upgrading ESX or other VMware-related components has nothing to do with
PostgreSQL.  Some of the products may embed a version of PostgreSQL to
store some of their meta-data or other things, in which case the upgrade
to a newer PostgreSQL version, if need be, will be taken care of by the
product itself.  If you are using your own set of PostgreSQL instances,
then that's up to what you have at OS level.
--
Michael


signature.asc
Description: PGP signature

Re: [GENERAL] missing public on schema public

[Michael Paquier]

On Mon, Mar 26, 2018 at 12:46:38PM -0300, Alvaro Herrera wrote:
> Was this ever fixed?

Ugh.  I have added a reminder on the open item page for v11 as an older
bug: 
https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items#Older_Bugs
--
Michael


signature.asc
Description: PGP signature

Re: Warning of .partial wal file in PITR and Replication Environment

[Michael Paquier]

On Mon, Mar 26, 2018 at 11:52:53AM +, Amee Sankhesara - Quipment India 
wrote:
> Warning : The failed archive command was: copy
> "D:\PostgreSQL\9.6\data\pg_xlog\00010A8800F8.partial"
> "\\10.0.0.35\Archive_Replication\00010A8800F8.partial" |
> copy "D:\PostgreSQL\9.6\data\pg_xlog\00010A8800F8.partial"
> "\\10.0.0.35\Archive_PITR\00010A8800F8.partial"

Am I reading that correctly or you are trying to copy twice the same
file?  Why?  Using only copy may cause corruptions if you are not
careful as a disabled write caching would cause the data to go to the OS
cache on Windows and not directly to disk.

> Due to this it has stopped to take backup of PITR
> 
> So we like to know how to clean up the ".partial" file from pg_xlog and run 
> PITR  backup smoothly
> Also like to know is there any database consistency related issue ?

This is the last, partial WAL segment from the previous timeline.
Normally such things are able to archive correctly, so you may want to
consider a more advanced archive command able to handle duplicates.
It is impossible to give a strict answer before knowing what you are
looking for in terms of WAL archive redundancy.

You may need to use target_timeline = 'latest' in your recovery.conf
settings as well.
--
Michael


signature.asc
Description: PGP signature

Re: pg_ctl promote causes error "could not read block" (PG 9.5.0 and 9.5.4)

[Michael Paquier]

On Wed, Mar 28, 2018 at 09:36:11AM -0700, raj 1988 wrote:
> Are we hitting some bug? tried to look around but not able to confirm if we
> are hitting a bug or not.  For us this is happening consistently on
> different servers whenever we do pg_ctl promote and then it block WRITE on
> that table.

This has the strong smell of the FSM bug fixed in 9.5.5:
https://www.postgresql.org/docs/devel/static/release-9-5-5.html

So, in order to get things right:
1) Update to the latest version of Postgres 9.5.
2) Make sure that your cluster gets in a clean state.  There are
instructions here:
https://wiki.postgresql.org/wiki/Free_Space_Map_Problems

> As of now we get rid of the error either by doing vacuum full or CTAS, but
> i am afraid what we will do in case this happens to our few TB tables.

This rebuilds the file-space map, which is why it goes away.  You really
want to do the work I am mentioning above to get back to a clean state.
--
Michael


signature.asc
Description: PGP signature

Re: PostgreSQL Cascade streaming replication problem

[Michael Paquier]

On Sun, Apr 01, 2018 at 06:26:51PM +, Jakub Janeček wrote:
> What did i do wrong? I need stop comulating WAL files and remove old WAL
> files, which are processed and are only "waiting"..

Perhaps wal_keep_segments is set and you forgot about it or you used a
replication slot that you forgot to drop on slave 1?
--
Michael


signature.asc
Description: PGP signature

Re: difference between checkpoint_segments and wal_keep_segments in postgres configuration file

[Michael Paquier]

On Tue, Apr 10, 2018 at 11:06:54PM +0530, Raghavendra Rao J S V wrote:
> I am not clear the difference between checkpoint_segments and
> wal_keep_segments .
> 
> I would like to now below things. Please explain.Thanks in advance.
> 
>- Difference  between *checkpoint_segments *and *wal_keep_segments *
>value
>- Role  of  *checkpoint_segments *and *wal_keep_segments *
>- Which one should has higher value.

Documentation is king here.  For checkpoint_segments:
https://www.postgresql.org/docs/9.4/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS

  Maximum number of log file segments between automatic WAL
  checkpoints (each segment is normally 16 megabytes). The default is
  three segments. Increasing this parameter can increase the amount of
  time needed for crash recovery. This parameter can only be set in
  the postgresql.conf file or on the server command line.

For wal_keep_segments:
https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

  Specifies the minimum number of past log file segments kept in the
  pg_xlog directory, in case a standby server needs to fetch them for
  streaming replication. Each segment is normally 16 megabytes. If a
  standby server connected to the sending server falls behind by more than
  wal_keep_segments segments, the sending server might remove a WAL
  segment still needed by the standby, in which case the replication
  connection will be terminated. Downstream connections will also
  eventually fail as a result. (However, the standby server can recover by
  fetching the segment from archive, if WAL archiving is in use.)

Mentioning checkpoint_segments implies that you are using PostgreSQL 9.4
or older versions as this has been removed and replaced by max_wal_size
in 9.5.  You should consider upgrading to a newer version.

Hence the first is used in the context of normal operations to decide
the frequency of checkpoints when those are triggered by volume.  The
second can be used with streaming replication to give a standby a higher
catchup window.  Giving value to one or the other depends on the
context, and both are usable in completely different circumstances.
--
Michael


signature.asc
Description: PGP signature

Re: Can a broken Postgresql data directory be reconstructed without losing data?

[Michael Paquier]

On Sun, Apr 15, 2018 at 08:25:05AM +, A A wrote:
> I'm trying to restore a database from a broken data directory that
> have lost many of its files accidentally.

Let me guess, a wild set of rm commands?

> I have tried unsuccessfully to install new instance of the same
> Postgresql version and then copy (with overwrite) the broken datadir
> to the new one.here the remaining datadir that I'm working on :

This is the kind of things I have found myself doing some times, as well
as some people on this list, and this enters in the category of horror
stories.

So the answer to the question "Can I work on a broken data folder and
save as much data as possible?" is "yes".  However this enters in the
field of dark magics as this requires advanced skills and knowledge of
PostgreSQL, including its internals, its folder structure, and ways to
bypass some protocols or even perhaps patch the upstream code to bypass
some checks to the data you are looking for...  Additionally, this is
usually a step-by-step and case-by-case problem.

Please note that data deleted is gone.  You may be able to get back some
data from the file system which has been deleted, though this requires a
special set of skills and luck.  Hiring a professional is recommended
for such operations.
--
Michael


signature.asc
Description: PGP signature

Re: pg_dump to a remote server

[Michael Nolan]

On Mon, Apr 16, 2018 at 6:58 PM, Ron  wrote:

> We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump
> file will be more than 1TB, and there's not enough disk space on the
> current system for the dump file.
>
> Thus, how can I send the pg_dump file directly to the new server while the
> pg_dump command is running?  NFS is one method, but are there others
> (netcat, rsync)?  Since it's within the same company, encryption is not
> required.
>

Can you run pg_dump on the new server, connecting remotely to the current
one?
--
Mike Nolan

Re: Old active connections?

[Michael Paquier]

On Tue, Apr 17, 2018 at 05:11:10PM -0700, David G. Johnston wrote:
> Long-lived non-idle statements would likely be waiting for a lock to be
> released.

Be very careful with transactions marked as "idle in transaction" for a
long time.  Long-running transactions prevent VACUUM to do its work as
the oldest XID in view is not updated, causing performance to go down,
and bloat to go up.
--
Michael


signature.asc
Description: PGP signature

Re: Can PostgreSQL create new WAL files instead of reusing old ones?

[Michael Paquier]

On Tue, Apr 17, 2018 at 02:57:03PM -0400, Tom Lane wrote:
> I think possibly the OP doesn't understand why it's designed that way.
> The point is not really to "recycle old WAL files", it's to avoid having
> disk space allocation occur during the critical section where we must
> PANIC on failure.  Now, of course, that doesn't really work if the
> filesystem is COW underneath, because it's allocating fresh disk space
> anyway even though semantically we're overwriting existing data.
> But what I'd like to see is a fix that deals with that somehow, rather
> than continue to accept the possibility of ENOSPC occurring inside WAL
> writes on these file systems.  I have no idea what such a fix would
> look like :-(

That looks like a rather difficult problem to solve in PostgreSQL
itself, as the operator running the cluster is in charge of setting up
the FS options which would control the COW behavior, so it seems to me
that there is room as well for an in-core option to tell the
checkpointer to enforce the removal of past files instead of simple
recycling them, because this actually breaks max_wal_size.

max_wal_size is of course a soft limit, and it has been discussed a
couple of times that it would be nice to get that to a hard limit, but
it is really a hard problem to avoid the system to not slow down or even
stop its I/O if the hard is close by or reached..
--
Michael


signature.asc
Description: PGP signature

Re: Pulling initial physical replication pg_basebackup from a downstream server

[Michael Paquier]

On Wed, Apr 25, 2018 at 09:52:47AM -0500, Scott Frazer wrote:
> I'm changing out a data center and I need to setup a new replicated server.
> The bandwidth speeds between the new data center and the master are slower
> than the speeds between the new data center and the current replica.
> 
> Can I get the pg_base_backup from the current replica and then tell the new
> server to connect to the master?

Taking a base backup from a replica is a supported operation.  The base
backup taken can then be used to connect to the root primary, even if
that's not the server you took the base backup from.  Being part of the
same cluster is enough (same system ID generated at initialization
time). 

> Would I need to do anything special to
> keep the master from discarding the transaction logs needed to come up to
> speed?

You could create a physical replication slot on the primary, and then
take a backup after being sure that the standby has fetched enough WAL
to be ahead of the LSN position where the replication slot has been
taken.  And finally create a recovery.conf in the new replica so as it
uses the slot created on the master.  You will finish by retaining a bit
more WAL than necessary, but a client can request WAL data using a
replication slot which is at least the oldest position.  Asking for a
newer position would cause the replication slot to just not retain
anymore the data past the point requested.
--
Michael


signature.asc
Description: PGP signature

Re: Asynchronous Trigger?

[Michael Loftis]

As suggested, note in ToDo table, also maybe look at LISTEN and NOTIFY
and have a job runner process LISTENing (and cleaning up the queue,
or, marking an item as in progress if you've multiple workers)  The
work queue table is to help maintain state...if noone is LISTENing
then the table acts as backup so when a worker fires up it can start
working on whatever's already there and empty that.

On Fri, Apr 27, 2018 at 4:46 AM, Olleg Samoylov  wrote:
> Try to look at PGQ from SkyTools.
>
> On 2018-03-30 01:29, Cory Tucker wrote:
>
> Is it possible to have the execution of a trigger (or any function) not
> block the completion of the statement they are associated with?
>
> A pattern I had hoped to implement was to do a quick update of rows that
> signaled they needed attention, and then an async per-row trigger would come
> and do the maintenance (in this case, make an expensive materialized view).
>
> Any suggestions welcome.
>
> thanks!
> --Cory
>
>



-- 

"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler

Re: relkind='p' has no pg_stat_user_tables

[Michael Paquier]

On Thu, May 03, 2018 at 01:24:59PM -0500, Justin Pryzby wrote:
> Yes, I was surprised about the difference between ANALYZE relkind_p
> and relkind_r.
> 
> But I see that's a documented behavior I'd missed until now:
> 
> https://www.postgresql.org/docs/current/static/sql-analyze.html
> |If the specified table is a partitioned table, both the inheritance 
> statistics
> |of the partitioned table as a whole and statistics of the individual 
> partitions
> |are updated.

When I read this thread, it seems to me that more user-friendly system
functions able to extract a sub-tree of child relations (by inheritance
and/or partition) is something which would help.  Now users are limited
to things like large WITH RECURSIVE queries when willing to extract a
full tree.  While that's easily done with a custom function, there is
room for an in-core function as well.  I recall that Amit Langote has
sent a patch which introduces a wrapper function on top of
find_all_inheritors, perhaps that would get into v12.
--
Michael


signature.asc
Description: PGP signature

Re: recovery_target_time and WAL fetch with streaming replication

[Michael Paquier]

On Sun, May 13, 2018 at 01:39:48AM +0200, Hannes Erven wrote:
> what is Postgresql's strategy when to fetch WAL from the master while in
> streaming replication, and could it be tweaked?
>
> I'm using a physical streaming replication slave to have a database lagging
> behind about one month behind the primary, by setting "recovery_target_time"
> to the desired point in time.
> This setting is periodically advanced by a cronjob to allow the replica to
> roll forward. It's a 10.3-1 install on Debian.

I think that you are coplicating your life here.

Fetching WAL from a primary (or another standby) cannot be directly per
se.  By definition, any WAL present will e automatically fetched and
synced on the standby.  Now, it is not usually the moment WAL is fetched
and synced on a standby that matters, it is the moment it is replayed.
For example, what people usually want to be protected from is an
infortunate DROP TABLE on the primary run by an administrator to be
immediately replayed on the standby, losing the data.  Hence delaying
when WAL is replayed can offer some protection, and this can be achieved
by setting recovery_min_apply_delay in recovery.conf.  This will cause
WAL records replaying transactions commits to wait for the amount of
time specified by this parameter, giving you the time to recover from
any failures with a standby which has a controlled synced delta.

> One option of course would be to use some transfer mechanism external to
> Postgresql... but so far I'm thinking there must be any easier way?

Another option I can think of here is to use a specific restore_command
instead of streaming replication.  Simply archive a WAL segment on the
primary with some meta-data like the time it was archived, and then
allow the standby to recover the segment only after a delta has passed.
The can allow a more evenly distribution of segments.
--
Michael


signature.asc
Description: PGP signature

Re: recovery_target_time and WAL fetch with streaming replication

[Michael Paquier]

On Sun, May 13, 2018 at 09:42:42AM +0200, Hannes Erven wrote:
> But when new WAL is needed, the standby will fetch /all/ WAL present on the
> master.

Fetching as much WAL as possible when recovery happens is wanted by
design, so as it recovers as much as possible.  And that's documented.

> I'd say, the standby should either:
> - always connect to the primary and fetch any WAL present

This is what a hot standby does.  It keeps waiting for WAL to become
available whichever the source used (archive, local pg_xlog or stream)
and switches between one or the other.  You can look at
WaitForWALToBecomeAvailable to get an idea of the details.  

> - stop fetching/streaming WAL when it is not needed for the current
> recovery_target

The startup process is in charge of recovery (WAL replay and definition
of from where to get the WAL available), and is the one which decides if
using streaming is necessary or not.  if streaming is used, then it
starts a WAL receiver.  If a switch from streaming to another WAL source
(local pg_xlog or archives is done), then it shuts down the WAL
receiver, consisting in sending SIGTERM to the WAL receiver and stopping
it immediately with a FATAL message (stops process immediately).  The
key point is that  WAL receiver is designed to be a light-weight
transport layer for WAL data.  In short, to be simple, it receives a set
of WAL bytes and writes them.  It does not include any logic to decode
WAL records, so it cannot know when a stop point happens or not.  It
also has no idea of the configuration within recovery.conf, which is
loaded by the startup process.

> Yes, but thats far less simple than just setting restore_target_time .

It seems to me that archiving provides the control you are looking for.
--
Michael


signature.asc
Description: PGP signature

Re: 10.4 upgrade, function markings, and template0

[Michael Paquier]

On Mon, May 14, 2018 at 05:22:39PM -0400, Tom Lane wrote:
> Maybe it'd be worth building some sort of infrastructure that would
> allow this to be done at a lower level.  It's not hard to imagine
> an autovacuum-like or bgworker-based thingy that could run around
> and apply a given SQL script in every database, bypassing the usual
> worries about authentication and connections-disabled databases.

A portion of the infrastructure is already available for background
workers which can use BGWORKER_BYPASS_ALLOWCONN since Postgres 11 to
enforce connections to databases even if an administrator disables
connections to it.
--
Michael


signature.asc
Description: PGP signature

Re: Out of memory error with PG10.3, 10.4 but not 9.3.19

[Michael Paquier]

On Tue, May 15, 2018 at 03:02:48PM +, ChatPristi wrote:
> I got the error with PG 10.3 and 10.4 on CentOS 7 up-to-date.The
> command works with a smaller size database. 
> The command works with the same database with PG 9.3.19 on RHEL 6.9
> up-to-date.
> 
> I attach the EXPLAIN SELECT command.

Well, instead of an explain output which takes 2.4MB compressed and
9.6MB uncompressed (take it as unreadable), could you produce a
self-contained test case with a glimpse of the schema you are using?
Where does the OOM happen, and how did you change your partitioned table
schema?  Are you using the native partitioning instead?
--
Michael


signature.asc
Description: PGP signature

Re: When use triggers?

[Michael Stephenson]

On Wed, May 16, 2018 at 6:36 PM, Adrian Klaver
 wrote:
> On 05/16/2018 03:19 PM, hmidi slim wrote:
>>
>> HI,
>>
>> I'm working on a microservice application and I avoid using triggers
>> because they will not be easy to maintain and need an experimented person in
>> database administration to manage them. So I prefer to manage the work in
>> the application using ORM and javascript.
>> However I want to get some opinions and advices about using triggers: when
>> should I use them? How to manage them when there are some problems?
>
>
> Two benefits triggers and their associated functions offer, with the
> understanding these are general statements:
>
> 1) Are in the database so tasks that always need happen do not need to be
> replicated across the various interfaces that touch the database.
>
> 2) Also since they run on the server the data does not necessarily cross a
> network, so less network bandwidth and better performance.
>
> An example of a use case is table auditing. If you need to track what is
> being INSERTed/UPDATEd/DELETEd in a table stick an audit trigger/function on
> the table and push the information to an audit table.
>
> As to managing, they are like any other code. I keep my schema code in
> scripts under version control and deploy them from there. I use
> Sqitch(http://sqitch.org/) which allows me to deploy and revert changes. I
> use a dev database to test and troubleshoot triggers and functions.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

The only appropriate use for triggers that I've ever found was for
auditing changes to tables.

It can be quite trivial for simple cases: just use an update trigger
and write all of the "deleted" (e.g., before the update)
values/columns that you want to track to your audit table.  Each row
in the audit represents the previous state before changes were made.
It's so trivial that you could write a sql script to generate a
simplest-case audit table and audit trigger for any table where you
don't need any custom behavior in the audit logic

As for management, you could also have a build or deployment task that
updates audit tables and the triggers as columns are added (or perhaps
removed) from their related tables (e.g., semi-automatic management).

To keep them lightweight, my typical audit table is insert-only (other
grants are removed) with a primary key, no foreign keys, and a single
nonunique index on the main table's primary key if the audits related
to a main-table row need to be viewable by an end user or something
similar.

It's kind of interesting that you speak of microservices and triggers.
If you go "all in" with microservices, the typical approach is to use
event sourcing and CQRS and then all of your writes __are__ your
audit.

~Mike Stephenson

Re: Problem compiling PostgreSQL.

[Michael Paquier]

On Thu, May 17, 2018 at 08:31:48AM +0100, Paul Linehan wrote:
> I'm having problems compiling PostgreSQL.

On which platform and/or distribution are you trying the code
compilation?
--
Michael


signature.asc
Description: PGP signature

initdb failing (10.4 centos7)

[Michael Nolan]

initdb is failing on Centos 7 with 10.4 because the install procedure does
not change the ownership of the /usr/local/pgsql directory to postgres.

Changing the ownership fixes the problem, but the install procedure should
do this.
--
Mike Nolan

Re: initdb failing (10.4 centos7)

[Michael Nolan]

On Sat, May 19, 2018 at 2:16 PM, Don Seiler  wrote:

> On Sat, May 19, 2018 at 2:10 PM, Michael Nolan  wrote:
>
>> initdb is failing on Centos 7 with 10.4 because the install procedure
>> does not change the ownership of the /usr/local/pgsql directory to
>> postgres.
>>
>> Changing the ownership fixes the problem, but the install procedure
>> should do this.
>>
>
>
> Are you using the PGDG repo packages, or the default CentOS repo packages?
> You should use PGDG and those should install under /var/lib/pgsql.
>
> Don.
> --
> Don Seiler
> www.seiler.us
>

I was installing from source code, so I did a configure and a build (as
postgres) and then an install (as root).
--
Mike Nolan

Re: initdb failing (10.4 centos7)

[Michael Nolan]

On Sat, May 19, 2018 at 5:23 PM, Adrian Klaver 
wrote:

> On 05/19/2018 03:16 PM, Michael Nolan wrote:
>
>>
>>
>> On Sat, May 19, 2018 at 2:16 PM, Don Seiler > d...@seiler.us>> wrote:
>>
>> On Sat, May 19, 2018 at 2:10 PM, Michael Nolan > <mailto:htf...@gmail.com>> wrote:
>>
>> initdb is failing on Centos 7 with 10.4 because the install
>> procedure does not change the ownership of the /usr/local/pgsql
>> directory to postgres.
>>
>> Changing the ownership fixes the problem, but the install
>> procedure should do this.
>>
>>
>> Are you using the PGDG repo packages, or the default CentOS repo
>> packages? You should use PGDG and those should install under
>> /var/lib/pgsql.
>>
>> Don.
>> -- Don Seiler
>> www.seiler.us <http://www.seiler.us>
>>
>>
>> I was installing from source code, so I did a configure and a build (as
>> postgres) and then an install (as root).
>>
>
> Then setting up the $DATADIR is on you:
>
> https://www.postgresql.org/docs/10/static/install-short.html
>
>
Shouldn't this also be mentioned in the INSTALL file in the source
directory then?
--
Mike Nolan

>
>
> --
>> Mike Nolan
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: initdb failing (10.4 centos7)

[Michael Nolan]

Just call me blind!
--
Mike Nolan

Re: PostgreSQL backup issue

[Michael Paquier]

On Tue, May 22, 2018 at 10:35:46PM -0700, David G. Johnston wrote:
> I would conclude that pg-basebackup is placing its output in stderr instead
> of stdout then...

The output of pg_basebackup's verbose mode goes to stderr (look for
example at the verbose flags in pg_basebackup.c).
--
Michael


signature.asc
Description: PGP signature

Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat and PostGreSQL 9.2.24 on Debian

[Michael Paquier]

On Thu, May 24, 2018 at 10:21:33AM +0200, talk to ben wrote:
> - Doing Streaming Replication between different minor version of PG is
>   possible but not recommended [2]

Standbys need to be updated first, hence be careful that the primary is
not updated before the standbys or WAL generated on the primary may not
be able to replay on its standbys.  Note however that you won't get
support for such configurations on the community lists, so just make
sure that all nodes in a cluster are on the same version and that it is
the latest one.
--
Michael


signature.asc
Description: PGP signature

Microsoft buys GitHub, is this a threat to open-source

[Michael Nolan]

Microsoft has bought GitHub for $7.5 billion, is this a threat to the open
source community?
--
Mike Nolan

Re: Microsoft buys GitHub, is this a threat to open-source

[Michael Nolan]

On Mon, Jun 4, 2018 at 12:15 PM, Tom Lane  wrote:

> Michael Nolan  writes:
> > Microsoft has bought GitHub for $7.5 billion, is this a threat to the
> open
> > source community?
>
> A fair question, but one that seems entirely off-topic for the Postgres
> lists, since we don't depend on GitHub.  (And that's a thing for which
> I'm very glad today.)
>
> regards, tom lane
>

Core postgres is OK, but I think there are a number of postgres-related
projects that might be on places like GitHub.
--
Mike Nolan

Re: Microsoft buys GitHub, is this a threat to open-source

[Michael Paquier]

On Mon, Jun 04, 2018 at 08:44:37PM +0200, Pavel Stehule wrote:
> I have few projects there - Orafce, plpgsql_check, and pspg. I hope so
> these projects are well protected by BSD licence - and distributed
> redundant nature of git. I hope so there is not reason for panic this
> moment. I have not a big data in non git sources - issues, and others.

Not sure myself if there is any need to worry about this stuff (which is
not related to Postgres lists as there is no dependency to github,
yeah!), still one thing that anybody hosting projects on remote places
should do anyway is to take automatic backups of what they have on those
places and keep a copy of them locally.  This way, you have an exit door
if something happens to the place where the code is located.  I do so
for all my stuff on github for example.  And git makes that really easy
to do.
--
Michael


signature.asc
Description: PGP signature

Re: Microsoft buys GitHub, is this a threat to open-source

[Michael Paquier]

On Thu, Jun 07, 2018 at 11:57:06AM +0200, Jehan-Guillaume (ioguix) de Rorthais 
wrote:
> How do you backup your projects issues on github? Using the API with some
> loops?

I personally don't care much about this part.  As long as the code
survives..
--
Michael


signature.asc
Description: PGP signature

Re: Print pg_lsn as a number?

[Michael Paquier]

On Tue, Jun 12, 2018 at 10:39:43AM -0700, Andres Freund wrote:
> On 2018-06-12 09:31:49 -0700, Scott Stroupe wrote:
>> According to the documentation[1], pg_lsn is a 64-bit integer that's
>> printed as two hex numbers separated by a slash, e.g. 68/1225BB70. Is
>> there a way to get the 64-bit integer in a common numeric
>> representation instead of the peculiar hex-slash-hex representation?
> 
> What do you want to do with the LSN?

While fixing the slot advance stuff, I would have liked to get an
automatic test able to reproduce the crash where the slot is first
advanced at a page boundary, and then again moved forward.  However it
happens that it is not that easy to do so, so I would be rather
supportive to at least the following operations:
pg_lsn + numeric = pg_lsn.
pg_lsn % numeric = numeric.

We map pg_wal_lsn_diff result to a numeric, so that could make sense to
use numeric as well here, which is not the greatest choice by the way as
that's an int64 internally, but that's more portable for any
(unlikely-to-happen) future changes.

Using the segment size value in pg_settings, you could also advance the
LSN worth a full segment for example...
--
Michael


signature.asc
Description: PGP signature

Re: Load data from a csv file without using COPY

[Michael Paquier]

On Tue, Jun 19, 2018 at 02:32:10PM -0700, David G. Johnston wrote:
> ​You really need to describe what you consider to be a "real life​
> scenario"; and probably give a better idea of creation and number of these
> csv files.  In addition to describing the relevant behavior of the
> application you are testing.
> 
> If you want maximum realism you should probably write integration tests for
> your application and then execute those at high volume.
> 
> Or at minimum give an example of the output you would want from this
> unknown program...

Hard to say what you are especially looking for that psql's \copy cannot
do, but perhaps you have an interest in pg_bulkload?  Here is a link to
the project:
https://github.com/ossc-db/pg_bulkload/

It has a couple of fancy features as well, like preventing failures of
rows if loading a large file, etc.
--
Michael


signature.asc
Description: PGP signature

Re: using pg_basebackup for point in time recovery

[Michael Paquier]

Hi Pierre,

On Tue, Jun 19, 2018 at 12:03:58PM +, Pierre Timmermans wrote:
> Here is the doc, the sentence that I find misleading is "There are
> backups that cannot be used for point-in-time recovery", also
> mentioning that they are faster than pg_dumps add to confusion (since
> pg_dumps cannot be used for PITR):
> https://www.postgresql.org/docs/current/static/continuous-archiving.html

Yes, it is indeed perfectly possible to use such backups to do a PITR
as long as you have a WAL archive able to replay up to the point where
you want the replay to happen, so I agree that this is a bit confusing.
This part of the documentation is here since the beginning of times,
well 6559c4a2 to be exact.  Perhaps we would want to reword this
sentence as follows:
"These are backups that could be used for point-in-time recovery if
combined with a WAL archive able to recover up to the wanted recovery
point.  These backups are typically much faster to backup and restore
than pg_dump for large deployments but can result as well in larger
backup sizes, so the speed of one method or the other is to evaluate
carefully first."

I am open to better suggestions of course.
--
Michael


signature.asc
Description: PGP signature

Re: using pg_basebackup for point in time recovery

[Michael Paquier]

Hi Pierre,

On Wed, Jun 20, 2018 at 08:06:31AM +, Pierre Timmermans wrote:
> Hi Michael

You should avoid top-posting on the Postgres lists, this is not the
usual style used by people around :)

> Thanks for the confirmation. Your rewording removes the confusion. I
> would maybe take the opportunity to re-instate that pg_dump cannot be
> used for PITR, so in the line of 
> "These are backups that could be used for point-in-time recovery if
> combined with a WAL archive able to recover up to the wanted recovery
> point.  These backups are typically much faster to backup and restore
> than pg_dump for large deployments but can result as well in larger
> backup sizes, so the speed of one method or the other is to evaluate
> carefully first. Consider also that pg_dump backups cannot be used for
> point-in-time recovery."

Attached is a patch which includes your suggestion.  What do you think?
As that's an improvement, only HEAD would get that clarification.

>  Maybe the confusion stems from the fact that if you restore a
> standalone (self-contained) pg_basebackup then - by default - recovery
> is done with the recovery_target immediate option, so if one needs
> point-in-time recovery he has to edit the recovery.conf and brings the
> archives..

Perhaps.  There is really nothing preventing one to add a recovery.conf
afterwards, which is also why pg_basebackup -R exists.  I do that as
well for some of the framework I work with and maintain.
--
Michael
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index 982776ca0a..ccc0a66bf3 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -1430,12 +1430,15 @@ restore_command = 'cp /mnt/server/archivedir/%f %p'
  Standalone Hot Backups
 
  
-  It is possible to use PostgreSQL's backup facilities to
-  produce standalone hot backups. These are backups that cannot be used
-  for point-in-time recovery, yet are typically much faster to backup and
-  restore than pg_dump dumps.  (They are also much larger
-  than pg_dump dumps, so in some cases the speed advantage
-  might be negated.)
+  It is possible to use PostgreSQL's backup
+  facilities to produce standalone hot backups.  These are backups that
+  could be used for point-in-time recovery if combined with a WAL
+  archive able to recover up to the wanted recovery point.  These backups
+  are typically much faster to backup and restore than pg_dump for large
+  deployments but can result as well in larger backup sizes, so the
+  speed of one method or the other is to evaluate carefully first.  Note
+  also that pg_dump backups cannot be used
+  for point-in-time recovery.
  
 
  


signature.asc
Description: PGP signature

Re: using pg_basebackup for point in time recovery

[Michael Paquier]

On Thu, Jun 21, 2018 at 04:42:00PM -0400, Ravi Krishna wrote:
> Same here even though I use Mac mail. But it is not yahoo alone. 
> Most of the web email clients have resorted to top posting.  I miss
> the old days of Outlook Express which was so '>' friendly.  I think
> Gmail allows '>' when you click on the dots to expand the mail you
> are replying to, but it messes up in justifying and formatting it.

Those products have good practices when it comes to break and redefine
what the concept behind emails is...
--
Michael


signature.asc
Description: PGP signature

Re: using pg_basebackup for point in time recovery

[Michael Paquier]

On Thu, Jun 21, 2018 at 04:50:38PM -0700, David G. Johnston wrote:
> Generally only actual bug fixes get back-patched; but I'd have to say
> this looks like it could easily be classified as one.

Everybody is against me here ;)

> Some comments on the patch itself:
> 
> "recover up to the wanted recovery point." - "desired recovery point" reads
> better to me
> 
> 
> "These backups are typically much faster to backup and restore" - "These
> backups are typically much faster to create and restore"; avoid repeated
> use of the word backup

Okay.

> "but can result as well in larger backup sizes" - "but can result in larger
> backup sizes", drop the unnecessary 'as well'

Okay.

> I like adding "cold backup" here to help contrast and explain why a base
> backup is considered a "hot backup".  The rest is style to make that flow
> better.

Indeed.  The section uses hot backups a lot.

What do all folks here think about the updated attached?
--
Michael
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index 982776ca0a..af48aa64c2 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -1430,12 +1430,15 @@ restore_command = 'cp /mnt/server/archivedir/%f %p'
  Standalone Hot Backups
 
  
-  It is possible to use PostgreSQL's backup facilities to
-  produce standalone hot backups. These are backups that cannot be used
-  for point-in-time recovery, yet are typically much faster to backup and
-  restore than pg_dump dumps.  (They are also much larger
-  than pg_dump dumps, so in some cases the speed advantage
-  might be negated.)
+  It is possible to use PostgreSQL's backup
+  facilities to produce standalone hot backups.  These are backups that
+  could be used for point-in-time recovery if combined with a WAL
+  archive able to recover up to the wanted recovery point.  These backups
+  are typically much faster to create and restore than
+  pg_dump for large deployments but can result
+  in larger backup sizes.  Note also that
+  pg_dump backups cannot be used for
+  point-in-time recovery.
  
 
  


signature.asc
Description: PGP signature

Re: using pg_basebackup for point in time recovery

[Michael Paquier]

On Mon, Jun 25, 2018 at 12:51:10PM -0400, Bruce Momjian wrote:
> FYI, in recent discussions on the docs list:
> 
>   
> https://www.postgresql.org/message-id/CABUevEyumGh3r05U3_mhRrEU=dfacdrr2hew140mvn7fsbm...@mail.gmail.com

I did not recall this one.  Thanks for the reminder, Bruce.

> There was the conclusion that:
> 
>   If it's a clean backpatch I'd say it is -- people who are using
>   PostgreSQL 9.6 will be reading the documentation for 9.6 etc, so they
>   will not know about the fix then.
>   
>   If it's not a clean backpatch I can certainly see considering it, but if
>   it's not a lot of effort then I'd say it's definitely worth it.
> 
> so the rule I have been using for backpatching doc stuff has changed
> recently.

In the case of this thread, I think that the patch applies cleanly
anyway as this comes from the period where hot standbys have been
introduced.  So that would not be a lot of work...  Speaking of which,
it would be nice to be sure about the wording folks here would prefer
using before fixing anything ;p
--
Michael


signature.asc
Description: PGP signature

Re: Specifying WAL Location in Streaming Replication

[Michael Paquier]

On Sun, Jul 08, 2018 at 09:51:47AM -0400, Matt Dee wrote:
> In the documentation for START_REPLICATION, a required argument is the WAL
> location to begin streaming at, and I'm not sure what to use here.  I have
> been using 0, and it seems to work fine.  Additionally, it seems that when
> --startpos is not provided to pg_recvlogical, it defaults to just sending a
> wal location of 0.
> 
> While this seems to work, I feel a bit uneasy about it since I can't find
> any documentation explaining why this works or what this argument does when
> using a replication slot.  Any clarification would be appreciated.

0/0 can be used in the case where the client does not know which
position it should try to use, in which case the server will choose by
itself from the oldest LSN position where changes have been left
unconsumed.  If you have an idea of documentation improvement, I think
that it would be welcome, say in the replication protocol page for the
command START_REPLICATION.  This level of details is really internal,
but fresh ideas may bring more clarity in this area, and nowhere in the
docs are used references to either 0/0 or InvalidXLogRecPtr for WAL
positions.
--
Michael


signature.asc
Description: PGP signature

Re: Reconnecting a slave to a newly-promoted master

[Michael Paquier]

On Mon, Jul 09, 2018 at 05:58:53PM -0700, Shawn Mulloney wrote:
> There are three PostgreSQL machines: A, B, and C. B and C are slaves off of
> the master, A. A fails, and B is promoted to being the new master. Can C
> just be pointed at A and have it "just work"?

In your question I am pretty sure that you mean "B" instead of "A" as
"A" has died and is off the grid.  The answer to that question would be
perhaps no, as if C could have replayed WAL ahead of B in which case you
could finish with a corrupted C instance if trying to reconnect it
directly to the promoted B.
--
Michael


signature.asc
Description: PGP signature

Re: Waiting connections postgres 10

[Michael Paquier]

On Mon, Jul 23, 2018 at 04:41:59PM +, Nicola Contu wrote:
> we used to monitor waiting connections with postgres 9.6.6 via this
> query: 
> 
> select count (*) from pg_stat_activity where wait_event IS NOT NULL

Please note that a wait event becomes NULL once it is reset, so what
this query does is to monitor backends which are not waiting on
something, not backends which are idle.  Hence you would include
backends also doing some active work.

> select count (*) from pg_stat_activity where wait_event_type ='Client'
> and wait_event IN ('ClientRead','ClienteWrite') and state='idle'.
>
> Can anyone help me?

You are visibly looking for a WHERE clause defined with state IN
('idle', 'idle in transaction') when looking for connections waiting for
some activity to be generated by the application, which works also with
9.6.
--
Michael


signature.asc
Description: PGP signature

Re: Pg_rewind cannot load history wal

[Michael Paquier]

On Wed, Aug 01, 2018 at 09:09:30PM +, Richard Schmidt wrote:
> Our procedure that runs on machine A and B is as follows:
> 
>   1.  Build new databases on A and B, and configure A as Primary and B
>   as Standby databases. 
>   2.  Make some changes to the A (the primary) and check that they are
>   replicated to the B (the standby) 
>   3.  Promote B to be the new primary
>   4.  Switch of the A (the original primary)
>   5.  Add the replication slot to B (the new primary) for A (soon to
>   be standby)
>   6.  Add a recovery.conf to A (soon to be standby). File contains
>   recovery_target_timeline = 'latest' and restore_command = 'cp
>   /ice-dev/wal_archive/%f "%p" 
>   7.  Run pg_rewind on A - this appears to work as it returns the
>   message 'source and target cluster are on the same timeline no
>   rewind required'; 
>   8.  Start up server A (now a slave)

Step 7 is incorrect here, after promotion of B you should see pg_rewind
actually do its work.  The problem is that you are missing a piece in
your flow in the shape of a checkpoint on the promoted standby to run
after 3 and before step 7.  This makes the promoted standby update its
timeline number in the on-disk control file, which is used by pg_rewind
to check if a rewind needs to happen or not.

We see too many reports of such mistakes, I am going to propose a patch
on the -hackers mailing list to mention that in the documentation...
--
Michael


signature.asc
Description: PGP signature

Re: Sv: WAL-dir filling up with wal_level = logical

[Michael Paquier]

On Fri, Aug 03, 2018 at 12:40:16PM +0200, Andreas Kretschmer wrote:
> On 3 August 2018 12:33:26 CEST, Andreas Joseph Krogh  
> wrote:
>>Hm, a co-worker noticed that we have max_wal_size = 16GB, that might be
>>the 
>>reason:-)
> 
> Yes ;-)

Worth mentioning that this is a soft size, and not a hard size, hence
depending on your worload you may see more WAL segments than what is set
in 16GB.  The docs mention that, so no need to be surprised.
--
Michael


signature.asc
Description: PGP signature

Re: Pg_rewind cannot load history wal

[Michael Paquier]

On Sat, Aug 04, 2018 at 07:44:59AM +0100, Simon Riggs wrote:
> I think the problem is that writing the online checkpoint is deferred
> after promotion, so this is a timing issue that probably doesn't show
> in our regression tests.

Somewhat.  It is a performance improvement of 9.3 to let the startup
request a checkpoint to the checkpointer process instead of doing it
itself.

> Sounds like we should write a pending timeline change to the control
> file and have pg_rewind check that instead.
> 
> I'd call this a timing bug, not a doc issue.

Well, having pg_rewind enforce a checkpoint on the promoted standby
could cause a performance hit as well if we do it mandatorily as if
there is delay between the promotion and the rewind triggerring a
checkpoint could have already happen.  So it is for me a documentation
bug first regarding the failover workflow, and potentially a patch for a
new feature which makes pg_rewind trigger directly a checkpoint.
--
Michael


signature.asc
Description: PGP signature

Re: Pg_rewind cannot load history wal

[Michael Paquier]

On Sat, Aug 04, 2018 at 04:59:45AM -0700, Andres Freund wrote:
> On 2018-08-04 10:54:22 +0100, Simon Riggs wrote:
>> pg_rewind doesn't work correctly. Documenting a workaround doesn't change 
>> that.
> 
> Especially because most people will only understand this after they've
> been hit, as test scenarios will often just be quick enough.

Well, since its creation we have the tool behave this way.  I am not
sure either that we can have pg_rewind create a checkpoint on the source
node each time a rewind is done, as it may not be necessary, and it
would enforce WAL segment recycling more than necessary, so if we were 
to back-patch something like that I am pretty much convinced that we
would get complains from people already using the tool, with existing
failover flows which are broken.  Making this stuff to not need a
checkpoint is actually possible.  When the source is offline, the
control file can be relied on as the shutdown checkpoint would update
the on-disk control file.  When the source is online, pg_rewind only
needs to know the new timeline number from the source, which we could
provide via a SQL function, but that would work only on HEAD (look at
ControlFile_source, you would see that only the new TLI matters, and
that getTimelineHistory does not really need to know the contents of the
control file).
--
Michael


signature.asc
Description: PGP signature

Re: Pg_rewind cannot load history wal

[Michael Paquier]

On Sat, Aug 04, 2018 at 07:54:36AM -0700, Christophe Pettus wrote:
> Would having pg_rewind do a checkpoint on the source actually cause
> anything to break, as opposed to a delay while the checkpoint
> completes?

Users relying only on streaming without archives would be impacted as
potentially two checkpoints could be used on the promoted standby,
making all past segment needed from the divergence point not to be
around.  That's a problem which exists in v11 as only WAL segments worth
one checkpoint are kept around, not for 9.5, 9.6 and 10.

> The current situation can create a corrupted target, which seems far
> worse than just slowing down pg_rewind.

Hm?  pg_rewind requires the target to be stopped properly, meaning that
the divergence point is known to both nodes.  If the source is online
and has not created the first post-recovery checkpoint, then you would
get a no-op with pg_rewind, and when restarting the old master witha
recovery.conf you would get a failure.  If you stop the old master
so as at next startup it needs crash recovery to recover, then there is
indeed a risk of corrupted instance, but that would be the same problem
even if pg_rewind is used.
--
Michael


signature.asc
Description: PGP signature

Re: upgrading from pg 9.3 to 10

[Michael Paquier]

On Tue, Aug 14, 2018 at 04:15:12PM -0300, Martín Marqués wrote:
> I'd recommend testing with a clone of the server to verify that it works
> properly (not only pg_upgrade, but your application with the new version
> of postgres). Also to time the window you'll need and see if there are
> things to be aware of, like extensions which are upgraded and might
> break the upgrade.
> 
> Now if you are going to first jump to 9.4, I'd recommend using pglogical
> after getting to 9.4 and upgrade straight from 9.4 to 10 (always after
> testing your application against 10)

Well, pglogical has the advantage of reducing the downtime, which may
not matter depending on your application and you may be able to accept a
it of downtime, and pg_upgrade --link can be pretty quick at its job.
Test it before as --link is a no-return trip.
--
Michael


signature.asc
Description: PGP signature

Re: pg_basebackup failed to read a file

[Michael Paquier]

On Tue, Aug 14, 2018 at 12:14:59PM -0400, Tom Lane wrote:
> That seems like a pretty expensive thing to do, if there are lots of
> files ... and you'd still end up failing, so it's not moving the ball
> very far.

Yeah, I would think that with many small relations it is going to have a
measurable performance impact if we scan the whole data directory a
second time.

> More generally, this seems closely related to bug #14999 [1]
> which concerned pg_rewind's behavior in the face of unexpected file
> permissions within the data directory.  We ended up not doing anything
> about that except documenting it, which I wasn't very satisfied with,
> but the costs of doing better seemed to exceed the benefits.

Please feel free to read the end of the thread about details on the
matter.  There are many things you could do, all have drawbacks.

> It'd be nice to have a more coherent theory about what needs to be copied
> or not, and not fail on files that could simply be ignored.  Up to now
> we've resisted having any centrally defined knowledge of what can be
> inside a PG data directory, but maybe that bullet needs to be bitten.

Yeah, I have not really come up with a nice idea yet, especially when
things sometimes move with custom files that some users have been
deploying, so I am not completely sure that we'd need to do something
anyway, nor that it is worth the trouble.  One saner strategy may be to
split your custom file into a directory out of the main data folder...
--
Michael


signature.asc
Description: PGP signature

Re: Reeving an error while taking the backup using "pg_basebackup" utility.

[Michael Paquier]

On Tue, Aug 28, 2018 at 07:19:12AM +0530, Raghavendra Rao J S V wrote:
> pg_basebackup: could not get transaction log end position from server:
> FATAL:  requested WAL segment 00010285008F has already been
> removed
> 
> Please guide me why and how to handle this error. Do you want me to change
> any of the option in my pg_basebackup command let me know.

This means that while taking a backup, a checkpoint has come in and has
recycled past segments.  Lack of luck it is, as depending on the load
you may see such failures.  One way to correct the problem would be to
take a base backup without WAL segments included and with a WAL archive
used by the base backup taken.  A second is to use a physical
replication slot which guarantee the presence of the wanted segments.
--
Michael


signature.asc
Description: PGP signature

Re: pg_basebackup + SSL error: bad length

[Michael Paquier]

On Mon, Aug 27, 2018 at 04:40:34PM -0700, Adrian Klaver wrote:
> Is there more then one copy of pg_basebackup on the machines?

Or this user has created a tablespace directly in the main data folder,
which can cause pg_basebackup to fail because of recursion issues.  It
is no wonder that a WARNING is created during a CREATE TABLESPACE if you
do that.
--
Michael


signature.asc
Description: PGP signature

Re: WAL replay issue from 9.6.8 to 9.6.10

[Michael Paquier]

On Wed, Aug 29, 2018 at 08:31:50AM +0200, Alexander Kukushkin wrote:
> 2018-08-29 6:02 GMT+02:00 Dave Peticolas :
>> Hello, I'm seeing some issues with WAL replay on a test server running
>> 9.6.10 using WAL archived from a 9.6.8 primary server. It reliably PANICs
>> during replay with messages like so:
>>
>> WARNING:  page 1209270272 of relation base/16422/47496599 does not exist
>> CONTEXT:  xlog redo at 4810/C84F8A0 for Btree/DELETE: 88 items
>> PANIC:  WAL contains references to invalid pages
> 
> 
> it looks like you are hitting pretty much the same problem as I:
> https://www.postgresql.org/message-id/flat/153492341830.1368.3936905691758473953%40wrigleys.postgresql.org
> The only major difference, you are restoring from the backup, while in
> my case the host running replica has crashed.
> Also in my case, the primary was already running 9.6.10.
> 
> In my case, it also panics during "Btree/DELETE: XYZ items" and page
> number of relation is insanely huge.

That would be the same problem.  Dave, do you have a background worker
running in parallel or some read-only workload with backends doing
read-only operations on a standby once it has reached a consistent
point?
--
Michael


signature.asc
Description: PGP signature

Re: WAL replay issue from 9.6.8 to 9.6.10

[Michael Paquier]

On Wed, Aug 29, 2018 at 09:15:29AM -0700, Dave Peticolas wrote:
> Oh, perhaps I do, depending on what you mean by worker. There are a couple
> of periodic processes that connect to the server to obtain metrics. Is that
> what is triggering this issue? In my case I could probably suspend them
> until the replay has reached the desired point.

That would be it.  How do you decide when those begin to run and connect
to Postgres.  Do you use pg_isready or similar in a loop for sanity
checks?

> I have noticed this behavior in the past but prior to 9.6.10 restarting the
> server would fix the issue. And the replay always seemed to reach a point
> past which the problem would not re-occur.

You are picking my interest here.  Did you actually see the same
problem?  In 9.6.10 what happens is that I have tightened the consistent
point checks and logic so as inconsistent page issues would actually
show up when they should, and that those become reproducible so as we
can track down any rogue WAL record or inconsistent behavior.
--
Michael


signature.asc
Description: PGP signature

Re: WARNING: could not flush dirty data: Function not implemented

[Michael Paquier]

On Sun, Sep 02, 2018 at 04:31:18PM -0700, Andres Freund wrote:
> Please note that nobody has verified that postgres works correctly via
> the emulation stuff MS is doing.  There is a native version of postgres
> for windows however, and that is tested (and exercised by a lot of
> installations).

If there are folks willing to put enough effort in getting this to work,
it could work, assuming that a buildfarm animal is able to get down this
road.  From what I can see on this thread we are not yet at that stage
though.
--
Michael


signature.asc
Description: PGP signature

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Wed, Sep 05, 2018 at 03:29:31AM +, Alessandro Gherardi wrote:
> It looks like scram-sha-256 doesn't work when postgres is linked
> against FIPS-enabled OpenSSL and FIPS mode is turned on.
> 
> Specifically, all login attempts fail with an OpenSSL error saying
> something along the lines of "Low level API call to digest SHA256
> forbidden in fips mode".

The error comes from libc, right?  Postgres can of course be configured
to work with FIPS without patching it, it just needs to be enabled
system-wide, which is what RedHat does, and what you are doing I guess?

> I think this issue could be solved by refactoring the code in
> sha2_openssl.c to use the OpenSSL EVP interface
> (see https://wiki.openssl.org/index.php/EVP_Message_Digests ). 
> Any thoughts? Is this a known issue?

This report is the first of this kind since Postgres 10, which is where
the SHA2 interface for OpenSSL has been introduced.  So likely we'd need
to look into that more deeply..  This has the strong smell of a bug.  If
your system is new enough, you should have sha256() & co as system
functions, so you would see the failure as well?  The regression tests
would have likely complained.
--
Michael


signature.asc
Description: PGP signature

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Wed, Sep 05, 2018 at 01:19:39PM +, Alessandro Gherardi wrote:
> Hi Michael,I'm actually running postgres on Windows.

First you may want to avoid top-posting.  This is not the style of the
community lists and this breaks the logic of a thread.

> I added code to fe-secure-openssl.c and be-secure-openssl.c that reads
> the Windows "standard" FIPS registry entry, and if FIPS is enabled
> calls FIPS_mode_set(1). This is to mimic to behavior of the .NET
> framework.

That's rather uncharted territory, as you are patching both the backend
*and* the client.  If we could prove that sha2-openssl.c is actually
unreliable even if FIPS is enabled system-wide with either SCRAM
authentication or any of the other hashing functions, then I would be
ready to accept a patch.  Now, as far as I can see and heard from other
folks for at least Linux, if FIPS is enabled at the OS level, then
Postgres would use it automatically and SCRAM is able to work.  I have
yet to hear that this part is broken.  As far as I know from companies
within the community which worked on STIG requirements, the thing
works.

> Below is the code I added to fe-secure-openssl.c, the code in
> be-secure-openssl.c is similar: 
> Thoughts? I can try to fix the scram-sha-256 issue by using EVP and
> send you a merge request for the patch and the code below if you think
> my approach is correct.

That's a bit unreadable I am afraid :)
You may want to attach a patch after producing it with for example "git
format-patch -1".
--
Michael


signature.asc
Description: PGP signature

Re: how to know current xlog location on standby after primary is down

[Michael Paquier]

On Sun, Sep 09, 2018 at 10:29:08PM +0800, magodo wrote:
> So I want to know what is the correct way to do it. Thank you in
> advance!

There are pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn() which
allow you to know what is the last LSN received and replayed on a
standby.  Those can be used when an instance is in recovery.
--
Michael


signature.asc
Description: PGP signature

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Mon, Sep 10, 2018 at 02:52:00PM +, Alessandro Gherardi wrote:
> I changed the implementation of the other SHA digests to use EVP
> also.  I verified that, with these changes, scram-sha-256 works when
> FIPS is enabled.

Hm...  I have spent a couple of hours setting up a CentOS 7 VM with FIPS
enabled to track down if there is actually a problem.  Here is what I
have done to enable it:
1) yum install dracut-fips
dracut -v -f
2) Update boot loader, where it is necessary to update
GRUB_CMDLINE_LINUX by adding to it "fips=1 boot=UUID=$PARTITION_UUID"
into /etc/default/grub.  PARTITION_UUID can be found with "blkid=/boot"
(depends on the partition layer by the way).
3) Disable prelinking (requires installation of package prelink) by
adding PRELINKING=no to /etc/sysconfig/prelink, then remove existing
prelinks with "prelink -u -a".

After a reboot, it is possible to see /proc/sys/crypto/fips_enabled set
to 1.  Once I did that, unfortunately I have not been able to spot
deficiencies when calling the low-level SHA APIs from OpenSSL, where
both SCRAM and all the in-core SSL functions are proving to work
correctly.  Calling directly FIPS_mode() within Postgres backends also
prove that FIPS is effectively enabled.  Anyway, on top of the remark
Alessandro has done above, this line from the OpenSSL docs has caught my
eyes:
https://www.openssl.org/docs/man1.1.0/crypto/SHA512_Init.html
"Applications should use the higher level functions EVP_DigestInit
etc. instead of calling the hash functions directly."

This is present in OpenSSL docs for some time:
commit: 4facdbb5fa9d791fc72dc78b9c3512ea1384df33
author: Ulf Möller 
date: Sun, 6 Feb 2000 23:26:31 +

Hence, intrinsically, we are in contradiction with the upstream docs.  I
have worked on the problem with the patch, which works down to OpenSSL
0.9.8, and should fix your issue.  This is based on what you sent
previously, except that I was not able to apply what was sent, so I
reworked the whole.  Alessandro, does this fix your problems?  I would
like to apply that down to v10 where SCRAM has been introduced.
--
Michael


signature.asc
Description: PGP signature

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Tue, Sep 11, 2018 at 12:02:50PM +0900, Michael Paquier wrote:
> Hence, intrinsically, we are in contradiction with the upstream docs.  I
> have worked on the problem with the patch, which works down to OpenSSL
> 0.9.8, and should fix your issue.  This is based on what you sent
> previously, except that I was not able to apply what was sent, so I
> reworked the whole.  Alessandro, does this fix your problems?  I would
> like to apply that down to v10 where SCRAM has been introduced.

With the actual patch attached things are better.  So here it is. 
--
Michael
From 1b450dee61855f4fd8b9e4a37d2f95c07f26db55 Mon Sep 17 00:00:00 2001
From: Michael Paquier 
Date: Tue, 11 Sep 2018 11:34:48 +0900
Subject: [PATCH] Change SHA algorithms to use EVP_CTX from OpenSSL

This seems to fix issues with FIPS mode on Windows.
---
 src/common/sha2_openssl.c | 45 +++
 src/include/common/sha2.h | 10 -
 2 files changed, 36 insertions(+), 19 deletions(-)

diff --git a/src/common/sha2_openssl.c b/src/common/sha2_openssl.c
index 362e1318db..e80dec7b4d 100644
--- a/src/common/sha2_openssl.c
+++ b/src/common/sha2_openssl.c
@@ -20,83 +20,100 @@
 #include "postgres_fe.h"
 #endif
 
-#include 
-
 #include "common/sha2.h"
 
+static void
+digest_init(EVP_MD_CTX **ctx, const EVP_MD *type)
+{
+	*ctx = EVP_MD_CTX_create();
+	EVP_DigestInit_ex(*ctx, type, NULL);
+}
+
+static void
+digest_update(EVP_MD_CTX **ctx, const uint8 *data, size_t len)
+{
+	EVP_DigestUpdate(*ctx, data, len);
+}
+
+static void
+digest_final(EVP_MD_CTX **ctx, uint8 *dest)
+{
+	EVP_DigestFinal_ex(*ctx, dest, 0);
+	EVP_MD_CTX_destroy(*ctx);
+}
 
 /* Interface routines for SHA-256 */
 void
 pg_sha256_init(pg_sha256_ctx *ctx)
 {
-	SHA256_Init((SHA256_CTX *) ctx);
+	digest_init(ctx, EVP_sha256());
 }
 
 void
 pg_sha256_update(pg_sha256_ctx *ctx, const uint8 *data, size_t len)
 {
-	SHA256_Update((SHA256_CTX *) ctx, data, len);
+	digest_update(ctx, data, len);
 }
 
 void
 pg_sha256_final(pg_sha256_ctx *ctx, uint8 *dest)
 {
-	SHA256_Final(dest, (SHA256_CTX *) ctx);
+	digest_final(ctx, dest);
 }
 
 /* Interface routines for SHA-512 */
 void
 pg_sha512_init(pg_sha512_ctx *ctx)
 {
-	SHA512_Init((SHA512_CTX *) ctx);
+	digest_init(ctx, EVP_sha512());
 }
 
 void
 pg_sha512_update(pg_sha512_ctx *ctx, const uint8 *data, size_t len)
 {
-	SHA512_Update((SHA512_CTX *) ctx, data, len);
+	digest_update(ctx, data, len);
 }
 
 void
 pg_sha512_final(pg_sha512_ctx *ctx, uint8 *dest)
 {
-	SHA512_Final(dest, (SHA512_CTX *) ctx);
+	digest_final(ctx, dest);
 }
 
 /* Interface routines for SHA-384 */
 void
 pg_sha384_init(pg_sha384_ctx *ctx)
 {
-	SHA384_Init((SHA512_CTX *) ctx);
+	digest_init(ctx, EVP_sha384());
 }
 
 void
 pg_sha384_update(pg_sha384_ctx *ctx, const uint8 *data, size_t len)
 {
-	SHA384_Update((SHA512_CTX *) ctx, data, len);
+	digest_update(ctx, data, len);
 }
 
 void
 pg_sha384_final(pg_sha384_ctx *ctx, uint8 *dest)
 {
-	SHA384_Final(dest, (SHA512_CTX *) ctx);
+	digest_final(ctx, dest);
 }
 
 /* Interface routines for SHA-224 */
 void
 pg_sha224_init(pg_sha224_ctx *ctx)
 {
-	SHA224_Init((SHA256_CTX *) ctx);
+	digest_init(ctx, EVP_sha224());
 }
 
 void
 pg_sha224_update(pg_sha224_ctx *ctx, const uint8 *data, size_t len)
 {
-	SHA224_Update((SHA256_CTX *) ctx, data, len);
+	digest_update(ctx, data, len);
 }
 
 void
 pg_sha224_final(pg_sha224_ctx *ctx, uint8 *dest)
 {
-	SHA224_Final(dest, (SHA256_CTX *) ctx);
+	digest_final(ctx, dest);
 }
diff --git a/src/include/common/sha2.h b/src/include/common/sha2.h
index f3fd0d0d28..701647713f 100644
--- a/src/include/common/sha2.h
+++ b/src/include/common/sha2.h
@@ -51,7 +51,7 @@
 #define _PG_SHA2_H_
 
 #ifdef USE_SSL
-#include 
+#include 
 #endif
 
 /*** SHA224/256/384/512 Various Length Definitions ***/
@@ -70,10 +70,10 @@
 
 /* Context Structures for SHA-1/224/256/384/512 */
 #ifdef USE_SSL
-typedef SHA256_CTX pg_sha256_ctx;
-typedef SHA512_CTX pg_sha512_ctx;
-typedef SHA256_CTX pg_sha224_ctx;
-typedef SHA512_CTX pg_sha384_ctx;
+typedef EVP_MD_CTX *pg_sha256_ctx;
+typedef EVP_MD_CTX *pg_sha512_ctx;
+typedef EVP_MD_CTX *pg_sha224_ctx;
+typedef EVP_MD_CTX *pg_sha384_ctx;
 #else
 typedef struct pg_sha256_ctx
 {
-- 
2.19.0.rc2



signature.asc
Description: PGP signature

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Tue, Sep 11, 2018 at 04:32:27PM +0200, Peter Eisentraut wrote:
> I recommend letting this bake in the master branch for a while.  There
> are a lot weirdly patched and alternative OpenSSL versions out there
> that defy any documentation.

Good point.  Such things have bitten in the past.  Okay, then let's do
something about sha2_openssl.c only on HEAD for now then, which I am
fine to finish wrapping.

> Of course, we should also see if this actually fixes the reported problem.

It seems to me that addressing FIPS concerns on Windows and getting our
hashing functions plugged with OpenSSL correctly are two separate
issues.  The second one also says that we are in the grey based on
OpenSSL docs, which worryies me.  And EVP_DigestInit is used in pgcrypto
for ages, where I don't recall seeing reports about that.
--
Michael


signature.asc
Description: PGP signature

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Wed, Sep 12, 2018 at 07:24:24AM +0900, Michael Paquier wrote:
> Good point.  Such things have bitten in the past.  Okay, then let's do
> something about sha2_openssl.c only on HEAD for now then, which I am
> fine to finish wrapping.

I was looking at trying to commit this patch, however more needs to be
done in terms of error handling, as the proposed patch would happily
crash if EVP_MD_CTX cannot be allocated (understand OOM) in
EVP_DigestInit_ex if I read the OpenSSL code correctly (see
crypto/evp/digest.c).  Our lives would be facilitated if it was possible
to use directly EVP_MD_CTX and EVP_MD_CTX_init so as no allocation is
done but that's not doable as of 1.0.2.
--
Michael


signature.asc
Description: PGP signature

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Mon, Sep 17, 2018 at 02:55:55PM +, Alessandro Gherardi wrote:
> Therefore, I believe the best option, at least for now, is calling
> FIPS_mode_set(1) in the application. 

I am not so sure about that.  As you rightly mention, CentOS and RedHat
patch OpenSSL to allow FIPS to work.  Per my research, Ubuntu can also
enable FIPS but that's not the case of Debian, which is very popular (I
may be wrong about the last one but I use it daily).

One question I have is how are you actually able to use FIPS on Windows
with OpenSSL?  Is that from one of the tarballs available in
openssl.org, which are more than 1 year old?  Pure upstream code does
not give this option, and CentOS/RHEL use a customly-made patch, based
on which Postgres does not complain when calling the low-level hashing
functions, and we rely now on FIPS being enabled system-wide.  And that
actually works.  It seems to me that you are yourself using a custom
patch for OpenSSL, and that's actually a different flavor than the Linux
version as in your case the low-level hashing functions complain if
called directly in FIPS mode.

At the end, I think that we ought to wait and see if upstream OpenSSL
comes up with support for FIPS and how it integrates with it, on both
Linux *and* Windows, and then consider if Postgres needs to do more.
There is little point in merging now a patch for something which may or
may not be supported by OpenSSL now.  My bet, as things stand, is that
we could finish with something similar to what happens on Linux with a
system-wide switch that Postgres knows nothing about.  Perhaps that will
not be the case, but let's think about that once we know for sure.
--
Michael


signature.asc
Description: PGP signature

Re: help with startup slave after pg_rewind

[Michael Paquier]

On Wed, Sep 19, 2018 at 10:29:44PM +, Dylan Luong wrote:
> After promoting slave to master, I completed a pg_rewind of the slave
> (old master) to the new master. But when I try to start the slave I am
> getting the following error.
>
> I tried to run pg_rewind again, but now it says I cannot do it as its
> already same timeline.

What did pg_rewind tell you after the first run?  If you remove the set
of WAL segments on the rewound instance and let it replay only segments
from the archive, are you able to get past?

There is an inconsistency in the WAL records you are trying to replay.
In this case a contrecord refers to a WAL record split across multiple
pages.  The WAL reader is expecting one, and cannot find it.  And that's
not normal.  My bet is that something is wrong in your failover flow
which you think is right.  It is hard to get that right.
--
Michael


signature.asc
Description: PGP signature

Re: How to maintain the csv log files in pg_log directory only for past 30 days

[Michael Paquier]

On Fri, Sep 28, 2018 at 10:33:30AM +0530, Raghavendra Rao J S V wrote:
> Log file will be generated in *csv* format at *pg_log* directory in our
> PostgreSQL. Every day we are getting one log file. We would like to
> maintain only max 30 days. Which setting need to modify by us in
> “postgresql.conf” in order to recycle the log files after 30 days.

If you use for example log_filename = 'postgresql-%d.log', then the
server uses one new file every day.  This truncates the contents from
the last month automatically.
--
Michael


signature.asc
Description: PGP signature

Re: How to maintain the csv log files in pg_log directory only for past 30 days

[Michael Paquier]

On Fri, Sep 28, 2018 at 06:19:16AM -0700, Adrian Klaver wrote:
> If log_truncate_on_rotation = 'on', correct?

Yup, thanks for precising.
--
Michael


signature.asc
Description: PGP signature

Re: pg9.6: no backup history file (*.backup) created on hot standby

[Michael Paquier]

On Mon, Oct 08, 2018 at 03:23:47PM +0800, magodo wrote:
> Is this as expected?

Yes.

> If so, in which case should I do backup on primary and in which case
> should I do it on standby?

This depends on your use cases, sometimes you don't want to make the
production server, the primary use more CPU than necessary so you can
leverage the activity on a standby.
--
Michael


signature.asc
Description: PGP signature

Re: pg9.6: no backup history file (*.backup) created on hot standby

[Michael Paquier]

(Please do not forget to add the community mailing list in CC.)

On Tue, Oct 09, 2018 at 10:33:56AM +0800, magodo wrote:
> Since the backup history aims to aid administrator to identify the
> point from which wal archive should be kept and before which the
> archive could be cleaned. It is very helpful in kinds of use cases.
> 
> Why not also create it when do basebackup on standby?

The backup_label file is equally helpful, and backup history files are
not mandatory for backups.  One of the reasons behind why we cannot have
those on standbys is that there is no actual way to ensure the
uniqueness of this file, as two backups could be started in parallel
with the same start location and the *same* file name.  If an archive
command is not able to handle correctly the duplicates, you could bloat
pg_wal.  And that's a real problem.
--
Michael


signature.asc
Description: PGP signature

Re: pg9.6: no backup history file (*.backup) created on hot standby

[Michael Paquier]

On Tue, Oct 09, 2018 at 11:45:50AM +0800, magodo wrote:
> Yet, I am still not so clear how does the bloat of pg_wal happen? Do
> you mean pg_wal will be filled up by many .backup(s)?

If your archive_command is for example a simple cp (which it should not
be by the way), and if you try to archive twice the same file, then the
archive command would continuously fail and prevent existing WAL
segments to be archived.  Segments are continuously created, and pg_wal
grows in size.
--
Michael


signature.asc
Description: PGP signature

Re: pg9.6: no backup history file (*.backup) created on hot standby

[Michael Paquier]

On Tue, Oct 09, 2018 at 03:26:35PM +0800, magodo wrote:
> Yes, but does this differ whether I'm archiving a general WAL or
> archiving the backup history? I mean if user doesn't handle duplicate
> archive, then pg_wal will still be filled up when archiving WAL.

A WAL segment has a unique name, and would be finished to be used once.
The problem with backup history files on standbys is that the *same*
file can could finish by being generated *multiple* times with base
backups taken in parallel.  That's a completely different story, and the
window to those backup history files having the same name gets larger
the more the window between two checkpoints is.  That's a problem I
studied a couple of months back.
--
Michael


signature.asc
Description: PGP signature

Re: Compile psql 9.6 with SSL Version 1.1.0

[Michael Paquier]

On Fri, Oct 12, 2018 at 12:17:40PM -0400, Tom Lane wrote:
> So the immediate fix for building on Windows is you need to manually
> define all of those in pg_config.h.  We probably ought to try to make
> that less painful, though.  At the very least it'd be good if there
> were just one symbol you had to set manually ...

The root of the issue is that we have no way to change dynamically the
set of flags used in pg_config.h.win32 with the MSVC scripts depending
on the version of OpenSSL compiled.  We can tweak the scripts to do that
automatically  Patches welcome.
--
Michael


signature.asc
Description: PGP signature

Re: PostgreSQL upgrade from 9.4.2 to 9.6.12

[Michael Lewis]

This may also be of interest to you, but checking official docs as Adrian
recommended is best.
https://why-upgrade.depesz.com/show?from=9.4.2&to=9.6.12&keywords=

Re: Partitioning an existing table - pg10.6

[Michael Lewis]

I have not personally used this, but the write-up seems solid to minimize
downtime to help you to shift data gradually. Be sure you understand the
limitations of partitioning, particularly when you are still on 10x not yet
on v11 where updates will shift a row to a new partition if the partition
key is updated.

https://www.depesz.com/2019/03/19/migrating-simple-table-to-partitioned-how/

Re: Active connections are terminated because of small wal_sender_timeout

[Michael Paquier]

On Fri, Jul 05, 2019 at 10:03:16AM -0400, Tom Lane wrote:
> ayaho...@ibagroup.eu writes:
>> Do you have any thoughts regarding this issue? 
> 
> I do not think anybody thinks this is a bug.  Setting wal_sender_timeout
> too small is a configuration mistake.

Yeah.  I don't see any bug here.  Please note that it can be also a
problem to set up a too high value in some configuration setups.  The
lack of flexibility in this area is why wal_sender_timeout has been
switch to be user-settable in v12.  In short you can configure it in
the connection string to enforce a custom value per standby.
--
Michael


signature.asc
Description: PGP signature

Re: How to run a task continuously in the background

[Michael Nolan]

A cron job will only run once a minute, not wake up every second.  But you
could write a PHP program that does a one-second sleep before checking if
there's something to do, and a batch job that runs periodically to see if
the PHP program is running, and if not, launch it.

That's how I handle a job that opens a tunnel from our PostgreSQL server to
a MySQL server running at AWS so we can synchronize data between those two
database servers.  It dies periodically for reasons we've never figured
out, so every 5 minutes I check to make sure it's running.
--
Mike Nolan
no...@tssi.com

On Thu, Jul 11, 2019 at 5:44 AM Steven Winfield <
steven.winfi...@cantabcapital.com> wrote:

> pg_cron, perhaps?
>
> https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/
>
> I _think_ it ticks all three of your boxes.
>
>
> Steve.
>
>
>
> --
>
>
> *This email is confidential. If you are not the intended recipient, please
> advise us immediately and delete this message. The registered name of
> Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See -
> http://www.gam.com/en/Legal/Email+disclosures+EU
>  for further information
> on confidentiality, the risks of non-secure electronic communication, and
> certain disclosures which we are required to make in accordance with
> applicable legislation and regulations. If you cannot access this link,
> please notify us by reply message and we will send the contents to you.GAM
> Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and
> use information about you in the course of your interactions with us. Full
> details about the data types we collect and what we use this for and your
> related rights is set out in our online privacy policy at
> https://www.gam.com/en/legal/privacy-policy
> . Please familiarise yourself
> with this policy and check it from time to time for updates as it
> supplements this notice-- *
>

Re: Download link for postgres 10.8 edb software

[Michael Paquier]

On Fri, Jul 12, 2019 at 02:55:55AM +, Nanda Kumar wrote:
> Currently we are using postgres database 9.7 version.

You may be mistaken.  There is a major release of PostgreSQL called
9.6, but after that we have jumped directly to 10, reducing the number
of digits to mark a given minor version from 3 to 2.
--
Michael


signature.asc
Description: PGP signature

Re: Download link for postgres 10.8 edb software

[Michael Paquier]

On Fri, Jul 12, 2019 at 05:57:10AM +, Nanda Kumar wrote:
> Its my bad. Yes, currently  we are using 9.6 version. Now we are
> looking for 10.8 edb software for testing purpose . Kindly advise
> where we can download the 10.8 edb software ?

10.9 is the latest version in the 10.X series:
https://www.postgresql.org/download/windows/
--
Michael


signature.asc
Description: PGP signature

pg_stat_progress_vacuum comes up empty ...?

[Michael Harris]

Hello,

We have a database cluster which recently got very close to XID Wraparound. To 
get
it back under control I've been running a lot of aggressive manual vacuums.

However, I have noticed a few anomolies. When I try to check the status of 
vacuum commands:

qtodb_pmxtr=# select * from  pg_stat_progress_vacuum;
 pid | datid | datname | relid | phase | heap_blks_total | heap_blks_scanned | 
heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
-+---+-+---+---+-+---+++-+-
(0 rows)

Yet there definitely are plenty running:

qtodb_pmxtr=# select pid, state, current_timestamp-query_start as 
duration,query from pg_stat_activity where datname='qtodb_pmxtr' and 
query~'VACUUM' ;
  pid  | state  |duration | 
  query
---++-+---
 40615 | active | 13:46:35.081203 | autovacuum: VACUUM 
qn.mtrds_cnestmeas_oo_18032
 40617 | active | 00:46:35.270232 | autovacuum: VACUUM 
qn.mtrds_cantu100_oo_18046
 40622 | active | 00:00:04.55167  | autovacuum: VACUUM 
qn.mtrds_cbeekops_on_17684 (to prevent wraparound)
 25685 | active | 00:00:04.568989 | VACUUM FREEZE pg_toast.pg_toast_228072029;
 25686 | active | 00:00:02.716111 | VACUUM FREEZE pg_toast.pg_toast_228072943;
 25687 | active | 00:00:03.788131 | VACUUM FREEZE pg_toast.pg_toast_228069006;
 25688 | active | 00:00:02.531885 | VACUUM FREEZE pg_toast.pg_toast_228067023;
 25689 | active | 00:00:02.098389 | VACUUM FREEZE pg_toast.pg_toast_228071980;
 25690 | active | 00:00:00.621036 | VACUUM FREEZE pg_toast.pg_toast_228071852;
 25691 | active | 00:00:11.424717 | VACUUM FREEZE pg_toast.pg_toast_228069597;
 25692 | active | 00:00:03.359416 | VACUUM FREEZE pg_toast.pg_toast_228073892;
 25693 | active | 00:00:04.569248 | VACUUM FREEZE pg_toast.pg_toast_228068022;
 25694 | active | 00:00:20.151786 | VACUUM FREEZE pg_toast.pg_toast_228068878;
 25695 | active | 00:00:00.517688 | VACUUM FREEZE pg_toast.pg_toast_228068478;
 25696 | active | 00:00:23.746402 | VACUUM FREEZE pg_toast.pg_toast_228067431;
 25697 | active | 00:00:10.759025 | VACUUM FREEZE pg_toast.pg_toast_228072997;
 25698 | active | 00:00:14.281798 | VACUUM FREEZE pg_toast.pg_toast_228074613;
 25699 | active | 00:00:05.631052 | VACUUM FREEZE pg_toast.pg_toast_228074247;
 25700 | active | 00:00:00.056749 | VACUUM FREEZE pg_toast.pg_toast_228071681;
 28008 | active | 00:00:00| select pid, state, 
current_timestamp-query_start as duration,query from pg_stat_activity where 
datname='qtodb_pmxtr' and query~'VACUUM' ;
(20 rows)

Why don't any of these (manual OR auto) show up in the pg_stat_progress_vacuum?

Another concern: the normal autovacuums seem to be stalling. The table 
qn.mtrds_cnestmeas_oo_18032 should surely not take more than 13 hours to
vacuum, since it is only 160KB in size ...!

qtodb_pmxtr=# select 
pg_size_pretty(pg_relation_size('qn.mtrds_cnestmeas_oo_18032'::regclass));
 pg_size_pretty

 160 kB
(1 row)

We have autovacuum_cost_delay set to 0.

I also don't understand why only one autovac worker is working on the
wraparound issue, as there are thousands of tables with oldest xid > 
autovacuum_freeze_max_age.
I would have thought it would be prioritizing those.

I'm worried that something is wrong with autovacuum on this database, which 
might
be responsible for it getting into this state to begin with. Other similar 
databases we
have, running the same application and with similar configuration, are managing 
to
keep up with the xid freezing nicely.

The database was on 9.6, but was recently upgraded to 11.4.

Any advice welcome!

Cheers
Mike.

Re: Too slow to create new schema and their tables, functions, triggers.

[Michael Lewis]

On Tue, Jul 23, 2019 at 1:36 PM Adrian Klaver 
wrote:

> On 7/23/19 12:20 PM, PegoraroF10 wrote:
> > We have in a single database 190 identical schemas. Now, when we create
> a new
> > one, with exactly same structure as the previous ones, it takes 20 or 30
> > minutes to finish. Usual time to finish that script was 30 seconds.


 Can you create 200 schemas using your script without the data load step
and reproduce the issue? With 19,000 tables on that database, how
aggressive is autovacuum?

Re: How to check if a field exists in NEW in trigger

[Michael Lewis]

As a note to the original poster, you might want to check out-

https://www.postgresql.org/docs/current/citext.html

Re: question about client/server version mismatches

[Michael Paquier]

On Tue, Aug 13, 2019 at 12:45:35PM +1000, raf wrote:
> Since the backup itself was from a 9.5.12 server, it
> seems that the 9.6 parameter, idle_in_transaction_session_timeout,
> must have been set by the 9.6 client even though it was
> connected to a 9.5 server. Is that expected behaviour?

Yes, the compatibility of pg_dump is mentioned in the docs, and things
happen so as pg_dump can be used to transfer data to newer versions:
https://www.postgresql.org/docs/devel/app-pgdump.html
Please see from "Because pg_dump is used to transfer data to newer
versions of PostgreSQL...".

So doing a dump from a 9.5 instance using pg_dump from 9.6 would have
set the parameter.
--
Michael


signature.asc
Description: PGP signature

Re: Converting Access .mdb to postgres

[Michael Nolan]

A few years ago I tried to take an app someone had written for us in
Access  years ago and convert it to Postgres.

It seemed like for every rule I tried there were a handful of exceptions.

We wound up just rewriting the app and not trying to export the data from
the previous one.

I hope your project is more successful at extracting the data than ours was.
--
Mike Nolan

Re: slow queries on system tables

[Michael Lewis]

Are you autovacuuming and analyzing aggressively enough? Is there bloat or
other concern for these system tables? I expect it may be a concern based
on what I remember about your environment having thousands of tables and
perhaps autovacuum not keeping up (re: Too slow to create new schema and
their tables, functions, triggers).

Can you check results for last (auto)vacuum and analyze?
select * from pg_stat_all_tables where relname IN( 'pg_class',
'pg_attribute', 'pg_index' );

Re: Complex filters -> Bad row estimates -> bad query plan

[Michael Lewis]

If those conditions that are throwing off the stats are expected to be
minimally impactful/filtering few rows, then you can use the one
tried-and-true optimizer hint (aside from materialized CTEs, stylized
indexes, etc) --- OFFSET 0 at the end of a sub-query.

SELECT * FROM ( [your existing query without the sub-selects that are
complicated and produce bad estimates] OFFSET 0 ) WHERE [your other
conditions that don't produce good estimates]

If there is correlation between field1 and field2, you might also look at
CREATE STATISTICS assuming you are on PG 10 or 11.

Before I do any of that, I would try LEFT JOIN for Table3 and Table4 then
use the where conditon  "AND 2 = COALESCE( Table3.Status, Table4.Status"
and see if the optimizer likes that option better.

Re: Complex filters -> Bad row estimates -> bad query plan

[Michael Lewis]

-- I'm thinking the OFFSET 0 create an optimization barrier that prevents
the planner from collapsing that sub-query into the top query, and enforces
ordering in the query?

That's my understanding. I think it is an optimizer hint by another name. I
used to put things in a CTE (which is always materialized until v12, which
will change it to inlined unless keyword MATERIALIZED is included) or I
would create a temp table if the dataset is expected to contain many rows
such that I can do ANALYZE pg_temp.table_table; so the optimizer has stats
to make good decisions.

Note- Replying to messages with a full quote of the conversation below your
comment (aka top-posting) is discouraged on these mailing lists. Please
quote the portion you are responding to and that's it.

Support for using alias in having clause

[Michael Lewis]

Why can I use an alias for group by and order by and not for having? I am
just wondering if there is some barrier to it being implemented, or if it
just hasn't been.

select
table_schema || '.' || table_name AS schema_qualified_table_name,
count( column_name ) as column_count
from
information_schema.columns
group by
schema_qualified_table_name
having
count( column_name ) > 50 /* this works, but trying column_count > 50 does
not */
order by
column_count desc;

Re: How to use brin_summarize_range

[Michael Lewis]

What is default_statistics_target set to? Or is there custom statistics
value set for this table/column? Perhaps the planner is making sub-optimal
choices because it only has a vague idea about the data and the histogram
is not very informative. Planning time will increase when statistics target
is increased, but perhaps 250 instead of default 100 would give *enough*
more information to make better choices. Or perhaps your target is already
max 1 and then I have no idea why btree would be chosen. Except, if
correlation is too low for the optimizer to consider BRIN to be best
perhaps. What does pg_stats say about the column in question?

>

Re: slow queries on system tables

[Michael Lewis]

Might the issue be with pg_index or pg_attribute rather than pg_class? Is
the query still slow? Do you have the output of explain analyze for use on
https://explain.depesz.com/ or the output of EXPLAIN (ANALYZE, COSTS,
VERBOSE, BUFFERS, FORMAT JSON) for use on
http://tatiyants.com/pev/#/plans/new website?

Re: slow queries on system tables

[Michael Lewis]

Can you manually execute vacuum analyze on all three involved tables and
again share the plan for the same query? If it is significantly improved,
it would seem like following the recommendation to tune autovacuum (and
analyze) to be more frequent would be prudent.

You haven't seemed to change from default vacuum/analyze settings despite
the default parameters being suggested only for bare minimum hardware and
very light use databases.

Re: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

[Michael Lewis]

On Tue, Aug 27, 2019 at 9:45 PM Laurenz Albe 
wrote:

> Holtgrewe, Manuel wrote:
> > Switching off fsync leads to a drastic time improvement but still
> > higher wall-clock time for four threads.
>
> Don't do that unless you are ready to start from scratch with a new
> "initdb" in the case of a crash.
>
> You can do almost as good by setting "synchronous_commit = off",
> and that is crash-safe.


It seems like it depends on your definition of crash-safe. Data loss can
occur but not data corruption, right? Do you know any ballpark for how much
difference in performance it makes to turn off synchronous_commit or what
type of hardware or usage it would make the biggest (or least) difference?

Re: Query using 'LIKE' returns empty set

[Michael Lewis]

You need the wildcard character at front and back.

select * from fish_counts where stream_tribs ilike '*%*Nehalem*%*';

Re: Bad Estimate for multi tenant database queries

[Michael Lewis]

>
> CREATE STATISTICS MT_ReservationDepartureUtc (ndistinct) on "AccountCode",
> "DepartureUtc" from "Reservation"."Reservation";
> CREATE STATISTICS MT_ReservationArrivalUtc (ndistinct) on "AccountCode",
> "ArrivalUtc" from "Reservation"."Reservation";
> CREATE STATISTICS MT_ReservationNoShowFeeId (ndistinct) on "AccountCode",
> "NoShowFeeId" from "Reservation"."Reservation";
> CREATE STATISTICS MT_ReservationTimeSliceDefinitionId (ndistinct) on
> "AccountCode", "TimeSliceDefinitionId" from "Reservation"."Reservation";
>
> but that didn't help either
>

Did you try with 'dependencies' for the statistics_kind or only ndistinct?
What was default_statistics_target set to at the time you created the
extended statistics? I am not sure if that value is used, but I would
assume so.

Re: literal vs dynamic partition constraint in plan execution

[Michael Lewis]

I think I would expect this behavior with how you have defined the
constraints as the function results rather than just being strict
comparisons to the timestamp field.

Instead of this-
Check constraints:
> "y2018_mis_ora_check" CHECK (date_part('year'::text, mis_ora) =
> 2018::double precision)

I would expect this-
Check constraints:
> "y2018_mis_ora_check" CHECK mis_ora) >= make_timestamp(2018, 1,
> 1, 0, 0, 0::double precision) AND < make_timestamp(2019, 1,
> 1, 0, 0, 0::double precision)