Thread: easy way to copy all DBs and users on a server
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
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
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?
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
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
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
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
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
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