Thread: very slow postgresql startup

very slow postgresql startup

From
zz_11@mail.bg
Date:

Hi,


After stopping my pg server with pg_ctl -m immediate stop options it take hours to startup.

With vmstat I find hi pcu and io useages.

Any idea about this problem? I think it can be pg_xlog files.

How can I debug or trace this problem?

pg version is 9.4.5 running on centos 6.

Many thanks in advance.




-------------------------------------
ПП: Ако имаш идеи за сайт, виж какво предлагат СуперХостинг.БГ. Тази пролет дават -75% отстъпка за нов хостинг план.

Re: very slow postgresql startup

From
Venkata Balaji N
Date:

After stopping my pg server with pg_ctl -m immediate stop options it take hours to startup.

With vmstat I find hi pcu and io useages.

Any idea about this problem? I think it can be pg_xlog files.

How can I debug or trace this problem?

Do you see anything suspicious in the postgresql logfiles ? That can give you a fair idea on what could be the issue.

Regards,
Venkata B N

Fujitsu Australia

Re: very slow postgresql startup

From
Magnus Hagander
Date:


On May 23, 2016 10:35, <zz_11@mail.bg> wrote:
>
> Hi,
>
>
> After stopping my pg server with pg_ctl -m immediate stop options it take hours to startup.
>
> With vmstat I find hi pcu and io useages.
>
> Any idea about this problem? I think it can be pg_xlog files.
>
> How can I debug or trace this problem?
>
> pg version is 9.4.5 running on centos 6.

This is not uncommon when you shutdown with immediate. It's the main reason you should normally use fast and not immediate.

And yes, it's likely the replay of xlog. Hours is a very long time though, but with very high checkpoint timeout that's certainly possible.

/Magnus

Re: very slow postgresql startup

From
zz_11@mail.bg
Date:

Hi,

Thank you.


Any idea how to view the progress?


Regards,

Ivan.

----- Цитат от Magnus Hagander (magnus@hagander.net), на 23.05.2016 в 11:28 -----


On May 23, 2016 10:35, <zz_11@mail.bg> wrote:
>
> Hi,
>
>
> After stopping my pg server with pg_ctl -m immediate stop options it take hours to startup.
>
> With vmstat I find hi pcu and io useages.
>
> Any idea about this problem? I think it can be pg_xlog files.
>
> How can I debug or trace this problem?
>
> pg version is 9.4.5 running on centos 6.

This is not uncommon when you shutdown with immediate. It's the main reason you should normally use fast and not immediate.

And yes, it's likely the replay of xlog. Hours is a very long time though, but with very high checkpoint timeout that's certainly possible.

/Magnus



-------------------------------------
ПП: Видя ли специалните предложения на СуперХостинг.БГ? Ако имаш идеи за сайт, това е правилното място!

Re: very slow postgresql startup

From
zz_11@mail.bg
Date:

Hi all,


after some investigation I found in pg database directory many files starting with "t". For exmple:

-rw------- 1 postgres postgres       8192 May 25 11:13 t55_16771477
-rw------- 1 postgres postgres       8192 May 25 08:12 t55_16771482
-rw------- 1 postgres postgres       8192 May 25 09:07 t55_16771614
-rw------- 1 postgres postgres       8192 May 25 08:13 t55_16771632
-rw------- 1 postgres postgres      32768 May 25 07:05 t55_16772120
-rw------- 1 postgres postgres      24576 May 25 09:04 t55_16772120_fsm
-rw------- 1 postgres postgres       8192 May 25 12:11 t55_16772129
-rw------- 1 postgres postgres          0 May 25 08:40 t55_16772131
-rw------- 1 postgres postgres          0 May 25 12:09 t55_16772285
-rw------- 1 postgres postgres          0 May 25 09:44 t55_16772293

Can this one be the reason for slow pg start?


Many thanks in advance,

Ivan.


----- Цитат от zz_11@mail.bg, на 23.05.2016 в 10:33 -----

Hi,


After stopping my pg server with pg_ctl -m immediate stop options it take hours to startup.

With vmstat I find hi pcu and io useages.

Any idea about this problem? I think it can be pg_xlog files.

How can I debug or trace this problem?

pg version is 9.4.5 running on centos 6.

Many thanks in advance.




-------------------------------------
ПП: Ако имаш идеи за сайт, виж какво предлагат СуперХостинг.БГ. Тази пролет дават -75% отстъпка за нов хостинг план.



-------------------------------------
ПП: Видя ли специалните предложения на СуперХостинг.БГ? Ако имаш идеи за сайт, това е правилното място!

Re: very slow postgresql startup

From
"Vicky Soni - Quipment India"
Date:

Hi,

 

Can you please change log_min_message to debug2 and start service.

 

Check what postgresql is doing at start which takes time.

 

Also share some important lines from log file.

 

 

                                                                                             

 

Thanks & Regards,

Vicky Soni
Database Administrator

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of zz_11@mail.bg
Sent: 25 May 2016 16:32
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] very slow postgresql startup

 

Hi all,

 

after some investigation I found in pg database directory many files starting with "t". For exmple:

-rw------- 1 postgres postgres       8192 May 25 11:13 t55_16771477
-rw------- 1 postgres postgres       8192 May 25 08:12 t55_16771482
-rw------- 1 postgres postgres       8192 May 25 09:07 t55_16771614
-rw------- 1 postgres postgres       8192 May 25 08:13 t55_16771632
-rw------- 1 postgres postgres      32768 May 25 07:05 t55_16772120
-rw------- 1 postgres postgres      24576 May 25 09:04 t55_16772120_fsm
-rw------- 1 postgres postgres       8192 May 25 12:11 t55_16772129
-rw------- 1 postgres postgres          0 May 25 08:40 t55_16772131
-rw------- 1 postgres postgres          0 May 25 12:09 t55_16772285
-rw------- 1 postgres postgres          0 May 25 09:44 t55_16772293

Can this one be the reason for slow pg start?

 

Many thanks in advance,

Ivan.

 

----- Цитат от zz_11@mail.bg, на 23.05.2016 в 10:33 -----

Hi,

 

After stopping my pg server with pg_ctl -m immediate stop options it take hours to startup.

With vmstat I find hi pcu and io useages.

Any idea about this problem? I think it can be pg_xlog files.

How can I debug or trace this problem?

pg version is 9.4.5 running on centos 6.

Many thanks in advance.

 



-------------------------------------
ПП: Ако имаш идеи за сайт, виж какво предлагат СуперХостинг.БГ. Тази пролет дават -75% отстъпка за нов хостинг план.



-------------------------------------
ПП: Видя ли специалните предложения на СуперХостинг.БГ? Ако имаш идеи за сайт, това е правилното място!

Re: very slow postgresql startup

From
Alex Ignatov
Date:

On 23.05.2016 11:33, zz_11@mail.bg wrote:

Hi,


After stopping my pg server with pg_ctl -m immediate stop options it take hours to startup.

With vmstat I find hi pcu and io useages.

Any idea about this problem? I think it can be pg_xlog files.

How can I debug or trace this problem?

pg version is 9.4.5 running on centos 6.

Many thanks in advance.




-------------------------------------
ПП: Ако имаш идеи за сайт, виж какво предлагат СуперХостинг.БГ. Тази пролет дават -75% отстъпка за нов хостинг план.


Hi!
What do you see in strace output? I think that pg falls in unlinking temp file from previous incarnation. Check this out or show us strace output.
Thank you in advance.


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


[ADMIN] postgres account disabled . version 9.2

From
koffi BADOH
Date:
Hi all,

I'm in a big trouble.

I'm running postgreSQL 9.2.
For migration reason  I disabled  all postgres user account login .
Unfortunatly That's mean  the  postgres admin  account cannot also connect.
Now I'm trying to enable all account , Then I got error


" psql fatal role postgres is not permitted to log in"

    help please
koff




Re: [ADMIN] postgres account disabled . version 9.2

From
Achilleas Mantzios
Date:
On 14/12/2016 16:16, koffi BADOH wrote:
> Hi all,
>
> I'm in a big trouble.
>
> I'm running postgreSQL 9.2.
> For migration reason  I disabled  all postgres user account login .
> Unfortunatly That's mean  the  postgres admin  account cannot also connect.
> Now I'm trying to enable all account , Then I got error
>
>
> " psql fatal role postgres is not permitted to log in"

start it standalone

>
>      help please
> koff
>
>
>
>


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



Re: [ADMIN] postgres account disabled . version 9.2

From
Achilleas Mantzios
Date:
On 14/12/2016 16:28, koffi BADOH wrote:
> PostgreSql is already started.
>
> I cannot connect    as postgres account

stop it via sending a SIGTERM, SIGINT, or SIGQUIT signal

kill -TERM <pgsql_pid>

>
> Le 14/12/2016 à 15:21, Achilleas Mantzios a écrit :
>> On 14/12/2016 16:16, koffi BADOH wrote:
>>> Hi all,
>>>
>>> I'm in a big trouble.
>>>
>>> I'm running postgreSQL 9.2.
>>> For migration reason  I disabled  all postgres user account login .
>>> Unfortunatly That's mean  the  postgres admin  account cannot also
>>> connect.
>>> Now I'm trying to enable all account , Then I got error
>>>
>>>
>>> " psql fatal role postgres is not permitted to log in"
>> start it standalone
>>
>>>       help please
>>> koff
>>>
>>>
>>>
>>>
>>


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



Re: [ADMIN] postgres account disabled . version 9.2

From
Achilleas Mantzios
Date:
On 14/12/2016 17:02, Achilleas Mantzios wrote:
> On 14/12/2016 16:28, koffi BADOH wrote:
>> PostgreSql is already started.
>>
>> I cannot connect    as postgres account
>
> stop it via sending a SIGTERM, SIGINT, or SIGQUIT signal
>
> kill -TERM <pgsql_pid>
>

Then do :

postgres --single -D <your data cluster> <your db name>

backend> ALTER ROLE postgres WITH LOGIN;

or any command that would revert the effects previous command that you gave.



>>
>> Le 14/12/2016 à 15:21, Achilleas Mantzios a écrit :
>>> On 14/12/2016 16:16, koffi BADOH wrote:
>>>> Hi all,
>>>>
>>>> I'm in a big trouble.
>>>>
>>>> I'm running postgreSQL 9.2.
>>>> For migration reason  I disabled  all postgres user account login .
>>>> Unfortunatly That's mean  the  postgres admin  account cannot also
>>>> connect.
>>>> Now I'm trying to enable all account , Then I got error
>>>>
>>>>
>>>> " psql fatal role postgres is not permitted to log in"
>>> start it standalone
>>>
>>>>       help please
>>>> koff
>>>>
>>>>
>>>>
>>>>
>>>
>
>


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