Re: I have an exporting need... - Mailing list pgsql-hackers

From Heikki Linnakangas
Subject Re: I have an exporting need...
Date
Msg-id cc4a0510-1832-41d6-869d-2cf8901cdf19@iki.fi
Whole thread Raw
In response to I have an exporting need...  (Juan Hernández <dejesusog@gmail.com>)
Responses Re: I have an exporting need...
List pgsql-hackers
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)




pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: BitmapHeapScan streaming read user and prelim refactoring
Next
From: Peter Eisentraut
Date:
Subject: Re: consider -Wmissing-variable-declarations