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

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

Thank you.

Best Regards,
Robert

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Nov 12, 2016 at 2:31 PM, Adrian Klaver
<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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Adrian,

thank you for the answer.
There is one password for the postgres database and one for the postgres user.
Both are not working somehow. Is there a way to look up the passwords? I saw in the documentation that there is a .pgpass file. But I can't find it.

I changed the auth method to trust for all users. This worked for now.
Thank you.

Best Regards,
Robert

On 12 Nov 2016, at 23:31, Adrian Klaver <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

Thank you.

Best Regards,
Robert

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.
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 … ?

Thank you for your help.

Regards,
Robert

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

Hi Adrian,

thank you for the answer.
There is one password for the postgres database and one for the postgres user.

How are you determining this?

More to the point are you talking about the application(DaVinci Resolve)
or the database itself?

Both are not working somehow. Is there a way to look up the passwords? I saw in the documentation that there is a .pgpass file. But I can't find it.

That is optional and I sort of doubt the application using one.

I changed the auth method to trust for all users. This worked for now.
Thank you.

Best Regards,
Robert

On 12 Nov 2016, at 23:31, Adrian Klaver <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

Thank you.

Best Regards,
Robert

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Adrian,

I assume that the postgres database password is the one which is shown in the DaVinci connect database window.

user: postgres
password: DaVinci

But if I work in the Terminal and change the user "sudo su - postgres" I have to use my admin password.
From the DaVinci manual:

• Make sure you log in as a user with Admin privileges, then open the Terminal application, located in the /Applications/Utilities folder, and open up the postgres environment for editing by typing:

sudo su - postgres

• At the prompt, type that computer’s administrator password and press return.

• When the “postgres$” prompt appears, you’ll create a backup of the “pg_hba.conf” setup

le for safety by typing:

Therefore I assumed that the postgres user has the same password as my admin account. But maybe I am wrong with these assumptions.

That is optional and I sort of doubt the application using one.

You think DaVinci is not using any password … ?

Thank you.
Robert

Hi Adrian,

thank you for the explanation.
I will look into you links.

I am doing this because I want to make backups from the database. Ideally automatic backups every night.
The Blackmagic support says they can't help me with this. The Backup and Restore button in DaVinci does not work.
Every database related question I ask the Blackmagic support stays unanswered.
For example: How can I restart the SQL server? Oh sorry we can't help you with this … ?

Thank you.
Robert

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

I assume that the postgres database password is the one which is shown
in the DaVinci connect database window.

user: postgres
password: DaVinci

there are no database passwords, thats the password for the postgres
database role/user.

But if I work in the Terminal and change the user "sudo su - postgres"
I have to use my admin password.

sudo expects the password of the user running sudo. this has nothing to
do wih postgres.

That is optional and I sort of doubt the application using one.

You think DaVinci is not using any password … ?

he meant, he doubts DaVinci is using a .pgpass file. The .pgpass
file is another way to pass your passwords to postgres, but its NOT
where postgres stores its passwords, those are stored as hashes in the
pg_catalog.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Hi Adrian,

I assume that the postgres database password is the one which is shown
in the DaVinci connect database window.

user: postgres
password: DaVinci

But if I work in the Terminal and change the user "sudo su - postgres" I
have to use my admin password.

Two different users:

The first instance is the database user postgres

The second is the system user postgres.

When you run command line commands against the Postgres database and
they ask for the user and password that is the database user postgres
and its associated password, so when you do something like:

psql -d template1 -U postgres

the password you will be prompted for is DaVinci

Now the above also depends on what is set in pg_hba.conf for the various
combinations of host, database, user and auth_method. For more
information see:

https://www.postgresql.org/docs/9.5/static/client-authentication.html

From the DaVinci manual:

� Make sure you log in as a user with Admin privileges, then open the
Terminal application, located in the /Applications/Utilities folder,
and open up the postgres environment for editing by typing:

sudo su - postgres

� At the prompt, type that computer�s administrator password and press
return.

� When the �postgres$� prompt appears, you�ll create a backup of the
�pg_hba.conf� setup

le for safety by typing:

Therefore I assumed that the postgres user has the same password as my
admin account. But maybe I am wrong with these assumptions.

That is optional and I sort of doubt the application using one.

You think DaVinci is not using any password � ?

Thank you.
Robert

On 13 Nov 2016, at 17:57, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

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

Hi Adrian,

thank you for the answer.
There is one password for the postgres database and one for the
postgres user.

How are you determining this?

More to the point are you talking about the application(DaVinci
Resolve) or the database itself?

Both are not working somehow. Is there a way to look up the
passwords? I saw in the documentation that there is a .pgpass file.
But I can't find it.

That is optional and I sort of doubt the application using one.

I changed the auth method to trust for all users. This worked for now.
Thank you.

Best Regards,
Robert

On 12 Nov 2016, at 23:31, 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

Thank you.

Best Regards,
Robert

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/13/2016 09:04 AM, aws backup wrote:

Hi Adrian,

thank you for the explanation.
I will look into you links.

I am doing this because I want to make backups from the
database. Ideally automatic backups every night.
The Blackmagic support says they can't help me with this. The Backup and
Restore button in DaVinci does not work.
Every database related question I ask the Blackmagic support stays
unanswered.

Guessing they are treating the Postgres database as an embedded one that
should not be touched by the end user.

For example: How can I restart the SQL server? Oh sorry we can't help
you with this … ?

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

Thank you.
Robert

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/13/2016 9:13 AM, Adrian Klaver wrote:

For example: How can I restart the SQL server? Oh sorry we can't help
you with this … ?

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

the best way of doing this depends on the way postgres was installed,
and varies by OS/distribution. for instance, on RHEL/CentOS <= 6,

service postgresql-X.Y restart

on RHEL/CentOS 7

systemctl restart postgresql-X.Y

(both these run by root, or via sudo)

--
john r pierce, recycling bits in santa cruz

Hi,

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.
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 … ?

Best Regards,
Robert

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

On 11/13/2016 09:04 AM, aws backup wrote:

Hi Adrian,

thank you for the explanation.
I will look into you links.

I am doing this because I want to make backups from the
database. Ideally automatic backups every night.
The Blackmagic support says they can't help me with this. The Backup and
Restore button in DaVinci does not work.
Every database related question I ask the Blackmagic support stays
unanswered.

Guessing they are treating the Postgres database as an embedded one that should not be touched by the end user.

For example: How can I restart the SQL server? Oh sorry we can't help
you with this … ?

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

Thank you.
Robert

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/13/2016 01:01 PM, aws backup wrote:

Hi,

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.

Just remember that trust is just that, trust. If someone knows a valid
username they have access, subject to other conditions set in pg_hba.conf.

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 … ?

It does if you want to backup the state of the entire cluster. Could be
that DaVinci is including things in the template1 and postgres databases
that are needed. The other thing that pg_dumpall gets you is data global
to the cluster:

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

" ... pg_dumpall also dumps global objects that are common to all
databases. (pg_dump does not save these objects.) ..."

Though you can have your cake and eat it to by using pg_dump for
individual databases and then

pg_dumpall -g

where -g is:

"-g
--globals-only

Dump only global objects (roles and tablespaces), no databases.
"

Best Regards,
Robert

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

On 11/13/2016 09:04 AM, aws backup wrote:

Hi Adrian,

thank you for the explanation.
I will look into you links.

I am doing this because I want to make backups from the
database. Ideally automatic backups every night.
The Blackmagic support says they can't help me with this. The Backup and
Restore button in DaVinci does not work.
Every database related question I ask the Blackmagic support stays
unanswered.

Guessing they are treating the Postgres database as an embedded one that should not be touched by the end user.

For example: How can I restart the SQL server? Oh sorry we can't help
you with this … ?

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

Thank you.
Robert

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you, I understand. Nobody else can access the database.
As the database backup button in DaVinci started to work I could understand that DaVinci is only making pg_dump of the database. The restore works fine from any DaVinci installation to another one. I tested this. I still can make a automatic daily shell script with a pg_dumpall. Will see.

Thank you.

thank you.
it listens to the local network, which are my two other MacBooks. I reported the bug to DaVinci. Hopefully the have a fix then I can put it back on md5.

thank you for your script.
will try it.

best
Robert

On 13 Nov 2016, at 23:27, John R Pierce <pierce@hogranch.com> wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general