Re: Backup routine

On Friday 08 August 2003 18:59, Enio Schutt Junior wrote:
> Hi
>
> Here, where I work, the backups of the postgresql databases are being done
> the following way: There is a daily copy of nearly all the hd (excluding
> /tmp, /proc, /dev and so on) in which databases are and besides this there
> is also one script which makes the pg_dump of each one of the databases on
> the server. This daily copy of the hd is made with postmaster being active
> (without stopping the daemon), so the data from /usr/local/pgsql/data would
> not be 100% consistent, I guess.
> Supposing there was a failure and it was needed to restore the whole thing,
> I think the procedure to recovery would be the following:
> 1) Copy data from the backup hd to a new hd
> 2) Once this was done, delete the postmaster.pid file and start the
> postmaster service 3) Drop all databases and recreate them from those
> pg_dump files
>
> There are some questions I have about this backup routine:
> If I recover data from that "inconsistent" backup hd, I know that the
> binary files (psql, pg_dump and so on) will remain ok. The data may have
> some inconsistencies. Would these inconsistencies let the postmaster start
> and work properly (that is, even with the possible presence of inconsistent
> data) Would it start and be able to work normally and keep the information
> about users and groups? I am talking about users and groups information
> because these ones are not dumped by pg_dump. I was thinking about using
> "pg_dump -g" to generate this information.
>
> I was also thinking about excluding /usr/local/pgsql/data from the backup
> routine, as the data is also in other files generated by pg_dump. The
> problem is that this directory has not only the databases data but also
> some config files, like postgresql.conf.
>
    In most cases so long as you have not actually had a hard disk failure. When
postgres comes up after it has crashed, it uses its WAL logs to clean up the
database. So If your machine crashes with out hard disk failure you database
should come up fine.
    If you have had a disk failure. The problem is that the beginning of the
database may be backed up before the end. So you will need to use the dump.
ie WAL logs may not agree with database and Table A not agree with Table B
etc. Hence the general advise is bring it up from dump. The database will
either come up so you can drop the databases or failure badly in which case
you can always work out whats gone wrong and work round it.
    However there is a third way. That should be safe but some people may
disagree with me! If you can "freeze" the disk while you take the backup. The
backup can be used as if the computer had crashed with no hard disk failure
at all. Ie WAL will be consistant and database may take longer but once it is
up it will be safe (like paragaph 1). Now freezeing a disk for backup is not
that difficult. You should be doing it anyway for user file consistancy. (You
don't want the first 30 pages of you document to disagree with the end
because somone was saving it during the backup!
    Freezing a disk can be done with LVM for example.
http://www.tldp.org/HOWTO/LVM-HOWTO/ and is not difficult. RAID can also be
used to avoid disk failure in the first place.
    Always however keep a dump as the dump will be your only way out if your
basic sanity checks fail!

Peter Childs