Sample Header Ad - 728x90

pg_dump does not return schema for repmgr objects

1 vote
1 answer
352 views
While trying to browse repmgr.monitoring_history, I noticed it has many tens of millions of records. Wanting to get a peak at the indexes that (may or may not) already exist on the table, I try a quickie pg_dump ...
# pg_dump -U postgres -d repmgr --table=repmgr.monitoring_history --schema-only
...which frustratingly returns only the boilerplate header/footer info...
--
-- PostgreSQL database dump
--

-- Dumped from database version 10.8
-- Dumped by pg_dump version 10.8

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
--
...well darnit! Did I typo something? 🤔
# pg_dump -U postgres -d repmgr --table=foo.bar --schema-only
pg_dump: no matching tables were found
...no... I _am_ connected to the host I thought I was, right?...
# psql -U postgres -d repmgr -c 'select count(*) from repmgr.monitoring_history;'
  count   
----------
 43597932
(1 row)
... so what gives? Why can't I dump the schema definition for this table? --- Possibly also worth noting that **pg_dump -s -d repmgr** returns only the **CREATE EXTENSION...** & **CREATE SCHEMA...** commands and none of the tables. The behaviour is consistent across a few different repmgr installations. Perhaps I'm having the same issue as this guy ? Also _also_ worth noting that attempting to dump data _only_ also comes up blank. And that I've tried **sudo -u postgres pg_dump...** as well. Dumping another database on the same cluster works just fine returning data & schema as expected.
Asked by Peter Vandivier (5708 rep)
Jun 10, 2019, 02:45 PM
Last activity: Jan 4, 2023, 04:27 PM