how to sync the system table with pg_dump

Gary Fu wrote:

I tried to use pg_dump to restore (sync) a database, but I noticed that
the system table pg_namespace was not synced.

If you restore a database, entries in pg_namespace will be created if
the dump contains any CREATE SCHEMA statements, i.e. if there are
schemas in your original database.

Check if the dump was created and restored by a database user with
the appropriate permissions (a superuser ideally), and look out for
error messages.

Do not try to manually change pg_namespace. Just don't.

Yours,
Laurenz Albe

Gary Fu wrote:

I tried to use pg_dump to restore (sync) a database, but I noticed that
the system table pg_namespace was not synced.

If you restore a database, entries in pg_namespace will be created if
the dump contains any CREATE SCHEMA statements, i.e. if there are
schemas in your original database.

Check if the dump was created and restored by a database user with
the appropriate permissions (a superuser ideally), and look out for
error messages.

Do not try to manually change pg_namespace. Just don't.

Yours,
Laurenz Albe

Thanks for the response. I think the problem is because there are
temporary schemas (pg_temp_1, ..) in the source db and the pg_dump
does not allow them to be restored (see below).

My question now is why those temporary schemas won't be cleaned
after I restart the db ?

Thanks,
Gary

% pg_dump -n pg_temp_1 -h nppdist
--
-- PostgreSQL database dump
--
SET client_encoding = 'LATIN1';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: pg_temp_1; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA pg_temp_1;

ALTER SCHEMA pg_temp_1 OWNER TO postgres;

--
-- PostgreSQL database dump complete
--

-----------------------------------------------------
% pg_dump -n pg_temp_1 -h nppdist | psql -h nppsds1
SET
SET
SET
SET
SET
ERROR: unacceptable schema name "pg_temp_1"
DETAIL: The prefix "pg_" is reserved for system schemas.
ERROR: schema "pg_temp_1" does not exist

Gary Fu <gfu@saicmodis.com> writes:

My question now is why those temporary schemas won't be cleaned
after I restart the db ?

Just leave them alone and you'll be fine. These tools actually have
had most of the bugs worked out of them ;-) ... if you think pg_dump is
omitting something, you are probably mistaken.

regards, tom lane

Tom Lane wrote:

Gary Fu <gfu@saicmodis.com> writes:

My question now is why those temporary schemas won't be cleaned
after I restart the db ?

Just leave them alone and you'll be fine. These tools actually have
had most of the bugs worked out of them ;-) ... if you think pg_dump is
omitting something, you are probably mistaken.

regards, tom lane

Thanks for the response. Yes, normally it will be okay. However, when
I tried PgAdmin with Pgpool, it will cause problem. The PgAdmin will
try to access pg_namespace when making a connection to a db, if the
temporary schemas are different between the backend db servers,
the pgpool will return mismatch error and fail the PgAdmin connection.

Thanks,
Gary

Gary Fu <gfu@saicmodis.com> writes:

Thanks for the response. Yes, normally it will be okay. However, when
I tried PgAdmin with Pgpool, it will cause problem. The PgAdmin will
try to access pg_namespace when making a connection to a db, if the
temporary schemas are different between the backend db servers,
the pgpool will return mismatch error and fail the PgAdmin connection.

You'll want to take that up on the pgAdmin lists. Or perhaps it's a
pgpool problem, it's hard to tell with such a sketchy description.

regards, tom lane