PostgreSQL :- Database Backup & Load – File is in Text Format

Background

Here is our second post.

The first post covers database backup and restore with the backup file formatted using a custom format.

That post is here.

Backup & Restore

Introduction

In this post we will take the following track :-

  1. Backup
    • Invoke the pg_dump utility
    • File format will be plain text ( -Fp )
  2. Restore
    • Invoke the psql utility

Outline

  1. _base.cmd
    • Set up environments
  2. backupText.cmd
  3. loadText.cmd

Scripts

_base.cmd


SET "_server=localhost"

SET "_port=5432"

SET "_databaseSystem=postgres"

SET "_username=script"

SET "_password=tub5q4vLtr"

set PGPASSWORD=%_password%

backupText.cmd


setlocal

rem Database Name
set "_database=bible"

rem backup folder
set "_backupFolder=backup"

rem backup file extension
set "_backupExt=sql"

rem backup file name is [database-name].bak
set "_backupFile=%_database%.%_backupExt%"

rem full backup file
set "_backupFileFull=%_backupFolder%\%_backupFile%"

rem set shared environment variables
call _base.cmd

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

rem PostgreSQL app name
set "_binApp=pg_dump.exe"

rem backup format is compressed
rem set "_backupFormat=-Fc"

rem backup format is plain
set "_backupFormat=-Fp"

rem command line parameter
set "_cmdLineParm=-h %_server% -p %_port% -U %_username% %_backupFormat% %_database% "

rem make bcakup directory if is does not currently exist
if not exist "%_backupFolder%" mkdir %_backupFolder%

rem initiate backup
"%_binFolder%\%_binApp%" %_cmdLineParm% > %_backupFileFull%

rem done
:completed

endlocal

loadText.cmd


setlocal

rem source database
set "_databaseSource=bible"

rem destination database
set "_database=bibletoo"

rem backup folder
set "_backupFolder=backup"

rem backup file extension
set "_backupExt=sql"

rem backup file name is [database-name].bak
set "_backupFile=%_databaseSource%.%_backupExt%"

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% "

rem set "_binApp=pg_restore.exe"
set "_binApp=psql.exe"

rem prepare command line parameter
set "_cmdLineParm=-d %_database% -h %_server% -p %_port% -U %_username%"

rem restore database
"%_binFolder%\%_binApp%" %_cmdLineParm%  -qtAX -f %_backupFileFull%

:completed

endlocal

Review Backup File

Backing up the file as a plain text makes it a lot easier to see the review the artifacts produced by the the pg_dump utility.

Outline

Here is a brief outline :-

  1. Versioning
    • PostgreSQL Server Instance Version database backup sourced from
    • Version of pg_dump utility
  2. Set Environment variables
  3. Create Objects
    • Create Schema
    • Create Table
    • Create View
  4. Sequence Manipulate
    • Set Sequence Values
      • Invoke the last identity value used by invoking the pg_catalog.setval function
  5. Add Object Level Constraint
    • Add Constraint – Primary Key
  6. Add Indexes
    • Add Indexes to each table
  7. Create and Add Triggers

Images

Image Versioning

backupandrestore.text.version

Environment – Set

backupandrestore.text.environment.Set

Create Objects

Create Objects – Schema

backupandrestore.text.ddl.object.schema

Create Objects – Table
bible.book

backupandrestore.text.ddl.object.table.bible.book

bible.kjv

backupandrestore.text.ddl.object.table.bible.kjv.PNG

Create Objects – View
bible.vw_kjv

backupandrestore.text.ddl.object.view.bible.vw_kjv

Sequence Manipulate
Set Sequence Values

backupandrestore.text.sequence.setValue

Constraint
Constraint – Primary Key – bible.book

backupandrestore.constraint.primaryKey

Constraint – Primary Key – bible.kjv

backupandrestore.constraint.primaryKey.bible.kjv

Indexes
Index – bible.kjv

backupandrestore.index.bible.kjv

Index – bible.niv

backupandrestore.index.bible.niv

Triggers
Index – bible.vw_kjv

backupandrestore.trigger.vw_kjv

Permissions
Permission – bible.vw_kjv

backupandrestore.permission.bible.vw_kjv

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