pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to create directory format backup
7+ messages / 4 participants
[nested] [flat]

* How to create directory format backup
@ 2023-02-08 15:00  Andrus <kobruleht2@hot.ee>
  0 siblings, 1 reply; 7+ messages in thread

From: Andrus @ 2023-02-08 15:00 UTC (permalink / raw)
  To: pgsql-general

Hi!

Creating backup in directory format using

     pg_dump -f "sba" -Fdirectory --jobs=32 --verbose sba

throws error

     pg_dump: error: could not stat file "sba/282168.data.gz": value too 
large

How to fix it ?

Server is Postgres 12 running in Debian Linux 10 under WSL
Client is pg_dump from Postgres 15 running in Windows 11

Andrus

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to create directory format backup
@ 2023-02-08 17:59  hubert depesz lubaczewski <depesz@depesz.com>
  parent: Andrus <kobruleht2@hot.ee>
  0 siblings, 1 reply; 7+ messages in thread

From: hubert depesz lubaczewski @ 2023-02-08 17:59 UTC (permalink / raw)
  To: Andrus <kobruleht2@hot.ee>; +Cc: pgsql-general

On Wed, Feb 08, 2023 at 05:00:10PM +0200, Andrus wrote:
> Hi!
> 
> Creating backup in directory format using
> 
>     pg_dump -f "sba" -Fdirectory --jobs=32 --verbose sba
> 
> throws error
> 
>     pg_dump: error: could not stat file "sba/282168.data.gz": value too
> large
> 
> How to fix it ?
> 
> Server is Postgres 12 running in Debian Linux 10 under WSL
> Client is pg_dump from Postgres 15 running in Windows 11

Looks like your filesystem on client is having limits on file sizes.

Use better filesystem, or just dump on linux, it's filesystems usually
don't hit these limits.

Best regards,

depesz






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to create directory format backup
@ 2023-02-08 20:59  Andrus <kobruleht2@hot.ee>
  parent: hubert depesz lubaczewski <depesz@depesz.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Andrus @ 2023-02-08 20:59 UTC (permalink / raw)
  To: depesz@depesz.com; +Cc: pgsql-general

Hi!

 >Looks like your filesystem on client is having limits on file sizes. 
Use better filesystem, or just dump on linux, it's filesystems usually 
don't hit these limits.

This file size is only 6.2 GB. If custom format is used, pg_dump creates 
large file without problems. There are no file size limits. Error 
message is not about this.

Backup client is running in Windows 11 and this cannot changed.

How to create backup in format from which tables can selectively restored?

Posted also in 
https://stackoverflow.com/questions/75387616/how-to-make-directory-format-backup

Andrus.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to create directory format backup
@ 2023-02-08 21:17  Erik Wienhold <ewie@ewie.name>
  parent: Andrus <kobruleht2@hot.ee>
  1 sibling, 1 reply; 7+ messages in thread

From: Erik Wienhold @ 2023-02-08 21:17 UTC (permalink / raw)
  To: Andrus <kobruleht2@hot.ee>; depesz@depesz.com; +Cc: pgsql-general

> On 08/02/2023 21:59 CET Andrus <kobruleht2@hot.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.

--
Erik





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to create directory format backup
@ 2023-02-08 21:37  Andrus <kobruleht2@hot.ee>
  parent: Erik Wienhold <ewie@ewie.name>
  0 siblings, 1 reply; 7+ messages in thread

From: Andrus @ 2023-02-08 21:37 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; depesz@depesz.com; +Cc: pgsql-general

Hi!


>> 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.

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

Andrus.


> --
> Erik

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to create directory format backup
@ 2023-02-08 22:45  Erik Wienhold <ewie@ewie.name>
  parent: Andrus <kobruleht2@hot.ee>
  0 siblings, 0 replies; 7+ messages in thread

From: Erik Wienhold @ 2023-02-08 22:45 UTC (permalink / raw)
  To: Andrus <kobruleht2@hot.ee>; depesz@depesz.com; +Cc: pgsql-general

> On 08/02/2023 22:37 CET Andrus <kobruleht2@hot.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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to create directory format backup
@ 2023-02-08 22:57  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Andrus <kobruleht2@hot.ee>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 2023-02-08 22:57 UTC (permalink / raw)
  To: Andrus <kobruleht2@hot.ee>; +Cc: pgsql-general

Andrus <kobruleht2@hot.ee> writes:
> This file size is only 6.2 GB. If custom format is used, pg_dump creates 
> large file without problems. There are no file size limits. Error 
> message is not about this.

Are you certain that the pg_dump you're using is v15, and not something
pre-v14?  We got rid of the 4GB limit on stat() results in v14,
or so we thought.

Anyway, as far as I can find pg_dump can only reach that error message
via fsync_dir_recurse(), which suggests that a workaround might be
to add --no-sync so it doesn't try to fsync its output.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2023-02-08 22:57 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-08 15:00 How to create directory format backup Andrus <kobruleht2@hot.ee>
2023-02-08 17:59 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-02-08 20:59   ` Andrus <kobruleht2@hot.ee>
2023-02-08 21:17     ` Erik Wienhold <ewie@ewie.name>
2023-02-08 21:37       ` Andrus <kobruleht2@hot.ee>
2023-02-08 22:45         ` Erik Wienhold <ewie@ewie.name>
2023-02-08 22:57     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox