Archive

Posts Tagged ‘PostgresQL’

Backup methods and tools for PostgresQL

January 17th, 2013 No comments

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.

 

Categories: Postgresql, tools Tags: