Re: pg_dump, shemas, backup strategy

> On 24 Jul 2010, at 24:20, Michael A. Peters wrote:
>
*snip*
>
> Schema's in Postgres are similar to different databases in MySQL. They
> allow you to organise your tables in groups of tables belonging to similar
> functionality, for example. They have their own permissions too, which is
> nice if you need to restrict certain users to certain functionality. And
> of course you can access tables cross-schema, if you aren't denied the
> permissions.
>
> In your case, you could move those "troublesome" tables into their own
> schema and adjust the search_path accordingly for the user your PHP
> application uses to connect to the DB.

I spent last night playing with schemas and I must say, they absolutely
rock. I especially like the fact that you can still do queries involving
multiple schemas if you need to because they are still part of the same
database, and pg_dump keeping track of the various user authentications
granted to a schema and its tables is class.

It's the right way to do things.

*snip*

>
> You could move Sphyder's tables into a separate schema too, but... if you
> disallow the accompanying role (let's say 'sphyder') access to the public
> schema, then it can't read various system tables either. That can cause
> issues with looking up FK constraints and the like.
> Mind that I've never been in a situation where I needed to disallow some
> roles to access to the public schema, I'm not 100% sure about this - a
> simple test case is easy to create though.
>
> I'd probably just put most (or all) of my main database in a schema other
> than 'public' so that the sphyder role can still access the system tables
> it needs (and it won't be able to change those if that role is set up with
> sufficiently restrictive permissions).

That's what I'm doing now.

>
> As an aside; I'm not familiar with Sphyder, but Postgres' TSearch 2 is
> pretty good too. It's built into the main database since version 8.3, not
> in your version. For 8.1 there is an extension with largely the same
> functionality, in case you're interested. I'm not sure how easy that would
> be to upgrade to the builtin version once you get to 8.3 or newer
> though...

I am going to look into that.

-----
Michael A. Peters

http://www.shastaherps.org/