Thread: 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
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
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