Re: wal_level not changing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I just tried it myself on my local db to make sure i didn't forget anything and it worked. 
What does your postgresql log say ? it should be ( assuming you are using default log_directory) in /data/9.6/data/pg_log/


On 4 Sep 2017, at 19:25, Sumeet Shukla <sumeet.k.shukla@xxxxxxxxx> wrote:

archive_mode is already off but it doesn't seem to work at all..
and I'm unable to change max_wal_senders to 0; encountering same error..



Thanks & Regards,
Sumeet Shukla
Ph. No. 962 323 4700

On Mon, Sep 4, 2017 at 9:46 PM, Vasilis Ventirozos <v.ventirozos@xxxxxxxxx> wrote:
you should have postgres archiving enabled , can you edit /data/9.6/data/postgresql.conf making sure that :
archive_mode = off
and
max_wal_senders = 0

otherwise you will be getting errors like :

FATAL:  WAL archival cannot be enabled when wal_level is "minimal"
and 
FATAL:  WAL streaming (max_wal_senders > 0) requires wal_level "replica" or "logical"

Regards, 
Vasilis Ventirozos


On 4 Sep 2017, at 19:08, Sumeet Shukla <sumeet.k.shukla@xxxxxxxxx> wrote:

I restarted the service using systemctl and now the status shows green but the problem persists.

Thanks & Regards,
Sumeet Shukla


On Mon, Sep 4, 2017 at 9:28 PM, Sumeet Shukla <sumeet.k.shukla@xxxxxxxxx> wrote:
Tried but does not work.

I see below output when I check the status of the service.

systemctl status postgresql-9.6

postgresql-9.6.service - PostgreSQL 9.6 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)

   Active: failed (Result: exit-code) since Mon 2017-09-04 11:25:42 EDT; 27min ago

  Process: 28093 ExecStop=/opt/postgresql/9.6/bin/pg_ctl stop -m fast -w -D /data/9.6/data (code=exited, status=1/FAILURE)

  Process: 28052 ExecStart=/opt/postgresql/9.6/bin/pg_ctl start -w -t ${TimeoutSec} -D /data/9.6/data -l /data/9.6/data/pg_log/startup.log (code=exited, status=0/SUCCESS)

 Main PID: 28054 (code=exited, status=0/SUCCESS)


Sep 04 11:25:36 ashprmlpdb-p05 systemd[1]: Starting PostgreSQL 9.6 database server...

Sep 04 11:25:36 ashprmlpdb-p05 systemd[1]: Started PostgreSQL 9.6 database server.

Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: postgresql-9.6.service: control process exited, code=exited status=1

Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: Unit postgresql-9.6.service entered failed state.

Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: postgresql-9.6.service failed.


Thanks & Regards,
Sumeet Shukla


On Mon, Sep 4, 2017 at 9:20 PM, Vasilis Ventirozos <v.ventirozos@xxxxxxxxx> wrote:
try ALTER SYSTEM  set wal_level = 'minimal';
and then restart , just make sure that archiving is disabled first.

Vasilis Ventirozos


On 4 Sep 2017, at 18:42, Sumeet Shukla <sumeet.k.shukla@xxxxxxxxx> wrote:


Hi,

update pg_settings set reset_val='minimal' where name='wal_level';

ERROR:  parameter "wal_level" cannot be changed without restarting the server

 

set wal_level='minimal';

ERROR:  parameter "wal_level" cannot be changed without restarting the server


update pg_settings set setting='minimal' where name='wal_level';


ERROR:  parameter "wal_level" cannot be changed without restarting the server


Even after restarting and reloading the server multiple times the setting does not update and remains as below:

show wal_level;

+-----------+

| wal_level |

+-----------+

| replica   |


+-----------+



Thanks & Regards,
Sumeet Shukla








[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux