PostgreSQL Backup – Slow Performance Using gzip

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

Do you have a fast spindle or spindle set that is not part of the DB itself to back up to?

Or even a tape drive local to SQL ?

If backup space is snug, a tool like redgate sql backup will compress a backup. A 1TB database might be 100-300mb compressed. The backup is much faster also, because the amount being written is so much smaller (our backups of 0.5TB went from an hour to 19 mins).

The key is to have sufficient space, either local on the box or local on a fast LAN, so you can do a straight, single shot backup. The target should be a spindle set that is not used for SQL data or logs, or any other high IO operation (as the backup itself is heavy IO).

Additionally: If possible, take the db to single user, or read only state before running the backup. In some cases this can improve performance (by eliminating concurrent IO to the db itself during the backup).