pg_dump of partitioned table not working.

From: Ron <ronljohnsonjr(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Subject: pg_dump of partitioned table not working.
Date: 2020-12-02 23:27:36
Message-ID: 6b713437-69e3-0383-0c5f-f2e01bd46daa@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2020-12-02 23:35:13 Re: pg_dump of partitioned table not working.
Previous Message Ron 2020-12-02 22:35:05 Re: Is the Halloween problem an issue in Postgres