PostgreSQL – v12 – Importing data from previous version

Background

Let us import data from PostgreSQL Version 11 into Version 12.

Lineage

  1. PostgreSQL – v12 – Installation/Upgrade on MS Windows
    Link

 

Outline

  1. Previous Instance
    • Start previous Instance
    • Export Data
      • Options
        • pg_dumpall
          • Using pg_dumpall, export data
    • Stop previous Instance
  2. New Instance
    • Start new Instance
    • Import Data
      • Options
        • psql
          • Using psql, import data
    • Review imported data

Steps

Previous Instance

Export Data

Outline

There are a few tools to export data from a PostgreSQL instance.  Inclusive are :-

  1. pg_dumpall

Script

Script – _baseOut.cmd

SET "_server=localhost"

SET "_port=5432"

SET "_databaseSystem=postgres"

SET "_username=script"

SET "_password=hello"

set PGPASSWORD=%_password%

Script – ExportData.cmd

setlocal

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

set "_appProgram=pg_dumpall.exe"
set "_appName=%_appFolder%\%_appProgram%"

set "_dataFolder=c:\tmp"
set "_dataFile=postgreSQL.data"
set "_data=%_dataFolder%\%_dataFile%"

call _baseOut.cmd %_databaseContext%

set "_cmdLineParm=--host=%_server% --username=%_username% --database=%_databaseSystem% --port=%_port%"

if not exist %_dataFolder% mkdir %_dataFolder%

"%_appName%" %_cmdLineParm% > %_data%

endlocal

New Instance

Import Data

Outline

Here are the tools available for importing data into a PostgreSQL instance.

  1. psql

Script

Script – _baseIn.cmd

SET "_server=localhost"

SET "_port=5432"

SET "_databaseSystem=postgres"

SET "_username=postgres"

SET "_password=hello"

set PGPASSWORD=%_password%

Script – ImportData.cmd

setlocal

set "_appFolder=C:\Program Files\PostgreSQL\12\bin"
set "_appProgram=psql.exe"
set "_appName=%_appFolder%\%_appProgram%"

set "_dataFolder=c:\tmp"
set "_dataFile=postgreSQL.data"
set "_data=%_dataFolder%\%_dataFile%"

set "_databaseContext=postgres"

call _baseIn.cmd %_databaseContext%

set "_cmdLineParm=--host=%_server% --username=%_username% -d %_databaseContext% --port=%_port%"

if not exist %_dataFolder% mkdir %_dataFolder%

"%_appName%" %_cmdLineParm% -f %_data%

endlocal

Summary

Thankfully everything worked well.

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