Re: wal_level not changing

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Tue, 5 Sep 2017 10:38:43 +0300



    On 04/09/2017 18:58, Sumeet Shukla
      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.
          

    Just check the value of your current wal_level 

    cat /data/9.6/data/postgresql.conf /data/9.6/data/postgresql.auto.conf |
    egrep -v '^#|^$' | grep wal_level | tail -1

    and your max_wal_senders

    cat /data/9.6/data/postgresql.conf /data/9.6/data/postgresql.auto.conf |
    egrep -v '^#|^$' | grep max_wal_senders | tail -1

    Make sure this as suggested.

    Then, for the moment try to start pgsql as :

    /opt/postgresql/9.6/bin/pg_ctl start -w -D
      /data/9.6/data

    echo $?

    ^^^ what does it return? How does your stderr say?

    
                  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
                                              

    -- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt