Thread: easy way to copy all DBs and users on a server

easy way to copy all DBs and users on a server

From
exciteworks hosting
Date:
Is there an easy way to copy all DBs and users on a server to another
server?

I need to get an exact duplicate.

Thanks in avance!

---
Josh Trefethen
Exciteworks, Inc
http://exciteworks.com



Re: easy way to copy all DBs and users on a server

From
"A.Bhuvaneswaran"
Date:
On Thu, 2003-11-13 at 07:47, exciteworks hosting wrote:
> Is there an easy way to copy all DBs and users on a server to another
> server?

pg_dumpall is a good partner. If i am correct, you can also copy the
entire data directory, provided if both versions are same.

regards,
bhuvaneswaran


Attachment

Re: easy way to copy all DBs and users on a server

From
Christopher Browne
Date:
In an attempt to throw the authorities off his trail, jtnews@exciteworks.com (exciteworks hosting) transmitted:
> Is there an easy way to copy all DBs and users on a server to another
> server?
>
> I need to get an exact duplicate.

How exact is "exact"?

One notion of "exact" would involve stopping the database, building a
tarball that contains all of the data, copying it over and extracting
it.

Another would involve using "pg_dumpall" to dump all of the databases
and users and generating the SQL required to recreate it all.

If you're not sure how those approaches' differences would affect your
system, then it's pretty likely that pg_dumpall is the preferable
answer...
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/rdbms.html
If you've done  six impossible things  this morning, why not  round it
off  with breakfast  at  Milliways, the  Restaurant at the  End of the
Universe?

Re: easy way to copy all DBs and users on a server

From
"scott.marlowe"
Date:
On Wed, 12 Nov 2003, exciteworks hosting wrote:

> Is there an easy way to copy all DBs and users on a server to another
> server?
>
> I need to get an exact duplicate.

pg_dump -h source_server|psql -h dest_server



dropped users appear as numbers in ACL

From
DHS Webmaster
Date:
I have experienced this in the past with members of groups, but now with
relation ACLs. It goes like this:
Certain users are given specific permissions of different tables and
such.
The users are later dropped but reference to them remains in the ACL as
their former ID number.
When trying to revoke permissions on the number, it fails.
I noticed this with groups in the past and have become accustomed to
deleting these users from the groups before dropping them. Otherwise I
had to create dummy users with those IDs, go about cleaning things out
and then drop the dummy user.
Will I have to perform this same procedure for ACLs?
Are there any plans to handle these issues transparently when a user is
dropped?
--
Bill MacArthur
Webmaster
DHS Club

Re: dropped users appear as numbers in ACL

From
Peter Eisentraut
Date:
DHS Webmaster writes:

> I noticed this with groups in the past and have become accustomed to
> deleting these users from the groups before dropping them. Otherwise I
> had to create dummy users with those IDs, go about cleaning things out
> and then drop the dummy user.
> Will I have to perform this same procedure for ACLs?

Yes.

> Are there any plans to handle these issues transparently when a user is
> dropped?

"Wishlist" maybe, but no concrete plans.

--
Peter Eisentraut   peter_e@gmx.net


Re: dropped users appear as numbers in ACL

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> DHS Webmaster writes:
>> Are there any plans to handle these issues transparently when a user is
>> dropped?

> "Wishlist" maybe, but no concrete plans.

The reason it's difficult is that users span databases; when you drop a
user in database A, you have no way of seeing/removing references to him
that exist in databases B, C, ...

I'm not sure there's any really good answer to this short of changing
the way that users and databases work, which so far no one has wanted
to do.

            regards, tom lane

Re: dropped users appear as numbers in ACL

From
Peter Eisentraut
Date:
Tom Lane writes:

> The reason it's difficult is that users span databases; when you drop a
> user in database A, you have no way of seeing/removing references to him
> that exist in databases B, C, ...
>
> I'm not sure there's any really good answer to this short of changing
> the way that users and databases work, which so far no one has wanted
> to do.

One possibility would be to have an explicit "flush privileges" command
that you can run over a database to clean up after this.  That might also
help to support grant options on groups, which suffer from a similar
can't-look-into-other-databases problem.

--
Peter Eisentraut   peter_e@gmx.net


Re: dropped users appear as numbers in ACL

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> I'm not sure there's any really good answer to this short of changing
>> the way that users and databases work, which so far no one has wanted
>> to do.

> One possibility would be to have an explicit "flush privileges" command
> that you can run over a database to clean up after this.  That might also
> help to support grant options on groups, which suffer from a similar
> can't-look-into-other-databases problem.

One thing that would go a long way towards alleviating the problem is to
get rid of user-selectable sysids for users and groups, and instead assign
the sysids from a cluster-wide sequence (or equivalently, revert to
using OIDs as user/group identifiers).  Then dead privileges wouldn't
come back to life as belonging to some newly-created user.  You could
imagine making vacuum delete ACL entries for no-longer-known users,
or put it into a separate cleanup operation as Peter suggests.

However, that has its own downside.  IIRC, the reason we haven't done
this already is that if you unintentionally drop a user who still owns
objects, it's nice to be able to resurrect him with the same sysid.
The cross-database problem makes it hard to prevent such mistakes with
dependencies.

Maybe rather than a flat prohibition of WITH SYSID, just change the
default assignment to be from a sequence rather than the error-prone
MAX()+1 behavior?

            regards, tom lane