Re: pg_dumpall: could not connect to database "template1": FATAL:

On 11/13/2016 1:01 PM, aws backup wrote:
> thank you so much.
> With your help I could solve all my problems.
> DaVinci has a bug somewhere with the database configuration.
> I installed everything new and set all auth method to trust instead of md5 which was default from DaVinci.

trust is not very secure as it means any process on your system has full
access to any database user including the postgres database
administrator. hopefully this database server only has
listen_addresses='localhost' and isn't listening on external network
interfaces, otherwsie trust means any machine on your network can access it.

> Now everything is working as expected. No errors anymore with the backup and restore buttons in DaVinci which are just linked to the pg_dump and pg_restore scripts.
> The pg_dumpall approach was from the example scripts which are posted in the DaVinci Forum.
> But this approach doesn't make sense … ?

on a Linux system, my pg backup script looks like...

#!/bin/bash
## postgres backup script
DD=$(date +%a)
PP=/backups/pgsql/

pg_dumpall -g -f $PP/pg_dumpall.$p.globals-$DD.sql
for db in $(psql -tc "select datname from pg_database where not
datistemplate"); do
pg_dump -Fc -f $PP/pgdump.$p.$db.$DD.dump -d $db
done

this generates a globals backup file, and a compressed backup from each
non-system database. these compressed backups are restorable with the
pg_restore command which has a lot of useful options (restore schema
only, or restore data only, etc etc). It also puts the day-of-the-week
in the filename of each of these backups (thats what the DD value is
for), so if you run this daily, you end up with 7 sets of files, one for
each day of the week. if you change %a to %F, then the filenames will
instead contain like 2016-11-13 ...

--
john r pierce, recycling bits in santa cruz