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 -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.