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:
On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com
<mailto: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-onlyI’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.
On 12/2/20 5:35 PM, David G. Johnston wrote:
On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com
<mailto: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 5433
Usually different ports means different clusters
That's right. What's your point?
--
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 5433
Usually 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:
On Wednesday, December 2, 2020, Tom Lane <tgl@sss.pgh.pa.us
<mailto: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.
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:On 12/2/20 5:35 PM, David G. Johnston wrote:
On Wednesday, December 2, 2020, Ron <ronljohnsonjr@gmail.com
<mailto: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 5433
Usually 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.
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:
On Wed, Dec 2, 2020 at 5:14 PM Ron <ronljohnsonjr@gmail.com
<mailto: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.
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...
postgres=# \d+ measurement
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.