From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Erik Serrano <eserranos@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: backup to partitioned table
Date: Wed, 16 Nov 2022 09:06:14 -0700
Message-ID: <DD0761A0-7FBA-4EF4-BDAF-5BAE55553C64@elevated-dev.com> (raw)
In-Reply-To: <CA+dvXXt=G+a=4C5j8rn9ZTjP+g-6tu3MtnMC0cKbWSH5Nk5ovA@mail.gmail.com>
References: <CA+dvXXt=G+a=4C5j8rn9ZTjP+g-6tu3MtnMC0cKbWSH5Nk5ovA@mail.gmail.com>
> 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.
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: scott_ribe@elevated-dev.com, eserranos@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: backup to partitioned table
In-Reply-To: <DD0761A0-7FBA-4EF4-BDAF-5BAE55553C64@elevated-dev.com>
* 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