Thread: Create Linux Script for PostgreSQL database backup
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# Im 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
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
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 > > >
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
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
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?
Christopher Browne wrote:
True, but I see that as a *big* security hole.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...
-- ------------------------------------------------------------------------- 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.
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
Tom Lane wrote:
Anything would plain text would be a problem. Isnt .pgpass plain text?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
-- ------------------------------------------------------------------------- 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.
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
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
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.
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
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