From the following link:
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP

Step 3 says to perform the back up.

Does this mean a File System Backup of the Data directory?
OR
Does this mean performing a pg_dumpall and backing up the dump file?

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Search Discussions

  • Steve at Aug 27, 2008 at 12:46 am
    Hi Richard,

    This means a file systems backup. eg.

    tar -cvpf data_bakup.tar /var/lib/pgsql/data



    Here's a script I use to automate this process. It may be helpful to
    customize for yourself.

    #!/bin/bash
    #
    # PostgreSQL Weekly Backup
    #

    DATE=$(date +%G%m%d)
    MAILLOG="/backup/weekly_$DATE.log"
    WALARCH="/pgbackup"
    DESTINATION="X.X.X.X::rsyncshare/"
    EMAILDEST=""

    touch $MAILLOG
    echo -e "::::::::::::::: $DATE Pallas1 Weekly WAL Backup :::::::::::::::"
    $MAILLOG
    psql -U postgres -c "select pg_start_backup ('$DATE');"

    cd /var/lib/pgsql
    tar -cvpzf "pgbackup_$DATE.tar.gz" data/ >> $MAILLOG 2>&1

    psql -U postgres -c "select pg_stop_backup ();"

    find $WALARCH -perm 600 -mmin "+360" -exec rm {} \; >> $MAILLOG 2>&1
    find $WALARCH/data_dir_backup -mtime "+5" -exec rm {} \; >> $MAILLOG 2>&1

    ls -la $WALARCH >> $MAILLOG 2>&1

    mv /var/lib/pgsql/pgbackup_$DATE.tar.gz /$WALARCH/data_dir_backup/.

    rsync -a -v -v --progress --stats --delete "/$WALARCH" $DESTINATION >>
    $MAILLOG 2>&1

    /bin/cat $MAILLOG | mail -s "Weekly WAL Backup - Successful" $EMAILDEST &&
    rm $MAILLOG





    On Tue, 26 Aug 2008 15:53:33 -0700, "Richard Broersma"
    wrote:
    From the following link:
    http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP
    Step 3 says to perform the back up.

    Does this mean a File System Backup of the Data directory?
    OR
    Does this mean performing a pg_dumpall and backing up the dump file?

    --
    Regards,
    Richard Broersma Jr.

    Visit the Los Angeles PostgreSQL Users Group (LAPUG)
    http://pugs.postgresql.org/lapug
  • Richard Broersma at Aug 27, 2008 at 1:11 am

    On Tue, Aug 26, 2008 at 5:19 PM, wrote:

    This means a file systems backup. eg.

    tar -cvpf data_bakup.tar /var/lib/pgsql/data
    Thanks also for the script. I will take a close look.


    --
    Regards,
    Richard Broersma Jr.

    Visit the Los Angeles PostgreSQL Users Group (LAPUG)
    http://pugs.postgresql.org/lapug
  • Merlin Moncure at Aug 27, 2008 at 12:58 am

    On Tue, Aug 26, 2008 at 6:53 PM, Richard Broersma wrote:
    From the following link:
    http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP

    Step 3 says to perform the back up.

    Does this mean a File System Backup of the Data directory?
    OR
    Does this mean performing a pg_dumpall and backing up the dump file?
    File system backup. You tar up the database folder except pg_xlog
    (and maybe pg_log). You then expand it on the other side (making sure
    to set up pg_xlog properly). If you ever want to mess around with log
    shipping I strongly suggest you go through the motions of setting up a
    warm standby vi the pg_standby utility and practice popping the
    standby out of recovery. It will give you a good understanding of the
    process.

    merlin
  • Richard Broersma at Aug 27, 2008 at 1:13 am

    On Tue, Aug 26, 2008 at 5:38 PM, Merlin Moncure wrote:


    If you ever want to mess around with log
    shipping I strongly suggest you go through the motions of setting up a
    warm standby vi the pg_standby utility and practice popping the
    standby out of recovery.
    Thanks for the reply! I want to be certain that I understand the
    point you are making:

    Is setting up a warm standby server good pratice for gaining
    experience in PITR or do you mean that PITR is a good starting point
    for setting up a warm standby server?



    --
    Regards,
    Richard Broersma Jr.

    Visit the Los Angeles PostgreSQL Users Group (LAPUG)
    http://pugs.postgresql.org/lapug
  • Merlin Moncure at Aug 27, 2008 at 12:19 pm

    On Tue, Aug 26, 2008 at 9:04 PM, Richard Broersma wrote:
    On Tue, Aug 26, 2008 at 5:38 PM, Merlin Moncure wrote:

    If you ever want to mess around with log
    shipping I strongly suggest you go through the motions of setting up a
    warm standby vi the pg_standby utility and practice popping the
    standby out of recovery.
    Thanks for the reply! I want to be certain that I understand the
    point you are making:

    Is setting up a warm standby server good pratice for gaining
    experience in PITR or do you mean that PITR is a good starting point
    for setting up a warm standby server?
    setting up a warm standby is good for understanding pitr. A warm
    standby just 'lays on top' of pitr and you should learn how to do it.

    merlin
  • Michael Nolan at Aug 27, 2008 at 1:18 pm
    I have what I have sometimes called a 'tepid spare' backup. Once a week I
    copy the physical files over to another system (actually to two of them) and
    every few hours I make sure the archived WAL log files are in sync (using
    rsync.)

    Anyway, here's the cookbook guide I wrote for updating one of the backup
    servers, which I have used several times, so I don't have to rediscover the
    steps each time. I think it has sufficient detail that someone other than
    me could follow them. Obviously it would have to be changed to apply to
    your file organization scheme.

    Enjoy!
    --
    Mike Nolan


    How to restore the PostgreSQL low level backup tar files and WAL files
    to do a point-in-time recovery (PITR) files on a backup server.

    This note will explain how to perform a point-in-time recovery of the
    PostgreSQL database using the low-level backup files and the archived
    WAL (write-ahead log) files. (These steps should work on either server,
    with any differences in actual file locations dealt with using symbolic
    links.)

    The low level backup files and WAL log files should already be present
    on the backup servers. The low level tar files files are copied when they
    are created (usually early on a Tuesday morning each week) and the WAL log
    files are rsync'ed to both servers every few hours so that both backup
    servers should have files enabling them to be restored to a state that is
    no more than a few hours out of sync with the live database server. (In
    the future, we hope to keep one of the backup servers in full real-time
    synchronization with the live server, using a different technique.)

    The steps given below will generally take 3-5 hours to run, depending
    on how many archived log files need to be processed when the PostgreSQL
    server is restarted.

    These instructions assume some familiarity with Unix/Linux system
    administration tools, including the vi editor, and with database
    administration procedures, though not necessarily detailed knowledge
    of PostgreSQL.

    For more details, see chapter 23 of the PostgreSQL documentation,
    especially section 23.3. The documentation for version 8.2 is at
    http://www.postgresql.org/docs/8.2/static/backup.html


    1. su to root on the backup server that the restore will be performed on

    2. su to postgres (su - postgres)

    3. Shut down the Postgresql server running on the backup server, if any
    pg_ctl stop
    (Use 'ps ax' to make sure the server is stopped.)


    4. cd to /disk1/postgres/data and copy two files to /tmp

    cd /disk1/postgres/data
    cp pg_hba.conf /tmp
    cp recovery.done /tmp

    5. Delete the entire contents of the /disk1/postgres/data directory tree.
    MAKE ABSOLUTELY SURE YOU ARE IN THE /disk1/postgres/data directory!

    cd /disk1/postgres/data
    pwd
    rm -rf *

    6. Restore the tar file for the low-level backup from the live server

    tar xvf /usr/local/pgsql/tardir/pg_part1.tar

    (This restore may take 2-3 hours)

    7. Remove the PostgreSQL log file and the WAL files that were restored
    from the tar archive

    rm log.out
    cd pg_xlog
    rm 00*

    (do not remove the archive_status subdirectory)

    8. Copy the pg_hba.conf file back from the /tmp directory

    cd /disk1/postgres/data
    cp /tmp/pg_hba.conf .

    (This is necessary to keep this database server configured for
    restricted
    access. If more expansive access is needed the restored pg_hba.conf
    file may be more appropriate.)

    9. Copy the recovery configuration file from the /tmp directory (changing
    the file name)

    cp /tmp/recovery.done recovery.conf

    (This file may be edited to do a point-in-time recovery other than
    to the end of the PITR log files, see the PostgreSQL documentation for
    details.)

    10. Delete the entire contents of the /disk2/postgres/data directory tree.
    MAKE ABSOLUTELY SURE YOU ARE IN THE /disk2/postgres/data directory!

    cd /disk2/postgres/data
    pwd
    rm -rf *

    11. Restore the 2nd tar archive

    tar xvf /usr/local/pgsql/tardir/pg_part2.tar

    (This restore may take around a half hour)

    12. Go to the directory where the WAL files have been archived on
    the server and remove all files older than the file matching the
    last .backup file. The fastest way to do this is as follows:

    cd /usr/local/pgsql/archivedir
    ls -1 > files

    This will produce a file listing all files in the archivedir directory.

    Now edit that file (using vi) to take out all lines after the
    latest 'backup' file (including the file just ahead of that backup
    file, since it is actually the first file that needs to be processed.
    (Note: These ones after the latest backup file are the files we're
    NOT going to delete, so we don't want those file names in this text
    file.)

    For example, the directory list may look like this:

    00000001000000960000000A
    00000001000000960000000B
    00000001000000960000000C
    00000001000000960000000C.004027C0.backup
    00000001000000960000000D

    There will generally be several files with the name ending in 'backup',
    find the most recent one (ie, the last one in the file.)

    In this case, the fie with the name ending in '000C' needs to be
    restored, along with all subsequent files, so in this case we would
    delete all lines from the one ending in '000C' to the end of the edit
    buffer, then edit the lines to change the lines into 'rm' commands,
    ie, something like this:

    rm 00000001000000960000000A
    rm 00000001000000960000000B


    Save the edited file, then execute it

    sh -x files

    13. You are now ready to restart PostgreSQL and have it process all the
    archived log files:

    pg_ctl -l /usr/local/pgsql/data/log.out start

    14. While the WAL files are being processed, a 'ps -ax' display will
    have lines in it that look something like this:

    28039 pts/0 S 0:00 /disk1/postgres/pgsql/bin/postgres
    28040 ? Ds 0:00 postgres: startup process

    You can also look at the /usr/local/pgsql/data/log.out file to see
    which WAL file it is currently processing:

    tail /usr/local/pgsql/data/log.out

    Once all the WAL files have been processed (which could take an
    hour or longer, depending on how many WAL files need to be processed,
    'ps -ax' will look more like this:

    28039 pts/0 S 0:00 /disk1/postgres/pgsql/bin/postgres
    28310 ? Ss 0:00 postgres: writer process
    28311 ? Ss 0:00 postgres: archiver process
    28312 ? Ss 0:00 postgres: stats collector process

    Once this happens, the backup database server is ready for use.
    However, it will only be as current as the most recent WAL file that
    was processed, and in order to bring it to a more current state all of
    the steps given above have to be performed again.
  • Merlin Moncure at Aug 27, 2008 at 1:33 pm

    On Wed, Aug 27, 2008 at 9:18 AM, Michael Nolan wrote:
    I have what I have sometimes called a 'tepid spare' backup. Once a week I
    copy the physical files over to another system (actually to two of them) and
    every few hours I make sure the archived WAL log files are in sync (using
    rsync.)

    I have a couple of comments...see below:
    3. Shut down the Postgresql server running on the backup server, if any
    pg_ctl stop
    (Use 'ps ax' to make sure the server is stopped.)
    probably pg_ctl -m fast stop or -m immediate...since we are overwriting it.
    5. Delete the entire contents of the /disk1/postgres/data directory tree.
    MAKE ABSOLUTELY SURE YOU ARE IN THE /disk1/postgres/data directory!

    cd /disk1/postgres/data
    pwd
    rm -rf *
    why not just rm -rf /disk1/postgres/data?
    6. Restore the tar file for the low-level backup from the live server

    tar xvf /usr/local/pgsql/tardir/pg_part1.tar

    (This restore may take 2-3 hours)

    7. Remove the PostgreSQL log file and the WAL files that were restored
    from the tar archive
    I prefer to exclude WAL files from the original tar, and recreate the
    folders here (being careful to chown them to postgres account). Every
    little bit helps.

    12. Go to the directory where the WAL files have been archived on
    the server and remove all files older than the file matching the
    last .backup file. The fastest way to do this is as follows:

    cd /usr/local/pgsql/archivedir
    ls -1 > files
    This is a nice touch. With a little bash-fu you could do a find |
    xargs rm and list/kill the files in one pass. In the standby setups
    I've done I usually script the whole process, a prep on the main and a
    startup on the standby.

    merlin
  • Michael Nolan at Aug 27, 2008 at 1:52 pm

    On Wed, Aug 27, 2008 at 8:32 AM, Merlin Moncure wrote:



    3. Shut down the Postgresql server running on the backup server, if any
    pg_ctl stop
    (Use 'ps ax' to make sure the server is stopped.)
    probably pg_ctl -m fast stop or -m immediate...since we are overwriting it.

    Good point, but sometimes there are tasks running on the backup server
    (recently I did a test of a revised procedure that took 5 days to run.)
    I'll probably update the cookbook guide to deal with that possibility.
    5. Delete the entire contents of the /disk1/postgres/data directory tree.
    MAKE ABSOLUTELY SURE YOU ARE IN THE /disk1/postgres/data directory!

    cd /disk1/postgres/data
    pwd
    rm -rf *
    why not just rm -rf /disk1/postgres/data?

    I prefer the visual confirmation that I am indeed in the right directory
    rather than risk a typo in the longer rm command.
    6. Restore the tar file for the low-level backup from the live server
    tar xvf /usr/local/pgsql/tardir/pg_part1.tar

    (This restore may take 2-3 hours)

    7. Remove the PostgreSQL log file and the WAL files that were restored
    from the tar archive
    I prefer to exclude WAL files from the original tar, and recreate the
    folders here (being careful to chown them to postgres account). Every
    little bit helps.

    Another good point.
    12. Go to the directory where the WAL files have been archived on
    the server and remove all files older than the file matching the
    last .backup file. The fastest way to do this is as follows:

    cd /usr/local/pgsql/archivedir
    ls -1 > files
    This is a nice touch. With a little bash-fu you could do a find |
    xargs rm and list/kill the files in one pass. In the standby setups
    I've done I usually script the whole process, a prep on the main and a
    startup on the standby.

    The scripts to create the system level backups and copy them to the backup
    servers and to rsync the WAL files are both cron jobs.

    I've considered writing a script to do all the restore tasks on the backup
    server, but I figure if someone other than me ever has to do it, for example
    if there is a problem when I'm unavailable, that person will probably want
    to watch the entire process carefully, since he or she will be far less
    familiar with the configuration.

    As disk space permits, I actually keep TWO sets of the tar archive base
    files, the most recent one and the one from the previous week. That way I
    could go back further for a PITR recovery up to some incident. I've never
    needed to do that and I hope I never do, but it's nice to have that
    capability just in case.
    --
    Mike Nolan
  • Merlin Moncure at Aug 27, 2008 at 2:18 pm

    On Wed, Aug 27, 2008 at 9:52 AM, Michael Nolan wrote:
    This is a nice touch. With a little bash-fu you could do a find |
    xargs rm and list/kill the files in one pass. In the standby setups
    I've done I usually script the whole process, a prep on the main and a
    startup on the standby.
    The scripts to create the system level backups and copy them to the backup
    servers and to rsync the WAL files are both cron jobs.

    I've considered writing a script to do all the restore tasks on the backup
    server, but I figure if someone other than me ever has to do it, for example
    if there is a problem when I'm unavailable, that person will probably want
    to watch the entire process carefully, since he or she will be far less
    familiar with the configuration.
    Here are some basic facts of life about PITR/log shipping. It is a
    disaster prevention feature. Here's the scenario:

    You are going to depend upon it on 3 a.m. early saturday morning three
    years in the future, only you are not going to be there. A drive just
    went out on the main, but instead of degrading the entire backplane
    went amber. You are going to be in Barbados on your honeymoon, with
    no connectivity to the outside world (your blushing bride made you
    leave the laptop at home). The guy doing the switchover to the
    standby is the only one that could be gotten a hold of, he still
    hasn't gotten over the 12 hour bender from Friday. He's never really
    understood why your company took your advice and went with PostgreSQL
    instead of SQL Server, is cranky, and doesn't like you that much. He
    secretly hopes the standby wont come up and barely knows how to use a
    console.

    write the script.
    test it.

    merlin
  • Lennin Caro at Aug 27, 2008 at 1:59 pm
    --- On Tue, 8/26/08, Richard Broersma wrote:

    From: Richard Broersma <richard.broersma@gmail.com>
    Subject: [GENERAL] PITR - base backup question
    To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>, pgsql-admin@postgresql.org
    Date: Tuesday, August 26, 2008, 10:53 PM
    From the following link:
    http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP

    Step 3 says to perform the back up.

    Does this mean a File System Backup of the Data
    directory?
    OR
    Does this mean performing a pg_dumpall and backing up
    the dump file?
    is a file system backup of he data directory
    --
    Regards,
    Richard Broersma Jr.
  • Julio Leyva at Aug 28, 2008 at 2:02 pm
    ----------------------------------------
    Date: Wed, 27 Aug 2008 06:58:33 -0700
    From: lennin.caro@yahoo.com
    Subject: Re: [ADMIN] [GENERAL] PITR - base backup question
    To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org; richard.broersma@gmail.com




    --- On Tue, 8/26/08, Richard Broersma wrote:
    From: Richard Broersma
    Subject: [GENERAL] PITR - base backup question
    To: "pgsql-general@postgresql.org" , pgsql-admin@postgresql.org
    Date: Tuesday, August 26, 2008, 10:53 PM
    From the following link:
    http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP

    Step 3 says to perform the back up.

    Does this mean a File System Backup of the Data
    directory?\
    It is a file system backup, we use PITR and every time we need a new checkpoint we create a TAR file of the postgresql home directory

    OR
    Does this mean performing a pg_dumpall and backing up
    the dump file?
    is a file system backup of he data directory
    --
    Regards,
    Richard Broersma Jr.





    --
    Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-admin

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 26, '08 at 10:53p
activeAug 28, '08 at 2:02p
posts12
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase