Thread: I have an exporting need...
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.
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?
Example...
PGHOST="/tmp" PGPASSWORD="mydbpass" pg_dump -U dbusername --separatetables -Fp --inserts dbname > "/route/dbname.sql"
And if this database has tables table1...table10, then 10 files are created...
dbname_table1.sql
dbname_table2.sql
dbname_table3.sql
...
dbname_table8.sql
dbname_table9.sql
dbname_table10.sql
In each file, all main parameters will be generated again. For example the file dbname_table1.sql...
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.21
-- Dumped by pg_dump version 15.6
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
...
...
SET default_tablespace = '';
--
-- Name: table1; Type: TABLE; Schema: public; Owner: dbusername
--
CREATE TABLE public.table1 (
code numeric(5,0),
name character varying(20)
)
I dont know if many developers have same need as me. I hope this help in future.
Thanks for reading me and thanks for what you've done.. You are doing fine! Cheers!
______________
Juan de Jesús
On Tue, 14 May 2024 at 06:18, Juan Hernández <dejesusog@gmail.com> wrote: > Do you consider useful to add a parameter (for example, --separatetables) so when used the exporting file process can createa different tablename.sql file for each table in database automatically? > > Example... > > PGHOST="/tmp" PGPASSWORD="mydbpass" pg_dump -U dbusername --separatetables -Fp --inserts dbname > "/route/dbname.sql" > > And if this database has tables table1...table10, then 10 files are created... pg_dump has code to figure out the dependency of objects in the database so that the dump file produced can be restored. If one file was output per table, how would you know in which order to restore them? For example, you have a table with a FOREIGN KEY to reference some other table, you need to restore the referenced table first. That's true for both restoring the schema and restoring the data. David
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)
Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On 13/05/2024 16:01, Juan Hernández wrote:
>> 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.
It's a lot worse than that, as it's entirely possible to have circular
FK dependencies, meaning there is no "right order" if you think of
each table file as self-contained DDL plus data. Other sorts of
circularities are possible too.
pg_dump deals with that hazard by splitting things up: first create
all the tables, then load all the data, then create all the indexes
and foreign keys. You can tell it to just emit the parts relevant to
a particular table, but it's on your head whether that's actually
going to be useful in your context. I doubt that it's widely enough
useful to justify creating a special mode beyond what we already
have.
regards, tom lane
Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On 13/05/2024 16:01, Juan Hernández wrote:
>> 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.
It's a lot worse than that, as it's entirely possible to have circular
FK dependencies, meaning there is no "right order" if you think of
each table file as self-contained DDL plus data. Other sorts of
circularities are possible too.
pg_dump deals with that hazard by splitting things up: first create
all the tables, then load all the data, then create all the indexes
and foreign keys. You can tell it to just emit the parts relevant to
a particular table, but it's on your head whether that's actually
going to be useful in your context. I doubt that it's widely enough
useful to justify creating a special mode beyond what we already
have.
regards, tom lane