pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbackup to partitioned table
6+ messages / 5 participants
[nested] [flat]
* backup to partitioned table
@ 2022-11-16 15:56 Erik Serrano <eserranos@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Erik Serrano @ 2022-11-16 15:56 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Guys,
I have a case that I would like you to help me with.
I have a partitioned table in postgresql version 9,5 to which I select and
it returns data perfectly, but when I want to make a backup of the table,
it only saves the structure without records.
It should be noted that I am not making a backup of the structure, but of
the data or complete, but it does not back up the data anyway.
When consulting the administrator, he informs me that it is a partitioned
table and that it is always zero, but the data is in its "daughter" tables
and that the select that it returns is the information that is housed in
all its daughter tables.
Now, given this, I wanted to know if there is any way to make a backup of
the table that throws all the data from its child tables into a flat file.
Is it possible to perform this action?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup to partitioned table
@ 2022-11-16 16:04 David G. Johnston <david.g.johnston@gmail.com>
parent: Erik Serrano <eserranos@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2022-11-16 16:04 UTC (permalink / raw)
To: Erik Serrano <eserranos@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Nov 16, 2022 at 8:56 AM Erik Serrano <eserranos@gmail.com> wrote:
> I have a partitioned table in postgresql version 9,5 to which I select and
> it returns data perfectly, but when I want to make a backup of the table,
> it only saves the structure without records.
> It should be noted that I am not making a backup of the structure, but of
> the data or complete, but it does not back up the data anyway.
> When consulting the administrator, he informs me that it is a partitioned
> table and that it is always zero, but the data is in its "daughter" tables
> and that the select that it returns is the information that is housed in
> all its daughter tables.
>
> Now, given this, I wanted to know if there is any way to make a backup of
> the table that throws all the data from its child tables into a flat file.
>
>
I presume you are doing something with pg_dump...don't.
Use a "COPY" command (or psql \copy) to produce the CSV file instead.
pg_dump has its own ideas about how to go about producing an actual backup
when all you really want is a copy of the data. Because its idea of a
backup means having one csv file for each of the child tables in the
partition scheme.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup to partitioned table
@ 2022-11-16 16:06 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Erik Serrano <eserranos@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Scott Ribe @ 2022-11-16 16:06 UTC (permalink / raw)
To: Erik Serrano <eserranos@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Nov 16, 2022, at 8:56 AM, Erik Serrano <eserranos@gmail.com> wrote:
>
> Hello Guys,
> I have a case that I would like you to help me with.
>
> I have a partitioned table in postgresql version 9,5 to which I select and it returns data perfectly, but when I want to make a backup of the table, it only saves the structure without records.
> It should be noted that I am not making a backup of the structure, but of the data or complete, but it does not back up the data anyway.
> When consulting the administrator, he informs me that it is a partitioned table and that it is always zero, but the data is in its "daughter" tables and that the select that it returns is the information that is housed in all its daughter tables.
>
> Now, given this, I wanted to know if there is any way to make a backup of the table that throws all the data from its child tables into a flat file.
>
> Is it possible to perform this action?
> From already thank you very much
You have to dump all the child tables; usually partitions are named reasonably such that a wildcard can do it:
pg_dump -t root_table_name\* > backup.sql
You don't exactly get a "flat" backup, as all the individual tables are included, but you do get it all into one file, if that's good enough for you.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup to partitioned table
@ 2022-11-16 16:12 Ron <ronljohnsonjr@gmail.com>
parent: Erik Serrano <eserranos@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2022-11-16 16:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/16/22 09:56, Erik Serrano wrote:
> Hello Guys,
> I have a case that I would like you to help me with.
>
> I have a partitioned table in postgresql version 9,5 to which I select and
> it returns data perfectly, but when I want to make a backup of the table,
> it only saves the structure without records.
> It should be noted that I am not making a backup of the structure, but of
> the data or complete, but it does not back up the data anyway.
> When consulting the administrator, he informs me that it is a partitioned
> table and that it is always zero, but the data is in its "daughter" tables
> and that the select that it returns is the information that is housed in
> all its daughter tables.
That is correct.
>
> Now, given this, I wanted to know if there is any way to make a backup of
> the table that throws all the data from its child tables *into a flat file*.
Flat file as in a CSV file?
>
> Is it possible to perform this action?
If you want to export it to a CSV (or tab-separated, etc, etc) flat file,
then -- if using psql, use the COPY or \COPY command; instead of simply
specifying a table name, copy the *query* (SELECT * FROM parent_table).
If that's not what you mean, please clarify.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup to partitioned table
@ 2022-11-16 16:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2022-11-16 16:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Erik Serrano <eserranos@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Nov 16, 2022 at 8:56 AM Erik Serrano <eserranos@gmail.com> wrote:
>> I have a partitioned table in postgresql version 9,5 to which I select and
>> it returns data perfectly, but when I want to make a backup of the table,
>> it only saves the structure without records.
You realize of course that 9.5 is two years past EOL.
>> When consulting the administrator, he informs me that it is a partitioned
>> table and that it is always zero, but the data is in its "daughter" tables
>> and that the select that it returns is the information that is housed in
>> all its daughter tables.
>> Now, given this, I wanted to know if there is any way to make a backup of
>> the table that throws all the data from its child tables into a flat file.
> I presume you are doing something with pg_dump...don't.
Yeah, pg_dump is chartered to reproduce the original situation, so it
won't help here.
Also, 9.5 didn't have what we would now call partitioned tables.
Presumably this is actually an inheritance tree, which complicates
matters because there's no guarantee that the child tables even have
the same column set as the parent.
> Use a "COPY" command (or psql \copy) to produce the CSV file instead.
You'll need to use "COPY (SELECT * FROM parent_table) TO wherever";
just plain "COPY parent_table" won't do it.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup to partitioned table
@ 2022-11-18 11:37 Erik Serrano <eserranos@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 6+ messages in thread
From: Erik Serrano @ 2022-11-18 11:37 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
Scott,
Thank you very much for your comment, apply your recommendation and solve
my problem.
Thank you very much, it was exactly what I needed.
Cheers
Thanks
*Erik Serrano *
El mié, 16 nov 2022 a las 13:06, Scott Ribe (<scott_ribe@elevated-dev.com>)
escribió:
> > On Nov 16, 2022, at 8:56 AM, Erik Serrano <eserranos@gmail.com> wrote:
> >
> > Hello Guys,
> > I have a case that I would like you to help me with.
> >
> > I have a partitioned table in postgresql version 9,5 to which I select
> and it returns data perfectly, but when I want to make a backup of the
> table, it only saves the structure without records.
> > It should be noted that I am not making a backup of the structure, but
> of the data or complete, but it does not back up the data anyway.
> > When consulting the administrator, he informs me that it is a
> partitioned table and that it is always zero, but the data is in its
> "daughter" tables and that the select that it returns is the information
> that is housed in all its daughter tables.
> >
> > Now, given this, I wanted to know if there is any way to make a backup
> of the table that throws all the data from its child tables into a flat
> file.
> >
> > Is it possible to perform this action?
> > From already thank you very much
>
> You have to dump all the child tables; usually partitions are named
> reasonably such that a wildcard can do it:
>
> pg_dump -t root_table_name\* > backup.sql
>
> You don't exactly get a "flat" backup, as all the individual tables are
> included, but you do get it all into one file, if that's good enough for
> you.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-11-18 11:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-16 15:56 backup to partitioned table Erik Serrano <eserranos@gmail.com>
2022-11-16 16:04 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-16 16:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-16 16:06 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-11-18 11:37 ` Erik Serrano <eserranos@gmail.com>
2022-11-16 16:12 ` Ron <ronljohnsonjr@gmail.com>
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