[ale] Postgreq backup help

[Scott Plante]

You'll also need to look at the pg_hba.conf file in the PostgreSQL data directory. It's often in ~postgres/data or ~postgres/9.3/data (sub your version number) but can vary by distro. This file controls, who can login from what hosts using what kind of credentials. So to use the method below, you'd need a line allowing your host, or host range, to connect via password (md5). 


If you have your pg_dumpall command working locally, you may consider executing it remotely via ssh. You could create a dedicated user on the DB machine (say 'dbhost') with limited rights, but the ability to perform a pg_dumpall. Then you could do something like: 


ssh dbhost pg_dumpall (other params here) > dbhost.sql 


The dbhost.sql would be created on the local machine, not dbhost.sql. You could set up your ssh key without a passphrase so no prompt would occur. 

----- Original Message -----

From: "James Sumners" <james.sumners at gmail.com> 
To: "Atlanta Linux Enthusiasts" <ale at ale.org> 
Sent: Monday, September 28, 2015 10:46:14 AM 
Subject: Re: [ale] Postgreq backup help 





On Mon, Sep 28, 2015 at 10:35 AM, Beddingfield, Allen < allen at ua.edu > 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 

Any ideas? 


Wildcards work in pretty much any field. So put a '*' in the database field. 



-- 





James Sumners 
http://james.sumners.info/ (technical profile) 
http://jrfom.com/ (personal site) 
http://haplo.bandcamp.com/ (band page) 
_______________________________________________ 
Ale mailing list 
Ale at ale.org 
http://mail.ale.org/mailman/listinfo/ale 
See JOBS, ANNOUNCE and SCHOOLS lists at 
http://mail.ale.org/mailman/listinfo 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ale.org/pipermail/ale/attachments/20150928/a6c6383c/attachment.html>