PostGIS Troubleshooting

Table Listing in the PostGIS Reader

If the table list in the PostGIS Reader Parameters box does not display your table, try the following:

  1. Type the table name by itself. If this works, then your table may not be properly registered in the PostGIS metadata tables or it may not have a geometry column. If this does not work, try step 2 or 3 below.
  2. Type the name with the schema prefix, i.e. public.mytable. If this works, it could mean that you likely did not specify the correct schema to search in the settings box. If this does not work, try step 3.
  3. If both steps above do not work, it is possible that the table does not exist. You can verify this by looking at the table list provided by the PostgreSQL Reader Parameters box.

If the table list in the PostGIS Reader Parameters box lists your table, but you receive an error message that the table does not exist when you run the translation, then it is likely that the PostgreSQL table has been deleted without updating the PostGIS metadata tables. Orphaned metadata may continue to exist in the PostGIS metadata tables. It is suggested that the PostGIS metadata table for the geometry columns be corrected to match only existing PostgreSQL tables.

Connecting to a PostgreSQL/PostGIS Database

Problems sometimes arise when attempting to connect to a PostGIS/PostgreSQL database. This is almost always due to a misconfiguration in the user’s environment.

The following suggestions can often help detect and overcome such problems.

  • Ensure you can connect to the database with the host, port, database, user name, and password using psql or pgAdmin. See PostgreSQL documentation for proper security and connection information, and for the usage of the psql utility.
  • If you try to list the tables and nothing happens, check the log file. There may have been an underlying error that didn’t generate a dialog. Usually this means a parameter does not exist or permissions are not sufficient to access the requested resource.
  • In most cases, the POSTGIS_DATABASE directive should be left with blank values, with the POSTGIS_DATASET directive containing the name of the PostGIS database.
  • When using a UNIX operating system, the environment variables PGHOST, PGPORT, PGDATABASE, PGUSER and PGPASSWORD can be used to specify the PostgreSQL connection parameters.
  • If the table list in the PostGIS reader input settings box does not display your table, try typing the name with the schema prefix (e.g., public.mytable). If this works, then your table may not be properly registered in the PostGIS metadata tables or it may not have a geometry column.
  • If the table list in the PostGIS reader input settings box lists your table, but you receive an error message that the table does not exist when you run the translation, then it is likely that the PostgreSQL table has been deleted without updating the PostGIS metadata tables. Orphaned metadata may continue to exist in the PostGIS metadata tables. It is suggested that the PostGIS metadata table for the geometry columns be corrected to match only existing PostgreSQL tables.

Connecting to PostgreSQL/PostGIS Tables in Another User's Schema

FME uses the Postgres search path to determine which schemas' tables to show in the table list. To set a user's search path for a session:

SET search_path TO "$user",public;

SET search_path TO "$user",public,schema2,schema3;

To set a user's search path for all future sessions:

ALTER USER <username> SET search_path TO "$user",public;

ALTER USER <username> SET search_path TO "$user",public,schema2,schema3;

To see the current search path:

show search_path;