Re: Backing up postgresql databases
От | Tim Frank |
---|---|
Тема | Re: Backing up postgresql databases |
Дата | |
Msg-id | 20010320.141132@cr625228-a.ktchnr1.on.wave.home.com обсуждение исходный текст |
Ответ на | Re: Backing up postgresql databases ("Peter Schuller" <peter.schuller@infidyne.com>) |
Список | pgsql-admin |
Peter, I agree with Tom that it isn't the best solution to have to store a user/pass in a cron script, but sometimes things have to be done for a greater purpose. I still have to fiddle with how local ident would work in my own situation, but the current solution I have is the following. Have your shell script do export PGUSER=username export PGPASSWORD=password before you run pg_dumpall in the same script. The user/pass would most likely have to be a superuser to have access to all databases (this is also not guaranteed depending on your pg_hba.conf). Make the script read/execute by root but not by anyone else and it will help a tiny bit with security. It's ugly but it works. This was taken from some previous suggestions on the mailing list to get around this same problem for other versions of pg_dumpall. I'm still hoping to find something better. Hope that helps. Tim Frank >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 19/03/01, 5:06:16 AM, peter.schuller@infidyne.com ("Peter Schuller") wrote regarding Re: [ADMIN] Backing up postgresql databases: > Hello, > > From what I understand (and I'm still fairly new to Postgres) you won't get > > a perfect copy while Postmaster is running, but there is a way to backup the > > entire database while it's running: pg_dumpall. > Ah, perfekt! And thanks to Grant for that bash script :) > I do have a question though. Contrary to Grant, I can't use a trust policy, > even on the local machine. As a result, I need to use password > authentication. However, pg_dumpall seems to generally screw up. > Wheather or not I do -u has no effect; in either case I get prompted to the > password (but not the username). And in either case, I get a buch of random > "Password:" type outputs at first (as if it's trying to authenticate > unsuccessfully several times) intermixed with the other output. When I > finally get the password prompt, I enter it and I seem to get logged in (as > postgres). Then I get another error (invalid script or sql query of some > sort it looks like). > Below is some example output; it looks really messy in general with a bunch > of failed authentications. Note that I didn't get to enter the password > until the very last Username:Password: > defiant:~$ /usr/lib/postgresql/bin/pg_dumpall -u > Password: psql: Password authentication failed for user 'postgres' > \connect template1 > select datdba into table tmp_pg_shadow from pg_database where datname > = 'template1'; > delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; > drop table tmp_pg_shadow; > copy pg_shadow from stdin; > Password: > psql: Password authentication failed for user 'postgres' > \. > delete from pg_group; > copy pg_group from stdin; > Password: > psql: Password authentication failed for user 'postgres' > \. > Password: Password: psql: Password authentication failed for user 'postgres' > \connect template1 > create database "postgres"; > \connect postgres > Username: Password: > Connection to database 'postgres' failed. > ERROR: Missing '=' after '31' in conninfo > pg_dump failed on postgres, exiting > Is there something obvious I'm doing wrong? > Thanks! > -- > / Peter Schuller, InfiDyne Technologies HB > PGP userID: 0x5584BD98 or 'Peter Schuller <peter.schuller@infidyne.com>' > Key retrival: Send an E-Mail to getpgpkey@scode.infidyne.com > E-Mail: peter.schuller@infidyne.com Web: http://scode.infidyne.com > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
В списке pgsql-admin по дате отправления: