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
