Planning Factors

• Depending on the size of your database, this could take multiple days or could happen overnight.  Plan accordingly.  
• Plan on having 2 times your database size in free space on your server for the migration.  Once done you can shrink things back down to normal.  However, temporarily you will have your current database, a backup copy, and the new PostgreSQL database all with the same data.  The backup copy can be on an external drive if needed.
• Must be on release W1177A1 or later to enable proper copying of the database tables.

Download and Install PostgreSQL

1. Download PostgreSQL Version 18 for Windows x86-64 https://sbp.enterprisedb.com/getfile.jsp?fileid=1259914 
2. Install PostgreSQL
   • Check your download folder and double click the installer
   • Click Next
     
   • Recommend the default directory.  Click Next.
     
   • Recommend selecting all components.  Click Next.
     
   • At this point you will need to select the drive in which you wish you database to reside on. (we recommend creating a folder on the root of the desired drive called OASISpg)
     
   • Enter “oasispg” for the database superuser and click next.  Note:  You may choose to use a different one, however, you will need to make sure someone knows it that is on support calls with Ingen.
     
   • Recommend using the default port and click next.
     
   • Accept Default and click next.
     
   • Click next.
     
   • Click next to begin the installation process.
     
   • Click Finish to complete the installation. Also uncheck the option to launch Stack Builder.
     

Create and Start the new OASIS database

1. Run the pgAdmin Program by clicking its icon in your programs.
   
2. Enter the “oasispg” used in Step 2 above. 
   
3. Create a login by right clicking on Login/Group Roles then Create and then Login/Group Role.
   
4. On the General Tab name the login oasis.
   
5. On the Definition Tab set the password for the user.  Recommend oasis.
   
6. On the Priveleges Tab set the rights as follows and click Save
   
7. Now create the database by right clicking on Databases and then selecting Create and Database.
   
8. On the General Tab, name the database oasis and set the owner to oasis.  Then click Save.
   
9. Under the database that was just created, right click on Schema and choose Create Schema.  Then name it the same as the database with owner as “oasis”.  Then click Save.  Note: We are finding that the schema ends up in public schema.  To date there are no issues with that.

Make a backup of your Sybase DB

1. Coordinate a time to stop your OASIS Database when everyone can be out of OASIS.  This could be overnight or over the weekend, depending on the size of your database.
2. Locate the server that hosts your OASIS Database and then Run ASA Configuration Utility.
3. Click the "Stop" button in the ASA Configuration utility for the OASIS Database.
4. Create a new File/Directory on the server to host the backup of your database.
5. Keep in mind this needs to be located on a drive with adequate room
6. Copy/Paste the “oasis.db” and “oasis.log” file from the existing machine to the newly-created directory on the new server.  NOTE – Copy/Paste. Do Not Move. 
7. Keep this backup untouched so you have a fall back option if anything goes wrong with the migration.
8. Click the “Start” button in the ASA Configuration utility for the OASIS Database

Copy from Sybase Production to PostgreSQL database

1. Ensure everyone is still out of the database as any changes made to a table that is already copied won’t show up in the new database.
2. Run OASIS to connect to your Sybase Production database.
3. Go to Configuration and then Software.
4. Click Tools-> OASIS DB TOOLS.
   
5. In the new window, click Tools->Copy Database
   
6. On the database Information Tab, enter the From connection info for your Sybase Database and enter the To connection information for the new PostgreSQL Database.  Check Create Tables, Search Tables, INGEN_MANIFEST Tables, and PAT Tables.  In the Threads selection choose 4 if you have a basic computer running your OASIS Database. If the server performance is higher, you can choose more Threads.  This will speed up things.  However, it will rob more from the server if it is running other things for your company.  If in doubt, ask support for a recommendation based on your unique situation.  4 is pretty safe in all cases.
   

Click Copy.  Then you will see the progress on the Status tab.  CPU usage is another way to ensure there is action.  Once all tables are complete, then you can start using your new PostgreSQL database.  Any issues or errors, you can contact Support to confirm next steps.  There might be a way to resolve, or you might have to move the copy you made back into position to try again later.  There is a chance that attachments will take a while.  If all other tables are done and you find Project, Quote or Order attachments is still running, please contact Support for an assessment.  It is possible you can let your teams back into OASIS while attachments finish up. 
NOTE: Don’t close Copy Tool until all Tables are reporting 100% or you have ran the red result by Support.

Setup the PostgreSQL database to be seen from outside of localhost

1. You will need to find the pg_hba.conf file.  It is located at C:\Program Files\PostgreSQL\<version>\data\pg_hba.conf
2. Open this file with Admin Privileges in a text editor like Notepad.
3. Scroll down to # IPv4 local connections section.
4. Add entries for the IP Addresses you want to allow access to.  For example “host      all        all        127.0.0.1/32    scram-sha-256” will allow access to all local IPs. 
   
   You can enter specific IP Addresses or ranges.
5. If you are needing to add remote access you will also need to set “listen_addresses = '*'” in the postgresql.conf file and open port 5432 in the Windows Defender Firewall.

Stop the old Sybase Database

1. Back in the ASA configuration Utility, click the “Stop” button.
2. Remove the OASIS service on the old server by clicking the "Remove" button in the ASA Configuration utility.

Notify Support if all went well or if you had any issues and needed to fall back to copy.