Backup methods and tools for PostgresQL
There are different methods that one can use in order to backup and restore files.
Using pgdump, new file for each day
Process
Go to Server create a Directory called Drive:\PostgresqlBack then create a sub directory called “bin” in the Drive:\PostgresqlBack
comerr32.dll
gssapi32.dll
k5sprt32.dll
krb_32.dll
libeay32.dll
libiconv2.dll
libpq.dll
Microsoft.VC80.CRT.manifest
msvcm80.dll
msvcp80.dll
msvcr80.dll
pg_dump.dll
ssleay32.dll
zlib1.dll
Create batch file called something, example is postgresqlBackup.bat. The file must be located in PostgresqlBack directory not the bin folder.
Open the File then Copy/Paste the following
@echo off
for /f “tokens=1-4 delims=/ ” %%i in (“%date%”) do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set BACKUP_FILE=<NameOfTheFile>_%datestr%.backup
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=<PassWord>
echo on
bin\pg_dump -i -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>
Change <NameOfTheFile> to something. Setting is the first part of the file name then followed by the date the file was created with the extension .backup
Change the <PassWord > setting above to the correct password for the backup users.
Change <HostName> either to ip address or dns name of the server hosting Postgresql.
Change <UserName> to backup user make sure this users has access to database for backup purposes
Change <DATABASENAME> to the database name being backed up.
Save the File
Create Task for the MS Task Scheduler
Using .pgpass and pgdumpall, same file
Achieving automated backup in a windows environment
Create a .pgpass file
Lock down the .pgpass file
Using NTFS permissions, disable access to this file for everybody except the user pg is running as
Create a script to call pg_dumpall
SET PGPASSFILE=C:\foo\bar\PG_BACKUP\PGPASSFILE\pgpass.conf
“C:\Program Files\PostgreSQL\8.2\bin\pg_dumpall.exe” -U scfcu_postgres > C:\foo\bar\PG_BACKUP\db.out cu
Handling Large Databases
Use compressed dumps. You can use your favorite compression program, for example gzip:
pg_dump dbname | gzip > filename.gz
Reload with:
gunzip -c filename.gz | psql dbname
or:
cat filename.gz | gunzip | psql dbname
Use split. pg_dump dbname | split -b 1m – filename
Reload with: cat filename* | psql dbname
You can use PostgreSQL’s continuous WAL archiving method. First you need to set wal_level=archive, then do a full filesystem-level backup (between issuing pg_start_backup() and pg_stop_backup() commands) and then just copy over newer WAL files by configuring the archive_command option.
Update: Check out Barman for an easier way to set up WAL archiving for backup.
You can use PostgreSQL’s continuous WAL archiving method. First you need to set wal_level=archive, then do a full filesystem-level backup (between issuing pg_start_backup() and pg_stop_backup() commands) and then just copy over newer WAL files by configuring the archive_command option.
Advantages:
Incremental, the WAL archives include everything necessary to restore the current state of the database
Almost no overhead, copying WAL files is cheap
You can restore the database at any point in time (this feature is called PITR, or point-in-time recovery)
Disadvantages:
More complicated to set up than pg_dump
The full backup will be much larger than a pg_dump because all internal table structures and indexes are included
Doesn’t work work well for write-heavy databases, since recovery will take a long time.