Re: I have an exporting need...

On 13/05/2024 16:01, Juan Hernández wrote:
> Hi team!
>
> First, i want to thank you for having your hands in this. You are doing
> a fantastic and blessing job. Bless to you all!
>
> I have a special need i want to comment to you. This is not a bug, is a
> need i have and i write here for been redirected where needed.
>
> I have to make a daily backup. The database is growing a lot per day,
> and sometimes i've had the need to recover just a table. And would be
> easier move a 200M file with only the needed table instead of moving a
> 5G file with all the tables i don't need, just a matter of speed.
>
> I've created a script to export every table one by one, so in case i
> need to import a table again, don't have the need to use the very big
> exportation file, but the "tablename.sql" file created for every table.
>
> My hosting provider truncated my script because is very large (more than
> 200 lines, each line to export one table), so i think the way i do this
> is hurting the server performance.

Some ideas for you to explore:

- Use "pg_dump -Fcustom" format. That still creates one large file, but
you can then use "pg_restore --table=foobar" to extract a .sql file for
single table from that when restoring.

- "pg_dump -Fdirectory" format does actually create one file per table.
It's in pg_dump's internal format though, so you'll still need to use
pg_restore to make sense of it.

- Use rsync to copy just the changed parts between two dump.

> Then my question.
>
> Do you consider useful to add a parameter (for example,
> --separatetables) so when used the exporting file process can create a
> different tablename.sql file for each table in database automatically?

It'd be tricky to restore from, as you need to restore the tables in the
right order. I think you'd still need a "main" sql file that includes
all the other files in the right order. And using the table names as
filenames gets tricky if the table names contain any funny characters.

For manual operations, yeah, I can see it being useful nevertheless.

--
Heikki Linnakangas
Neon (https://neon.tech)