Re: pg_dump not dumping some schemas - Mailing list pgsql-admin

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,
nspaclFROM 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.albe@wien.gv.at] 
Sent: Monday, June 01, 2015 10:08 AM
To: Guo, Yun; pgsql-admin@postgresql.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