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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[tim hall]

Lawrence C,

Post by Lawrence C
Anyone good in writing Linux scripting for backup
PostgreSQL. I just required a simple scripts only.
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]

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

Post by tim hall
Lawrence C,

Post by Lawrence C
Anyone good in writing Linux scripting for backup
PostgreSQL. I just required a simple scripts only.
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Geoffrey]

Post by Kaolin Fire
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.

[Sandro Garoffolo]

Post by Geoffrey

Post by Kaolin Fire
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Christopher Browne]

Post by Sandro Garoffolo
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...

[Sanjay Arora]

Post by Kaolin Fire
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.



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Lawrence C]

Hi Tim,

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

thanks :)

Post by tim hall
Lawrence C,

Post by Lawrence C
Anyone good in writing Linux scripting for backup
PostgreSQL. I just required a simple scripts only.
In root
root# su - postgres
bash# pgdumpall >  backup.sql
bash# exit
root#
I'm planning to write script.sh for cronjob but

not

Post by Lawrence C
too sure about it. Can anyone contribute the

scripts

Post by Lawrence C
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Geoffrey]

Post by Lawrence C
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'

[Naomi Walker]

Post by Christopher Browne

Post by Sandro Garoffolo
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. ***@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.

[Tom Lane]

Post by Naomi Walker

Post by Christopher Browne

Post by Sandro Garoffolo
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Naomi Walker]

Post by Tom Lane

Post by Naomi Walker

Post by Christopher Browne

Post by Sandro Garoffolo
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. ***@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.

[Tom Lane]

Post by Naomi Walker
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Christopher Browne]

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...