Thread: pg_dump of partitioned table not working.
What am I missing?
(Specifying the whole file name because multiple versions are installed.)
$ /usr/lib/postgresql/12/bin/pg_dump --version
pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
postgres=# \d+ measurement
Partitioned table "public.measurement"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
city_id | integer | | not null | | plain
| |
logdate | date | | not null | | plain
| |
peaktemp | integer | | | | plain
| |
unitsales | integer | | | | plain
| |
Partition key: RANGE (logdate)
Indexes:
"measurement_pkey" PRIMARY KEY, btree (city_id, logdate)
Partitions: measurement_y2019h1 FOR VALUES FROM ('2019-01-01') TO
('2019-07-01'),
measurement_y2019h2 FOR VALUES FROM ('2019-07-01') TO
('2020-01-01'),
measurement_y2020h1 FOR VALUES FROM ('2020-01-01') TO
('2020-07-01'),
measurement_y2020h2 FOR VALUES FROM ('2020-07-01') TO
('2021-01-01')
postgres=# select * from measurement;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
5 | 2019-05-03 | |
5 | 2020-11-22 | 77 | 45
4 | 2020-11-22 | 77 | 45
(3 rows)
$ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement
--data-only
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
-- Dumped by pg_dump version 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- PostgreSQL database dump complete
--
--
Angular momentum makes the world go 'round.
On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com> wrote:
What am I missing?
(Specifying the whole file name because multiple versions are installed.)
$ /usr/lib/postgresql/12/bin/pg_dump --version
pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
postgres=# \d+ measurement
psql? on (default) port 5432
$ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement --data-only
pg_dump on port 5433
Usually different ports means different clusters
David J.
Ron <ronljohnsonjr@gmail.com> writes:
> What am I missing?
There's no data in a partitioned table per se, so the result is
not surprising.
What you need here is something like "pg_dump -t measurement*"
to indicate that you want measurement's child tables too, but
AFAIR pg_dump has no such feature. Maybe we should add it.
regards, tom lane
On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com> wrote:
What am I missing?
postgres=# \d+ measurement
Partitioned table "public.measurement"
$ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement --data-only
I’m unsure whether to expect a dump of only the partitioned table’s data to be empty or include everything. I suspect “empty” is the correct answer. If you dump everything the individual tables would be dumped, and not all partition should be dumped.
David J.
On Wednesday, December 2, 2020, Tom Lane <tgl@sss.pgh.pa.us> wrote:
What you need here is something like "pg_dump -t measurement*"
to indicate that you want measurement's child tables too, but
AFAIR pg_dump has no such feature.
-t accepts a pattern in pg_dump. But that requires the user to adhere to a naming scheme. There is room for a long-form argument in a similar vein to
--load-via-partition-root
to export through partition root.
David J.
I wrote:
> What you need here is something like "pg_dump -t measurement*"
> to indicate that you want measurement's child tables too, but
> AFAIR pg_dump has no such feature. Maybe we should add it.
Or actually: that syntax does do something, but it selects
tables by pattern matching not hierarchy, ie you get everything
whose name starts with "measurement". Depending on your naming
conventions, that might be close enough.
It does seem like there might be reason to have a switch along
the lines of "--include-child-tables".
regards, tom lane
On 12/2/20 5:42 PM, David G. Johnston wrote:
The individual partition tables regularly (for some site-specific definition of "regularly") change, as new partitions are added and old partitions are dropped. Or the DBA decides to change the partition scheme.
Needing to remember all the partition names is absurd, especially when there might be dozens of them
On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com> wrote:What am I missing?
postgres=# \d+ measurement
Partitioned table "public.measurement"
$ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement --data-only I’m unsure whether to expect a dump of only the partitioned table’s data to be empty or include everything. I suspect “empty” is the correct answer. If you dump everything the individual tables would be dumped, and not all partition should be dumped.
The individual partition tables regularly (for some site-specific definition of "regularly") change, as new partitions are added and old partitions are dropped. Or the DBA decides to change the partition scheme.
Needing to remember all the partition names is absurd, especially when there might be dozens of them
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On 12/2/20 5:35 PM, David G. Johnston wrote:
That's right. What's your point?
On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com> wrote:What am I missing?
(Specifying the whole file name because multiple versions are installed.)
$ /usr/lib/postgresql/12/bin/pg_dump --version
pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
postgres=# \d+ measurement
psql? on (default) port 5432$ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement --data-only pg_dump on port 5433Usually different ports means different clusters
That's right. What's your point?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On Wed, Dec 2, 2020 at 5:06 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 12/2/20 5:35 PM, David G. Johnston wrote:On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com> wrote:What am I missing?
(Specifying the whole file name because multiple versions are installed.)
$ /usr/lib/postgresql/12/bin/pg_dump --version
pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
postgres=# \d+ measurement
psql? on (default) port 5432$ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement --data-onlypg_dump on port 5433Usually different ports means different clusters
That's right. What's your point?
That you were comparing apples and oranges - specifically that the database you were dumping was empty but the one you were checking was not.
David J.
On 12/2/20 5:49 PM, David G. Johnston wrote:
Not only adhering to a naming scheme, but ensuring that there aren't any other tables which match "measurement*".
On Wednesday, December 2, 2020, Tom Lane <tgl@sss.pgh.pa.us> wrote:
What you need here is something like "pg_dump -t measurement*"
to indicate that you want measurement's child tables too, but
AFAIR pg_dump has no such feature.-t accepts a pattern in pg_dump. But that requires the user to adhere to a naming scheme.
Not only adhering to a naming scheme, but ensuring that there aren't any other tables which match "measurement*".
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On 12/2/20 6:08 PM, David G. Johnston wrote:
While I could have shown the exact psql commands (/usr/lib/postgresql/12/bin/psql -p5433) it wasn't necessary.
I know that was the command, because I use a set of aliases:
alias pgdump12='/usr/lib/postgresql/12/bin/pg_dump -p5433'
alias pgdump96='/usr/lib/postgresql/9.6/bin/pg_dump -p5432'
alias pgrestore12='/usr/lib/postgresql/12/bin/pg_restore -p5433'
alias pgrestore96='/usr/lib/postgresql/9.6/bin/pg_restore -p5432'
alias psql12='/usr/lib/postgresql/12/bin/psql -p5433'
alias psql96='/usr/lib/postgresql/9.6/bin/psql -p5432'
On Wed, Dec 2, 2020 at 5:06 PM Ron <ronljohnsonjr@gmail.com> wrote:On 12/2/20 5:35 PM, David G. Johnston wrote:On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com> wrote:What am I missing?
(Specifying the whole file name because multiple versions are installed.)
$ /usr/lib/postgresql/12/bin/pg_dump --version
pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
postgres=# \d+ measurement
psql? on (default) port 5432$ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement --data-onlypg_dump on port 5433Usually different ports means different clusters
That's right. What's your point?That you were comparing apples and oranges - specifically that the database you were dumping was empty but the one you were checking was not.
While I could have shown the exact psql commands (/usr/lib/postgresql/12/bin/psql -p5433) it wasn't necessary.
I know that was the command, because I use a set of aliases:
alias pgdump12='/usr/lib/postgresql/12/bin/pg_dump -p5433'
alias pgdump96='/usr/lib/postgresql/9.6/bin/pg_dump -p5432'
alias pgrestore12='/usr/lib/postgresql/12/bin/pg_restore -p5433'
alias pgrestore96='/usr/lib/postgresql/9.6/bin/pg_restore -p5432'
alias psql12='/usr/lib/postgresql/12/bin/psql -p5433'
alias psql96='/usr/lib/postgresql/9.6/bin/psql -p5432'
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On 12/2/20 5:50 PM, Tom Lane wrote: > I wrote: >> What you need here is something like "pg_dump -t measurement*" >> to indicate that you want measurement's child tables too, but >> AFAIR pg_dump has no such feature. Maybe we should add it. > Or actually: that syntax does do something, but it selects > tables by pattern matching not hierarchy, ie you get everything > whose name starts with "measurement". Depending on your naming > conventions, that might be close enough. > > It does seem like there might be reason to have a switch along > the lines of "--include-child-tables". That would be great, but won't help me in v12. -- Angular momentum makes the world go 'round.
On 12/2/20 4:13 PM, Ron wrote: > On 12/2/20 6:08 PM, David G. Johnston wrote: >> On Wed, Dec 2, 2020 at 5:06 PM Ron <ronljohnsonjr@gmail.com >> <mailto:ronljohnsonjr@gmail.com>> wrote: >> That you were comparing apples and oranges - specifically that the >> database you were dumping was empty but the one you were checking was not. >> > > While I could have shown the exact psql commands > (/usr/lib/postgresql/12/bin/psql -p5433) it wasn't necessary. From the POV of the mailing list participants it was necessary as the below constitutes hidden information we didn't have access to. When presenting a issue explicit is better then implicit. I cannot count the number of times issues where solved on this list when someone got around to asking for a explicit command. > > I know that was the command, because I use a set of aliases: > > alias pgdump12='/usr/lib/postgresql/12/bin/pg_dump -p5433' > alias pgdump96='/usr/lib/postgresql/9.6/bin/pg_dump -p5432' > alias pgrestore12='/usr/lib/postgresql/12/bin/pg_restore -p5433' > alias pgrestore96='/usr/lib/postgresql/9.6/bin/pg_restore -p5432' > alias psql12='/usr/lib/postgresql/12/bin/psql -p5433' > alias psql96='/usr/lib/postgresql/9.6/bin/psql -p5432' > > -- > Angular momentum makes the world go 'round. -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Dec 2, 2020 at 5:14 PM Ron <ronljohnsonjr@gmail.com> wrote:
> It does seem like there might be reason to have a switch along
> the lines of "--include-child-tables".
That would be great, but won't help me in v12.
I'd probably just relocate the table to a separate schema and require that all partitions are placed there as well. Otherwise, the necessary information exists in the catalogs, so a solution is within reach (minor concern regarding concurrency).
David J.
On 12/2/20 6:21 PM, David G. Johnston wrote:
That's doable, but the developer (of the very large mission-critical existing application) would probably push back.
On Wed, Dec 2, 2020 at 5:14 PM Ron <ronljohnsonjr@gmail.com> wrote:
> It does seem like there might be reason to have a switch along
> the lines of "--include-child-tables".
That would be great, but won't help me in v12.I'd probably just relocate the table to a separate schema and require that all partitions are placed there as well. Otherwise, the necessary information exists in the catalogs, so a solution is within reach (minor concern regarding concurrency).
That's doable, but the developer (of the very large mission-critical existing application) would probably push back.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On 12/2/20 6:21 PM, Adrian Klaver wrote: > On 12/2/20 4:13 PM, Ron wrote: >> On 12/2/20 6:08 PM, David G. Johnston wrote: >>> On Wed, Dec 2, 2020 at 5:06 PM Ron <ronljohnsonjr@gmail.com >>> <mailto:ronljohnsonjr@gmail.com>> wrote: > >>> That you were comparing apples and oranges - specifically that the >>> database you were dumping was empty but the one you were checking was not. >>> >> >> While I could have shown the exact psql commands >> (/usr/lib/postgresql/12/bin/psql -p5433) it wasn't necessary. > > From the POV of the mailing list participants it was necessary as the > below constitutes hidden information we didn't have access to. When > presenting a issue explicit is better then implicit. I cannot count the > number of times issues where solved on this list when someone got around > to asking for a explicit command. Shame on me for assuming, based on the explicit pg_dump command in the example. > >> >> I know that was the command, because I use a set of aliases: >> >> alias pgdump12='/usr/lib/postgresql/12/bin/pg_dump -p5433' >> alias pgdump96='/usr/lib/postgresql/9.6/bin/pg_dump -p5432' >> alias pgrestore12='/usr/lib/postgresql/12/bin/pg_restore -p5433' >> alias pgrestore96='/usr/lib/postgresql/9.6/bin/pg_restore -p5432' >> alias psql12='/usr/lib/postgresql/12/bin/psql -p5433' >> alias psql96='/usr/lib/postgresql/9.6/bin/psql -p5432' >> >> -- >> Angular momentum makes the world go 'round. > > -- Angular momentum makes the world go 'round.
On Wed, Dec 2, 2020 at 5:38 PM Ron <ronljohnsonjr@gmail.com> wrote:
Shame on me for assuming, based on the explicit pg_dump command in the example.
This is what you wrote: >
> (Specifying the whole file name because multiple versions are installed.)The path of the executable doesn't generally make a difference here
> $ /usr/lib/postgresql/12/bin/pg_dump --version
> pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1)Your 5432 database...
No idea how you got to this psql prompt, the last command you showed doesn't give you one. Not going to assume this isn't "one of the other of the multiple versions you have installed".
Then you show that your 5432 database has data.
> pg_dump -p 5433
Your dump of your 5433 database doesn't have data..
Typos or otherwise, the imprecise nature of your example drew attention to a possible typo-related problem, as opposed to the true "partitioned table" problem.
The fact that you didn't ask a better (more specific question), or otherwise state your expectations (which I presume would have pointed out the partitioned table dynamic) didn't help.
Sure, I could have been more careful in my reviewing of the posted material and made more of an effort to figure out what is correct, what is wrong, and what is confusing you. But this is also best-effort, and typos end up being the solution often enough that I don't usually dive deeper until that is ruled out (though here I did register the partitioned table aspect eventually).
David J.
On 12/2/20 4:38 PM, Ron wrote: > On 12/2/20 6:21 PM, Adrian Klaver wrote: >> On 12/2/20 4:13 PM, Ron wrote: >>> On 12/2/20 6:08 PM, David G. Johnston wrote: >>>> On Wed, Dec 2, 2020 at 5:06 PM Ron <ronljohnsonjr@gmail.com >>>> <mailto:ronljohnsonjr@gmail.com>> wrote: >> >>>> That you were comparing apples and oranges - specifically that the >>>> database you were dumping was empty but the one you were checking >>>> was not. >>>> >>> >>> While I could have shown the exact psql commands >>> (/usr/lib/postgresql/12/bin/psql -p5433) it wasn't necessary. >> >> From the POV of the mailing list participants it was necessary as the >> below constitutes hidden information we didn't have access to. When >> presenting a issue explicit is better then implicit. I cannot count >> the number of times issues where solved on this list when someone got >> around to asking for a explicit command. > > Shame on me for assuming, based on the explicit pg_dump command in the > example. The implied part was this: postgres=# \d+ measurement There was no indication of how you got there. You knew but we didn't and given how many times it has happened that folks where looking at one instance in one part of their problem report and another instance in separate part of the report it is only prudent to ask. > >> >>> >>> I know that was the command, because I use a set of aliases: >>> >>> alias pgdump12='/usr/lib/postgresql/12/bin/pg_dump -p5433' >>> alias pgdump96='/usr/lib/postgresql/9.6/bin/pg_dump -p5432' >>> alias pgrestore12='/usr/lib/postgresql/12/bin/pg_restore -p5433' >>> alias pgrestore96='/usr/lib/postgresql/9.6/bin/pg_restore -p5432' >>> alias psql12='/usr/lib/postgresql/12/bin/psql -p5433' >>> alias psql96='/usr/lib/postgresql/9.6/bin/psql -p5432' >>> >>> -- >>> Angular momentum makes the world go 'round. >> >> > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/2/20 6:54 PM, Adrian Klaver wrote: > On 12/2/20 4:38 PM, Ron wrote: >> On 12/2/20 6:21 PM, Adrian Klaver wrote: >>> On 12/2/20 4:13 PM, Ron wrote: >>>> On 12/2/20 6:08 PM, David G. Johnston wrote: >>>>> On Wed, Dec 2, 2020 at 5:06 PM Ron <ronljohnsonjr@gmail.com >>>>> <mailto:ronljohnsonjr@gmail.com>> wrote: >>> >>>>> That you were comparing apples and oranges - specifically that the >>>>> database you were dumping was empty but the one you were checking was >>>>> not. >>>>> >>>> >>>> While I could have shown the exact psql commands >>>> (/usr/lib/postgresql/12/bin/psql -p5433) it wasn't necessary. >>> >>> From the POV of the mailing list participants it was necessary as the >>> below constitutes hidden information we didn't have access to. When >>> presenting a issue explicit is better then implicit. I cannot count the >>> number of times issues where solved on this list when someone got around >>> to asking for a explicit command. >> >> Shame on me for assuming, based on the explicit pg_dump command in the >> example. > > The implied part was this: > > postgres=# \d+ measurement > > There was no indication of how you got there. You knew but we didn't and > given how many times it has happened that folks where looking at one > instance in one part of their problem report and another instance in > separate part of the report it is only prudent to ask. You're absolutely right. Like I said, shame on me. -- Angular momentum makes the world go 'round.
On 12/2/20 6:14 PM, Ron wrote: > On 12/2/20 5:50 PM, Tom Lane wrote: >> I wrote: >>> What you need here is something like "pg_dump -t measurement*" >>> to indicate that you want measurement's child tables too, but >>> AFAIR pg_dump has no such feature. Maybe we should add it. >> Or actually: that syntax does do something, but it selects >> tables by pattern matching not hierarchy, ie you get everything >> whose name starts with "measurement". Depending on your naming >> conventions, that might be close enough. >> >> It does seem like there might be reason to have a switch along >> the lines of "--include-child-tables". > > That would be great, but won't help me in v12. To clarify: I'm not being sarcastic. Such a feature really would be useful. -- Angular momentum makes the world go 'round.