Thread: Backing Up a Postgres database...

Backing Up a Postgres database...

From
Jonathan Telep
Date:
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.

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.

Thanks in advance,

Jon


Re: Backing Up a Postgres database...

From
Bruno Wolff III
Date:
On Wed, Sep 24, 2003 at 14:51:14 -0400,
  Jonathan Telep <jon2@autoweb.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 .

Re: Backing Up a Postgres database...

From
Ron Johnson
Date:
On Wed, 2003-09-24 at 13:51, Jonathan Telep 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.
>
> 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.

If Postgresql was installed via apt, then you can start/stop it
using /etc/init.d/postgresql.

Backup with pg_dump, and restore with pg_restore.
http://www.postgresql.org/docs/7.3/static/index.html
http://www.postgresql.org/docs/7.3/static/reference-client.html
http://www.postgresql.org/docs/7.3/static/app-pgdump.html
http://www.postgresql.org/docs/7.3/static/app-pg-dumpall.html
http://www.postgresql.org/docs/7.3/static/app-pgrestore.html

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"As the night fall does not come at once, neither does
oppression. It is in such twilight that we must all be aware of
change in the air - however slight - lest we become unwitting
victims of the darkness."
Justice William O. Douglas


Re: Backing Up a Postgres database...

From
"vijay"
Date:
Hi Jon,

Hope this helps you.

More detailed info about backing up
---------------------------------------------------

Examples
To dump a database:

$ pg_dump mydb > db.out
        In the above example "pg_dump " is the command for backing up
            "mydb"     is the name of the db you want to backup
            ">"          is used for redirecting the output
            "db.out"   is the file where the backup of the DB will be stored

To reload this database:

$ psql -d database -f db.out
        In the above example "psql" is the command for reloading DB from the
backup
            "-d"               is the option indicating the database you are
backing it into
            "database"     is the name of the db you want to restore into
            "-f"                is used for restoring it from the following
file
            "db.out"         is the file where the DB will be restored from

To dump a database called mydb that contains BLOBs( BIG LARGE OBJECTS, a
special object in postgresql) to a tar file:

$ pg_dump -Ft -b mydb > db.tar
        In the above example "pg_dump " is the command for backing up
            "mydb"     is the name of the db you want to backup
            ">"           is used for redirecting the output
            "db.tar"     is the file where the backup of the DB will be
stored

To reload this database (with BLOBs) to an existing database called newdb:

$ pg_restore -d newdb db.tar
        In the above example "pg_restore" is the command for reloading DB
from the backup
            "-d"         is the option indicating the database you are
backing it into
            "newdb"   is the name of the db you want to restore into
            "db.tar"     is the file where the DB will be restored from


Vijay

----- Original Message -----
From: "Jonathan Telep" <jon2@autoweb.net>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, September 24, 2003 1:51 PM
Subject: [NOVICE] Backing Up a Postgres database...


> 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.
>
> 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.
>
> Thanks in advance,
>
> Jon
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: Backing Up a Postgres database...

From
Aarni Ruuhimäki
Date:
Hi Jon,

Here's briefly what I do:

1. A scheduled cronjob on the production machine that dumps all databases.

(on linux RH)

/usr/local/pgsql/bin/pg_dumpall -c > /pg_backup/prod_all.pgdump_c

2. A cronjob on the backup machine that retrieves the dump file ( and what
ever you like ) over ssh-ftp from the production machine.

Please see http://tennis.ecs.umass.edu/~czou/linux/backupSSH.html ( this is a
good one ! )

3. A cronjob that first drops and then creates all db's ( the -c switch in
pg_dumpall command ) on the backup machine.

psql -e template1 < /backup_down/prod_all.pgdump_c

And there's no need to shut down either postmaster, you can do this 'on the
fly'.

Hope this helps.

BR,

Aarni



----- Original Message -----
From: "Jonathan Telep" <jon2@autoweb.net>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, September 24, 2003 1:51 PM
Subject: [NOVICE] Backing Up a Postgres database...


> 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.
>
> 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.
>
> Thanks in advance,
>
> Jon
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Aarni Ruuhimäki
Megative Tmi
KYMI.com

Pääsintie 26
45100 Kouvola
FINLAND

info@kymi.com / aarni.ruuhimaki@kymi.com

+358-5-3755 035 / +358-50-4910 037
------------------------------------------------
Linux RedHat / KDE