[ale] Postgreq backup help

Beddingfield, Allen allen at ua.edu
Mon Sep 28 10:35:49 EDT 2015


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?

Thanks.
Allen B.
--
Allen Beddingfield
Systems Engineer
The University of Alabama
________________________________
From: ale-bounces at ale.org [ale-bounces at ale.org] on behalf of James Sumners [james.sumners at gmail.com]
Sent: Wednesday, September 23, 2015 4:39 PM
To: Atlanta Linux Enthusiasts
Subject: Re: [ale] Postgreq backup help

Or using a pgpass dotfile in the backup user's home directory. chmod appropriately.

On Wednesday, September 23, 2015, Robert Slaughter <robert.s.slaughter at gmail.com<mailto:robert.s.slaughter at gmail.com>> wrote:
You can avoid password prompting by setting the PGPASS environment variable to point to an appropriate file. Google for details I can't recall.


----
Bob Slaughter
http://www.facebook.com/robert.s.slaughter
"The price good men pay for indifference to public affairs is to be ruled by evil men." -- Plato
"All that is necessary for the triumph of evil is that good men do nothing." -- Edmund Burke
"The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing." -- Albert Einstein
"Nothing emboldens the wicked so greatly as the lack of courage on the part of the good." -- Pope Leo XIII, encyclical 'On Christians as Citizens'

On Wed, Sep 23, 2015 at 5:17 PM, Beddingfield, Allen <allen at ua.edu<UrlBlockedError.aspx>> wrote:
So, after 20 years of being a Unix/Linux admin, I've finally got a situation where I need to setup a Postgres server (aside from a few provided-with-the-product cases).  Anyway, I've got it all up and running with no issue, but I'm struggling with the best way to do a remote backup of the whole server.
In MySQL/MariaDB, I do:

"mysqldump --host=servername.domain -u username --password "plain text pw" --all-databases > filename.sql"

I need an equivalent for Postgres.  I've discovered that I can do "pg_dump -h servername.domain -U username -W databasename", that I'm prompted for the password, and it will dump out that database.

I've been experimenting with pg_dumpall, but it dumps a portion, prompts for the password, etc...

Suggestions/options here?
I have a script I use for MariaDB that dumps out an -all-databases backup, with a date-based naming convention, then handles a 30 day rotation of the backups.  I just want to plug in some pg_dump pg_dumpall into there and do the same.  I would prefer a full server backup that will capture users, grants, etc... so that I can put the entire server back from one backup.

Thanks.
Allen B.

--
Allen Beddingfield
Systems Engineer
The University of Alabama

_______________________________________________
Ale mailing list
Ale at ale.org<UrlBlockedError.aspx>
http://mail.ale.org/mailman/listinfo/ale
See JOBS, ANNOUNCE and SCHOOLS lists at
http://mail.ale.org/mailman/listinfo



--
James Sumners
http://james.sumners.info/ (technical profile)
http://jrfom.com/ (personal site)
http://haplo.bandcamp.com/ (band page)

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


More information about the Ale mailing list