Product FAQ

EMS SQL Manager for PostgreSQL is a powerful graphical tool for PostgreSQL administration and development. It makes creating and editing PostgreSQL database objects easy and fast, and allows you to run SQL scripts, manage users and their privileges, build SQL queries visually, view database statistics, extract and print metadata, export/import data, view and edit BLOB fields, and much more.

First of all you must have an opportunity to connect to some local or remote PostgreSQL server. Besides you need your computer to satisfy the system requirements of EMS SQL Manager for PostgreSQL.

These editions of SQL Manager for PostgreSQL differ in their functionality. Being a light edition, SQL Manager Freeware has certain restrictions, for example, it can handle not more than 5 databases, and some others. You can view the list of all functional differences between full and freeware versions at our Features Page.

EMS Data Export/Import for PostgreSQL includes some additional features, which are not available in SQL Manager for PostgreSQL such as:

  • export/import data from/to several tables at once;
  • export/import data from/to tables selected from different databases on one host;
  • command line utility to export/import data using the configuration file with all the export/import options.

First of all, EMS SQL Query for PostgreSQL works faster as it is a much lighter product. Besides it provides additional features for query building, e.g.:

  • "favorite" queries to find and execute often-used queries quickly;
  • keeping query history, which allows you to rollback to any edited query;
  • various interface improvements for more productive and easy work.

EMS DB Extract for PostgreSQL includes some additional features, which are not available in EMS SQL Manager for PostgreSQL, such as:

  • extracting metadata from several databases on one host;
  • console application for executing extract in one-touch;
  • faster execution speed.

The DDL tab of all the object editors is read-only. It displays the SQL text of the operations you carry over the object on other editor tabs. To modify this text you can copy it to the clipboard and modify it using SQL Script Editor.

SSH (Secure Shell Host) protocol is used to heighten computer security when working with Unix systems on the Internet. SSH uses several encryption algorithms of different reliability. The spread of SSH is also connected with the fact that a number of Linux-like OS's (for example FreeBSD) include SSH server in their standard integration. To learn more information on this issue, please, visit http://openssh.org. SSH tunneling feature of SQL Manager is a means of ensuring secure connection to PostgreSQL servers when working over insecure connection channels. You can also use SSH tunnel to get access to the remote PostgreSQL servers when port 5432 is closed for external connections due to some reasons. The connection over SSH tunnel works in the following way. First a connection is established and authentication between SSH client built in SQL Manager and remote PostgreSQL server is performed. Then all incoming and outgoing information between the program and PostgreSQL server is transmitted through SSH server with the help of communication port (usually it is 22), and SSH server transfers this information directly to PostgreSQL server. To register a database to which one connects through SSH tunnel, you should set the following parameters in Database Registration Wizard of SQL Manager for PostgreSQL: On the first step:
- Host is a host where PostgreSQL server is located from the point of view of SSH server. If SSH and PostgreSQL server are on the same machine, it is equal to SSH Host, or may be 'localhost'.
- Port is a port of PostgreSQL server on Remote Host, by default it is 5432.
- User Name is a PostgreSQL user name.
- Password is a password of the PostgreSQL user.
Please note that PostgreSQL host name should be set relative to the SSH server in this case. For example, if both of PostgreSQL and SSH servers are located on the same computer, you should specify localhost as Host name instead of server's external host name or IP address. Please, do not forget to choose Use tunneling and SSH tunneling.
On the second step:
- SSH host name is a host where SSH server is activated.
- SSH port is a port where SSH server is activated.
- SSH user name is a user on Linux machine. (It is a Linux user. It is not a user of PostgreSQL server.)
- SSH password is a Linux user password.

You need to input the following:
On SSH tab:
- SSH host name is a host where SSH server is activated.
- SSH port is a port where SSH server is activated.
- SSH user name is a user on Linux machine. (It is a Linux user. It is not a user of PostgreSQL server.)
- SSH password is a Linux user password.
On General tab:
- Host is a host where PostgreSQL server is located from the point of view of SSH server. If SSH and PostgreSQL server are on the same machine, it is equal to SSH Host, or may be 'localhost'.
- Port is a port of PostgreSQL server on Remote Host, by default it is 5432
- User name is a PostgreSQL user name.
- Password is password of the PostgreSQL user.

HTTP tunneling is a method that allows to connect and transmit data between the program and a PostgreSQL server through the HTTP/HTTPS protocols using port 80, which is used by a regular internet browser. This method is used to connect to the remote PostgreSQL server of a hosting company when direct connection is not available because of the security reasons. The HTTP tunnel works the following way: All outgoing queries and commands sent by the client's software are encoded and transmitted through the HTTP/HTTPS protocol using port 80 to the special script that decodes the received data and sends it for processing to PostgreSQL server and then sends the result back. This method requires the HTTP server (Apache) and PHP with PostgreSQL to be installed on the remote server. Normally this software is provided by a hosting company that offers Linux hosting solutions. Also you need to upload the special emsproxy.php script to your web-server to access it remotely (you can place it to the folder with your other PHP scripts). If your web-server complies with the requirements and the script is installed correctly, you'll see the message "EmsProxy v 1.31" (version number can be different) in your browser when opening the http:// /emsproxy.php page. To register a remote database that will be accessed through the HTTP tunnel you have to enter the following parameters in the Database Registration Wizard of SQL Manager for PostgreSQL.
The First step of the Wizard:
- Host is a host where PostgreSQL server is located from the point of view of HTTP server. Usually HTTP and PostgreSQL server are on the same machine, and it is 'localhost'.
- Port is a port of PostgreSQL server on Remote Host, by default it is 5432.
- User Name is a PostgreSQL user name.
- Password is a password of the PostgreSQL user.
- Check "Use tunneling" and "HTTP tunneling".
The Second step of the Wizard:
- URL is the address of emsproxy.php script (e.g. http://mywebserver /emsproxy.php).

You need to switch off the "Convert created object's names to lower case" option in Environment Options dialog, on Tools | Object Editors tab.

To get data source in a report you need to add a dialog form to the report (menu 'Edit' 'Add dialog form' of Report Designer), then place database and query components from the left designer panel on it and set connection and query properties. Then the query appears in data source lists.

For your convenience and to speed up your work Data Grid allows to customize many data display parameters. Here are the most important of them (accessible through Grid | Data Options tab of Environment Options dialog):
- Limit options in table and view editors. The “Select all records of a table” option will enable you to see all table records without extra references to the server, yet in case of large tables or low speed connection channel the data may be fetched with huge delays and the incoming traffic might grow considerably. This mode is recommended when working with local databases or in a private network. The “Select only” mode restricts the maximum number of records returned after the query. Man can't process a massive amount of information at once. Hence, we came up with this mode. To request and display the next portion of data use the Next button in the Data Grid toolbar. This mode speeds up table data viewing considerably, prevents hanging and connection timeout. It is recommended to work with large tables, in case of low speed connection channels and when the traffic volume is of importance. This is a default mode. With this mode enabling the “Use SQL sorting in data view” and “Use SQL filter in data view” options comes really helpful.
- Default Grid Mode. This option defines whether the requested rows will be loaded in the Grid all at once ("Load all rows"), or in parts ("Load visible rows") as the user scrolls down table data. The first mode increases the query opening time but speeds up scrolling. In the second mode the query opens very fast but there might be delays when navigating the grid.
- Use SQL sorting in data view. With this option enabled, when the user sets data sorting a new request is sent to the server, and the result is shown anew in the Grid. Enabling this option is very helpful with the “Select only” mode enabled, but it increases the traffic. When this option is disabled, the sorting is made on the client computer without any references to the server but only loaded data are sorted. That is, if you set "Select only 1,000 records", then only these records will be sorted.
- Use SQL filter in data view. With this option enabled, the data will be sorted on the server to which a new request is sent, and the result will be shown anew in the Grid. Enabling this option is also helpful when the “Select only” mode is on, but it increases the traffic. When this option is disabled, the sorting is made on the client computer without any references to the server but only loaded data are sorted. That is, if you set "Select only 1,000 records", then only these records will be sorted.
We recommend that you set the following option values to achieve maximum efficiency when working with large tables:
- Select only - On
- Load visible rows – On
- Use SQL sorting in data view – On
- Use SQL filter in data view – On

You need to set Grid Mode for the table to 'Load Visible Rows'. Please right click on the table's grid and select 'Grid Mode' 'Load Visible Rows' context menu item.

Database Registration Info dialog contains Log tab where you can enable logging metadata changes, which are performed on a database, and SQL statements, that are executed in SQL Editor.

Columns of types TEXT, BYTEA, OID and also VARCHAR of large size or without size definition are not exported by default. You should select these columns manually on the Columns tab.
"Export As SQL Script" is intended to export table data that will be inserted into a database system other than PostgreSQL server. Use "Extract Database" to copy data to a table on PostgreSQL server.

Follow the steps below to change directory:
- Right click on the database you need in DB Explorer and click on "Database Registration Info..." item in popup menu (you can also find this item in main menu "Database"). The Database Properties form will be opened.
- Click on the "Directories" tab.
- In the "Default Directory for Export Data" section you can choose the default directory for export file.

You need to increase timeout values on Tools/Timeouts tab of Environment Options dialog, or set them to 0 - unlimited.

Please, make sure that the libpq.dll file exists in EMS SQL Manager for PostgreSQL directory. Reinstallation of the application can solve the problem.

You can customize all display formats: integer, float, date, time and date/time in the Environment Options window, on Grid/Color & Formats tab.

Yes, that is possible. It doesn't matter what OS the PostgreSQL Server is under, the SQL Manager works only in the Windows environment.

If due to the security policy accepted in your corporate network or by your host provider you cannot connect to your PostgreSQL server directly through TCP/IP (for example a firewall prevents this), you can use SSH or HTTP tunneling feature of SQL Manager to fulfill your task.

1. Make sure that there is a record for your client PC in pg_hba.conf file on PostgreSQL server. This file can be found in database cluster's data directory.
2. If you connect to the remote server, then the TCP/IP connections should be allowed on the remote server. In this case you need to edit file postgresql.conf on the server: just add the name or TCP/IP of the client PC in the listen_addresses list. Or you can as well put * character; in this case the connection from any host will be allowed. You can get the same result if you run the server with -i option, which is the same as listen_addresses = '*'.
3. Make sure that you enter the correct login/password.
4. You use password authentication, but it is not supported in SQL Manager.

SQL Manager stores all your settings in the Windows registry. It means that the only way to transfer your database data is to get access to the Windows registry. You can do it by loading OS from your old HDD (if possible) or by opening the registry file with a special editor program. If you managed it, you can unload the 'HKEY_CURRENT_USER\Software\EMS\PostgreSQL Manager' branch to the *.REG file, transfer this file to a new system and add information to the registry by double-clicking the file.