Thread: PG11.2 - wal_level =minimal max_wal_senders = 0

PG11.2 - wal_level =minimal max_wal_senders = 0

From
Jason Ralph
Date:

Hello,

I have recently upgraded 10 Postgres databases to 11.2, these databases are used for data warehousing / ETL.  These are stand alone systems, no replication. They perform a lot of inserts, drop tables and create tables etc…

I have been working on tuning the databases, I am seeing the following in the log:

2019-09-03 10:40:21.660 EDT [5635] LOG:  checkpoints are occurring too frequently (13 seconds apart)

2019-09-03 10:40:21.660 EDT [5635] HINT:  Consider increasing the configuration parameter "max_wal_size".

 

My first thought was to increase the max_wal_size which may take care of the logged message.  Then I looked at the wal_level setting.  I see that it defaults to replica out of the box and max_wal_senders = 10.

postgres=# show wal_level;

wal_level

-----------

replica

(1 row)

postgres=# show max_wal_senders;

max_wal_senders

-----------------

10

(1 row)

 

The question I have is, for a production database not doing replication, can I safely set the following parameters, I understand that minimal will also disable wal_archiving so I am concerned about that as well.

wal_level = minimal

max_wal_senders = 0

 

I have been looking at the documentation and I guess I am looking for a yes this is ok, I figured it’s on by default for a reason so I was hesitant to change it.

 

 

 

 

Jason Ralph

 

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.

Re: PG11.2 - wal_level =minimal max_wal_senders = 0

From
Luca Ferrari
Date:
On Wed, Sep 4, 2019 at 5:15 PM Jason Ralph <jralph@affinitysolutions.com> wrote:
> The question I have is, for a production database not doing replication, can I safely set the following parameters, I
understandthat minimal will also disable wal_archiving so I am concerned about that as well. 
>
> wal_level = minimal
>
> max_wal_senders = 0
>
>
>
> I have been looking at the documentation and I guess I am looking for a yes this is ok, I figured it’s on by default
fora reason so I was hesitant to change it. 

I don't see why you should not set them as you described.
The idea is that the overhead of having replica over minimal is today
enough "low" in contrast to the need for replication (e.g., backup
tools like pgbackrest).

Luca



RE: PG11.2 - wal_level =minimal max_wal_senders = 0

From
Jason Ralph
Date:
Thank you Luca,
Can I ask one more related question, I have no need for replication as noted below, can I safely disable the worker
processvia the setting below? In my sandbox it does turn off the logical replication launcher, I just wanted to be sure
Iam not affecting anything other than replication with this setting.
 


max_logical_replication_workers = 0     # taken from max_worker_processes
                                        # (change requires restart)


Jason Ralph

-----Original Message-----
From: Luca Ferrari <fluca1978@gmail.com>
Sent: Wednesday, September 4, 2019 12:16 PM
To: Jason Ralph <jralph@affinitysolutions.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: PG11.2 - wal_level =minimal max_wal_senders = 0

On Wed, Sep 4, 2019 at 5:15 PM Jason Ralph <jralph@affinitysolutions.com> wrote:
> The question I have is, for a production database not doing replication, can I safely set the following parameters, I
understandthat minimal will also disable wal_archiving so I am concerned about that as well.
 
>
> wal_level = minimal
>
> max_wal_senders = 0
>
>
>
> I have been looking at the documentation and I guess I am looking for a yes this is ok, I figured it’s on by default
fora reason so I was hesitant to change it.
 

I don't see why you should not set them as you described.
The idea is that the overhead of having replica over minimal is today enough "low" in contrast to the need for
replication(e.g., backup tools like pgbackrest).
 

Luca
This message contains confidential information and is intended only for the individual named. If you are not the named
addresseeyou should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if
youhave received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be
guaranteedto be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete,or contain viruses. The sender therefore does not accept liability for any errors or omissions in the
contentsof this message, which arise as a result of e-mail transmission. If verification is required please request a
hard-copyversion.
 

Re: PG11.2 - wal_level =minimal max_wal_senders = 0

From
Luca Ferrari
Date:
On Wed, Sep 4, 2019 at 10:44 PM Jason Ralph
<jralph@affinitysolutions.com> wrote:
>
> Thank you Luca,
> Can I ask one more related question, I have no need for replication as noted below, can I safely disable the worker
processvia the setting below? In my sandbox it does turn off the logical replication launcher, I just wanted to be sure
Iam not affecting anything other than replication with this setting. 
>
>
> max_logical_replication_workers = 0     # taken from max_worker_processes
>                                         # (change requires restart)
>

Quite frankly I've never done, but I don't see why you should not turn it off.

Luca



Re: PG11.2 - wal_level =minimal max_wal_senders = 0

From
Benoit Lobréau
Date:
Hi,

Make sure that the new settings of wal_level and max_wal_senders don't interfere with your backup strategy.
The two parameters have an impact on wal archiving as well.

Ben.

Le jeu. 5 sept. 2019 à 08:33, Luca Ferrari <fluca1978@gmail.com> a écrit :
On Wed, Sep 4, 2019 at 10:44 PM Jason Ralph
<jralph@affinitysolutions.com> wrote:
>
> Thank you Luca,
> Can I ask one more related question, I have no need for replication as noted below, can I safely disable the worker process via the setting below? In my sandbox it does turn off the logical replication launcher, I just wanted to be sure I am not affecting anything other than replication with this setting.
>
>
> max_logical_replication_workers = 0     # taken from max_worker_processes
>                                         # (change requires restart)
>

Quite frankly I've never done, but I don't see why you should not turn it off.

Luca


Re: PG11.2 - wal_level =minimal max_wal_senders = 0

From
Jason Ralph
Date:
Thanks,
I currently have the systems running a parallel pg_dump each night to a separate partition mounted on the VM. Then I perform a full backup of the VM and all mounted drives each night. Would this be affected by disabling wal archiving? I noted that I understood wal archiving was affected in my initial question. But I was not able to see how it could affect my setup. Please school me if I am being naive.

Jason Ralph

From: Benoit Lobréau <benoit.lobreau@gmail.com>
Sent: Friday, September 6, 2019 3:41:44 AM
To: Luca Ferrari <fluca1978@gmail.com>
Cc: Jason Ralph <jralph@affinitysolutions.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: PG11.2 - wal_level =minimal max_wal_senders = 0
 
Hi,

Make sure that the new settings of wal_level and max_wal_senders don't interfere with your backup strategy.
The two parameters have an impact on wal archiving as well.

Ben.

Le jeu. 5 sept. 2019 à 08:33, Luca Ferrari <fluca1978@gmail.com> a écrit :
On Wed, Sep 4, 2019 at 10:44 PM Jason Ralph
<jralph@affinitysolutions.com> wrote:
>
> Thank you Luca,
> Can I ask one more related question, I have no need for replication as noted below, can I safely disable the worker process via the setting below? In my sandbox it does turn off the logical replication launcher, I just wanted to be sure I am not affecting anything other than replication with this setting.
>
>
> max_logical_replication_workers = 0     # taken from max_worker_processes
>                                         # (change requires restart)
>

Quite frankly I've never done, but I don't see why you should not turn it off.

Luca


This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.

Re: PG11.2 - wal_level =minimal max_wal_senders = 0

From
Luca Ferrari
Date:
On Sat, Sep 7, 2019 at 2:15 AM Jason Ralph <jralph@affinitysolutions.com> wrote:
> I currently have the systems running a parallel pg_dump each night to a separate partition mounted on the VM. Then I
performa full backup of the VM and all mounted drives each night. Would this be affected by disabling wal archiving? I
notedthat I understood wal archiving was affected in my initial question. But I was not able to see how it could affect
mysetup. Please school me if I am being naive. 

No, if you are using pg_dump you are not affected by archiving.
Archiving is used to backup with tools like pgbackrest, barman, wal-e
and alike. If you are running your database isolated (i.e., no slaves,
no advanced backups), running it without replication level in wal and
using only pg_dump is fine.

Luca