From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Erik Serrano <eserranos@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: backup to partitioned table
Date: Wed, 16 Nov 2022 11:15:05 -0500
Message-ID: <1349430.1668615305@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwZFApPf=Zrq2tsJ16tRoG_t-50wfPCLwiK6F3wOzZXtEw@mail.gmail.com>
References: <CA+dvXXt=G+a=4C5j8rn9ZTjP+g-6tu3MtnMC0cKbWSH5Nk5ovA@mail.gmail.com>
<CAKFQuwZFApPf=Zrq2tsJ16tRoG_t-50wfPCLwiK6F3wOzZXtEw@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, eserranos@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: backup to partitioned table
In-Reply-To: <1349430.1668615305@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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