I have a severe struggle with the pg_dumpall executable under Windows. I cannot make it work and I'm getting frustrated.

First of all some "environment data". I use the following software:

- Windows 7 Professional and Ultimate (i have the same problem on both OS's)
- Postgresql v9.0.6 (that sais psql (PostgreSQL) 9.0.6)

My goal is simple: Just backup all databases, all data, all users, roles, groups, restrictions, logins, etc. ... from the whole server.

The first thing I tried is to simply copy the whole ./data directory, since it's easy for me to shutdown the server, copy the files and startup

again the whole story. It was the thing, that makes the most sense for me: Then i'm sure i have everything. But i learned that here are 2

drawbacks for me:

1.) You MUST restore (copy back) the data directory to the exact same postgresql server version. Otherwise, the server seems to be unable to

start. I somewhere read it has something todo with file/folder security, but also after setting "everyone-full control" on the data directory I

was unable to start the server.

2.) Even if you restore the data back to a server with the exact same version number, there seems to be something "broken". E.g. I use NaviCat as

Frontend. It's perfectly possible to start the tool, connect to the server, also my own software i've written can select data from such a

"restored" server, but every time I want to select data via the Navicat UI or just want to watch the table definitions (so every time i access the

"definition data" of the database, i get an error). Also the postgres command line tools react the same way. The cannot access the "database

definition data" properly anymore.

These two major reasons leads me to use the pg_dumpall executable, since this should be the perfect way to backup a database server. After reading

a lot of how-to's and stuff like that I figured out the following command line statement:

C:\Program Files\PostgreSQL\9.0\bin>pg_dumpall.exe --clean --file=C:\Temp\pgbackup.file.sql --verbose --host=localhost --port=5432 --

username=postgresql

The first thing that occures is a password challenge.

"Passwort: "

I can enter whatever I want there for password, I always get the following error (sry in German, since my regional settings are set german):

pg_dumpall: konnte nicht mit der Datenbank »template1« verbinden: FATAL: Passwort-Authentifizierung für Benutzer »postgresql« fehlgeschlagen

Translation: pg_dumpall: cannot connect to database "template1": FATAL: Password-Authentication for user "postgresql" failed!

Okay, I'm not sure, if I give the correct user: I assumed that it is the user that was created during the windows-setup wizzard (in my case UID:

postgres PWD: postgresql)?

The the whole thing leads me to the "pgpass.conf" that contains the following data:
localhost:5432:*ostgresostgresql

It's located under:
C:\Users\test\AppData\Roaming\postgresql

(test is my username on this machine)

Still: There is no chance to get the backup working.

Then I thought I have to place it under the "postgres"-User directory maybe. I created the "postgresql" directory in the "C:\Users\postgres

\AppData\Roaming\postgresql" and copied the pgpass.conf into it.

But still: There is no chance to get the backup working.

Then I found something about a ph_hba.conf file, located in the C:\Program Files\PostgreSQL\9.0\data directory which seems to handle who can

access the server and how. I read something about "METHOD" and md5 in this file and thought that it is maybe better using the METHOD "password"

since this seems to be the way of submitting the password in plain-text. Now my config file shows the following configuration:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# IPv4 local connections:
#host all all 127.0.0.1/32 md5
host all all 127.0.0.1/32 password
# IPv6 local connections:
#host all all ::1/128 md5
host all all ::1/128 password

But guess what: pg_dumpall is still not working with the same error message.

After all these try's I still don't knwo, why the pg_dumpall will not work for me
Maybe you have the same issues and have a hint for me?
Best regards, Mike