From: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Minimize checkpointer and walwriter io during pg_restore |
Date: | 2024-06-05 12:22:26 |
Message-ID: | CANzqJaA=A_dSZO-tnZS=CGkJhMv3_=EoAY+gE5TxdGsQWi3kPA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Wed, Jun 5, 2024 at 5:52 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | M Sarwar | 2024-06-05 21:23:05 | Truncate DUMMY_mid is blocked DELETE DUMMY_init TABLE |
Previous Message | Laurenz Albe | 2024-06-05 09:52:55 | Re: Minimize checkpointer and walwriter io during pg_restore |