I have deployed for my data base in production a new groups of users;

  1. An admin user with full access to the DB ;
  2. A read only user ;
  3. A super admin user to manage all the DBs ;

The matter is for the backup file or the data base save file, can i just use the read only user to save the DB or i shall use the super admin user to insure all data will be saved.

  • It mostly depends on which user has access to which table, either directly (as a result from some earlier GRANT commands, for example) or through roles assigned to them. It's basically very easy to test it yourself: create the dumps with the different users and compare them (directly or restoring the database under different names). – dezso May 4 '15 at 9:29
  • 1
    I have tested by myself all the cases and I have deduced the following : it is better to backups with the super admin, because it allows to recover all the informations related to the DB. By cons with a simple user with read_only role, it is possible at certain time this user does not have all necessary rights on all the tables or schemas at the time of the backup. – bilelovitch May 4 '15 at 14:51
  • There is a mention of something related at postgresql.org/docs/9.4/static/app-pgdump.html at the --no-owner option. – dezso May 4 '15 at 14:58

First think about what you want to achieve with a backup. To my humble opinion, a dump is not a backup good enough for DR. It can help restoring to a point in time but with a dump you can not recover the transactions that were created since the 'backup' was taken.

So if you just want an SQL dump that allows you to restore to a time in the past and ignores all transactions made between the dump time and the restore time, you can do that with a user that has read only access to those tables.

If you want a real backup, one that also enables you to recover the transactions made since the backup, use pg_backup to make a physical backup of the database and regularly make wall backups (there is a parameter for that "archive_command") and of course, your cluster needs archive_mode = ON, and wal_level to archive or replica (depends on version, replica for >= 9.6) This backup needs to be done by the database owner, often the postgres OS user.

Not the answer you're looking for? Browse other questions tagged or ask your own question.