Обсуждение: very slow postgresql startup

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% отстъпка за нов хостинг план.

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

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

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

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% отстъпка за нов хостинг план.

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

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% отстъпка за нов хостинг план.

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

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

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

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

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