Re: How to create directory format backup

> On 08/02/2023 22:37 CET Andrus <kobruleht2(at)hot(dot)ee> wrote:
>
> > > How to create backup in format from which tables can selectively
> > > restored?
> > >
> > Dump as custom-format archive (-F custom) and use that with pg_restore
> > and options --table or --list/--use-list to select what should be
> > restored.
> >
> How to select tables interactively like pgAdmin allows to select when
> directory format is used ?
>
> Database contains hundreds of schemas. I need to restore public and other
> other schema.
>
> Whole backup file is scanned to restore only two schemas. It takes lot of
> time.

pg_dump also accepts options --table and --schema to only dump what you need.

> Also directory format allows to use all cores with --jobs=32 parameter.
> Dump and partial restore using custom format are much slower.

Run multiple pg_dump processes in parallel where each processes a subset of
tables with the options mentioned above.

--
Erik