Thread

1. Backup question

   Lukas Ertl <l.ertl@univie.ac.at> — 2001-07-03T08:43:03Z

   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
   
   
   

2. RE: Backup question

   tamsin <tg_mail@bryncadfan.co.uk> — 2001-07-03T10:07:55Z

   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
   [mailto: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
   
   
   

3. RE: Backup question

   Lukas Ertl <l.ertl@univie.ac.at> — 2001-07-03T10:16:25Z

   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
   > [mailto: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