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 Parameters 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 Parameters 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;