Re: Rearchitecting for storage
On Thu, Jul 18, 2019 at 10:09 PM Matthew Pounsett wrote: > That would likely keep the extra storage requirements small, but still > non-zero. Presumably the upgrade would be unnecessary if it could be done > without rewriting files. Is there any rule of thumb for making sure one has > enough space available for the upgrade? I suppose that would come down to > what exactly needs to get rewritten, in what order, etc., but the pg_upgrade > docs don't seem to have that detail. For example, since we've got an ~18TB > table (including its indices), if that needs to be rewritten then we're still > looking at requiring significant extra storage. Recent experience suggests > postgres won't necessarily do things in the most storage-efficient way.. we > just had a reindex on that database fail (in --single-user) because 17TB was > insufficient free storage for the db to grow into. > I've done a test on a virtual machine of mine, with the following three databases: one 0f 4.9 GB, one of 500 MB, one of 50 MB. I know this is not even close to your environment, however upgrading with pg_upgrade from 10.9 to 11.4 _without_ the link option ask for 85% of space. On a machine with a single database of 8.9 GB and a space occupation, as reported by df, of 64% (mean 46% available) I was able to upgrade from 10.9 to 11.4 without the link option. Space occupation increased of 90%. Using the link option on the same cluster required 1.1% of extra space (around 100 MB). Of course, these are poor-man results, but give you an advice on the space required by pg_ugprade (which seems to be less than 100% or 2x). Hope this helps. Luca
Re: How do I create a Backup Operator account ?
On 7/30/19 12:58 AM, Luca Ferrari wrote: On Tue, Jul 30, 2019 at 2:50 AM Marcos Aurelio Nobre wrote: I was wondering if it was possible to configure the pgAdmin4 menus to be available for a particular login, only Backup & Restore items. But I'm not sure how to associate a bank login account with a specific menu setting of a client tool. I think that hiding features from a GUI is a poor idea to protect your data (from what?). If you are talking of a single database (or a restricted set of), you can provide a role with the less privileges, like only SELECT and use such account to do the backup. But you need all the grants on another account to restore the backup. Anyway, I think you should rethink about your aim: what's the point of having a restricted user who can backup ALL the data? Luca, it is common some large Enterprise environments to have Operations staff that can run backups without being able to do anything else. For example, SQL Server has a per-database user mapping named db_backupoperator. -- Angular momentum makes the world go 'round.
Re: How do I create a Backup Operator account ?
On Tue, Jul 30, 2019 at 2:22 PM Ron wrote: > Luca, it is common some large Enterprise environments to have Operations > staff that can run backups without being able to do anything else. For > example, SQL Server has a per-database user mapping named db_backupoperator. Yes, but I think here we have to solve it with external tools, e.g., sudo. I see, however, an hard time configuring an account to execute only pg_dump without being able to "escape" into the database itself. Luca
Re: How do I create a Backup Operator account ?
I've also been thinking about this possibility. Create a Linux (S.O.) user account that has the ability to run pg_dump and pg_restore and enable this user account to be able to connect to a database - I believe this is possible through PG_HBA.CONF But I don't know how to implement this on Linux, nor how to write this entry in the pg_hba.conf file. Em ter, 30 de jul de 2019 às 10:22, Luca Ferrari escreveu: > On Tue, Jul 30, 2019 at 2:22 PM Ron wrote: > > Luca, it is common some large Enterprise environments to have Operations > > staff that can run backups without being able to do anything else. For > > example, SQL Server has a per-database user mapping named > db_backupoperator. > > > Yes, but I think here we have to solve it with external tools, e.g., > sudo. I see, however, an hard time configuring an account to execute > only pg_dump without being able to "escape" into the database itself. > > Luca > > >
Re: How do I create a Backup Operator account ?
On Wed, Jul 31, 2019 at 2:48 AM Marcos Aurelio Nobre wrote: > But I don't know how to implement this on Linux, nor how to write this entry > in the pg_hba.conf file. I would start with an entry in pg_hba.conf like the following: hostall pg_backup_usernamelocalhost md5 or hostall pg_backup_usernamelocalhost md5 The problem then comes on how to prevent the operating system user to run psql. If you are doing backup from a backup machine, one solution would be to remove the psql executable and leave the backup ones. Again, this is a poor practice to me. Even something like the following (untested) in /etc/sudoers will NOT prevent the user to access the database: User_Alias PGBACKUPUSERS = pg_backup_username Cmd_Alias PGBACKUP = /usr/local/bin/pg_dump, /usr/local/bin/pg_restore, ! /usr/local/bin/psql PGBACKUPUSERS backup_host = PGBACKUP because the user could use another client to inspect the database. And again, I don't see the point in not allowing an user to access the database but to be able to take a full backup. Therefore, I would go to revoke all write grants to such user and see if he can still do a backup. Luca
