PostgreSQL :- Database Backup & Restore – File is in Custom Format

Background

A long three day weekend is a good time to catch up on house work, grocery runs, cooking, worship service.

Added in SQL Server and here Tuesday Morning a tiny bit of PostgreSQL, as well.

Backup & Restore

Outline

  1. _base.cmd
    • Set up environments
  2. backup.cmd
  3. restore.cmd

Scripts

_base.cmd


SET "_server=localhost"

SET "_port=5432"

SET "_databaseSystem=postgres"

SET "_username=script"

SET "_password=tub5q4vLtr"

set PGPASSWORD=%_password%

backup.cmd

setlocal

set "_database=bible"

set "_backupFolder=backup"

set "_backupFile=%_database%.bak"

set "_backupFileFull=%_backupFolder%\%_backupFile%"

call _base.cmd

set "_binFolder=C:\Program Files\PostgreSQL\11\bin"

set "_binApp=pg_dump.exe"

set "_backupFormat=-Fc"

set "_cmdLineParm=-h %_server% -p %_port% -U %_username% %_backupFormat% %_database% "

if not exist "%_backupFolder%" mkdir %_backupFolder%

"%_binFolder%\%_binApp%" %_cmdLineParm% > %_backupFileFull%

:completed

endlocal 

restore.cmd

setlocal

rem source database
set "_databaseSource=bible"

rem destination database
set "_database=bibletoo"

rem backup folder
set "_backupFolder=backup"

rem get database file based on source database
set "_backupFile=%_databaseSource%.bak"

rem get full file name ( folder name + file name)
set "_backupFileFull=%_backupFolder%\%_backupFile%"

call _base.cmd

set "_binFolder=C:\Program Files\PostgreSQL\11\bin"

set "_binApp=psql.exe"

set "_cmdLineParm=-h %_server% -U %_username% -d %_databaseSystem% -p %_port%"

rem drop database if it exists
"%_binFolder%\%_binApp%" %_cmdLineParm% -c "drop DATABASE if exists %_database% "

rem Create database
"%_binFolder%\%_binApp%" %_cmdLineParm% -c "create database %_database% "

set "_binApp=pg_restore.exe"

rem -F format to specify the format of the archive.
set "_backupFormat=-Fc"

rem -c to drop database objects before recreating them
rem -e exit on errot
set "_cmdLineParm=-d %_database% -h %_server% -p %_port% -U %_username% %_backupFormat% -e"

rem restore database
"%_binFolder%\%_binApp%" %_cmdLineParm% %_backupFileFull%

:completed

endlocal

References

  1. AxiomMQ
    • Jovana Dačić
      • Backup and Restore a PostgreSQL Database
        Link
  2. Linode
    • Jared Kobos
      • How to Back Up Your PostgreSQL Database
        Link
  3. Stack Exchange
    • Database Administrators
      • why pg_restore ignores –create ? Error: failed: FATAL: database “new_db” does not exist
        Link

One thought on “PostgreSQL :- Database Backup & Restore – File is in Custom Format

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s