Thread: database role for backups?

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?
KJ

PS. does news.postgresql.org work? I got 'unknown host'


--
http://blogdebart.pl/2012/06/24/hiena/
Pauca sed matura.
    [Few but excellent.]
        -- Gauss

2013/5/7 Kamil Jońca <kjonca@o2.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

Kamil Jońca wrote:

> PS. does news.postgresql.org work? I got 'unknown host'

Not anymore.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services