Database Reference
In-Depth Information
Standalone hot physical database backup
Hot physical backup is an important capability for databases.
Physical backup allows us to get a completely consistent view of all database changes at
once. Physical backup also allows us to backup even while DDL changes are being executed
on the database. Apart from resource constraints, there is no additional overhead or locking
with this approach.
Physical backup procedures are slightly more complex than logical backup procedures.
So, let's start with a simple procedure to produce a standalone backup.
How to do it...
The following steps assume that a number of environment variables have been set:
1. $PGDATA
is the path to the PostgreSQL data directory, ending with
/
2. $BACKUPNAME
is the filename of a backup file
3. All required PostgreSQL connection parameters have been set
The initial procedure is steps 1 onwards. If
running subsequent backups, start at step 6.
4. Create a new archive directory, if not already present, as follows:
cd $PGDATA
mkdir -p ../ standalone
5. Set an
archive_command
. In
postgresql.conf
you will need to add the following
lines and restart the server, or just confirm they are present:
archive_mode = on
archive_command = 'test ! ../standalone/archiving_active ||
cp -i %p ../standalone/archive/%f'
6. Start archiving as follows:
mkdir ../standalone/archive
touch ../standalone/archiving_active
7. Start the backup as follows:
psql -c "select pg_start_backup('standalone')"
8. Base backup—copy the data files (excluding the pg_xlog directory) as follows:
tar -cv --exclude=$PGDATA/pg_xlog \
-f ../standalone/$BACKUPNAME $PGDATA
