Home

Data Comparer for PostgreSQL - User`s Manual

1. E Data Comparer for PostgreSQL kabai Step 2 of 8 Set correspondence for tables and fields to compare DemoDB on ayz2 54383 lt gt DemoDB on localhost 54391 Set table correspondence Mask for autofill Autofill tables and fields Source Tables Target Tables Data Filter E HR DEPARTMENT ER HR DEPARTMENT sl lt Set Filter gt ES HR EMPLOYEE HR EMPLOYEE sl lt Set Filter gt ER HR EmployeeAddress l lp HR EmployeeDepartmentHistory Ed ES HR EmployeePayHistory Bal PostgreSQL Set field correspondence Autofill fields Source Fields Target Fields Key Field a 2 EMP ID integer i EMP ID integer 7 SS POSITION varchar 40 B POSITION varchar 40 R B FIRST NAME varchar 30 5 FIRST NAME varchar 30 EN 7 SS LAST NAME varchar 30 E LAST NAME varchar 30 E Cl GENDER chat GENDER char 1 p EG I MARITAL STATUS char 1 MARITAL STATUS chat B SS BIRTH DATE date BIRTH DATE date Le SS HIRE DATE date HIRE DATE date FI E Se EE Ignore case when comparing strings V Trim CHAR fields Compare BLOB values NOTE Synchronization for DID fields is possible if PL PGSGL language is installed on the server Number of comparison threads 3 W Help Tools Next gt Close Setting table correspondence The upper grid allows you to set correspondence
2. SSH port SSH password Use Private Key for authentication SSH key file http webserver_name emsproxy php To use HTTP tunneling just upload the tunneling script to the webserver where PostgreSQL server is located or to any other webserver from which direct connections to your PostgreSQL server are allowed This script exposes the PostgreSQL API as a set of web services which is used by Data Comparer for PostgreSQL For details see HTTP tunneling options Repeat the steps above for the target PostgreSQL connection or just check the Both databases on the same server option for comparing data from databases located on the same server When you are done press the Next button to proceed to the selecting schemas for refreshing or directly to Step 2 if the Select schemas dialog is disabled in the program preferences 2012 EMS Database Management Solutions 25 Data Comparer for PostgreSQL User s Manual 2 2 1 Selecting databases When the server connection settings are specified you should select databases for data comparison using the Select database dialog To call this dialog click the ellipsis d button of the Database control at Step 1 Selectdatabase B DellStore2 3 LargeDB 3 19230 H User B world 3 postgres 3 template H template empty 3 xtraLargeDB When you are done press OK to apply the database selection 2012 EMS Da
3. 20 Data Comparer for PostgreSQL User s Manual Using Wizard Application Data Comparer for PostgreSQL Wizard guides you through the entire comparison and synchronization process and provides an easy to use graphical interface allowing you to set all data comparison parameters visually Navigation through the steps of the wizard is performed with the help of the Next gt and the lt Back buttons Use the Tools button for calling the Preferences dialog or to load save a template Go through the steps of the wizard and follow the wizard instructions to tune all necessary comparison options according to your needs Getting started Step 1 Setting connection properties Step 2 Setting tables and fields correspondence Step 3 Browsing data comparison results Step 4 Specifying data synchronization options Step 5 Setting synchronization order Step 6 Editing synchronization script Step 7 Specifying additional scripts Step 8 Start of synchronization process See also Using console application Using templates Setting program preferences 2012 EMS Database Management Solutions 21 Data Comparer for PostgreSQL User s Manual 2 1 Getting started This is how Data Comparer for PostgreSQL application wizard looks when you first start it This page allows you to view registration information If you have not registered Data Comparer for PostgreSQL yet you can do it by pressing th
4. Both databases are located on the same server Target Database Connection Properties Connection Host localhost D Login postgres een Port Database 54391 D Password DemoDB E Client charset NOT SET ie ae E Tunneling settings To setup the connection via SSH tunnel proceed to the Tunneling tab and input the following values in the corresponding fields e SSH host name is the name of the host where SSH server is running e SSH port indicates the port where SSH server is activated e SSH user name stands for the user on the machine where SSH server is running Note it is a Linux Windows user not a user of PostgreSQL server e SSH password is the Linux Windows user password For details see SSH tunneling options 2012 EMS Database Management Solutions 24 Data Comparer for PostgreSQL User s Manual Source Database Connection Properties Tunneling Don t use tunneling Connect through the Secure SHell SSH tunnel SSH host name vadsrv D SSH user name tester Use Private Key for authentication SSH key file CASSHKeystdsa key ppk Connect through the HTTP tunnel Both databases are located on the same server Target Database Connection Properties Connection Tunneling Don t use tunneling Connect through the Secure SHell SSH tunnel
5. Set this option to compare the content of BLOB fields VI Trim CHAR fields Set this option to trim CHAR fields on synchronization Note that if data only contains spaces it will be regarded as empty string If the option is off data is processes as is including spaces at the end of the string Due to implementation of multithreaded comparison it is now possible to specify the Number of comparison threads When table comparison is complete the Summary dialog appears This dialog provides you with common information about the result of table s comparison The number of identical different missing and additional records for each pair of tables can be found at the corresponding columns of this report P E Summary Exa Source table Target table Identical Different Missing Additional Comparing time COUNTRY country a Jo fo fo 00000032 CUSTOMER CUSTOMER 15 0 0 0 00 00 00 283 DEPARTMENT DEPARTMENT 21 0 0 0 00 00 00 256 EMPLOYEE EMPLOYEE 34 8 0 0 00 00 00 439 SALES SALES 33 0 0 0 00 00 00 319 Do not show summary after comparing Copy to dipboard ox Check the Do not show summary option if there is no need in this dialog The Show summary dialog after comparison option at the preferences dialog allows you to manage this dialog appearance You can sort data in a column by clicking the column caption When you are done press the Next button to proceed to the next step 2012 EMS Database Mana
6. SQLManager net EMS Database Management Solutions Data Comparer for PostgreSQL User s Manual Em3 02012 EMS Database Management Solutions ie ef rd po ra om vee d Pe Sett n compared Vd bes zl ase m mPa By porer an veret bi rende Pene MEER Data Comparer for PostgreSQL User s Manual O 2012 EMS Database Management Solutions Al rights reserved This manual documents EMS Data Comparer for PostgreSQL No parts of this work may be reproduced in any form or by any means graphic electronic or mechanical including photocopying recording taping or information storage and retrieval systems without the written permission of the publisher Products that are referred to in this document may be either trademarks and or registered trademarks of the respective owners The publisher and the author make no claim to these trademarks While every precaution has been taken in the preparation of this document the publisher and the author assume no responsibility for errors or omissions or for damages resulting from the use of information contained in this document or from the use of programs and source code that may accompany it In no event shall the publisher and the author be liable for any loss of profit or any other commercial damage caused or alleged to have been caused directly or indirectly by this document Use of this documentation is subject to the following terms you may create a printed copy of this documentation s
7. e What do I need to start working with EMS Data Comparer e What is the easiest way to configure the template files for Data Comparer console application e How can I register the application e Are there any limitations implied in the trial version as compared with the full one e How can I automate comparison and synchronization process Question answer list Q What is EMS Data Comparer A EMS Data Comparer for PostgreSQL is a powerful and easy to use utility for data comparison and synchronization which allows you to view differences in tables and execute an automatically generated script to synchronize data between these tables Data Comparer for PostgreSQL includes a GUI wizard which guides you through the data comparison and synchronization process step by step and a command line version for synchronizing data in one touch What do I need to start working with EMS Data Comparer for PostgreSQL First of all you must have a possibility to connect to some local or remote PostgreSQL server to work with Data Comparer You can download PostgreSQL database server from http www postgresgl org download Besides you need your workstation to satisfy the system requirements of Data Comparer for PostgreSQL PO Q What is the easiest way to configure the template files for Data Comparer console application A You can configure the template files visually using the Data Comparer Wizard Set all the necessary options in each step o
8. Save template options File name C EMS DataComparer DataComparerTemplate edc ta Comment Console options Save script to file Fill correspondence automatically 0 Password options J Save password in template Q V Encrypt password Cancel Help Console options These options determine the behavior of Data Comparer for PostgreSQL for the synchronization session You can choose to save the synchronization script to a sq 2012 EMS Database Management Solutions 57 Data Comparer for PostgreSQL User s Manual file specify the script file name and select its location using the 6 button Execute script after creating If this option is checked after the script is generated it will be immediately executed by the console version of Data Comparer for PostgreSQL console only option Note that this option is only available for deselection if you have checked the Save script to file option Fill correspondence automatically If this option is checked the console ignores correspondence set at Step 2 and sets it automatically Password options VI Save password in template Set this option to remember the password s for accessing the database s If this option is disabled the password is prompted upon template load 7 Encrypt password The option enables disables encryption of your passwords stored in template file Please note that this option is only available if the Rem
9. 2012 EMS Database Management Solutions 61 Data Comparer for PostgreSQL User s Manual Setting Program Preferences Data Comparer for PostgreSQL provides full customization of the program by setting various options within the Preferences dialog This chapter is intended to inform you how to use all these options General These options define general behavior of Data Comparer for PostgreSQL Directories On this page you can specify the directory into which cache will be loaded Language On this page you can select a language to be applied to the GUI for your copy of Data Comparer for PostgreSQL Data font On this page you can specify the fonts to be used to display data in grid at Step 3 of the wizard Interface This branch contains several pages with a number of options allowing you to customize the application interface style according to your liking 2 Load template Save template d Preferences Reopen template gt See also Using application wizard Using templates 2012 EMS Database Management Solutions 62 Data Comparer for PostgreSQL User s Manual 4 1 General General Y Remember password Set this option to remember the password s for accessing the database s 7 Encrypt password in registry The option enables disables encryption of your passwords stored in Windows Registry Please note that this option is only available if the Remember password o
10. Different records colored in the grid VI Additional records colored in the grid 2012 EMS Database Management Solutions 39 Data Comparer for PostgreSQL User s Manual If necessary you can check the Open output file after export option to open the result file with the associated program Click the Export button to perform the operation For your convenience the progress bar displays the operation progress 2012 EMS Database Management Solutions 40 Data Comparer for PostgreSQL User s Manual 2 5 Step 4 Specifying data synchronization options At this step you can specify advanced data synchronization parameters Table synchronization options These options define the direction of synchronization from the source to the target or vice versa VI Synchronize in new table Check this option to create a new table with the synchronization applied In this case the name of the new table will be composed of the name of the source table and the user defined postfix sync by default VI Insert additional records Set this option to insert additional records to the destination tables Delete missing records Set this option to delete missing records from the destination tables v Update different records Set this option to correct different records during the synchronization process v Drop and create indexes This option forces dropping and subsequent creation of ind
11. Ee He ed a a ee eten bee 35 Exporting comparison reSUlts m memsennvrnnvnnvnnvnnennennennvnnennvnnnnnnennennennennenvennnnnnnnnnnnnnnnnnennennennennennennnnnnnnnennennennensenee 38 Step 4 Specifying data synchronization options REENEN EEN ENEE REENEN 40 Step 5 Setting synchronization Order ees EER RR RR EER RR AR EER RR AR EER RR RR EER RR RR EER RR RR GE ERK Re 43 Step 6 Editing synchronization Script sesse ee EE RR RR EER RR AR EER RR RR EER RR RR EER REENEN KEREN 44 Using the Find Textdialog 2 ee eebe AER KEN ee VERDER N EE oak EES ee eN de EE koe 46 Using the Replace Text dialog use Ee EE EENEG 48 Step 7 Specifying additional scripts ee EER RR RR EER RR AR EER RR RR EER RR RR KEREN EER REENEN 51 Step 8 Start of synchronization process EE RR ER EER RR RR EER RR RR EER RR RR EER RRE EER REENEN 52 Part Ill Using Configuration Files Templates 55 Saving templates LL RE EE OR AE ae deve cu cd N EE EE N EE 56 Loading ide ERGER EE EE EE RT EE OE N 58 Using Console Application es EER RR RR EER RR RR EER RR RR EER RR RR EER RR RR EER RR RR EER nennu nennen RR RR EER ER 59 Part IV Setting Program Preferences 61 Gonera EE EE EE OE Me EE Rs 62 RE 65 EI e LE 66 CR EE 67 2012 EMS Database Management Solutions 4 Data Comparer for PostgreSQL User s Manual Interface RE EE EE EE N EN EE ES 68 Part V Appendix 70 SSH tunneling options ese ees EER RR AR EER RR AR EER RR AR EER RR AR EER R
12. Script Editor working area in the direction specified by the Direction setting Selected text Searches only within the currently selected text in the direction specified by the Direction setting You can use the mouse or block commands to select a block of text Origin 9 From cursor The search starts at the cursor s current position and then proceeds either forward to the end of the scope or backward to the beginning of the scope depending on the Direction setting Entire scope The search covers either the entire block of selected text or the entire script no matter where the cursor is in the Editor area depending upon the Scope options vi Mark search result with stack marker The option toggles marking search results If this option is selected stack markers are set at all search positions this makes it possible to jump from one marker search result to another within the text Click the Show All button to highlight every occurrence of the search string See also Using the Replace Text dialog 2012 EMS Database Management Solutions 48 Data Comparer for PostgreSQL User s Manual 2 7 2 Using the Replace Text dialog The Replace Text dialog is provided for searching and replacing text within the Script Editor working area To open this dialog use the Ctrl R shortcut or press the corresponding Replace Text iss button on the toolbar This item is also available in the context menu of
13. compared tables which allows avoiding the Out of Memory error e The speed of auto fill in tables and fields is increased 2 Added the possibility to synchronize BLOB fields in InterBase 3 Added the option for viewing the Summary for the compared objects at the Step 3 Browsing data comparison results 4 Synchronization with deleting of records from tables containing foreign keys could result in an error Fixed now 5 Added the possibility to clear the template list 6 Some other small bugfixes and improvements 2012 EMS Database Management Solutions 15 Data Comparer for PostgreSQL User s Manual Version 2 3 0 1 e Added the Commit after synchronization option allowing to rollback all changes if an error occurs during the synchronization process Added the possibility to define table synchronization order manually Added the possibility to generate a summary report with table comparison results It is now possible to sort and filter comparison results Fields of the domain based type could not be selected as comparison keys Fixed now Encrypted passwords within templates created with older versions of the utility could not be read properly Fixed now e Some other small bugfixes and improvements Version 2 2 0 1 Added tools for exporting comparison results to HTML and RTF Implemented support of PostgreSQL 8 3 Optimized the array processing algorithm The new enhanced Script Editor Added the pos
14. create a template file follow the instructions below e start Data Comparer for PostgreSQL Application wizard e set all the required options in all steps of the wizard e test the comparison and synchronization process at the last step e save all comparison and synchronization options in the template The easiest way to start Data Comparer for PostgreSQL console application is to double click the generated edc template The other way is to enter the command line and type the appropriate command Usage lt path to Data Comparer for PostgreSQL console application gt PgDataComparerC exe TemplateFile L B TemplateFile Stands for the edc template file to be used as the console version execution parameter L Selects current localization set in Wizard Application GUI B Use this parameter in the command line to run the console version of Data Comparer for PostgreSQL in the background mode LOG This parameter sets path to the log file By default the log is written to the program s folder Example C Program Files EMS Data Comparer for PostgreSQL PgDataComparerC exe C EMS DataComparer 1st_sync edc L LOG C Logs datacomparer log Note The result of the latest task performed by Data Comparer for PostgreSQL can be found in the system variable ERRORLEVEL 0 successful completion 1 error s occurred during task performing See also Using GUI application Configuration file format
15. is running The Remote mode allows you to connect to PostgreSQL server launched on another computer in the network By default the program uses local connection It is indicated by switch Local selected If you wish to establish remote connection you should select the Remote switch For remote connection you should also enter PostgreSQL host name in the Host field For both types you should enter PostgreSQL port to connect through in the Connection port field Afterwards you should specify authorization settings Login and Password The default superuser name is postgres with the password specified during PostgreSQL server installation If necessary use the drop down list to specify the preferable Client charset to be used by the application After that it is necessary to specify the database you are going to work with type in its name in the Database field or use the ellipsis l button to select one from the Select database list Please note that you need to have sufficient privileges to be able to write to the destination database on PostgreSQL server 2012 EMS Database Management Solutions 23 Data Comparer for PostgreSQL User s Manual Step 1 of 8 Set PostgreSQL connection properties Connection Host ayz2 D Login ayz Port 54383 x Password Data i C omparer Database DemoDB fix Client charset NOT SET D for PostgreSQL
16. line speed through 2012 EMS Database Management Solutions 11 Data Comparer for PostgreSQL User s Manual Maintenance Reinstatement Renewal Interface After reinitiating renewal you will receive a confirmation e mail with all the necessary information See also How to register EMS Data Comparer 2012 EMS Database Management Solutions 12 Data Comparer for PostgreSQL User s Manual 1 5 How to register EMS Data Comparer To register your newly purchased copy of EMS Data Comparer for PostgreSQL perform the following e receive the notification letter from Share it with the registration info e enter the Registration Name and the Registration Key from this letter into the Register Data Comparer for PostgreSQL form at the Welcome Step e make sure that the registration process has been completed successfully check the registration information at the startup page Register Data Comparer for PostgreSQL x Please enter the registration information you received when purchasing Data Comparer for PostgreSQL Registration Name SES Registration Key See also Registration 2012 EMS Database Management Solutions 13 Data Comparer for PostgreSQL User s Manual 1 6 Version history Product name Data Comparer for PostgreSQL Data Comparer 2011 for PostgreSQL Data Comparer 2011 for PostgreSQL Data Comparer 2010 for PostgreSQL Data Com
17. text where N stands for the script line identifier Example Item_Count 2 LineO AFTER synchronization 2012 EMS Database Management Solutions 75 Data Comparer for PostgreSQL User s Manual Line1 script to be executed for the target database SrcBeforeScript This section stores the text of the script executed for the source database before data synchronization as specified at Step 6 of the Wizard application Parameter Description Item Count number of lines Line N script text where N stands for the script line identifier Example Item Count 2 LineO BEFORE synchronization Line1 script to be executed for the source database TrgBeforeScript This section stores the text of the script executed for the target database before data synchronization as specified at Step 6 of the Wizard application Parameter Description Item_Count number of lines Line lt N gt script text where N stands for the script line identifier Example Item Count 2 LineO BEFORE synchronization Line1 script to be executed for the target database SrcSchemas This section stores the list of source database schemas specified for comparison Parameter Description Item_Count number of schemas Line lt N gt schema name where N stands for the schema item identifier in the list Example Item_Count 1 LineO DEMO TrgSchemas This section stores the list of target database schemas sp
18. Al EI UPDATE HR EMPLOYEE f Find SET FIRST NAME Kelly AR Replace EE ee Ds Search Next EIUPDATE HR EMPLOYEE SET FIRST_NAME Peter Incremental Search WHERE EMP ID 14 E UPDATE HR EMPLOYEE SET POSITION Production Technician WHERE EMP ID 16 COMMIT MARITAL STATUS S Select Character Toggle Comment Selection Lower Case Selection Upper Case Selection Toggle Case Indent Unindent ai FOS GET For your convenience the syntax highlight code completion and a number of other features for efficient SQL editing are implemented The context menu of SQL Script Editor area contains most of the standard text processing functions Cut Copy Paste Select All and functions for working with the script as a whole e g you can toggle bookmarks move the cursor to a particular line Most of these operations can be also performed with the corresponding hot keys used 2012 EMS Database Management Solutions 45 Data Comparer for PostgreSQL User s Manual Implementation of the Find Text Replace Text dialogs and Incremental search bar contributes to more efficient work with the SQL code When you are done press the Next button to proceed to the next step 2012 EMS Database Management Solutions 46 Data Comparer for PostgreSQL User s Manual 2 7 1 Using the Find Text dialog The Fin
19. Interface 2012 EMS Database Management Solutions 66 Data Comparer for PostgreSQL User s Manual 4 3 Language The Language page is provided for managing Data Comparer localization files You can specify your own localization file by creating ng file similar to those available in the program directory VLanguages folder and place it there After it your language will be added to the list of available languages In the Languages area the list of available languages and the names of the corresponding localization ng files is displayed Here you can choose the preferable language General pe Language Directories Language Name Language File Language e e s guag Default English Default program language English C Program Files EMS EMS Data Comparer for PostgreSQL French C Program Files EMS EMS Data Comparer for PostgreSQL German C Program Files EMS EMS Data Comparer for PostgreSQL Russian C Program FilesVEMSYEMS Data Comparer for PostgreSQL Data font Interface See also General Directories Data fonts Interface 2012 EMS Database Management Solutions 67 Data Comparer for PostgreSQL User s Manual 4 4 Data font The Data font page is provided for setting up the fonts for displaying data in grid at Step 3 of the GUI wizard Use the drop down lists to select the preferable font and charset General Directories F
20. R ER EER RR RR EER nnmnnn EE RR RR EER nnmnnn EER Re 70 HTTP tunneling options esse ees RR RR EER RR RR EE EER RR EER RR RR EER RR RR EER R RR ARE EE RR EER RR RR EER RR RR EER RR RR RE EER 71 Configuration file format ees EER RR RR EER RR RR EER RR RR KEEN KEEN GEE RR KAR KEEN RR GE EER RR Re EE RR RR KEEN 72 2012 EMS Database Management Solutions 6 Data Comparer for PostgreSQL User s Manual 1 Welcome to EMS Data Comparer EMS Data Comparer for PostgreSQL is a powerful and easy to use utility for data comparison and synchronization You can view all the differences in the tables being compared and execute an automatically generated script to eliminate these differences With flexible customization of the comparison and synchronization process you can select tables and fields for comparison and tune many other options Data Comparer for PostgreSQL includes a graphical wizard guiding you through the data comparison and synchronization process step by step and a command line service for synchronizing data in one touch Visit our web site http www sqlmanager net for details Key features User friendly wizard interface Several interface languages available English German Russian and French Data comparison of several tables simultaneously Automatic and manual selection of data being compared Wide range of synchronization parameters Unicode data support Partial data synchronization Saving data s
21. Rich edit Image Html Multimedia Having finished browsing the BLOB data you can close the editor and continue browsing data comparison results in the grid See also Exporting comparison results 2012 EMS Database Management Solutions 38 Data Comparer for PostgreSQL User s Manual 2 4 2 Exporting comparison results When the comparison process is finished you can export comparison results using the corresponding dialog To open the dialog use the Export result list EN button which is available under the grid on the right Export comparison results E3 Export type 9 Current table All tables Export directory 2 Export format 6 MS Excel HTML RTF MS Excel 2007 Record types v Identical records V Different records V Missing records V Additional records 0 EI Open output file after export Export type amp Current table export comparison results for the current table only amp All Tables export comparison results for all tables Export directory Type in or use the B button to specify the output directory name and its location using the standard Save As dialog Export format This group allows you to select format of the output file MS Excel RTF HTML amp MS Excel 2007 Record types Use this group to define which records should be exported to the specified file VI Identical records colored in the grid VI Missing records colored in the grid VI
22. assword is the Linux Windows user password Please note that PostgreSQL host name should be set relatively to the SSH server in this case For example if both PostgreSQL and SSH servers are located on the same computer you should specify localhost as host name instead of the server external host name or IP address VI Use Private Key for authentication If the SSH encryption is enabled on the SSH server a user can generate a pair of cryptographic keys the Private key and the Public key The Public key is placed on the SSH server and the Private key is the part you keep secret inside a secure box that can only be opened with the correct passphrase or an empty string as the passphrase When you wish to access the remote system you open the secure box with your passphrase if any and use the private key to authenticate yourself with the Public key on the remote Linux computer SSH Key file Specify the location the secure box of the Private key file on your local machine Supported Private Key file formats are OpenSSH Putty SSH com Note that you need to trust your local machine not to scrape your passphrase or a copy of your Private key file while it is out of its secure box Passphrase dialog Please enter the passphrase for the key Cane 2012 EMS Database Management Solutions 71 Data Comparer for PostgreSQL User s Manual 5 2 HTTP tunneling options To use HTTP tunneling just upl
23. between tables of the source and the target databases If you wish to setup correspondence between tables as well as between their fields automatically on the basis of equivalence of their names press the Autofill tables and fields button If no correspondence is set for a table it will not be included in the data comparison process For your convenience the Mask for autofill of object names is added The filter is intended for excluding unnecessary tables from auto filling You can use standard wildcards like asterix or percent sign which are the same or the question mark To exclude the object names set in the filter use the exclamation mark at the beginning e g the result for T stands for all objects except the object names starting with T If any of these symbols presents the name of the object and you do need to apply filter to them just double that symbol e g the result for T will result in all objects with names that start with T 2012 EMS Database Management Solutions 28 Data Comparer for PostgreSQL User s Manual Data filter Click the button in the Data Filter column to set filter for comparing data Only data within specified condition will be compared and synchronized Use sr el buttons to Add Remove conditions and drop down lists to select column names and operators Note You can apply either OR or AND operator between conditions Data Filter HIRE DATE x
24. d Text dialog is provided for quick and flexible searching for specified text within the Script Editor working area To open this dialog use the Ctrl F shortcut or press the corresponding Find Text button on the toolbar This item is also available in the context menu of the Script Editor area Text to find Enter a search string in this box The Arrow Down button which can be found next to the input box allows you to select any of the previously entered search strings Options VICase sensitive This option can be used to differentiate uppercase characters from lowercase ones during the search process VI Whole words only Use this option to search for words only with this option off the search string might be found within longer words VI Regular expressions Recognizes regular expressions in the search string Find Text ma End Text to find Employee D Options Direction Case sensitive Forward Whole words only Regular expressions Backward Scope Origin Global From cursor Selected text Entire scope Mark search result with stack marker mr ale rule mier Direction Forward Searches from the current position to the end of the Script Editor area 2012 EMS Database Management Solutions 47 Data Comparer for PostgreSQL User s Manual Backward Searches from the current position to the beginning of the Script Editor area Scope Global Searches within the entire
25. d at Step 2 of the wizard When working with the BLOB viewer you can use the drop down list in the top left corner of the window for quick navigation The drop down list allows you to switch the source target fields easily e Gi Blob viewer Target DETAILS Switch between the BLOB viewer tabs to explore the field content The Hexadecimal tab allows you to view the BLOB data as hexadecimal dump Hexadecimal Text Rich edit Image Html Multimedia 0x00 4D 20 5 61 43 6D 6 65 MEMS Data Compare Ox10 20 69 73 20 61 20 70 6F 77 65 72 66 75 6C 20 Mis powerful Ox20 61 6E 64 20 65 61 73 79 2D 74 6F 2D 75 73 65 20 and easy to use Ox30 OD OA 75 74 69 6C 69 74 79 20 66 6F 72 20 64 61 utility for da Ox40 74 61 20 63 6F 6D 70 61 72 69 73 6F 6E 20 61 6E ta comparison an The Text tab allows you to view the BLOB data as plain text 2012 EMS Database Management Solutions 36 Data Comparer for PostgreSQL User s Manual Hexadecimal Text Bich edit Image Html Multimedia Encoding ANSI x EMS Data Comparer is a powerful and easy to use z utility for data comparison and synchronization You can view all the differences in the tables being compared and execute an automatically generated script to eliminate these differences m Specify text encoding in the Encoding drop down list The Rich Text tab allows you to view the BLOB data in Rich Text format RTF Hegadecima
26. d page available at our site e unzip the downloaded file to any local directory e g C unzipped e close Data Comparer application if it is running e run PgDataComparerSetup exe from the local directory and follow the instructions of the wizard See also System requirements 2012 EMS Database Management Solutions 10 Data Comparer for PostgreSQL User s Manual 1 4 Registration To make it easier for you to purchase our products we have contracted with share it registration service The Share it order process is protected via a secure connection and makes online ordering by credit debit card quick and safe The following information about Share it is provided for your convenience Share it is a global e commerce provider for software and shareware sales via the Internet Share it accepts payments in US Dollars Euros Pounds Sterling Japanese Yen Australian Dollars Canadian Dollars or Swiss Franks by Credit Card Visa MasterCard EuroCard American Express Diners Club Bank Wire Transfer Check or Cash If you have ordered EMS software online and would like to review your order information or if you have questions about ordering payments or shipping procedures please visit our Customer Care Center provided by Share it Please note that all of our products are delivered via ESD Electronic Software Delivery only After purchase you will be able to immediately download the registration keys o
27. ding to perform data comparison 2012 EMS Database Management Solutions 16 Data Comparer for PostgreSQL User s Manual Version 1 2 0 1 Viewing editing synchronization script with Script editor The Drop and create indexes synchronization option added For the fields which are not in comparison the default values will be applied now in previous versions NULL values were used Now it is possible to abort synchronization on error with the help of corresponding option The console version of Data Comparer for PostgreSQL now allows running the application in background mode Version 1 1 0 1 New BLOB viewer Higher speed of comparing process The ability to view the connection information is added The Use only compare key for where clause option is available at the specifying data synchronization options step now The Add computed and read only fields to the new table option is added The ability to specify the appropriate client library is implemented Primary key auto setting for the fields to be compared Color highlight for non identical records is implemented The Commit after records option is added See also What s new 2012 EMS Database Management Solutions 17 Data Comparer for PostgreSQL User s Manual 1 7 EMS Data Comparer FAQ Please read this page attentively if you have questions about EMS Data Comparer for PostgreSOL Table of contents e What is EMS Data Comparer
28. e Enter Registration Code button and specifying your registration information Si Data Compares for PostgreSOL Unregistered Copy SECHS Welcome to Data Comparer for PostgreSQL This application allows comparing table data on different servers with further synchronization Click Next to start working with the wizard Product Information Developers Michael Kuzevanov Alexey Butalov PostgreSQL Homepage http www sgimanager net products postgresql datacomparer Support Ticket System http sqlmanager net support Version 3 5 0 1 Unregistered Copy Enter Registration Code Trial Period 30 Day s Left This is a 30 day trial version To make sure you do not receive this notification any more you should purchase a software license and register your software http Aman sqlmanager net products postgresql datacomparer buy Please Note The Trial version is limited to compare 10 tables Press the Next button to proceed to the next step 2012 EMS Database Management Solutions 22 Data Comparer for PostgreSQL User s Manual 2 2 Step 1 Setting connection properties At this step you should specify necessary settings to establish connection to PostgreSQL databases Connection settings First select the connection type local or remote Local connection is used to connect to PostgreSQL server launched on the same machine where Data Comparer for PostgreSQL
29. e Step 4 Specifying data synchronization options which allows disabling triggers when inserting or modifying data for Data Comparer for PostgreSQL The Before synchronization script and After synchronization script on the Step 7 Specifying additional scripts are now executed in the same connection context where the synchronization is performed Script Editor When saving and re opening the editor the character set chosen in the previous session is saved If any database errors occur during the comparison process the program informs about errors and offers to continue The error texts are displayed as a hint in the Summary dialog BLOB Editor Added the possibility to view texts in Unicode The process of selecting tables to compare has become more convenient Added the possibility to disable the code folding in editors Added the possibility to export comparison results of all tables at once Added the possibility to use filters during the code completion Some other small bugfixes and improvements Version 3 0 0 1 1 The comparison and synchronization algorithm is significantly improved and is optimized for working with large databases e The comparison speed is considerably increased in some cases up to 5 times e The synchronization speed is increased especially in cases when compared tables are located on one server e The amount of the consumed RAM is reduced now it does not depend on the number of records in
30. e application wizard if you need to make some changes before data comparison or you can run it with the console application for quicker comparison synchronization e Saving templates e Loading templates Additionally to the GUI version which is implemented in the form of a wizard application the installation package of Data Comparer for PostgreSQL includes the console version which is intended for being run from Windows command line with a template file name used as the execution parameter C Program Files EMS Data Comparer for PostgreSQL gt PgDataComparerc exe Data Comparer for PostgreSQL command line utility is intended for quick and powerful data comparison of PostgreSQL tables e Using the console application See also Using application wizard Setting program preferences 2012 EMS Database Management Solutions 56 Data Comparer for PostgreSQL User s Manual 3 1 Saving templates Data Comparer templates are saved within the Save template options dialog To open this dialog press the Tools button and select the Save template popup menu item 2 Load template db Preferences Reopen template gt Templates can be saved at every step of the wizard Save template options File name Type in or use the button to specify the template file name and its location using the standard Save As dialog Comment If necessary set a comment for your template file in this field EI
31. ecified for comparison Parameter Description Item_Count number of schemas Line lt N gt schema name where N stands for the schema item identifier in the list 2012 EMS Database Management Solutions 76 Data Comparer for PostgreSQL User s Manual Example Item Count 2 LineO TEST Line 1 PRODUCTION Table lt N gt Sections of this type contain table field correspondences N stands for the correspondence identifier as specified at Step 2 of the Wizard application Parameter Description SourceTable source table name TargetTable target table name Synchronize 0 tables will not be synchronized 1 tables SourceTable and TargetTable are specified for synchronization SourceOwner schema owner of the source table SourceTable TargetOwner schema owner of the target table TargetTable lt field_name gt corresponding target table field lt field_name gt _Compa 0 the field is not used as key column for data comparison reKey 1 the field is used as key column for data comparison Example TableO SourceTable EMPLOYEE TargetTable EMPLOYEE_UPD Synchronize 1 SourceOwner DEMO TargetOwner PRODUCTION EMP_ID EMP_NO EMP ID CompareKey 1 FIRST NAME FIRST NAME FIRST NAME CompareKey 0 LAST NAME LAST NAME LAST NAME CompareKey 0 2012 EMS Database Management Solutions 77 Data Comparer for PostgreSQL User s Manual Credits Software Developers Michae
32. ember password option has been selected See also Loading templates 2012 EMS Database Management Solutions 58 Data Comparer for PostgreSQL User s Manual 3 2 Loading templates Data Comparer templates are loaded within the Open template dialog To open this dialog press the Tools button and select the Load template popup menu item LJ Save template d Preferences Reopen template gt Please note that you can reopen a template at any step of the wizard using the corresponding popup menu item of the Tools menu A Load template kd Save template Preferences Reopen template gt Clear template list Clear not existing templates C Templates DataComparerTemplate edc You can Clear template list and Clear not existing templates using corresponding menu items When the template file is loaded you are immediately forwarded to the Editing synchronization script step of the wizard If necessary you can return to any of the previous steps to make appropriate changes or proceed to the last step of the wizard to start the synchronization process See also Saving templates 2012 EMS Database Management Solutions 59 Data Comparer for PostgreSQL User s Manual 3 3 Using Console Application All the comparison options are set in template edc files A template can be also used in the Console version of Data Comparer for PostgreSQL To
33. exes by using corresponding SQL statements in the target script This option increases synchronization speed Note Turn off the Synchronize in new table option to enable this option VI Disable triggers By setting this option you can disable triggers to avoid undesirable effects during data synchronization Note Turn off the Synchronize in new table option to enable this option Disable foreign keys By setting this option you can disable foreign keys to avoid undesirable effects during data synchronization Note Turn off the Synchronize in new table option to enable this option 2012 EMS Database Management Solutions 41 Data Comparer for PostgreSQL User s Manual Data Comparer for PostgreSQL ede Step 4 of 8 Set synchronization options and click Next for creating script Table synchronization options Synchronize from source to target DemoDB on localhost 54391 gt DemoDB on ayz2 54383 Synchronize from target to source table DemoDB on ayz2 54383 gt DemoDB on localhost 54391 Synchronize in new table Postfix of new table name sync Insert additional records Delete missing records Update different records Drop and create indexes Disable triggers Disable foreign keys PostgreSQL ISIS SN e Transaction preferences Commit after synchronization Commit after records Commit after 100 records Tables synchronizat
34. f the wizard and click the Tools Save template button All the options will be saved to the template file which can be used afterwards in the console application How can I register the application If you have already purchased Data Comparer for PostgreSQL you can register the product by entering the appropriate registration information Please refer to Registration and How to register EMS Data Comparer for details PO Q Are there any limitations implied in the trial version as compared with the full one A The trial version of the utility allows to compare and synchronize no more than 10 tables As for the rest the functionality of the trial version does not differ from the full one You can test the features implemented in Data Comparer for PostgreSQL within the 30 day trial period for free 2012 EMS Database Management Solutions 18 Data Comparer for PostgreSQL User s Manual Q How can I automate comparison and synchronization process A First go through all steps of the wizard setting the necessary options and save the template at the last step of the wizard The template can be run with the console version of the utility from the command line You can schedule the launch of the console with the template name as a parameter using native Windows Scheduler tool Scroll to top If you still have any additional questions please contact us at our Support Center 2012 EMS Database Management Solutions
35. fy the script that will be executed on the source server BEFORE data synchronization br Save E Load Clear Specify the script that will be executed on the source server AFTER data synchronization b Save E Load Clear When you are done press the Next button to proceed to the last step of the wizard 2012 EMS Database Management Solutions 52 Data Comparer for PostgreSQL User s Manual 2 9 Step 8 Start of synchronization process This step is intended to inform you that all the synchronization parameters have been set and you can now start the synchronization process itself VI Abort synchronization on error If this option is selected the synchronization process is stopped upon any execution error Detailed error messages Tick off the checkbox to get the detailed error log displayed on the screen The statements for which the execution resulted in errors will be listed in the Data synchronization log area Data Comparer for PostgreSQL Ss S ss Step 8 of amp Click the Synchronization button to start synchronization process Abort synchronization on error J Detailed error messages He Executing synchronization script 11 of 11 statements executed successfully Synchronizing tables DemoDB HR EMPLOYEE and DemoDB HR EMPLOYEE completed 3 of 3 records inserted successfully 1 of 1 records deleted successfull
36. gement Solutions 30 Data Comparer for PostgreSQL User s Manual 2012 EMS Database Management Solutions 31 Data Comparer for PostgreSQL User s Manual 2 3 1 Using the context menus For your convenience the context menus are implemented in the Setting table correspondence and the Setting field correspondence grids within Step 2 of the wizard To call a context menu use standard Windows means right click the grid or use the Shift F10 shortcut The context menu of the Setting table correspondence grid allows you to PS Clear all correspondences Clear selected correspondence s Ei Fil correspondence s for selected object s e clear all table correspondences e clear the selected table correspondences Hint selection of two or more rows in the grid is performed with the Ctrl or the Shift key pressed e find the matching table name in the list of available tables and set the table into correspondence with the selected one The context menu of the Setting field correspondence grid allows you to Ee Clear al correspondences Sei Clear selected correspondence s Dil Set all fields as key CR Unset all fields as key clear all field correspondences for the selected table e clear the selected field correspondences Hint selection of two or more rows in the grid is performed with the Ctrl or the Shift key pressed set all fields of the table as key fields for t
37. he comparison process unset all key fields 2012 EMS Database Management Solutions 32 Data Comparer for PostgreSQL User s Manual 2 4 Step 3 Browsing data comparison results At this step the results of data comparison are represented You can set options for each of the tables being compared Step 3 of amp See the results of comparison Table name DemoDB HR EMPLOYEE DemoDB HR EMPLOYEE E DemoDB on ayz2 54383 lt gt DemoDB on localhost 54391 Comparer for PostgreSQL View identical records View different records View missing records View additional records Number of records on page 100 kal Identical records 280 Different records 5 Missing records 1 j Additional records 3 ee H Table name The drop down list of available tables allows you to select a pair of tables to view their data comparison results In the Synchronize column the pairs to be compared are checked Table name Si hr country sakila country Es hr mymfavorites sakila mymfavorites Es hr mymreports sakila mymreports IAAF The header of the Key field specified at the previous step is marked out with bold font 2012 EMS Database Management Solutions 33 Data Comparer for PostgreSQL User s Manual All data records are divided into several groups distinguished by different colors in the data comparison re
38. ing data the number of records returned from the server is smaller than the expected amount to be synchronized i e because of triggers now a warning is written to the log file e Added the path parameter to the log file in the console version e Some other improvements and bugfixes Version 3 2 0 1 e RAM usage is optimized Now the product consumes less memory when working with large tables e Added the new option Fill correspondence automatically for the console version When enabled new tables in the database created after generating a template are also synchronized and compared when databases are compared 2012 EMS Database Management Solutions 14 Data Comparer for PostgreSQL User s Manual e When loading a template the application can now ask for a password for the database if necessary Added the possibility to encrypt passwords in the template Sometimes table data were sorted incorrectly when displaying comparison results Fixed now e When synchronizing databases located on one server it is now possible to generate synchronization scripts containing table data Added hints for options located on the application forms Other minor improvements and bugfixes Version 3 1 0 1 Added the Disable foreign keys option on the Step 4 Specifying data synchronization options which allows disabling foreign keys when inserting or modifying data Added the Disable triggers option on th
39. ion order Using dependencies Manually View synchronization script Close Transaction preferences amp Commit after synchronization Use this option to commit transaction only when the entire synchronization process is complete If an error occurs during synchronization it will be possible to rollback all the changes made amp Commit after records Define the number of records in each block of the synchronization script to be supplemented with the COMMIT statement Table synchronization order amp Using dependencies If this option enabled synchronization order will be defined automatically according to table dependencies amp Manually In this case synchronization order must be defined manually at Step 5 2012 EMS Database Management Solutions 42 Data Comparer for PostgreSQL User s Manual VI View synchronization script If you select this option you will be able to view edit the synchronization script at Step 6 Otherwise the next step will be skipped and you will be forwarded to Step 7 upon pressing the Next button When you are done press the Next button to proceed to the next step 2012 EMS Database Management Solutions 43 Data Comparer for PostgreSQL User s Manual 2 6 Step 5 Setting synchronization order Use this step of the wizard to set table synchronization order It is available only if the manual table synch
40. l Text Rich edit Image Html Multimedia EMS Data Comparer is a powerful and easy to use utility for data comparison and synchronization You can view all the differences in the tables being compared and execute an automatically generated script to eliminate these differences The Image tab allows you to view the BLOB data as an image FEE Hexadecimal Text Rich edit Image Html Multimedia SQL Manager net EMS Database Management Solutions The HTML tab allows you to view the BLOB data as HTML Hyper Text Markup Language format in the way this data would be displayed by your Internet browser Hexadecimal Text Rich edit Image ml Multimedia EMS Data Comparer is a powerful and easy to use utility for data comparison and synchronization You can view all the differences in the tables being compared and execute an automatically generated script to eliminate these differences With flexible customization of the comparison and synchronization process you can select tables and fields for comparison and tune many other options Visit our web site for details http www salmanager net 2012 EMS Database Management Solutions 37 Data Comparer for PostgreSQL User s Manual The Multimedia tab allows you to view the BLOB data as a multimedia audio video file Use the Play gt Pause nl Stop sl buttons to navigate within the multimedia content Hexadecimal Text
41. l Kuzevanov Alexey Butalov Dmitry Schastlivtsev Alexander Zhiltsov Technical Writers Dmitry Doni Olga Ryabova Serge Slobodenyuk Semyon Slobodenyuk Cover Designer Tatyana Mak urova Translators Anna Shulk ina Serge Fominik h Team Coordinators Alexey Butalov Alexander Chelyadin Roman Tkachenko 2012 EMS Database Management Solutions
42. lacing upon each occurrence of the search string When this option is off the search string is replaced automatically 2012 EMS Database Management Solutions 49 Data Comparer for PostgreSQL User s Manual Replace Text gt Text to find Dept D Text to replace Department el Options Direction Case sensitive Forward Replace with template Backward Prompt on replace E Scope Origin Global From cursor Selected text Entire scope Mark search result with stack marker L o Recea canei II me Scope amp Global Searches and replaces within the entire Script Editor working area in the direction specified by the Direction setting amp Selected text Searches and replaces only within the currently selected text in the direction specified by the Direction setting You can use the mouse or block commands to select a block of text Direction amp Forward Searches and replaces from the current position to the end of the Script Editor area amp Backward Searches and replaces from the current position to the beginning of the Script Editor area Origin amp From cursor The search and replace process starts at the cursor s current position and then proceeds either forward to the end of the scope or backward to the beginning of the scope depending on the Direction setting 2012 EMS Database Management Solutions 50 Data Comparer for PostgreSQL U
43. lt 01 09 2012 AND MANAGER ID ISNUL fl or BIRTHDATE r lt 01 01 1980 D Setting field correspondence The lower grid allows you to set correspondence between table fields of the source and the target databases If you wish to setup correspondence between table fields automatically on the basis of equivalence of their names press the Autofill fields button Please note that you can set field correspondence for fields of identical data types only If no correspondence is set for a field it will not be included in the data comparison process Tick off the checkboxes in the Key Field column for those fields which will be used as key columns to determine appropriate records in the tables being compared Please keep in mind that you can define a key field only after a correspondence has been set for this field Note you can define options for multiple tables fields at a time by using the context menus implemented for your convenience in the Setting table correspondence and the Setting field correspondence grids Attention You should define at least one key field for each pair of tables otherwise they will be painted red and you will be unable to proceed VI Ignore case when comparing strings Set this option for case insensitive comparison of strings VI Compare BLOB values 2012 EMS Database Management Solutions 29 Data Comparer for PostgreSQL User s Manual
44. nchronization script is not executed automatically 1 synchronization script is executed automatically by the console version after template creation DropIndexes O table indexes are not considered 1 table indexes are dropped and then recreated in the synchronization script CommitAlter number of committed records in the synchronization script Autofill 0 tables correspondence set at Step 2 will not be set automatically 1 tables correspondence will be set automatically Comment This section stores the template file comment as specified optionally in the Save template options dialog Parameter Description Line lt N gt comment text where N stands for the comment line identifier Example LineO Data Comparer for PostgreSQL Line1 Template file Line2 Data synchronization 1 SrcAfterScript This section stores the text of the script executed for the source database after data synchronization as specified at Step 6 of the Wizard application Parameter Description Item_Count number of lines Line lt N gt script text where N stands for the script line identifier Example Item Count 2 LineO AFTER synchronization Line1 script to be executed for the source database TrgAfterScript This section stores the text of the script executed for the target database after data synchronization as specified at Step 6 of the Wizard application Parameter Description Item_Count number of lines Line lt N gt script
45. nfirmation upon exiting the program Show complete message If this option is selected the application returns the complete message when data synchronization process is completed at the Start of synchronization process step of the wizard Show all identical tables warning Toggles displaying the warning message in case the compared tables are identical Confirm re compare If this option is selected on attempt to re compare data e g when you need to return to Step 2 to change tables and or fields correspondence you will be prompted to confirm this action Show trigger disabling dialog If this option is checked then the warning message asking about disabling triggers on synchronization is appeared after Step 4 See also 2012 EMS Database Management Solutions 64 Data Comparer for PostgreSQL User s Manual Directories Language Data fonts Interface 2012 EMS Database Management Solutions 65 Data Comparer for PostgreSQL User s Manual 4 2 Directories Use custom directory for cache If this option is enabled cache is loaded into user s folder specified at Cache directory Recommended if no free space is left on the system disk If it s off cache is loaded into Temp folder of the current user General Directories Use custom directory for cache data Language Data font Cache directory amp Interface See also General Language Data fonts
46. o the synchronization table name if SyncInNewTable 1 by default sync 0 synchronization script is saved to an external file upon saving template 1 synchronization script is not saved the parameter is not used by Data Comparer for PostgreSQL path to the file into which the synchronization script is saved if SaveScript 0 0 missing records are ignored during synchronization 1 missing records are inserted during synchronization O additional records are ignored during synchronization 1 additional records are deleted during synchronization 0 different records are ignored during synchronization 1 different records are updated during synchronization 0 synchronization is performed in the source if TargetToSource 1 or in the target if TargetToSource 0 table 1 a new table is created and the synchronized data are inserted into the table the parameter is not used by Data Comparer for PostgreSQL 0 synchronization script is not loaded to Script Editor 1 synchronization script is loaded to Script Editor and displayed at Step 5 of the wizard 0 synchronization process is not stopped if an error occurs 1 synchronization process is stopped upon any execution error 0 detailed error log is not displayed 1 detailed error log is displayed on the screen at Step 7 of the wizard 2012 EMS Database Management Solutions 74 Data Comparer for PostgreSQL User s Manual ExecuteScript 0 sy
47. oad the tunneling script to the webserver where PostgreSQL server is located or to any other webserver from which direct connections to your PostgreSQL server are allowed This script exposes the PostgreSQL API as a set of web services used by Data Comparer for PostgreSQL In case of using this connection method the response will be slower as compared to the direct connection or the SSH Tunneling method since the data are XML encoded and HTTP is stateless by nature However all the features of Data Comparer for PostgreSQL are available Note that the emsproxy php script file is included into the distribution package and can be found in Data Comparer installation directory 2012 EMS Database Management Solutions 72 Data Comparer for PostgreSQL User s Manual 5 3 Configuration file format The configuration template file used by Data Comparer for PostgreSQL is divided into several sections each corresponding to a particular group of settings specified at different steps of the GUI application wizard General This section stores general information about the utility Parameter Product Version SourceDB Description internal product name major version This section stores connection parameters for the source database The parameters correspond to the values entered at Step 1 of the Wizard application and are obligatory Parameter Host Port Remote DBName Login Password Charset F
48. olely for your own personal use Conversion to other formats is allowed as long as the actual content is not altered or edited in any way Document generated on 13 11 2012 3 Data Comparer for PostgreSQL User s Manual Table of Contents Part I Welcome to EMS Data Comparer 6 What s me we unable Sea 7 System requirements EE ME EE 8 Installation WE 9 Registration RR OE ORE AE ER RE RA ET ER 10 How to register EMS Data Comparer rannnvnnnnnnnennnnnnnnnnnnnnnennnnnnnennnnnnnennnnnnnnnnnnnnnennnnnnnennnnn 12 Version history AE a IE N OE N OE N ER AE OE 13 EMS Data Comparer FAQ nnnnnnnnnnnnnnnnnnnnnnnennnnnnnennnnnnnennnnnnnennnnnnnennnnnnnennnnnnnennnnnnnennnnnnnennnnn 17 Part II Using Wizard Application 20 Getting Started ER EE ER AE ED HERE EE EE A 21 Step 1 Setting connection properties rs EE RR RR EER RR AR EER RR RR EER RR RR EER RR RR EER RR RR GE ERK Re 22 Selecting database N N N EE EE DE N 25 Selecting Schemas for refreshing ees ses EE EE EE EE ESE EE ERA ERGE ERGE ERGE REG ERGE RE GE EER REEKSE ERGE ERGER Re REGEER KG ee Ee 26 Step 2 Setting tables and fields correspondence REENEN RR EER AR RE RR EER RR AA RR AE RR Ga 27 Using the context genug ss ER EE N SE Ge ed Ge Pg ee ee OE cadences Ee Re N ee Ge ER ee ee 31 Step 3 Browsing data comparison results ees EER RR AR EER RR RR EER RR RR EER RR RR EER RR mennan nnn 32 View ing BLOB data ies oes ee ER ee ee ese ee Re een Eg ER ee Gee Ve REG
49. ont OH Arial Unicode MS D Interface Charset DEFAULT CHARSET sl Language See also General Directories Language Interface 2012 EMS Database Management Solutions 68 Data Comparer for PostgreSQL User s Manual 4 5 Interface The Interface section of the Preferences dialog allows you to choose the application interface style according to your liking Use the Scheme name drop down list to select an interface scheme according to your liking Office 11 Office XP Office 2k Windows XP and Classic styles General Directories Language Data font Scheme name Dfficel1 D sample text edit Sample combo box Sample button edit LJ Sample button 2 Sample button 3 M Sample button 1 Sample group 7 Sample check box 1 Sample radio button Below the Scheme name drop down list you can view sample controls of the chosen scheme 2012 EMS Database Management Solutions 70 Data Comparer for PostgreSQL User s Manual 5 Appendix 5 1 SSH tunneling options To setup the connection via SSH tunnel input the following values in the corresponding fields e SSH host name is the name of the host where SSH server is running e SSH port indicates the port where SSH server is activated e SSH user name stands for the user on the machine where SSH server is running Note it is a Linux Windows user not a user of PostgreSQL server e SSH p
50. ontCharset TunnelType SSHHostName SSHPort SSHUserName SSHPassword SSHKey File SSHUsekKey File HTTPurl TargetDB Description host where the source database resides if Remote 7 port on which PostgreSQL is listening 0 local connection 1 remote connection source database name PostgreSQL login password to identify the login encrypted client character set specified for the connection the parameter is not used by Data Comparer for PostgreSQL indicates the tunneling type being used SSH HTTP or none TunnelType ttNotUse name of the host where SSH server is running port on which SSH server is activated user on the machine where SSH server is running password to identify SSH server user encrypted path to the Private Key used for the SSH connection if SSHUsekeyFile True True SSH Private Key is used False SSH Private Key is not used URL to the emsproxy php script file uploaded to your web server for HTTP tunneling This section stores connection parameters for the target database The parameters correspond to the values entered at Step 1 of the Wizard application and are obligatory The set of parameters is the same as for the source database SourceDB Options This section stores comparison options The parameters correspond to the values specified at Step 2 and Step 3 of the Wizard application 2012 EMS Database Management Solutions 73 Data Comparer for Postg
51. parer 2010 for PostgreSQL Data Comparer 2010 for PostgreSQL Data Comparer 2007 for PostgreSQL Data Comparer 2007 for PostgreSQL Data Comparer 2007 for PostgreSQL Data Comparer 2005 for PostgreSQL Data Comparer 2005 for PostgreSQL Full version history is available at htt datacomparer news Version 3 4 0 1 e Now the comparison results can be exported to MS Excel 2007 Version Version 3 4 0 1 Version 3 3 0 1 Version 3 2 0 1 Version 3 1 0 1 Version 3 0 0 1 Version 2 3 0 1 Version 2 2 0 1 Version 2 1 0 1 Version 2 0 0 1 Version 1 2 0 1 Version 1 1 0 1 www sqimanager net Release date March 7 2012 August 29 2011 March 21 2011 November 01 2010 May 04 2010 March 10 2009 October 2 2008 December 19 2007 February 5 2007 May 30 2006 March 14 2006 roducts postgresal e The progress window now displays the time remaining and the number of records per second e Data Filter Added the OR and AND operations e When selecting text fields as key fields the table comparison speed significantly increased e Added the possibility to save a template at any step of the wizard e Some other improvements and bugfixes Version 3 3 0 1 Added the possibility to compare data using filters Added the Trim CHAR Fields option which allows to trim spaces in CHAR and NCHAR fields while comparing and synchronizing data e Added the Jump List with a list of templates for Windows 7 If while synchroniz
52. ption has been selected v Show table definition hints This option enables disables popup definition hints for the tables upon moving the cursor on their aliases the Set tables and fields correspondence step of the wizard Save current options on exit Setting this option allows you to save all the comparison options automatically upon closing the application Show summary dialog after comparison If this option is enabled comparison summary report will be displayed at Step 2 v Disable code folding in editors disable the feature of the hierarchical script structure that allows hiding sub objects in editors Code folding Show schema selection dialog If this option is checked the list of PostgreSQL database schemas is displayed before you proceed to Step 2 of the wizard The list allows you to select schemas to be refreshed 2012 EMS Database Management Solutions 63 Data Comparer for PostgreSQL User s Manual Preferences amp Directories Se Language IR EEE Data font FE vw Interface Y Show table definition hints Cd Save current options on exit V Show summary dialog after comparison F Disable code folding in editors o V Show schema selection dialog Messages dl Confirm exit V Show complete message J Show all identical tables warning V Confirm re compare V Show trigger disabling dialog Messages Confirm exit Enables disables co
53. r passwords and download links for archives of full versions Also you will receive a copy of registration keys or passwords by e mail Please make sure to enter a valid e mail address in your order If you have not received the keys within 2 hours please contact us at sales sqlmanager net Product distribution Data Comparer for PostgreSQL Business license 1 Year Maintenance Data Comparer for PostgreSQL Business license 2 Year Maintenance Data Comparer for PostgreSQL Business license 3 Year Maintenance Data Comparer for PostgreSQL Non commercial license 1 Year Register Maintenance Now Data Comparer for PostgreSQL Non commercial license 2 Year Maintenance Data Comparer for PostgreSQL Non commercial license 3 Year Maintenance Data Comparer for PostgreSQL Trial version Download Now EMS Maintenance Program provides the following benefits e Free software bug fixes enhancements updates and upgrades during the maintenance period e Free unlimited communications with technical staff for the purpose of reporting Software failures e Free reasonable number of communications for the purpose of consultation on operational aspects of the software After your maintenance expires you will not be able to update your software or get technical support To protect your investments and have your software up to date you need to renew your maintenance You can easily reinitiate renew your maintenance with our on
54. reSQL User s Manual Parameter Blobs IgnoreCase Fillidentical ViewRecordsCount ThreadCount SavePassword PasswordEncripted TrimCharFields Description O BLOB fields are not specified for comparison 1 BLOB fields are specified for comparison 0 case is considered when comparing strings 1 case is ignored when comparing strings O identical records are not displayed at Step 3 of the wizard 1 identical records are displayed at Step 3 of the wizard number of records displayed on one page at Step 3 of the wizard number of comparison threads O Password is not saved in template file 1 Password is saved in template file O Password is not encrypted in template file 1 Password is encrypted in template file O CHAR fields are trimmed on synchronization 1 CHAR fields are not trimmed on synchronization CompareOnServerSideO comparison is performed on the client SyncOptions 1 comparison is performed on the server This section stores synchronization options The parameters correspond to the values specified at Step 4 and Step 7 of the Wizard application Parameter TargetToSource TablePostfix SaveScript DisableTriggers ScriptFileName InsertMissRecs DeleteAddRecs UpdateDiffRecs SyncInNewTable CreateComputed LoadToScriptEditor AbortExecuteOnError DetailErrors Description 0 synchronization from source to target 1 synchronization from target to source postfix added t
55. ring or Number of records on page options you should use the Refresh result list a button Summary Pressing the Summary button allows you to preview the result of object comparison 2012 EMS Database Management Solutions 34 Data Comparer for PostgreSQL User s Manual Identical Different Missing Additional Comparing time COUNTRY 14 Jo Jo jo 100 00 00 342 00 00 00 283 00 00 00 256 00 00 00 439 00 00 00 319 If an error occurs the line is highlighted red The error type is displayed in the hint that appears when moving the cursor over the error If necessary you can La export comparison results to MS Excel HTML RTF or MS Excel 2007 using the corresponding dialog See the Exporting comparison results page to learn more about this feature When you are done press the Next button to proceed to the next step 2012 EMS Database Management Solutions 35 Data Comparer for PostgreSQL User s Manual 2 4 1 Viewing BLOB data Data Comparer for PostgreSQL provides a BLOB viewer for browsing the content of BLOB Binary Large Object fields being compared The tool can be invoked from the data grid at Step 3 by clicking the ellipsis t button next to a record of the BLOB field Use the combo box control in the upper area of the viewer to specify the field for viewing Please note that BLOB Viewer is only available if the Compare BLOB values option has been checke
56. ronization order option was selected at the previous step Use the up and down buttons or drag and drop move the selected table pair in the list Step 5 of 8 Set tables order for synchronization Source table Target table ES HR DEPARTMENT HR DEPARTMENT FE HR EMPLOYEE HR EMPLOYEE Data Comparer for PostgreSQL Click the Next button to proceed with the next step of the wizard 2012 EMS Database Management Solutions 44 Data Comparer for PostgreSQL User s Manual 2 7 Step 6 Editing synchronization script This step of Data Comparer wizard allows you to view and edit the synchronization script This step is only available if the VI View synchronization script option has been selected at Step 4 Using the Script Editor area you can perform basic editing operations and or toggle comments If necessary you can save the result synchronization script to an external sq file using the Shift Ctrl S shortcut or the b Save as toolbar button Ef Deta Compares for PostgreSOL S ss Step 6 of amp View edit synchronization script Fa Synchronization for tabl Markers lt DemoDB HR DEPARTMENT Toggle Bookmarks ITMENT e lt DemoDB HR EMPLOYEE gt al Data Type Source to target Comparer for BEGIN PostgreSQL vi Go to Line Number Delete missing records DELETE FROM HR EMPLOYEE Paste Update different records Select
57. ser s Manual 9 Entire scope The search and replace process covers either the entire block of selected text or the entire script no matter where the cursor is in the Editor area depending upon the Scope options v Mark search result with stack marker The option toggles marking search results If this option is selected stack markers are set at all search positions this makes it possible to jump from one marker search result to another within the text Click the Replace All button to replace every occurrence of the search string If you have checked the Prompt on replace option the confirmation dialog box appears upon each occurrence of the search string See also Using the Find Text dialog 2012 EMS Database Management Solutions 51 Data Comparer for PostgreSQL User s Manual 28 Step 7 Specifying additional scripts This step allows you to specify additional scripts to be executed for the source database before and or after the main synchronization script execution Use the respective editors to create scripts Before synchronization script and After synchronization script The statements can be typed in directly or pasted from the clipboard use the context menu of the editing area for this purpose The Save and Load buttons provide saving loading script file operations for the current editor window The Clear button erases the text and clears the current editor window Speci
58. sibility to output the detailed error description in the console application Optimized the script generation mechanism now only different fields are added to the synchronization script Added tools for printing synchronization logs e Other minor improvements and bug fixes Version 2 1 0 1 e Now you are able to set up SQL statement batches which will be executed before and or after the synchronization process e The possibility to select DB schemas for data comparison is added e Fields of the serial type are set to nextval after synchronization is complete e Tables for which the set of key fields differs from primary key fields are synchronized significantly faster now Table lists are sorted by table and schema name now The possibility to save synchronization logs to a file is implemented The number of DB connections coincides with the number of selected threads Other minor improvements and bug fixes Version 2 0 0 1 Multi threading for data comparison Implementation of Private Key support for SSH authentication Boosted synchronization speed A more convenient way of browsing differences in compared data Implementation of the Find Text dialog in Script editor Processing of Foreign keys implemented An opportunity to select tables after data comparison Export of comparison results to MS Excel documents The Detailed error messages option at the synchronization step Encrypted passwords storage Implemented support for UTF 8 enco
59. sult grid identical records are the same in both tables different records are those having different data in one or more fields text of different records is marked out with bold font missing records are the records found in the table of the source database but not in the table of the target one additional records are the records found in the table of the target database but missing in the table of the source one Note To view the BLOB data you can use internal BLOB viewer To learn more see Viewing BLOB data Use the check boxes column to filter records that you want to synchronize If the record is checked then it will be changed in the target table You can sort data by the needed column Simply click a column title to sort the data Use the following options to filter data v View identical records Select this option to view records which are identical in source dataset and target one VI View different records Select this option to view records which vary from the source dataset to the target dataset VI Missing records Use this option if records missing from the source dataset should be displayed at the comparison result list VI Additional records Enable this option to view records missing from the target dataset Number of records on page This value determines the quantity of records displayed as one page in the grid Use the EI EI H CI buttons for navigation through the pages To apply changes in filte
60. tabase Management Solutions 26 Data Comparer for PostgreSQL User s Manual 2 2 2 Selecting schemas for refreshing Before you proceed to the Setting tables and fields correspondence step of the wizard you are offered to specify the schemas to be refreshed using the Select schemas for refreshing dialog Hint For your convenience the context menu is available in both the Source schemas and Target schemas lists Using the context menu you can Za Check all 1 Uncheck all and d Invert items selection in the lists Select schemas for refreshing IS Source schemas list v v HR Production Test public Target schemas list v HR Production Test public Refresh all schemas and don t show this window in the future o TI Cancel J Help Refresh all schemas and don t show this window in the future Set this option to specify all of your PostgreSQL schemas for refreshing and skip this dialog in the future sessions of the wizard Note To activate deactivate this dialog use the Show select schemas window option in the General section of the Preferences dialog When you are done press OK to proceed to Step 2 of the wizard 2012 EMS Database Management Solutions 27 Data Comparer for PostgreSQL User s Manual 2 3 Step 2 Setting tables and fields correspondence At this step you should select objects for data comparison
61. th SP4 or later Microsoft Windows 2000 Microsoft Windows 2000 Server Microsoft Windows XP Microsoft Windows 2003 Server Microsoft Windows 2008 Server Microsoft Windows Vista Microsoft Windows 7 Microsoft Windows 8 64MB RAM or more 128MB or more recommended e 20MB of available HD space for program installation Super VGA 800x600 or higher resolution video adapter and monitor Super VGA 1024x768 or higher resolution video adapter and monitor recommended e Microsoft Mouse or compatible pointing device Possibility to connect to any local or remote PostgreSQL server Supported PostgreSQL server versions from 7 3 up to 9 2 2012 EMS Database Management Solutions 9 Data Comparer for PostgreSQL User s Manual 1 3 Installation If you are installing Data Comparer for PostgreSQL for the first time on your PC e download the Data Comparer for PostgreSQL distribution package from the download page available at our site e unzip the downloaded file to any local directory e g C unzipped e run PgDataComparerSetup exe from the local directory and follow the instructions of the installation wizard e after the installation process is completed find the Data Comparer shortcut in the corresponding group of Windows Start menu If you want to upgrade an installed copy of Data Comparer for PostgreSOL to the latest version e download the Data Comparer for PostgreSOL distribution package from the downloa
62. the Script Editor area Text to find Enter a search string in this box The Arrow Down button which can be found next to the input box allows you to select any of the previously entered search strings Text to replace This box allows you to enter a string to replace the search string The Arrow Down button which can be found next to the input box allows you to select any of the previously entered strings To replace the search string with an empty string leave this input box blank Options Case sensitive This option can be used to differentiate uppercase characters from lowercase ones during the search process Whole words only Use this option to search for words only with this option off the search string might be found within longer words VI Regular expressions Recognizes regular expressions in the search string Y Replace with template This option requires the Regular expressions option selection Enable this option to use regular expressions in the Text to replace field Expression used in this field will be applied to each string that matches the Text to find expression Note The syntax of regular expressions that can be used in the Text to find and the Text to replace fields is similar to that used in Perl regular expressions Comprehensive information about it can be found at http perldoc perl org perlre html Regular Expressions Y Prompt on replace Check this option if you wish to be prompted before rep
63. y 5 of 5 records updated successfully Synchronizing tables DemoDB HR DEPARTMENT and DemoDB HR DEPARTMENT completed 0 of 0 records inserted successfully 0 of 0 records deleted successfully 0 of 0 records updated successfully Synchronization process completed Tools lt Back Synchronization If everything is correct press the Synchronize button to start the process If you want to change any options you can return to any of the wizard steps using the Back button You can bi save process log into rtf file or print it instantly using the corresponding buttons 2012 EMS Database Management Solutions 53 Data Comparer for PostgreSQL User s Manual Note When you press the el print button the utility saves the current log text as a temporary rtf file to open it with the editor that is associated with this file extension in OS and then print Please do not forget to save comparison templates if you need to repeat the synchronization process with the same or similar settings later 2012 EMS Database Management Solutions 55 Data Comparer for PostgreSQL User s Manual 3 Using Configuration Files Templates Data Comparer for PostgreSQL allows you to store its comparison and synchronization settings in external template edc files if you need to perform the data comparison synchronization process repeatedly You can load a previously saved template to th
64. ynchronization script to a file for future use The ability saving all the parameters specified within the current wizard session The command line utility to compare and synchronize data with a template used The possibility to compare data using filters Product information Homepage http www sqlmanager net en products postgresql datacomparer Support Ticket http www sqlimanager net support System Register online at http www sqlmanager net en products postgresql datacomparer bu 2012 EMS Database Management Solutions 7 Data Comparer for PostgreSQL User s Manual 1 1 What s new Version Release date Data Comparer for PostgreSQL 3 5 0 1 November 15 2012 What s new in Data Comparer 3 5 e Added the possibility to manually set a list of synchronized records Now it is possible to check for active triggers in the synchronized tables If there are triggers in the target table you will be prompted to disable them Added the possibility to set filters on the basis of the LIKE operator e Now at the Setting tables correspondence step a filter can be viewed as the pop up window See also Version history 2012 EMS Database Management Solutions 8 Data Comparer for PostgreSQL User s Manual 1 2 System requirements System requirements for Data Comparer for PostgreSQL e 300 megahertz MHz processor 600 megahertz MHz or faster processor recommended Microsoft Windows NT4 wi

Data Comparer for PostgreSQL - User`s Manual

Contents

Download Pdf Manuals

Related Search

Related Contents