Обсуждение: Postgres backups - when the backup operator isn't the postgres user ?

Hi,

I'm part of a team involved in creating an appliance product. We
collect data about stuff, put it in a postgres databse, and then allow
a UI to pull stuff out of the db and draw some pictures. Pretty
straightforward. Our application runs in the context of user A, and
currently our db runs in the context of postgres user B. Our
application provides login credentials to the db when we open it for
access. We did this as we thought this was 'best practice' to separate
the app user from the database user.

Now we would like our application to provide automatic backups of the
db and distribute copies to remote systems, but this is where things go
wrong. We're using PITA backups where we need to tar up parts of the
$PGDATA tree, and our application user
A does not have permissions to access the postgres files that we need
to make our backups.

The obvious solution to me was to make our app user and our db user the
same ... it's pretty simple and will solve our problem, but I would
rather the app user not have direct access to the db files.



This seems a simple and common enough scenario tho'
- someone must have faced this before and come up with a good solution.
Any pointers ?




-- Chris

On 21/08/2008, Chris Lidgate (Sun Edgemail) <Chris.Lidgate@sun.com> wrote:
>  Now we would like our application to provide automatic backups of the db
> and distribute copies to remote systems, but this is where things go wrong.
> We're using PITA backups where we need to tar up parts of the $PGDATA tree,
> and our application user A does not have permissions to access the postgres
> files that we need to make our backups.
I'm not a back-up expert by any stretch, but wouldn't it make more
sense to use pg_dump as the user and then ship that of? Or use
wal-shipping to begin with, instead of taking the file-system approach
for which you'd most likely need to stop the DB anyway?

Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm