Home > mailing lists

Thread: very slow postgresql startup

very slow postgresql startup

From

zz_11@mail.bg

Date:

23 May 2016, 11:33:52

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:

23 May 2016, 12:19:07

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:

23 May 2016, 12:28:42

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:

23 May 2016, 12:49:22

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:

25 May 2016, 14:01:49

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:

25 May 2016, 14:04:16

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:

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:

30 May 2016, 19:29:25

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:

14 December 2016, 20:16:44

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:

14 December 2016, 20:21:50

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:

14 December 2016, 21:02:50

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:

14 December 2016, 21:12:27

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