Обсуждение: Help automate pg_dump
We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to script the pg_dump routine. We use this tool for backup, but can not get around the requirement for username/password to be interactive. We require login from the localhost, and can not revert to trusted connections. If the username/password must be interactive, how can I script backup of the server? Evan,
Hmmm. Please let the list know if you get a solution, I am about to go to 7.2 on my production machines but that could be a show stopper if the cron job cannot automatically back it up anymore. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of > nothanks@nowhere.com > Sent: Monday, June 17, 2002 10:01 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Help automate pg_dump > > > We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to > script the pg_dump routine. We use this tool for backup, but can not > get around the requirement for username/password to be interactive. > > We require login from the localhost, and can not revert to trusted > connections. > > If the username/password must be interactive, how can I script backup > of the server? > > Evan, > > ---------------------------(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 >
Your best bet is probably some combination of the following: run your backups from a different machine, setting that ip up with a trusted connection in pg_hba.conf. (You might be able to do this locally and force connection over a tcpip socket if you can be sure your users can only use unix sockets, but it would be better to use a separate machine) (see http://www.postgresql.org/idocs/index.php?client-authentication.html#PG-HBA-CONF) try using the ident authentication type in pg_hba.conf, which (iirc) verifies the attempted postgres username as matching the unix user name (unless your giving everyone access to the postgres unix user, which is a bad idea(tm). (see http://www.postgresql.org/idocs/index.php?auth-methods.html#AEN16445) set the environment variable PGPASSWORD at the shell level. psql should use this variable for any prompting done by the database, but be forewarned that this method has its own security issues. (see http://www.postgresql.org/idocs/index.php?libpq-envars.html) create a "backups" user that only has read rights to the database and use that user for the sole purpose of backups. (http://www.postgresql.org/idocs/index.php?user-manag.html) hope this helps, Robert Treat On Mon, 2002-06-17 at 22:01, nothanks@nowhere.com wrote: > We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to > script the pg_dump routine. We use this tool for backup, but can not > get around the requirement for username/password to be interactive. > > We require login from the localhost, and can not revert to trusted > connections. > > If the username/password must be interactive, how can I script backup > of the server? > > Evan, > > ---------------------------(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
Hello, Evan, We use RedHat 7.2 Do an su to (or login as) postgres and create a cron job that runs your backup script. When cron runs the job it assumesthe postgres user ID and runs the job, hence no username or password needed. Note, it maybe necessary to re-establishposgtres' rights locally if they have been modified from the original install. Good luck. ======= At 2002-06-18, 02:01:00 you wrote: ======= >We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to >script the pg_dump routine. We use this tool for backup, but can not >get around the requirement for username/password to be interactive. > >We require login from the localhost, and can not revert to trusted >connections. > >If the username/password must be interactive, how can I script backup >of the server? > >Evan, Best regards. William Meloney bmeloney@mindspring.com 2002-06-18
> Please let the list know if you get a solution, I am about to go to 7.2 on > my production machines but that could be a show stopper if the cron job > cannot automatically back it up anymore. You might try this with expect (customizing necessary): #!/usr/bin/expect -f # wrapper to make passwd(1) be non-interactive # username is passed as 1st arg, passwd as 2nd set password xxx spawn /usr/bin/psql -d db expect "password:" sleep 1 send "$password\r" expect "password:" sleep 1 send "$password\r" expect eof Greetings, Bjoern
Hi all,
Below is what I have written to the list previously. All you need is to
install expect to your machine, if you do not have it.
****************
1. /usr/sbin/createdbbackups.sh
pg_dump dbname_1 > dbname_1.pgdump -p 5434 -u;
pg_dump dbname_2 > dbname_2.pgdump -p 5434 -u;
...
In this file, you should write the name of the databases that you want to
get the dump of.
Let's say you have 4 databases to backup.
2. /usr/sbin/dbbackup
#!/usr/bin/expect -f
set env(SHELL) /bin/sh
set env(HOME) /usr/sbin/
spawn /usr/sbin/createdbbackups.sh
expect 'User name':
send postgres\r
expect Password:
send PostgreSQL_Passwd\r
In this file, you'll write the last four lines 4 times (assuming that you
have 4 databases in your system.) Each 4 line must correspond the
databases listed in the first file.
I mean, if dbname_1 belongs to postgres user, that write postgres and its
password on the first line. If dbname_2 belongs to (let's say) surojit
user, then write suroojit and ist password there:
expect 'User name':
send surojit\r
expect Password:
send Passwd_Of_surojit\r
At the very end of this file, add the following:
spawn /usr/sbin/movedbbackups.sh
3. /usr/sbin/movedbbackups.sh
mv dbname_1.pgdump /backup/`date '+%d-%m-%Y'`;
mv dbname_2.pgdump /backup/`date '+%d-%m-%Y'`;
...
This is for moving the dumps in a daily directory.
(TO create the directory, you could use
cd /backup;
mkdir `date '+%d-%m-%Y'`
)
***************************
Regards,
Devrim
On Tue, 18 Jun 2002 terry@greatgulfhomes.com wrote:
> Hmmm.
>
> Please let the list know if you get a solution, I am about to go to 7.2 on
> my production machines but that could be a show stopper if the cron job
> cannot automatically back it up anymore.
>
> Terry Fielder
> Network Engineer
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> > nothanks@nowhere.com
> > Sent: Monday, June 17, 2002 10:01 PM
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] Help automate pg_dump
> >
> >
> > We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> > script the pg_dump routine. We use this tool for backup, but can not
> > get around the requirement for username/password to be interactive.
> >
> > We require login from the localhost, and can not revert to trusted
> > connections.
> >
> > If the username/password must be interactive, how can I script backup
> > of the server?
> >
> > Evan,
> >
> > ---------------------------(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
> >
>
>
> ---------------------------(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
>
--
Devrim GUNDUZ
devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr
Web : http://devrim.oper.metu.edu.tr
-------------------------------------
terry@greatgulfhomes.com wrote: > Hmmm. > > Please let the list know if you get a solution, I am about to go to 7.2 on > my production machines but that could be a show stopper if the cron job > cannot automatically back it up anymore. > > Terry Fielder > Network Engineer > Great Gulf Homes / Ashton Woods Homes > terry@greatgulfhomes.com > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of > > nothanks@nowhere.com > > Sent: Monday, June 17, 2002 10:01 PM > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] Help automate pg_dump > > > > > > We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to > > script the pg_dump routine. We use this tool for backup, but can not > > get around the requirement for username/password to be interactive. > > > > We require login from the localhost, and can not revert to trusted > > connections. > > > > If the username/password must be interactive, how can I script backup > > of the server? > > > > Evan, > > > > Could you not use expect--it is designed for that sort of problem. Don p.s. Any help on my point-polygon problem (yesterday's postings) would be appreciated.
On Tue, Jun 18, 2002 at 02:01:20AM +0000, nothanks@nowhere.com wrote:
> We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> script the pg_dump routine. We use this tool for backup, but can not
> get around the requirement for username/password to be interactive.
>
> We require login from the localhost, and can not revert to trusted
> connections.
Could you use ident? Or ident just for the user you want to use for
backups?
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
in the crontab of postgres user you could use
0 6 * * * ( cd /appl_logs/postgresql/ && /opt/pgsql/pg_backup.sh -db
dbname )
and pg_backup.sh could be like this
#!/bin/bash
#
# pg_backup.sh
#
# Database backup
#
# Parametres :
# -db <db name>
#
# a file (with the following format <db name>.<date>-<hour>.dump.gz) will be
created in the current
# directory
#
usage()
{
echo "`basename $0` -db <db name>"
exit 1
}
if [ "$1" != "-db" ]; then
usage
fi
DBNAME="$2"
DATE=`date +%Y%m%d`
TIME=`date +%H%M`
SAV_DB_FILENAME="$DBNAME.$DATE-$TIME.dump.gz"
~postgres/bin/pg_dump $DBNAME | gzip -c9 >$SAV_DB_FILENAME
-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de
terry@greatgulfhomes.com
Envoyé : mardi 18 juin 2002 15:24
À : nothanks@nowhere.com; pgsql-general@postgresql.org
Objet : Re: [GENERAL] Help automate pg_dump
Hmmm.
Please let the list know if you get a solution, I am about to go to 7.2 on
my production machines but that could be a show stopper if the cron job
cannot automatically back it up anymore.
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> nothanks@nowhere.com
> Sent: Monday, June 17, 2002 10:01 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Help automate pg_dump
>
>
> We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> script the pg_dump routine. We use this tool for backup, but can not
> get around the requirement for username/password to be interactive.
>
> We require login from the localhost, and can not revert to trusted
> connections.
>
> If the username/password must be interactive, how can I script backup
> of the server?
>
> Evan,
>
> ---------------------------(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
>
---------------------------(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
>>>>> "n" == nothanks <nothanks@nowhere.com> writes: n> If the username/password must be interactive, how can I script backup n> of the server? It honors the standard environment variables for user/password, so just set them. And make sure your script is not readable by anyone else, or your security is moot. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
On Tue, 2002-06-18 at 18:48, Vivek Khera wrote: > >>>>> "n" == nothanks <nothanks@nowhere.com> writes: > > n> If the username/password must be interactive, how can I script backup > n> of the server? > > It honors the standard environment variables for user/password, so > just set them. And make sure your script is not readable by anyone > else, or your security is moot. Actually, if you're using environment variables to preset usernames and passwords your security is moot. Try " ps exwww " and see the environment variables of all processes Cheers, Tycho -- Tycho Fruru tycho@fruru.com "Prediction is extremely difficult. Especially about the future." - Niels Bohr
Вложения
On Wed, Jun 19, 2002 at 12:32:29PM +0200, Tycho Fruru wrote: > On Tue, 2002-06-18 at 18:48, Vivek Khera wrote: > > >>>>> "n" == nothanks <nothanks@nowhere.com> writes: > > > > n> If the username/password must be interactive, how can I script backup > > n> of the server? > > > > It honors the standard environment variables for user/password, so > > just set them. And make sure your script is not readable by anyone > > else, or your security is moot. > > Actually, if you're using environment variables to preset usernames and > passwords your security is moot. > > Try " ps exwww " and see the environment variables of all processes Hmm, on linux this only shows the environment for your own processes. Does this work differently on other systems? $ cat /proc/1/environ cat: /proc/1/environ: Permission denied and if you're root, who cares? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Wed, 2002-06-19 at 12:42, Martijn van Oosterhout wrote: > On Wed, Jun 19, 2002 at 12:32:29PM +0200, Tycho Fruru wrote: > > Try " ps exwww " and see the environment variables of all processes > > Hmm, on linux this only shows the environment for your own processes. Does > this work differently on other systems? > > $ cat /proc/1/environ > cat: /proc/1/environ: Permission denied > > and if you're root, who cares? I tried this a long time ago (perhaps too long ago :-) with success. (checking different versions) It does the right thing (limit access) at least since 2.2.0 Looking at the source I think that in the 1.2 series it was still a problem (ie. you could see other user's environments) I'm growing old too fast. T. -- Tycho Fruru tycho@fruru.com "Prediction is extremely difficult. Especially about the future." - Niels Bohr
Вложения
On 18 Jun 2002 12:48:15 -0400, Vivek Khera <khera@kcilink.com> wrote: >>>>>> "n" == nothanks <nothanks@nowhere.com> writes: > >n> If the username/password must be interactive, how can I script backup >n> of the server? > >It honors the standard environment variables for user/password, so >just set them. And make sure your script is not readable by anyone >else, or your security is moot. We, that might be the case, and I thank you for your answer, however, on some platforms, the ps command can be used to view processes and thier environment variables, making this solution quite insecure. Perhaps another solution should be investigated by the postgres team. Thanks again. Evan