Thread: Scheduled back up
How to go about scheduled backup in Postgresql.
What are the exact steps to be followed?
Does anyone know this?
Thanks and Regards,
Kallol.
On 18 Jul 2003 at 15:58, Kallol Nandi wrote: > How to go about scheduled backup in Postgresql. You need to use cron and pgdump. Man pages for both of them will give you what you want. Bye Shridhar -- Cohen's Law: There is no bottom to worse.
As i experienced with pg_dump it looks like in ver.7.3 requires interactive
enter password for *custom users*.
The 7.1 will not make this kind of problems.
Still in 7.3 you can make it with cron, but as i know, only with a script
which might look like this
<?php
exec("pg_dump -u [other options] db_name < /path_to/password_file.txt >
the_dumped_file");
?>
where password_file.txt contains:
user_name
user_password
good luck, and if u find that i already said is wrong please notify me
[2 days ago i've send [almost] the same problem and this is the only answer
that i got it]
----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: "Pgsql-General" <pgsql-general@postgresql.org>
Sent: Friday, July 18, 2003 1:35 PM
Subject: Re: [GENERAL] Scheduled back up
> On 18 Jul 2003 at 15:58, Kallol Nandi wrote:
> > How to go about scheduled backup in Postgresql.
>
> You need to use cron and pgdump. Man pages for both of them will give you
what
> you want.
>
> Bye
> Shridhar
>
> --
> Cohen's Law: There is no bottom to worse.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
Hi Viorel,
what are the exact circumstances for this? I'm not experiencing
that behavior. Maybe it depends on your settings in pg_hba.conf?
And are you using -X set-session-authorization and so on?
Regards
Tino Wildenhain
Viorel Dragomir wrote:
> As i experienced with pg_dump it looks like in ver.7.3 requires interactive
> enter password for *custom users*.
>
> The 7.1 will not make this kind of problems.
>
> Still in 7.3 you can make it with cron, but as i know, only with a script
> which might look like this
>
> <?php
> exec("pg_dump -u [other options] db_name < /path_to/password_file.txt >
> the_dumped_file");
> ?>
> where password_file.txt contains:
> user_name
> user_password
>
> good luck, and if u find that i already said is wrong please notify me
> [2 days ago i've send [almost] the same problem and this is the only answer
> that i got it]
>
> ----- Original Message -----
> From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
> To: "Pgsql-General" <pgsql-general@postgresql.org>
> Sent: Friday, July 18, 2003 1:35 PM
> Subject: Re: [GENERAL] Scheduled back up
>
>
>
>>On 18 Jul 2003 at 15:58, Kallol Nandi wrote:
>>
>>>How to go about scheduled backup in Postgresql.
>>
>>You need to use cron and pgdump. Man pages for both of them will give you
>
> what
>
>>you want.
>>
>>Bye
>> Shridhar
>>
>>--
>>Cohen's Law: There is no bottom to worse.
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Sincerely i dunno why this happens and after couple of emails received two
days ago I stoped searching why.
In any case I need it to dump for custom users their dbs data from a php
script.
And now it works, but if i gave the same cmd which run through exec the
pg_dump force me to introduce the user_name and password to continue.
I add some of my emails received about this story.
"Viorel Dragomir" <bc@vio.ro> writes:
> In 7.3.x the pg_dump accepts the password only from stdin. The user must
en=
> ter his password.
> It's a feature or a bug, because i can't see any logic in this behaviour.
It's a feature ... or at least an intentional change.
If you want to keep your password in a file, see the ~/.pgpass feature.
regards, tom lane
----------------------------------------------------------------------------
-------
Viorel Dragomir wrote:
> In 7.1.2 you can launch this command without any problems:
> pg_dump -u -s database < file.txt
>
> where file.txt contains 2 lines:
> username
> password
> ~
> ~
> ~
>
> And you'll get the dump of the <database>.
>
> In 7.1.3 this command work, the only incovenience is that pg_dump
> shows "User name: Password:"
> but gets the password from file.
>
> In 7.3.x the pg_dump accepts the password only from stdin. The user
> must enter his password.
> It's a feature or a bug, because i can't see any logic in this behaviour.
It's a feature :-)
The logic is that it is insecure to keep passwords in files.
If you are not concerned about the security in this case, you can switch
to ident or trust identification. That won't prompt you for the password.
Dima.
----- Original Message -----
From: "Tino Wildenhain" <tino@wildenhain.de>
To: "Viorel Dragomir" <bc@vio.ro>
Cc: <shridhar_daithankar@persistent.co.in>; "Pgsql-General"
<pgsql-general@postgresql.org>
Sent: Friday, July 18, 2003 4:23 PM
Subject: Re: [GENERAL] Scheduled back up
> Hi Viorel,
>
> what are the exact circumstances for this? I'm not experiencing
> that behavior. Maybe it depends on your settings in pg_hba.conf?
> And are you using -X set-session-authorization and so on?
>
> Regards
> Tino Wildenhain
>
> Viorel Dragomir wrote:
> > As i experienced with pg_dump it looks like in ver.7.3 requires
interactive
> > enter password for *custom users*.
> >
> > The 7.1 will not make this kind of problems.
> >
> > Still in 7.3 you can make it with cron, but as i know, only with a
script
> > which might look like this
> >
> > <?php
> > exec("pg_dump -u [other options] db_name <
/path_to/password_file.txt >
> > the_dumped_file");
> > ?>
> > where password_file.txt contains:
> > user_name
> > user_password
> >
> > good luck, and if u find that i already said is wrong please notify me
> > [2 days ago i've send [almost] the same problem and this is the only
answer
> > that i got it]
> >
> > ----- Original Message -----
> > From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
> > To: "Pgsql-General" <pgsql-general@postgresql.org>
> > Sent: Friday, July 18, 2003 1:35 PM
> > Subject: Re: [GENERAL] Scheduled back up
> >
> >
> >
> >>On 18 Jul 2003 at 15:58, Kallol Nandi wrote:
> >>
> >>>How to go about scheduled backup in Postgresql.
> >>
> >>You need to use cron and pgdump. Man pages for both of them will give
you
> >
> > what
> >
> >>you want.
> >>
> >>Bye
> >> Shridhar
> >>
> >>--
> >>Cohen's Law: There is no bottom to worse.
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 6: Have you searched our list archives?
> >>
> >> http://archives.postgresql.org
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
On Fri, Jul 18, 2003 at 01:39:19PM +0300, Viorel Dragomir wrote:
> As i experienced with pg_dump it looks like in ver.7.3 requires interactive
> enter password for *custom users*.
I don't know what a custom user is, but if you put the password in
~/.pgpass, authentication happens automatically. That's a new
feature in 7.3.x.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
----- Original Message ----- From: "Andrew Sullivan" <andrew@libertyrms.info> To: "Pgsql-General" <pgsql-general@postgresql.org> Sent: Friday, July 18, 2003 2:45 PM Subject: Re: [GENERAL] Scheduled back up > On Fri, Jul 18, 2003 at 01:39:19PM +0300, Viorel Dragomir wrote: > > As i experienced with pg_dump it looks like in ver.7.3 requires interactive > > enter password for *custom users*. > > I don't know what a custom user is, but if you put the password in > ~/.pgpass, authentication happens automatically. That's a new > feature in 7.3.x. for user *user_name* ~/.pgpass will be kind of /home/user_name/.pgpass ? But I can't do that, the users that are granted to use the database don't have any user id on that machine, so no /home/user_name directory either. I'm sorry for *custom users*. The project it's kind of cpanel. A user can create and grant access for his databases. And thx to pg_dump he can export & import databases. So any user that have a db might want to export his data through his web interface. And I tested pg_dump with his options on command line and I couldn't get the password from a file, accepted only from stdin. Thx for infos vioss > > A > > > -- > ---- > Andrew Sullivan 204-4141 Yonge Street > Liberty RMS Toronto, Ontario Canada > <andrew@libertyrms.info> M2P 2A8 > +1 416 646 3304 x110 > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend
"Viorel Dragomir" <bc@vio.ro> writes:
>> I don't know what a custom user is, but if you put the password in
>> ~/.pgpass, authentication happens automatically. That's a new
>> feature in 7.3.x.
> But I can't do that, the users that are granted to use the database don't
> have any user id on that machine,
> so no /home/user_name directory either.
No, .pgpass is sought in the home directory of the user running pg_dump
(or any other client program). It's not a server-side file.
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Viorel Dragomir" <bc@vio.ro> Cc: "Andrew Sullivan" <andrew@libertyrms.info>; "Pgsql-General" <pgsql-general@postgresql.org> Sent: Friday, July 18, 2003 4:41 PM Subject: Re: [GENERAL] Scheduled back up > "Viorel Dragomir" <bc@vio.ro> writes: > >> I don't know what a custom user is, but if you put the password in > >> ~/.pgpass, authentication happens automatically. That's a new > >> feature in 7.3.x. > > > But I can't do that, the users that are granted to use the database don't > > have any user id on that machine, > > so no /home/user_name directory either. > > No, .pgpass is sought in the home directory of the user running pg_dump > (or any other client program). It's not a server-side file. In my case the user is apache. I dunno for sure but the apache doesn't have a home directory. I try to find information about pgpass but still i dunno what pgpass should contain. a) only one password - of the ~/ user b) username:password > > regards, tom lane
On Fri, 2003-07-18 at 15:49, Viorel Dragomir wrote: > > No, .pgpass is sought in the home directory of the user running pg_dump > > (or any other client program). It's not a server-side file. > > In my case the user is apache. > I dunno for sure but the apache doesn't have a home directory. > If you have root access to the box, you can fix that by assigning a home directory to the apache user in the /etc/passwd file. Just make sure the shell assigned to the apache user is invalid, so nobody can log in as apache user. Cheers, Csaba.
----- Original Message ----- From: "Csaba Nagy" <nagy@ecircle-ag.com> To: "Viorel Dragomir" <bc@vio.ro> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Andrew Sullivan" <andrew@libertyrms.info>; "Pgsql-General" <pgsql-general@postgresql.org> Sent: Friday, July 18, 2003 5:03 PM Subject: Re: [GENERAL] Scheduled back up > On Fri, 2003-07-18 at 15:49, Viorel Dragomir wrote: > > > No, .pgpass is sought in the home directory of the user running pg_dump > > > (or any other client program). It's not a server-side file. > > > > In my case the user is apache. > > I dunno for sure but the apache doesn't have a home directory. > > > If you have root access to the box, you can fix that by assigning a home > directory to the apache user in the /etc/passwd file. Just make sure the > shell assigned to the apache user is invalid, so nobody can log in as > apache user. Thanks for information. But this is not really a problem because apache doesn't have any grants on database. It launches a script that connects to db with a diff username [each username has a unique password]. So if it has only one password on .pgpass only one user can log in. > > > > >
On Fri, Jul 18, 2003 at 02:56:53PM +0300, Viorel Dragomir wrote:
>
> I'm sorry for *custom users*.
> The project it's kind of cpanel.
> A user can create and grant access for his databases.
> And thx to pg_dump he can export & import databases.
> So any user that have a db might want to export his data through his web
> interface.
Hmm. Well, you could use a super-user account to do the dump. It
would have access to everything, and would only need one password.
I'm wondering about security implications of that, however.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
"Viorel Dragomir" <bc@vio.ro> writes: > But this is not really a problem because apache doesn't have any grants on > database. > It launches a script that connects to db with a diff username [each username > has a unique password]. > So if it has only one password on .pgpass only one user can log in. Not so at all. Read the documentation about .pgpass. http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=libpq-files.html regards, tom lane
On Fri, Jul 18, 2003 at 10:28:16AM -0400, Tom Lane wrote: > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=libpq-files.html Note there's a mistake in that documentation: achieve this by the command chmod 0600 .pgaccess. Of couse, chmoding .pgaccess when you're trying to set the permissions on .pgpass won't help. (The development version has the mistake fixed.) A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110