Re: Backing Up a Postgres database...

On Wed, Sep 24, 2003 at 14:51:14 -0400,
Jonathan Telep <jon2(at)autoweb(dot)net> wrote:
> Hello, I'm not sure who I should be posing this question to but I'm a
> System Administrator who has inherited several Debian Linux servers one
> of which apparently hosts a rather large Postgres database. One of my
> responsibilites is to back that database up each night and be able to
> recover it "on the fly" in the event of a problem. I have no idea how
> to even check which version of Postgres is loaded on this server, let
> alone, be able to back it up.

You can check the version of the server by connecting to it and
issuing the following SQL:
select version()

You can also check the version using the binaries by using a --version option.
'postmaster' is the name of the server binary. 'psql' is the normal client
used when accessing the server through the command libe.

> Is there anyone who can give me just some basic commands on how to shut
> it down each night, back it up, verify that the back up is good and what
> to do in the event that I ever have to restore it? I know it seems like
> a lot but I'm swamped with a million things to do and would rather not
> have to read a couple hundred pages of material if I can avoid it.

You can get consistant hot backups using pg_dump. Otherwise for cold backups
the server must be shut down. Then you can just backup the data directory.
The data directory location is installation dependent. A typical location is
/usr/lib/pgsql/data .