My client asks to work on a Java batch to migrate data from PostgreSql to Oracle via JDBC, with almost 2 days to spend around preparing the server, loading dump, starting dev. around as well.

Here wanna to share what I went on (not sure if I went to the right way, give advise if any)

[toc]

1. Download PostgreSql & Install

2. Download Administration tool for PostgreSql: pgAdmin

3. Configure the postgreSql server be able to accept remote access from other client PC

Suppose using the default port of PostgreSql (Port: 5432)

I corrected for following configuration file:

  • postgresql.conf
      • Change listen_addresses from 'localhost' to '*'
    listen_addresses = '*'
  • pg_hba.conf
    • We need to enable client pc ip to access Postgresql with following rule:
    • # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
    • host    all         all         127.0.0.1/32          md5  ==> For local pc only
    • host    all         all         192.168.1.0/24     md5   ==> All PC in my LAN

4. Connecting to the server for the first time

PostgreSql pgAdmin

PostgreSql pgAdmin

PostgreSql pgAdmin Area

PostgreSql pgAdmin Area

Generally the query is the same to MySql or Plsql of oracle as well for basically… I haven’t gone detail yet.

5. Loading existing dump to server

Go to DOS at PostgreSql/bin folder and follow for following step:

  • — delete old db
    dropdb -U {admin_user} {db_name}

    • if the db is not exist will saw an error as well.
  • — create db
    createdb -U {admin_user} -T template0 {db_name}

    • I created the db with PostgreSql template “zero” (template0)
    • Why create with template0?
    • Because the dumps produced by pg_dump are relative to template0 that will also dump all from template1. (see explain detail in Manual on Restoring the Dump)
  • — execute to restore dump file to db
    psql -U {admin_user} -d {db_name} -f “{dump.sql file}”

Notice: sometimes not work when the database is accessing my client PC or currently being connected; Stop the service and restart the service before perform above action.

My first task is done now…

We are going on with Jdbc connection of PostgreSql, I’m reading following post:

To continue with around Jdbc dev. later.