Hello,
I am using Postgresql 9.5 and I have created backup_admin user and created dba_admin ROLE with SUPERUSER and REPLICATION ,after that GRANT dba_admin role to backup_admin user and executed pg_basebakup utility with backup_admin user.
But I am not able to use the pg_basebackup utility using backup_admin user and got below FATAL.
pg_basebackup: could not connect to server: FATAL: must be superuser or replication role to start walsender
However I have observed only issue with backup_admin user to use pg_basebackup utility.
Please help me to understand why pg_basebackup is throwing FATAL when i use backup_admin?.
Is there any limitation with pg_basebackup utility ?
The process i am following for backup_admin user :
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)
postgres=#
postgres=# create user backup_admin password 'XXXXX';
CREATE ROLE
postgres=# create role dba_admin SUPERUSER REPLICATION;
CREATE ROLE
postgres=# grant dba_admin to backup_admin;
GRANT ROLE
postgres=# alter user backup_admin set role to dba_admin;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
------------------+------------------------------------------------------------+--------------------
backup_admin | | {dba_admin}
dba_admin | Superuser, Cannot login, Replication | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
[postgres@pgserver ~]$ mkdir online_backups1
[postgres@pgserver ~]$ /opt/PostgreSQL/9.5/bin/pg_basebackup --format=t --pgdata=online_backups1 -p 5432 -U backup_admin -x -z --verbose
pg_basebackup: could not connect to server: FATAL: must be superuser or replication role to start walsender
Please help me why pg_basebackup is throwing FATAL when i use backup_admin?.
Is there any limitation in pg_basebackup utility ?
For information the pg_basebackup is working fine for Postgres user and it is successful.
[postgres@pgserver ~]$ /opt/PostgreSQL/9.5/bin/pg_basebackup --format=t --pgdata=online_backups -p 5432 -U postgres -x -z --verbose
transaction log start point: 0/2000028 on timeline 1
transaction log end point: 0/2000130
pg_basebackup: base backup completed
Thanks,
Chiru