pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Backing up users?
3+ messages / 3 participants
[nested] [flat]

* Backing up users?
@ 2005-04-30 08:16 Mauri Sahlberg <Mauri.Sahlberg@claymountain.com>
  2005-04-30 12:18 ` Re: Backing up users? Bruno Wolff III <bruno@wolff.to>
  2005-04-30 16:27 ` Re: Backing up users? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Mauri Sahlberg @ 2005-04-30 08:16 UTC (permalink / raw)
  To: pgsql-admin

Hi,

This might be a stupid question as it is not covered in the 7.4.7 and 
probably obvious to all others. Where do the system catalogs live? How 
to back them up? Should I back them up?

The admin manual states that to restore a pg_dumped database you should 
have those users created that will have permission related to objects 
created by pg_restore but gives no clear instructions how to back the 
users up and how to restore them before restoring the database.

According to the manual, pg_dumps are relative to template0 so all 
modifications relative to it will be backed up as well. This gives me a 
reason to believe that users could live in template0  but do they?

What if I just wish to move a single database with it's users to a new 
machine? It would be a disaster to overwrite whole existing system 
catalog on the target machine.

Is it sufficient to select rows from pg_shadow and pg_group that are 
related to the database in question and insert them in the new 
environment? (The rows to use could probably be deducted from other 
catalog tables and views by queries...)

Regards,
Mauri Sahlberg



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Backing up users?
  2005-04-30 08:16 Backing up users? Mauri Sahlberg <Mauri.Sahlberg@claymountain.com>
@ 2005-04-30 12:18 ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 3+ messages in thread

From: Bruno Wolff III @ 2005-04-30 12:18 UTC (permalink / raw)
  To: Mauri Sahlberg <Mauri.Sahlberg@claymountain.com>; +Cc: pgsql-admin

On Sat, Apr 30, 2005 at 11:16:59 +0300,
  Mauri Sahlberg <Mauri.Sahlberg@claymountain.com> wrote:
> Hi,
> 
> This might be a stupid question as it is not covered in the 7.4.7 and 
> probably obvious to all others. Where do the system catalogs live? How 
> to back them up? Should I back them up?
> 
> The admin manual states that to restore a pg_dumped database you should 
> have those users created that will have permission related to objects 
> created by pg_restore but gives no clear instructions how to back the 
> users up and how to restore them before restoring the database.

You want to use pg_dumpall.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Backing up users?
  2005-04-30 08:16 Backing up users? Mauri Sahlberg <Mauri.Sahlberg@claymountain.com>
@ 2005-04-30 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2005-04-30 16:27 UTC (permalink / raw)
  To: Mauri Sahlberg <Mauri.Sahlberg@claymountain.com>; +Cc: pgsql-admin

Mauri Sahlberg <Mauri.Sahlberg@claymountain.com> writes:
> What if I just wish to move a single database with it's users to a new 
> machine? It would be a disaster to overwrite whole existing system 
> catalog on the target machine.

Users (and groups) are global across each Postgres installation.
So what you'd need to do is something like this:

pg_dump the desired database, and do "pg_dumpall -g" to get a script
recreating all your users and groups.  Edit out the non-pertinent
part of that script.  Run the users/groups script and then the pg_dump
script on the target machine.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-04-30 16:27 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-30 08:16 Backing up users? Mauri Sahlberg <Mauri.Sahlberg@claymountain.com>
2005-04-30 12:18 ` Bruno Wolff III <bruno@wolff.to>
2005-04-30 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox