Ergebnis 1 bis 2 von 2

Thema: pg_dumpall funktioniert nicht - egal wie ich's versuche

  1. #1
    mik
    mik ist offline Neuer Benutzer
    Registriert seit
    12.09.2011
    Beiträge
    6

    Unglücklich pg_dumpall funktioniert nicht - egal wie ich's versuche

    Hallo zusammen

    Sry, dass der Text auf English ist: Ich habe versucht mein Problem so gut wie es geht zu beschreiben und das ist "reflexartig" in English passiert.

    Vielleicht kann mir trotzdem jemand aus meinem Schmerz helfen



    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:

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


    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

  2. #2
    akretschmer ist gerade online Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von mik Beitrag anzeigen
    Hallo zusammen

    Sry, dass der Text auf English ist: Ich habe versucht mein Problem so gut wie es geht zu beschreiben und das ist "reflexartig" in English passiert.

    Vielleicht kann mir trotzdem jemand aus meinem Schmerz helfen
    Setz Dir einen passenden host-Eintrag für localhost und setze den auf TRUST. Fürs erste.


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

Ähnliche Themen

  1. REVOKE funktioniert nicht
    Von Hannes23 im Forum Benutzer- & Rechteverwaltung
    Antworten: 3
    Letzter Beitrag: 13.10.2010, 11:32
  2. copy funktioniert nicht
    Von karl_ha im Forum SQL
    Antworten: 3
    Letzter Beitrag: 28.07.2010, 12:18
  3. Problem INSERT INTO funktioniert nicht.
    Von 881362 im Forum SQL
    Antworten: 6
    Letzter Beitrag: 19.12.2008, 11:28
  4. Backup funktioniert nicht
    Von Paul Album im Forum PGAdmin
    Antworten: 6
    Letzter Beitrag: 27.04.2007, 20:55
  5. Antworten: 9
    Letzter Beitrag: 28.07.2006, 11:08

Stichworte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum