Re: Minimize checkpointer and walwriter io during pg_restore

Поиск
Список
Период
Сортировка
От Ron Johnson
Тема Re: Minimize checkpointer and walwriter io during pg_restore
Дата
Msg-id CANzqJaA=A_dSZO-tnZS=CGkJhMv3_=EoAY+gE5TxdGsQWi3kPA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Minimize checkpointer and walwriter io during pg_restore  (Laurenz Albe <laurenz.albe@cybertec.at>)
Ответы Re: Minimize checkpointer and walwriter io during pg_restore
Список pgsql-admin
Дерево обсуждения
On Wed, Jun 5, 2024 at 5:52 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Tue, 2024-06-04 at 11:32 -0400, Ron Johnson wrote:
> > I would not set "fsync" to off.  It won't make a measurable difference for
> > pg_restore, and it may break your database.  The same applies to "full_page_writes".
>
> If the pg_restore fails, I'm dropping the database no matter what.  A few more minutes
> to drop $PGDATA and re-run initdb won't be noticed compared to the length of the pg_restore.

The *cluster* would be corrupted.
You'd have to start with a new "initdb".
If you want that... but it is a high price for very moderate gains.

This is a brand new instance with zero databases.

If the pg_restore which creates that one database fails for some reason, I just restart the pg_restore.  In the unlikely event that the VM crashes or becomes corrupt, doing an initdb and creating all the users again adds 10 minutes to the 7 hour pg_restore.  That's "line noise" compared to the variability in performance of the VM, SAN and network.
 
> > What might make a difference is if you use the --single-transaction option of
> > pg_restore.
>
> A single 4+TB transaction??

Yes, sure.  Why not?
 
"Giant transactions bad, small transactions good", right?  It's been drilled into me for 35 years.

В списке pgsql-admin по дате отправления:

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: Minimize checkpointer and walwriter io during pg_restore
Следующее
От: M Sarwar
Дата:
Сообщение: Truncate DUMMY_mid is blocked DELETE DUMMY_init TABLE