Skip site navigation (1) Skip section navigation (2)

Re: Problem with disabling triggers in pg_dump

From: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org, dyp(at)perchine(dot)com
Subject: Re: Problem with disabling triggers in pg_dump
Date: 2000-07-25 15:43:01
Message-ID: (view raw or flat)
Lists: pgsql-hackers
At 11:17 25/07/00 -0400, Tom Lane wrote:
>Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> The obvious solution is to reconnect as the datdba before running the code.
>> But that option may not be possible because passwords may be enabled
>We have talked about ways to solve the real problem.  One way is to
>run the entire restore script as superuser, doing something other than
>\connect to set ownership of created objects.  You could do that now
>with something like
>	create table newtable ...
>	update pg_class set relowner = xxx where relname = 'newtable';

I considered this, but lack of confidence as to the ramifications of
updating pg_class + the fact that it also requires superuser access, put me
off. But maybe I should put an 'Ownersip' phase in pg_restore (at the end,

>although it'd be nicer to invent ALTER commands to handle this.

I agree, but not for 7.0.2. Doing something as apparently simple might be a
good way for me to see how the process of dealing with a command works from
psql->backend->psql, which is something I'll need for 'insert...returning'.

>Another issue to think about is that it should be possible to run
>restore scripts as a non-superuser, with the restriction that all the
>created objects end up being owned by you not by their original owners.

This was part of the motivation; if a DBA creates a db and can set it's
ownership to the appropriate user, then the user should be able to restore
into it.

>(Compare the behavior of "tar x" when run as superuser or not.)
>This has not worked in the past (because those \connect commands can't
>be ignored), 

I don't you mean that psql barfs, or do you mean that there has
been no way to prevent them being output?

>but it would work with an ALTER-based approach, because
>the ALTERs would simply fail.  With a slightly smarter pg_restore,
>there'd be an option not to emit the ALTERs in the first place, but
>this is inessential.

--ignore-ownership or similar on pg_restore is about 10 lines of code.

>The thing that really bothers me about this reltriggers hack is that
>it doesn't work if the script is being run as non-superuser.  I don't
>see why it's necessary anyway; shouldn't the order of operations be
>	create table;
>	load data;
>	create triggers and indexes;

Yes, for a full restore that is true. But for a partial restore (where I
have assumed triggers may exist), it's nice to disable the triggers...but
(and this is a horrible idea), pg_restore could use pg_dump to dump the
triggers to a null archive, drop them, then restore them at the end of the
restore op...

>> So, how does this sound: add another arg to pg_restore, --superuser=name,
>> which allows the person restoring the database to specify the superuser
>> account to use, and if none is specified, then use the account it finds in
>> the dba field of the database it is restoring to.
>I think pg_restore ought to run under the same userid that would be
>established for a plain psql session --- ie, -u or $PGUSER or $USER.

It does; but with a direct db connection it reconnects appropriately as
each user (with password prompts if necessary). The idea was that for the
reltriggers hack it knows it needs to connect as a superuser, so I could
allow the user to specify a valid one with a known password.

>> I would also modify
>> pg_dump to dump the original datdba, in case a mythical future release does
>> the 'create database' part as well.
>Uh, how does pg_dumpall enter into this?

It doesn't really; pg_dumpall inserts text into the output stream and
creates one huge script for restoring all databases. It also messes with
pg_* tables, so I can't really make the two work together. 

The current version of pg_restore that I am working on has a --create (-C)
option which will issue the appropriate 'create database' commands. But
pg_dumpall is unaffected. Sounds like I need to add --ignore-owner.

At *some* point in the future, I'll try to put together a pg_backupall &
pg_restoreall, but that's not really high on my priorities - I tend to
backup DBs individually.

Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from   |/

In response to


pgsql-hackers by date

Next:From: Thomas SwanDate: 2000-07-25 16:05:15
Subject: DELETE/DROP on Columns
Previous:From: Tom LaneDate: 2000-07-25 15:17:53
Subject: Re: Problem with disabling triggers in pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group