Lists: | pgsql-general |
---|
From: | Gary Fu <gfu(at)saicmodis(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | how to sync the system table with pg_dump |
Date: | 2008-06-04 15:34:10 |
Message-ID: | g26cli$22sb$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi,
I tried to use pg_dump to restore (sync) a database, but I noticed that
the system table pg_namespace was not synced.
I tried the following pg_dump command to just restore that table without
success either.
Does pg_dump support for the system tables or something I missed ?
Is there another way to sync the system tables ?
Thanks,
Gary
% pg_dump -t pg_namespace -h nppdist nppsd3 | psql -h nppsds1 -d nppsd3
SET
SET
SET
SET
SET
SET
SET
SET
ERROR: relation "pg_namespace" already exists
ALTER TABLE
ERROR: duplicate key violates unique constraint
"pg_namespace_nspname_index"
CONTEXT: COPY pg_namespace, line 1: "pg_toast 10 \N"
ERROR: permission denied: "pg_namespace" is a system catalog
ERROR: permission denied: "pg_namespace" is a system catalog
REVOKE
REVOKE
GRANT
From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Gary Fu *EXTERN*" <gfu(at)saicmodis(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: how to sync the system table with pg_dump |
Date: | 2008-06-05 12:02:23 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C20230951A@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Gary Fu <gfu(at)saicmodis(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to clean up temporary schemas (how to sync the system table with pg_dump) |
Date: | 2008-06-05 14:08:25 |
Message-ID: | g28s0k$d0l$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> 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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Gary Fu <gfu(at)saicmodis(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Re: how to clean up temporary schemas (how to sync the system table with pg_dump) |
Date: | 2008-06-06 05:54:58 |
Message-ID: | 4476.1212731698@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Gary Fu <gfu(at)saicmodis(dot)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
From: | Gary Fu <gfu(at)saicmodis(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to clean up temporary schemas (how to sync the system table with pg_dump) |
Date: | 2008-06-06 13:35:16 |
Message-ID: | g2beeg$id8$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Tom Lane wrote:
> Gary Fu <gfu(at)saicmodis(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Gary Fu <gfu(at)saicmodis(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Re: how to clean up temporary schemas (how to sync the system table with pg_dump) |
Date: | 2008-06-06 19:08:58 |
Message-ID: | 28156.1212779338@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Gary Fu <gfu(at)saicmodis(dot)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