FAQ
Using PostgreSQL 8.3.7, I'd like to make a 'hot' backup of my dbases.



I've been using "pg_dumpall -c -o" for a while, but I realize that this is

no good. After all, for a restore, the PostgreSQL server needs to be up!

And what happens if the dbases crash/become corrupted (say, after a

power-outage), and PostgreSQL won't start up any more? In MySQL, this was

easy. In a Perl script I did something like: "FLUSH TABLES WITH READ

LOCK", and then I proceeded to tar the entire dbases directory (so I could

untar, without the MySQL server needing to be up).



Can something similiar be done in PostgreSQL, *without* bringing down the

server in its entirety?



Thanks,



- Mark

Search Discussions

  • Ognjen Blagojevic at May 15, 2009 at 10:16 am

    Mark wrote:
    I've been using "pg_dumpall -c -o" for a while, but I realize that this is
    no good. After all, for a restore, the PostgreSQL server needs to be up!
    And what happens if the dbases crash/become corrupted (say, after a
    power-outage), and PostgreSQL won't start up any more? In MySQL, this was
    easy. In a Perl script I did something like: "FLUSH TABLES WITH READ
    LOCK", and then I proceeded to tar the entire dbases directory (so I could
    untar, without the MySQL server needing to be up).
    You can always delete the DB cluster, initialize it, start Postgres and
    then restore it from the dump.

    See also:

    http://www.postgresql.org/docs/8.3/static/backup.html

    for other possibilities of backing up including file system backup.



    Regards,
    Ognjen
  • Mark at May 15, 2009 at 10:33 am
    -----Original Message-----
    From: pgsql-novice-owner@postgresql.org
    On Behalf Of Ognjen Blagojevic
    Sent: vrijdag 15 mei 2009 12:17
    To: pgsql-novice@postgresql.org
    Subject: Re: [NOVICE] 'Hot' backup of PostgreSQL dbases
    You can always delete the DB cluster, initialize it, start
    Postgres and then restore it from the dump.
    That actually sounds like a solid plan, and it will guarantee PostgreSQL
    can always start up, no matter how bad things may have gotten.

    Thanks,

    - Mark

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMay 14, '09 at 11:13a
activeMay 15, '09 at 10:33a
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Mark: 2 posts Ognjen Blagojevic: 1 post

People

Translate

site design / logo © 2022 Grokbase