Project

General

Profile

« Previous | Next » 

Revision 13290

Added by Aaron Marcuse-Kubitza over 10 years ago

web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: backups: documented how to set up online and offline backups (with two possible approaches for online backups)

View differences:

index.htm
717 717 
<DD>online backups:
718 718 

  		




  719
  719
  
    
pg_start_backup() method:


  		




  720
  
  
    
(more reliable, but requires informing the DB server to prepare for a backup)


  		




  
  720
  
    
(more reliable, but requires putting the DB server into a special mode during backup)


  		




  721
  721
  
    
# configure:


  		




  722
  722
  
    
## in postgresql.conf, set wal_level = hot_standby


  		




  723
  
  
    
## restart server


  		




  
  723
  
    
## $ sudo service postgresql restart


  		




  724
  724
  
    
# run:


  		




  725
  725
  
    
## $ sudo -u postgres psql &lt;&lt;&lt;&quot;SELECT pg_start_backup(&#39;backup&#39;, true);&quot;


  		




  726
  726
  
    
## copy files using rsync, etc.


  		




  727
  727
  
    
## $ sudo -u postgres psql &lt;&lt;&lt;&quot;SELECT pg_stop_backup();&quot;


  		




  728
  728
  
    

  		




  729
  729
  
    
wal_keep_segments method:


  		




  730
  
  
    
(potentially less reliable, but completely independent of the DB server)


  		




  
  730
  
    
(less reliable, but the backup is completely independent of the DB server)


  		




  
  731
  
    
# configure:


  		




  
  732
  
    
## in postgresql.conf:


  		




  
  733
  
    
### set wal_level = hot_standby


  		




  
  734
  
    
### set wal_keep_segments to long enough that the WAL chain spans the duration of the backup


  		




  
  735
  
    
#### to determine this, measure how quickly WAL segments are created at peak DB usage


  		




  
  736
  
    
## $ sudo service postgresql restart


  		




  
  737
  
    
# run:


  		




  
  738
  
    
## copy files using rsync, etc.


  		




  
  739
  
    
## copy again to get changes since last backup


  		




  
  740
  
    
## copy again to make sure there are no more changes


  		




  
  741
  
    

  		




  
  742
  
    
offline backups:


  		




  
  743
  
    

  		




  
  744
  
    
# copy files using rsync, etc.


  		




  
  745
  
    
# copy again to get changes since last backup


  		




  
  746
  
    
# $ sudo service postgresql stop


  		




  
  747
  
    
# copy again to make the copy a consistent snapshot


  		




  
  748
  
    
# $ sudo service postgresql start


  		




  731
  749
  
    
</DD>


  		




  732
  750
  
    
                            <DL><p>


  		




  733
  751
  
    
                                <DT><a name="pg_clog, pg_xlog" href="#pg_clog, pg_xlog"><H3 ADD_DATE="1397777677">pg_clog, pg_xlog</H3></a>


  		












Also available in:  Unified diff