Hi there,
I've spent this evening writing a little Bash script to email me a
backup copy of my SL database, which I attach in case it might be of
use to anyone.
In my case the mailserver runs on the same machine as the PostgreSQL
server, so this isn't stunningly proof against hard-drive failure,
but for me this is offset by the consideration that I don't have to
worry about the security issues of the data going offsite, and also
by my email usage - the arrival of the backup by email should serve
as a sufficient reminder to ensure that I'll copy the file to the
hard-drive of my workstation, or even to CD, and I'll notice if a
problem causes it not to arrive. If you're using this to email the
backup offsite then I'd advise you to take a look at LeRoy D.
Cressy's post of 22 June 2006 15:25:21 BST, in which he encrypts his
database dump with gpg - you could easily modify my script to
accommodate that.
My first question arising from the tinkering I've done this evening
is over the format of the file produced by `pg_dump`. When I search
my archive of messages from the list for the word "backup", everyone
says to backup using `pg_dump` and to restore a database - whether
produced in this manner or from the GUI of SL (under System > Backup)
- by feeding the file to pqsl. However, if I look at the file
produced by `pg_dump` it looks somewhat different from that obtained
from SL's GUI.
$ pg_dump -U postgres networkned-soletrad | head -n 10
--
-- PostgreSQL database dump
--
SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
$ zcat stroller-soletrad-2.6.4-20061001.gz | head -n 10
-- SQL-Ledger Backup
-- Dataset: stroller-soletrad
-- Version: 2.6.4
-- Host: localhost
-- Login: Stroller
-- User: Joe Stroller
-- Date: Sun Oct 1 04:30:27 2006
--
DROP TABLE makemodel;
DROP TABLE gl;
$
Can anyone explain why this is, please? And what the differences are
between the two formats?
(PS: regarding the version - I will upgrade later today)
Secondly, I originally set up my SL installation following the
instructions in the README [1], and I find that I am able to run
`pg_dump` and read the whole database as my normal user. Obviously
there are some security considerations to this - have I done
something wrong? I would have expected the database to be accessible
only to the postgres user.
Looking again at the README I see that it says:
# su postgres
$ createuser -d sql-ledger
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
if you use passwords to access postgres use this command
$ createuser -d -P sql-ledger
Am I right in thinking that the reason my normal user can access the
database is because I followed the first of these commands, rather
than the second? Will my database be secure if I simply add a
password to Postgre's sql-ledger user? How will this affect my backup
script? Will root be able to run `pg_dump` without authenticating to
Postgre?
Finally, I see in the post I mentioned earlier by LeRoy D. Cressy (22
June 2006 15:25:21 BST) that he uses `pg_dumpall ` rather than
`pg_dump`. Are there any advantages to this? I have no other
databases on this server, would there be any benefit to using this
command? In some of the past posts about restoring databases I see
that `create_db` is used before restoring with `psql`; would this be
avoided by Dave Ratte's advice (8 March 2006 16:33:55 GMT) that he
prefers `pg_dump` "-C to include db re-creation info, and -D column
inserts". What is considered the best & most reliable way to dump the
database, please?
Thanks in advance for any & all comments,
Stroller.
[1] <http://sql-ledger.org/cgi-bin/nav.pl?page=source/
readme.txt&title=README>
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
sql-ledger-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sql-ledger-users
