FAQ
Hi,

I have a question concerning database backups. From what I've learned in
the Admin guide, simply letting our ADSM backup run over the files will
not provide a consistent backup of the databases, so I should use pg_dump
or pg_dumpall.

Ok, now since this is a multi-user box I've setup password authentication
in pg_hba.conf for all users. With this configuration I cannot run
pg_dumpall anymore, I tried to run it with "pg_dumpall -u" and I get:

[pgsql@gerda ~/data]$ pg_dumpall -u
--
-- pg_dumpall (7.1.2) -u
--
\connect template1
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');

Password:
psql: Password authentication failed for user 'pgsql'

DELETE FROM pg_group;

Password:
Password:

--
-- Database template1
--
\connect template1 pgsql
\connect template1 pgsql
Username: Password:
DELETE FROM pg_group;

Password:
Password:

--
-- Database template1
--
\connect template1 pgsql
\connect template1 pgsql
Username: Password:

Connection to database 'template1' failed.
ERROR: Missing '=' after 'pgsql' in conninfo

pg_dump failed on template1, exiting

It doesn't accept my password (although I'm sure I typed correctly), and
it doesn't dump anything. Using pg_dump works, but this is quite
inacceptable since I don't want to dump each database on its own.

Another question is how I should dump the db on a nightly cron job, since
it always asks for a password, and I found no way of supplying it except
by typing it in at the prompt (do I really have to work with expect?).

TIA,
regards,
le

--
Lukas Ertl eMail: l.ertl@univie.ac.at
WWW-Redaktion Tel.: (+43 1) 4277-14073
Zentraler Informatikdienst (ZID) Fax.: (+43 1) 4277-9140
der Universität Wien

Search Discussions

  • Tamsin at Jul 3, 2001 at 10:08 am
    to do our nightly cron jobs we have a script like this:

    PGUSER=postgres
    PGPASSWORD=password
    export PGUSER PGPASSWORD
    pg_dump databasename | gzip > databasename.bak.gz

    hth
    tamsin

    -----Original Message-----
    From: pgsql-novice-owner@postgresql.org
    On Behalf Of Lukas Ertl
    Sent: 03 July 2001 09:43
    To: pgsql-novice@postgresql.org
    Subject: [NOVICE] Backup question


    Hi,

    I have a question concerning database backups. From what I've learned in
    the Admin guide, simply letting our ADSM backup run over the files will
    not provide a consistent backup of the databases, so I should use pg_dump
    or pg_dumpall.

    Ok, now since this is a multi-user box I've setup password authentication
    in pg_hba.conf for all users. With this configuration I cannot run
    pg_dumpall anymore, I tried to run it with "pg_dumpall -u" and I get:

    [pgsql@gerda ~/data]$ pg_dumpall -u
    --
    -- pg_dumpall (7.1.2) -u
    --
    \connect template1
    DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
    WHERE datname = 'template0');

    Password:
    psql: Password authentication failed for user 'pgsql'

    DELETE FROM pg_group;

    Password:
    Password:

    --
    -- Database template1
    --
    \connect template1 pgsql
    \connect template1 pgsql
    Username: Password:
    DELETE FROM pg_group;

    Password:
    Password:

    --
    -- Database template1
    --
    \connect template1 pgsql
    \connect template1 pgsql
    Username: Password:

    Connection to database 'template1' failed.
    ERROR: Missing '=' after 'pgsql' in conninfo

    pg_dump failed on template1, exiting

    It doesn't accept my password (although I'm sure I typed correctly), and
    it doesn't dump anything. Using pg_dump works, but this is quite
    inacceptable since I don't want to dump each database on its own.

    Another question is how I should dump the db on a nightly cron job, since
    it always asks for a password, and I found no way of supplying it except
    by typing it in at the prompt (do I really have to work with expect?).

    TIA,
    regards,
    le

    --
    Lukas Ertl eMail: l.ertl@univie.ac.at
    WWW-Redaktion Tel.: (+43 1) 4277-14073
    Zentraler Informatikdienst (ZID) Fax.: (+43 1) 4277-9140
    der Universität Wien


    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postgresql.org so that your
    message can get through to the mailing list cleanly
  • Lukas Ertl at Jul 3, 2001 at 10:16 am

    On Tue, 3 Jul 2001, tamsin wrote:
    to do our nightly cron jobs we have a script like this:

    PGUSER=postgres
    PGPASSWORD=password
    export PGUSER PGPASSWORD
    pg_dump databasename | gzip > databasename.bak.gz
    Thanks, this points me into the right direction, I didn't knew about the
    environment vars PGUSER and PGPASSWORD.

    regards,
    le
    -----Original Message-----
    From: pgsql-novice-owner@postgresql.org
    On Behalf Of Lukas Ertl
    Sent: 03 July 2001 09:43
    To: pgsql-novice@postgresql.org
    Subject: [NOVICE] Backup question


    Hi,

    I have a question concerning database backups. From what I've learned in
    the Admin guide, simply letting our ADSM backup run over the files will
    not provide a consistent backup of the databases, so I should use pg_dump
    or pg_dumpall.

    Ok, now since this is a multi-user box I've setup password authentication
    in pg_hba.conf for all users. With this configuration I cannot run
    pg_dumpall anymore, I tried to run it with "pg_dumpall -u" and I get:

    [pgsql@gerda ~/data]$ pg_dumpall -u
    --
    -- pg_dumpall (7.1.2) -u
    --
    \connect template1
    DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
    WHERE datname = 'template0');

    Password:
    psql: Password authentication failed for user 'pgsql'

    DELETE FROM pg_group;

    Password:
    Password:

    --
    -- Database template1
    --
    \connect template1 pgsql
    \connect template1 pgsql
    Username: Password:
    DELETE FROM pg_group;

    Password:
    Password:

    --
    -- Database template1
    --
    \connect template1 pgsql
    \connect template1 pgsql
    Username: Password:

    Connection to database 'template1' failed.
    ERROR: Missing '=' after 'pgsql' in conninfo

    pg_dump failed on template1, exiting

    It doesn't accept my password (although I'm sure I typed correctly), and
    it doesn't dump anything. Using pg_dump works, but this is quite
    inacceptable since I don't want to dump each database on its own.

    Another question is how I should dump the db on a nightly cron job, since
    it always asks for a password, and I found no way of supplying it except
    by typing it in at the prompt (do I really have to work with expect?).

    TIA,
    regards,
    le
    --
    Lukas Ertl eMail: l.ertl@univie.ac.at
    WWW-Redaktion Tel.: (+43 1) 4277-14073
    Zentraler Informatikdienst (ZID) Fax.: (+43 1) 4277-9140
    der Universität Wien

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 3, '01 at 8:43a
activeJul 3, '01 at 10:16a
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Lukas Ertl: 2 posts Tamsin: 1 post

People

Translate

site design / logo © 2022 Grokbase