How do I copy an Ingres database to another machine which has the same Ingres version and same operating system?

Document ID : KB000054324
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

You can use unloaddb and reloaddb, which copies the system catalogs, as well as the eTDir tables. This option is only recommended for in-place recovery, or recovery within the same release of Ingres on the same operating system. Ingres version is listed in "II_SYSTEM/ingres/version.rel".

Solution:

Before proceeding with the following procedure it is recommended to backup the databases by taking LDIF dumps via dxdumpdb.
NOTE: If there is any inconsistent data in the primary database, then this will be migrated to the secondary server's database as well. This should only be done when you are sure that your data in the primary server is consistent.

Eg. You have two peer dsas (democorp) replicating to each other and your dsas databases(democorpdb) are not in sync and you want to make an exact copy of your primary database on your secondary server.

On Windows

Do the following on primary server:

  1. Login as administrator.

  2. Run the following command to stop the dsa's which are connected to the database.
    % dxserver stop democorp
    NOTE: There could be other dsa's connected to the database as well. You will need to stop these as well.

  3. Create a folder and cd to it via the command prompt.
    % mkdir c:\democorp
    % cd c:\democorp

  4. Run the following commands to copy the democorpdb database.
    % unloaddb democorpdb
    % unload.bat

  5. Create a zip archive of the "c:\democorp" folder and copy it to you secondary server.

Do the following on secondary server

  1. Login as administrator.

  2. Unzip the compressed file which was copied from the primary server to the same location eg. (c:\democorp).

  3. Open a command prompt and enter the following command to destroy the database
    % dxdestroydb democorpdb

  4. Enter the following command to create a new database
    % createdb democorpdb
    NOTE: dxnewdb should NOT be used for creating a new database.

  5. Navigate to the extracted folder eg. "c:\democorp" via the command prompt
    % cd c:\democorp

  6. Enter the following command to reload the database
    % reload.bat
    NOTE:
    1. If the database name is different on the secondary server, then edit reload.bat to reflect the new name.
      sql -s -f4F79.38 -f8F79.38 -u$ingres +U democorpdb < "C:\democorp\copy.in"
      should be changed to
      sql -s -f4F79.38 -f8F79.38 -u$ingres +U myNewDB < "C:\democorp\copy.in"

    2. If the location of the extracted folder is different to the primary server then edit by replacing all the locations specified in reload.bat and copy.in files
      Change all C:\democorp to C:\myNewLocation

You will need to compare the output of "dxstatdb dbname" on both the servers. They should be exactly the same.

On Unix\Linux

Do the following on primary server:

  1. Login as user "dsa".

  2. Run the following command to stop the dsa's which are connected to the database.
    % dxserver stop democorp
    NOTE: There could be other dsa's connected to the database as well. You will need to stop these as well.

  3. Create a folder and cd to it via the command prompt
    % mkdir /tmp/democorp
    % cd /tmp/democorp

  4. Run the following commands to copy the democorpdb database
    % unloaddb democorpdb
    % unload.ing

  5. Create a tar file of the "/tmp/democorp" folder and copy it to you secondary server
    % cd /tmp
    % tar -cvf democorpdb.tar democorp

Do the following on secondary server:

  1. Login as user "dsa".

  2. Untar the compressed file which was copied from the primary server to the same location eg. (/tmp/democorp)
    % cd /tmp
    % tar -xvf democorpdb.tar

  3. Open a command prompt and enter the following command to destroy the database
    % dxdestroydb democorpdb

  4. Enter the following command to create a new database
    % createdb democorpdb
    NOTE: dxnewdb should NOT be used for creating a new database.

  5. Navigate to the extracted folder eg. "/tmp/democorp" via the command prompt
    % cd /tmp/democorp

  6. Enter the following command to reload the database
    % reload.ing
    NOTE:
    1. If the database name is different on the secondary server, then edit reload.ing to reflect the new name.
      sql -s -f4F79.38 -f8F79.38 -u$ingres +U democorpdb < "/tmp/democorp/copy.in"
      should be changed to
      sql -s -f4F79.38 -f8F79.38 -u$ingres +U myNewDB < "/tmp/democorp/copy.in"

    2. If the location of the extracted folder is different to the primary server then edit by replacing all the locations specified in reload.ing and copy.in files
      Change all /tmp/democorp to /tmp/myNewLocation

Verification:
You will need to compare the output of "dxstatdb dbname" on both the servers. They should be identical.