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