Re: pg_basebackup: could not receive data from WAL stream

[Kaixi Luo]

wal_sender_timeout should be as long as necessary. Each wal file is 16MB,
so it should be *at least* as long as the time needed to transfer
16MB*wal_keep_segments. Take a look at the size of your pg_xlog folder.

On Sun, Sep 2, 2018 at 3:41 PM Adrian Klaver 
wrote:

> On 09/01/2018 09:06 PM, greigwise wrote:
> > Hello.
> >
> > On postgresql 10.5, my pg_basebackup is failing with this error:
> >
> > pg_basebackup: could not receive data from WAL stream: server closed the
> > connection unexpectedly
> > This probably means the server terminated abnormally
> > before or while processing the request
> >
> > In the postgres log files, I'm seeing:
> >
> > 2018-09-02 00:57:32 UTC bkp_user 5b8b278c.11c3f [unknown] LOG:
> terminating
> > walsender process due to replication timeout
> >
> > I'm running the following command right on the database server itself:
> >
> > pg_basebackup -U repl -D /var/tmp/pg_basebackup_20180901 -Ft -z
> >
> > It seems to be an intermittent problem.. I've had it fail or succeed
> about
> > 50/50.  I even bumped up the wal_sender_timeout to 2000.  One notable
> thing
> > is that I'm running on an ec2 instance on AWS.
>
> The unit for wal_sender_timeout is ms so the above is 2 seconds whereas
> the default value is 60 seconds(60s in postgresql.conf file).
>
> See below for setting units in file:
>
> https://www.postgresql.org/docs/10/static/config-setting.html
>
> Also what is your max_wal_senders setting?
>
> >
> > Any advice would be helpful.
> >
> > Greig Wise
> >
> >
> >
> > --
> > Sent from:
> http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Matview size - space increased on concurrently refresh

[Kaixi Luo]

On Fri, Jul 12, 2019 at 4:34 PM Nicola Contu  wrote:

> P.S.: I am on postgres 11.3
>
> Il giorno ven 12 lug 2019 alle ore 16:32 Nicola Contu <
> nicola.co...@gmail.com> ha scritto:
>
>> Hello,
>> we noticed with a simple matview we have that refreshing it using the
>> concurrently item the space always increases of about 120MB .
>> This only happens if I am reading from that matview and at the same time
>> I am am refreshing it.
>>
>> cmdv3=# SELECT
>> pg_size_pretty(pg_relation_size('public.matview_nm_connections'::regclass));
>> pg_size_pretty
>> 
>> 133 MB
>> (1 row)
>>
>> cmdv3=# refresh materialized view matview_nm_connections;
>> REFRESH MATERIALIZED VIEW
>> cmdv3=# SELECT
>> pg_size_pretty(pg_relation_size('public.matview_nm_connections'::regclass));
>> pg_size_pretty
>> 
>> 133 MB
>> (1 row)
>>
>> cmdv3=# \! date
>> Fri Jul 12 13:52:51 GMT 2019
>>
>> cmdv3=# refresh materialized view matview_nm_connections;
>> REFRESH MATERIALIZED VIEW
>> cmdv3=# SELECT
>> pg_size_pretty(pg_relation_size('public.matview_nm_connections'::regclass));
>> pg_size_pretty
>> 
>> 133 MB
>> (1 row)
>>
>>
>> Let's try concurrently.
>>
>> cmdv3=# refresh materialized view CONCURRENTLY matview_nm_connections;
>> REFRESH MATERIALIZED VIEW
>> cmdv3=# SELECT
>> pg_size_pretty(pg_relation_size('public.matview_nm_connections'::regclass));
>> pg_size_pretty
>> 
>> 261 MB
>> (1 row)
>>
>>
>> So the matview is not really used and it does not have anything strange
>> but that matview growth to 12GB as we refresh it once an hour.
>> It had the free percent at 97%.
>> I understand with concurrenlty it needs to take copy of the data while
>> reading, but this seems to be too much on the space side.
>>
>> Is this a bug? Or is there anyone can help us understanding this?
>>
>> Thanks a lot,
>> Nicola
>>
>
This is normal and something to be expected. When refreshing the
materialized view, the new data is written to a disk and then the two
tables are diffed. After the refresh finishes, your view size should go
back to normal.