Обсуждение: Create Linux Script for PostgreSQL database backup

Поиск
Список
Период
Сортировка

Create Linux Script for PostgreSQL database backup

От
Lawrence C
Дата:
Hi All,

Anyone good in writing Linux scripting for backup
PostgreSQL. I just required a simple scripts only.

Normally we do the manual ways as following:

In root

root# su – postgres
bash# pgdumpall >  backup.sql
bash# exit
root#

I’m planning to write script.sh for cronjob but not
too sure about it. Can anyone contribute the scripts
for me?

Thanks  in advance


________________________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html

Re: Create Linux Script for PostgreSQL database backup

От
tim hall
Дата:
Lawrence C,
On Wednesday 01 September 2004 14:20, you went:
> Anyone good in writing Linux scripting for backup
> PostgreSQL. I just required a simple scripts only.
>
> Normally we do the manual ways as following:
>
> In root
>
> root# su - postgres
> bash# pgdumpall >  backup.sql
> bash# exit
> root#
>
> I'm planning to write script.sh for cronjob but not
> too sure about it. Can anyone contribute the scripts
> for me?

Er ...

#! /usr/bin/bash
pgdumpall >  backup.sql

I'm being cheeky, but surely if you set that up as a cronjob for user
'postgres' that would do it? Are your needs more complex than this?

cheers

tim hall

Re: Create Linux Script for PostgreSQL database backup

От
Kaolin Fire
Дата:
For that matter:

echo "1   3   *   *   *   postgres   /usr/local/bin/pg_dumpall >
~/backup.sql" >> /etc/crontab

note to remember the two >>'s so you don't obliterate your crontab like
I just did.

:ulp:

-kaolin

tim hall wrote:

>Lawrence C,
>On Wednesday 01 September 2004 14:20, you went:
>
>
>>Anyone good in writing Linux scripting for backup
>>PostgreSQL. I just required a simple scripts only.
>>
>>Normally we do the manual ways as following:
>>
>>In root
>>
>>root# su - postgres
>>bash# pgdumpall >  backup.sql
>>bash# exit
>>root#
>>
>>I'm planning to write script.sh for cronjob but not
>>too sure about it. Can anyone contribute the scripts
>>for me?
>>
>>
>
>Er ...
>
>#! /usr/bin/bash
>pgdumpall >  backup.sql
>
>I'm being cheeky, but surely if you set that up as a cronjob for user
>'postgres' that would do it? Are your needs more complex than this?
>
>cheers
>
>tim hall
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>


Re: Create Linux Script for PostgreSQL database backup

От
Geoffrey
Дата:
Kaolin Fire wrote:
> For that matter:
>
> echo "1   3   *   *   *   postgres   /usr/local/bin/pg_dumpall >
> ~/backup.sql" >> /etc/crontab
>
> note to remember the two >>'s so you don't obliterate your crontab like
> I just did.

Unless things have changed, and they might have, you have to tell cron
to reread the crontab file for it to process your addition.  Your better
solution is to use the tools designed for editing crontab, such as
'crontab -e.'  If you use this tool, crontab will automagically reread
the file.

--
Until later, Geoffrey       Registered Linux User #108567
                             AT&T Certified UNIX System Programmer - 1995

Re: Create Linux Script for PostgreSQL database backup

От
Sandro Garoffolo
Дата:

Geoffrey wrote:

> Kaolin Fire wrote:
>
>> For that matter:
>>
>> echo "1   3   *   *   *   postgres   /usr/local/bin/pg_dumpall >
>> ~/backup.sql" >> /etc/crontab
>>
>> note to remember the two >>'s so you don't obliterate your crontab
>> like I just did.
>
>
> Unless things have changed, and they might have, you have to tell cron
> to reread the crontab file for it to process your addition.  Your
> better solution is to use the tools designed for editing crontab, such
> as 'crontab -e.'  If you use this tool, crontab will automagically
> reread the file.

One question , if you set  access to server with password in pg_hba.conf
how can you pass the apssword in the script?



_____________________________________________________________________
For your security, this mail has been scanned and protected by Inflex

Re: Create Linux Script for PostgreSQL database backup

От
Christopher Browne
Дата:
A long time ago, in a galaxy far, far away, sandro.garoffolo@unito.it (Sandro Garoffolo) wrote:
> One question , if you set  access to server with password in
> pg_hba.conf how can you pass the apssword in the script?

You don't have to if you put it in $HOME/.pgpass; see the
documentation for the format of that file...
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/sap.html
What would the world be like with no hypothetical situations?

Re: Create Linux Script for PostgreSQL database backup

От
Naomi Walker
Дата:
Christopher Browne wrote:
A long time ago, in a galaxy far, far away, sandro.garoffolo@unito.it (Sandro Garoffolo) wrote: 
One question , if you set  access to server with password in
pg_hba.conf how can you pass the apssword in the script?   
You don't have to if you put it in $HOME/.pgpass; see the
documentation for the format of that file... 
True, but I see that as a *big* security hole.

-- 
-------------------------------------------------------------------------
Naomi Walker                          Chief Information Officer
Eldorado Computing, Inc.              nwalker@eldocomp.com 602-604-3100 
-------------------------------------------------------------------------
Freedom is not worth having if it does not include the freedom to make
mistakes.      -Mahatma Gandhi
------------------------------------------------------------------------- 

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.

 

Re: Create Linux Script for PostgreSQL database backup

От
Tom Lane
Дата:
Naomi Walker <nwalke@eldocomp.com> writes:
> Christopher Browne wrote:
>>> One question , if you set  access to server with password in
>>> pg_hba.conf how can you pass the apssword in the script?
>>
>> You don't have to if you put it in $HOME/.pgpass; see the
>> documentation for the format of that file...
>>
> True, but I see that as a *big* security hole.

As opposed to putting it in the script file?  Surely you jest.

            regards, tom lane

Re: Create Linux Script for PostgreSQL database backup

От
Naomi Walker
Дата:
Tom Lane wrote:
Naomi Walker <nwalke@eldocomp.com> writes: 
Christopher Browne wrote:   
One question , if you set  access to server with password in
pg_hba.conf how can you pass the apssword in the script?       
You don't have to if you put it in $HOME/.pgpass; see the
documentation for the format of that file...
     
True, but I see that as a *big* security hole.   
As opposed to putting it in the script file?  Surely you jest.
		regards, tom lane 
Anything would plain text would be a problem.  Isnt .pgpass plain text?

-- 
-------------------------------------------------------------------------
Naomi Walker                          Chief Information Officer
Eldorado Computing, Inc.              nwalker@eldocomp.com 602-604-3100 
-------------------------------------------------------------------------
Freedom is not worth having if it does not include the freedom to make
mistakes.      -Mahatma Gandhi
------------------------------------------------------------------------- 

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.

 

Re: Create Linux Script for PostgreSQL database backup

От
Tom Lane
Дата:
Naomi Walker <nwalke@eldocomp.com> writes:
> Anything would plain text would be a problem.  Isnt .pgpass plain text?

Plain text is irrelevant.  Securing the file properly is what's relevant
(and libpq will not read a .pgpass file with insecure permissions...)

            regards, tom lane

Re: Create Linux Script for PostgreSQL database backup

От
Christopher Browne
Дата:
Centuries ago, Nostradamus foresaw when nwalke@eldocomp.com (Naomi Walker) would write:
> Anything would plain text would be a problem.  Isnt .pgpass plain
> text?

Yes, it's plain text.  How do you propose to improve on that?

At _some_ point, there has GOT to be a password in plain text form
that libpq has access to, and any attempt to obfuscate it cannot
provide anything better than illusory security.

Suppose we decide we will store it in some "encrypted" form; libpq (or
some equivalent) necessarily has _got_ to contain the decryption
system, which means that anyone that can read the library and
therefore read that decryption key, allowing them to decrypt the file
containing the "encrypted" password.

In effect, we could _pretend_ to encrypt the passwords in .pgpass, but
it can't possibly provide any more security than we get storing them
unencrypted.

Suppose we characterize this in a sort of mathematical notation...

  P - plaintext password
  E: p ==> p' is an function mapping text into an "encrypted" form
  E':p' ==> p is the inverse function of E, mapping the encrypted form
              back into plaintext

You propose that we store an encrypted form in the password file.
That means that we have some tool that takes P, transforms it using
function E to E(P), and puts it in the encrypted password file.

But then there must be an instance of function E' either in libpq or
within the postmaster.  If I have access to the computer system, I
therefore have access to libpq+postmaster, and thus can take that
encrypted password, E(P), and use E' to find E'(E(P)) = P.

That's the plaintext password; you imagined it hidden, but it wasn't,
really.

Public key encryption, while seemingly magical for many purposes,
doesn't help with this.  Functions E and E' could both be PK-related;
the fact that E' MUST exist on the system means that E/E' can provide
no meaningful security.

This is a fundamental flaw that strikes just about any such sort of
automated process that cannot resort to asking an operator for a
"key."  (There's an exception, bt it requires having a
tamper-resistant cryptographic device connected to the computer
system, and those are really expensive to manage.)

I do prefer secure systems to those that aren't, but I also engage a
healthy distrust in people that tell me things that I know aren't
true.

If someone were to claim that encrypting these passwords provided
material improvements to security, they would either be lying to me,
or demonstrating that they don't understand what security this
would(n't) provide.

If PostgreSQL Core folk claimed that encrypting the passwords provided
improved security, I'd have to think uncomplimentary thoughts about
them...
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/advocacy.html
Science is like sex: sometimes something useful comes out, but that is
not the reason we are doing it.  -- Richard Feynman

Re: Create Linux Script for PostgreSQL database backup

От
Sanjay Arora
Дата:
On Thu, 2004-09-02 at 12:26, Kaolin Fire wrote:

>
> echo "1   3   *   *   *   postgres   /usr/local/bin/pg_dumpall >
> ~/backup.sql" >> /etc/crontab
>
> note to remember the two >>'s so you don't obliterate your crontab like
> I just did.
>

Would someone tell of a modification to this script that would:

1. create a file/directory based upon the date & time to create the
backup file in. ;-) [some people are shameless about asking about
pre-cooked food]

2. If a file DB has large objects..to dump it seperately...or does one
have to enumerate each of the databases individually & dump them, in
this case.

3. In case, databases have to be dumped seperately in the above...any
script to find out existing databases, if they have lo and dump
accordingly.

4. Take care that each file size does not exceed OS file limits.

You must always help the shameless & the lazy....for they are Gods own
people ;-)

With best regards.
Sanjay.



Re: Create Linux Script for PostgreSQL database backup

От
Lawrence C
Дата:
Hi Tim,

How do I setup cronjob for user "postgres" then in
scripts? that what i mean

thanks :)


 --- tim hall <tech@glastonburymusic.org.uk> wrote:
> Lawrence C,
> On Wednesday 01 September 2004 14:20, you went:
> > Anyone good in writing Linux scripting for backup
> > PostgreSQL. I just required a simple scripts only.
> >
> > Normally we do the manual ways as following:
> >
> > In root
> >
> > root# su - postgres
> > bash# pgdumpall >  backup.sql
> > bash# exit
> > root#
> >
> > I'm planning to write script.sh for cronjob but
> not
> > too sure about it. Can anyone contribute the
> scripts
> > for me?
>
> Er ...
>
> #! /usr/bin/bash
> pgdumpall >  backup.sql
>
> I'm being cheeky, but surely if you set that up as a
> cronjob for user
> 'postgres' that would do it? Are your needs more
> complex than this?
>
> cheers
>
> tim hall
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

________________________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html

Re: Create Linux Script for PostgreSQL database backup

От
Geoffrey
Дата:
Lawrence C wrote:
> Hi Tim,
>
> How do I setup cronjob for user "postgres" then in
> scripts? that what i mean
>
> thanks :)

log in or su to postgres and 'crontab -e' to edit the postgres crontab.

OR

as root 'crontab -u postgres -e'

--
Until later, Geoffrey       Registered Linux User #108567
                             AT&T Certified UNIX System Programmer - 1995