Thread: Unable to reload postgresql.conf without restarting

Unable to reload postgresql.conf without restarting

From
Jose Martinez
Date:
Hi,

I made some changes to postgresql.conf and I want them to take effect without having to restart the server.

I tried 

select pg_reload_conf();
/usr/pgsql-9.1/bin/pg_ctl reload

but when I do 'show all', I see no changes take effect. 

There settings I tried to change are:
-effective_cache_size 
-work_mem

Im using posgres 9.1 on Centos Linux (amazon ec2)

Thanks

Re: Unable to reload postgresql.conf without restarting

From
Bruce Momjian
Date:
On Thu, Jan  3, 2013 at 11:25:41AM -0500, Jose Martinez wrote:
> Hi,
>
> I made some changes to postgresql.conf and I want them to take effect without
> having to restart the server.
>
> I tried
>
> select pg_reload_conf();
> /usr/pgsql-9.1/bin/pg_ctl reload
>
> but when I do 'show all', I see no changes take effect.
>
> There settings I tried to change are:
> -effective_cache_size
> -work_mem
>
> Im using posgres 9.1 on Centos Linux (amazon ec2)

That is quite odd.  Can you show us the commands and the postgresql.conf
line you are changing?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


Re: Unable to reload postgresql.conf without restarting

From
Matheus de Oliveira
Date:

On Thu, Jan 3, 2013 at 2:25 PM, Jose Martinez <jmartinez@opencrowd.com> wrote:
Hi,

I made some changes to postgresql.conf and I want them to take effect without having to restart the server.

I tried 

select pg_reload_conf();
/usr/pgsql-9.1/bin/pg_ctl reload

but when I do 'show all', I see no changes take effect. 

There settings I tried to change are:
-effective_cache_size 
-work_mem


They should have been reloaded. Have you set them by role, database or tablespace?
Or even, has them been set, by mistake, twice at postgresql.conf? If so, the last one
will be used.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados PostgreSQL
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Re: [GENERAL] Unable to reload postgresql.conf without restarting

From
sk baji
Date:
> Hi,
>
> I made some changes to postgresql.conf and I want them to take effect without
> having to restart the server.
>
> I tried
>
> select pg_reload_conf();
> /usr/pgsql-9.1/bin/pg_ctl reload
>
> but when I do 'show all', I see no changes take effect.
>
> There settings I tried to change are:
> -effective_cache_size
> -work_mem
>
> Im using posgres 9.1 on Centos Linux (amazon ec2)

I think, these parameters are duplicated in postgresql.conf file. Can you check whether the same parameters are exits in bottom of the file. If so, you need to change at the bottom(or comment them).


Regards,
Baji Shaik.



On Thu, Jan 3, 2013 at 5:17 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Thu, Jan  3, 2013 at 11:25:41AM -0500, Jose Martinez wrote:
> Hi,
>
> I made some changes to postgresql.conf and I want them to take effect without
> having to restart the server.
>
> I tried
>
> select pg_reload_conf();
> /usr/pgsql-9.1/bin/pg_ctl reload
>
> but when I do 'show all', I see no changes take effect.
>
> There settings I tried to change are:
> -effective_cache_size
> -work_mem
>
> Im using posgres 9.1 on Centos Linux (amazon ec2)

That is quite odd.  Can you show us the commands and the postgresql.conf
line you are changing?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Unable to reload postgresql.conf without restarting

From
Scott Ribe
Date:
On Jan 3, 2013, at 10:30 AM, Matheus de Oliveira wrote:

> They should have been reloaded. Have you set them by role, database or tablespace?
> Or even, has them been set, by mistake, twice at postgresql.conf? If so, the last one
> will be used.

Also make sure you're changing the right postgresql.conf, that the server wasn't started with a different one than you
think.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice






Re: Unable to reload postgresql.conf without restarting

From
Matheus de Oliveira
Date:


On Thu, Jan 3, 2013 at 4:02 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
On Jan 3, 2013, at 10:30 AM, Matheus de Oliveira wrote:

> They should have been reloaded. Have you set them by role, database or tablespace?
> Or even, has them been set, by mistake, twice at postgresql.conf? If so, the last one
> will be used.

Also make sure you're changing the right postgresql.conf, that the server wasn't started with a different one than you think.


Yep. The config_file parameter can be used to check it:

SHOW config_file;

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados PostgreSQL
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Re: Unable to reload postgresql.conf without restarting

From
Jerry Sievers
Date:
Jose Martinez <jmartinez@opencrowd.com> writes:

> Hi,
>
> I made some changes to?postgresql.conf and I want them to take effect without having to restart the server.

Check your server log for any report of a syntax error in your .conf
file.

If there is one, that will prevent the changes being loaded.

pg_reload_conf() will still return 't' and you'll be unaware of the
problem

> I tried?
>
> select pg_reload_conf();
> /usr/pgsql-9.1/bin/pg_ctl reload
>
> but when I do 'show all', I see no changes take effect.?
>
> There settings I tried to change are:
> -effective_cache_size?
> -work_mem
>
> Im using posgres 9.1 on Centos Linux (amazon ec2)
>
> Thanks
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


Re: [GENERAL] Unable to reload postgresql.conf without restarting

From
Amit Kapila
Date:
On Thursday, January 03, 2013 9:56 PM Jose Martinez wrote:
>Hi,

>I made some changes to postgresql.conf and I want them to take effect
without having to restart the server.

>I tried 

>select pg_reload_conf();
>/usr/pgsql-9.1/bin/pg_ctl reload

>but when I do 'show all', I see no changes take effect. 

>There settings I tried to change are:
> -effective_cache_size 
> -work_mem

Tried in latest HEAD code(Windows OS) and its working, after select
pg_reload_conf(), the changed value is shown.
Can you try just with SET command and verify the behavior, as these
parameters are USERSET parameters,
so no need to change config file and reload or restart.

With Regards,
Amit Kapila.