Re: database role for backups?

2013/5/7 Kamil Jońca <kjonca(at)o2(dot)pl>:
>
> I tried to create make backup my postrgresql cluster with amanda.
> And I had to create database superuser.
>
> I tried to create no-superuser role with only "replication" privillege,
> but after run backup I got:
>
> --8<---------------cut here---------------start------------->8---
> 2013-05-06 13:33:02 CEST ERROR: must be superuser to switch transaction log files
> 2013-05-06 13:33:02 CEST STATEMENT: SELECT file_name from pg_xlogfile_name_offset(pg_switch_xlog())
> --8<---------------cut here---------------end--------------->8---
>
> So my question is:
> Can I establish postgresql role which is _not_ superuser and can execute
> above statement?

No, pg_switch_xlog() can only be executed by a superuser (see:
http://www.postgresql.org/docs/current/static/functions-admin.html )

The "createuser" example shown in the Amanda docs specifies the "-s" flag
for creating an Amanda backup user, which means the user will need to be
a superuser:

http://wiki.zmanda.com/index.php/How_To:Use_Amanda_to_Back_Up_PostgreSQL

I don't think the "replication" privilege is needed.

Regards

Ian Barwick