Thread: Scheduled back up

Scheduled back up

From
"Kallol Nandi"
Date:
How to go about scheduled backup in Postgresql.
What are the exact steps to be followed?
Does anyone know this?
 
Thanks and Regards,
Kallol.
 

Re: Scheduled back up

From
"Shridhar Daithankar"
Date:
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.


Re: Scheduled back up

From
"Viorel Dragomir"
Date:
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


Re: Scheduled back up

From
Tino Wildenhain
Date:
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)
>



Re: Scheduled back up

From
"Viorel Dragomir"
Date:
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)
> >
>
>


Re: Scheduled back up

From
Andrew Sullivan
Date:
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


Re: Scheduled back up

From
"Viorel Dragomir"
Date:
----- 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


Re: Scheduled back up

From
Tom Lane
Date:
"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

Re: Scheduled back up

From
"Viorel Dragomir"
Date:
----- 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


Re: Scheduled back up

From
Csaba Nagy
Date:
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.






Re: Scheduled back up

From
"Viorel Dragomir"
Date:
----- 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.

>
>
>
>
>


Re: Scheduled back up

From
Andrew Sullivan
Date:
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


Re: Scheduled back up

From
Tom Lane
Date:
"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

Re: Scheduled back up

From
Andrew Sullivan
Date:
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