Re: pg_dumpall: could not connect to database "template1": FATAL: - Mailing list pgsql-general

On 13 Nov 2016, at 18:09, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 11/13/2016 05:51 AM, aws backup wrote:

Hi,

now I have another problem. Sorry I am an absolute beginner.
When I restore the dumpall backup with

"psql -f infile postgres"

I get lot of errors > "already exists" and the database is not restored
to the point of the backup.

Yes, because it seems you are restoring back over existing databases in the cluster. An explanation of terms is in order. When Postgres is installed it init(ialize)s a cluster of databases, template0, template1(the one you had issues with) and postgres. The user then can create additional databases to serve their needs. When you do pg_dumpall you are doing a plain text dump of the entire cluster, which may or may not be what you want. When you do the psql -f above you are trying to restore all the databases in the cluster to the cluster, again something you may or may not want. For finer grained control over the dump/restore cycle you probably want pg_dump.

I mean after I made the backup I changed something and I expected that
this change is undone after I restore the backup which did not happen.
Looks for me like nothing was restored … ?

You probably should spend some time here:

https://www.postgresql.org/docs/9.5/static/app-pg-dumpall.html

https://www.postgresql.org/docs/9.5/static/app-pgdump.html

In the meantime do you really want to start over with the database(s)?

In other words what is the goal of your dump/restore process?

Thank you for your help.

Regards,
Robert

On 13 Nov 2016, at 00:37, Scott Marlowe <scott.marlowe@gmail.com
<mailto:scott.marlowe@gmail.com>> wrote:

On Sat, Nov 12, 2016 at 2:31 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 11/12/2016 01:20 PM, aws backup wrote:

Hi,

I try to make pg_dumpall backups from a PostgreSQL 9.5 server which is
part of the DaVinci Resolve 12.5.3 App on a Mac OS X 10.11.6 system.

Unfortunately I get following failure message:

pg_dumpall: could not connect to database "template1": FATAL: password
authentication failed for user "postgres"

Maybe you can help me to solve this problem.

Two choices:

1) Determine what the password is for the postgres user and provide
it when
you connect.

2) If you have access to the pg_hba.conf file create a access line
that uses
trust as the auth method for user postgres connect that way.

https://www.postgresql.org/docs/9.5/static/auth-methods.html#AUTH-TRUST

OR you could use the -l switch and specify another db.

pg_dumpall -l mydbnamehere

-- 
Adrian Klaver
adrian.klaver@aklaver.com