Re: pg_dump not dumping some schemas

Here¹s the output:

polling_etl=# SELECT oid FROM pg_catalog.pg_namespace n polling_etl-# WHERE n.nspname ~ '^(test)$';
oid
-------
17972
(1 row)
polling_etl=# SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, nspacl FROM pg_catalog.pg_namespace;
tableoid | oid | nspname | rolname |
nspacl
----------+---------+--------------------+-----------+------------------
----------+---------+--------------------+-----------+---
-----------------------------------------------------------
2615 | 99 | pg_toast | postgres |
2615 | 11215 | pg_temp_1 | postgres |
2615 | 11216 | pg_toast_temp_1 | postgres |
2615 | 11 | pg_catalog | postgres |
{postgres=UC/postgres,=U/postgres}
2615 | 2200 | public | postgres |
{postgres=UC/postgres,=UC/postgres}
2615 | 12348 | information_schema | postgres |
{postgres=UC/postgres,=U/postgres}
2615 | 1131042 | test2 | pb_writer |
{pb_writer=UC/pb_writer,test2=U/pb_writer}
2615 | 16389 | polling_etl | pb_writer |
{pb_writer=UC/pb_writer,polling_etl=UC/pb_writer}
2615 | 17944 | mdev1 | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev1=U/pb_writ
er}
2615 | 17946 | mdev2 | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev2=U/pb_writ
er}
2615 | 17957 | mdev3 | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev3=U/pb_writ
er}
2615 | 17970 | mdev4 | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev4=U/pb_writ
er}
2615 | 17972 | test | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,test=U/pb_write
r}
(13 rows)

Is there a way to repair the database if it's corrupted?

-----Original Message-----
From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
Sent: Monday, June 01, 2015 10:08 AM
To: Guo, Yun; pgsql-admin(at)postgresql(dot)org
Subject: RE: pg_dump not dumping some schemas

Yun Guo wrote:
> It doesn’t show up in custom format dump either… I begin to wonder if
> it’s a bug in pg_dump.
>
>
> -bash-4.1$ pg_dump -s -n test -Fc polling_etl | pg_restore -l | grep
> SCHEMA -bash-4.1$ pg_dump -s -n test2 -Fc polling_etl | pg_restore -l
> | grep SCHEMA 7; 2615 1131042 SCHEMA - test2 pb_writer

... or your database is corrupt.

Maybe it helps to issue the queries that pg_dump uses:

SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, nspacl FROM pg_namespace;

SELECT oid FROM pg_catalog.pg_namespace n WHERE (n.nspname = 'test');

Yours,
Laurenz Albe