[ale] Postgreq backup help

[Phil Turmel]

On 09/28/2015 10:35 AM, Beddingfield, Allen wrote:
> Okay, so I've gotten the .pgpass to work without any issue.
> The file is in ~/.pgpass, with permissions of 600
> 
> The file looks like:
> <hostname>.ua.edu:5432:<dbname>:<user>:<password>
> 
> I can do:
> pg_dump -h <hostname>.ua.edu -d <dbname> -U <user> -w
> 
> This works, dumping out that database, without prompting for a password.
> 
> I want to be able to do pg_dumpall (I want a one file backup to re-add
> users, databases, grants, etc...) - similar to a MySQL --all-databases
> backup.
> 
> When I try:
> pg_dumpall -h <hostname>.ua.edu -U <user> -w
> 
> I get:
> pg_dumpall: could not connect to database "template1": fe_sendauth: no
> password supplied

I don't use pg_dumpall myself, so I'm just going to offer an educated
guess:  your chosen user needs rights to all databases, including the
built-in templates.  Typically only the 'postgres' user has those rights.

If you really want to grab an entire instance for quick restore,
consider using the Point-In-Time-Recovery filesystem snapshot method.
With checkpoints and WAL logs, there's no downtime.  The pg_basebackup
tool is the simplest way to get it done.

http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP

Phil