I am trying to do a pg_dump using command line in the system caller. It is failing, when i implemented the same command in the linux terminal, it is prompting for the password. I tried all the ways to pass the password value, nothing has worked out.

What command are you putting into the SystemCaller? Is it prompting for anything else other than the password?

SELECT 'pg_dump --data-only --host $(POSTGIS_HOST) --port $(POSTGIS_PORT) --username $(POSTGIS_USERNAME) --password $(POSTGIS_PASSWORD) --format plain --verbose --file $(PostGISExtractFileLocation) ' ||

 array_to_string(ARRAY(SELECT ' --table ' || table_schema || '.\\"' || table_name  || '\\"'

    FROM information_schema.tables 

    WHERE table_schema IN('$(postgres_schema_name)')

    and table_name not like '%domain%'

  ), ' ') || ' $(POSTGIS_DBNAME)' as result

The above is the command which i am first passing in the SQLExecutor which selects the particular schemas, tables and then the result is passed into the system caller which is getting stuck. I tried many ways, but not successful.

I suspect the flag "--password" isn't doing what you think. From the documentation:

Force pg_dump to prompt for a password before connecting to a database.

Check here for possible solutions: https://stackoverflow.com/questions/2893954/how-to-pass-in-password-to-pg-dump

I tried giving PGPASSWORD="yourpassword" in one single command but the system caller is failing to execute the command.

I also tried pg_dump "host=localhost port=5432 dbname=mydb user=myuser password=mypass" way. These methods are working in linux terminals but not able to execute the command in the system caller.

Unfortunately you cannot use two separate SystemCallers to set PGPASSWORD and then execute pg_dump: the commands will run in separate instances of the command line interpreter, and environment variables set in one instance aren't automatically transferred to other instances.

I recommend either setting PGPASSWORD in the system environment (which is shared between all instances of the command line interpreter) or to use something like a shell script/batch file that executes both PGPASSWORD and pg_dump in the same interpreter context.

I will try to implement through shell script/batch file

I have tried using the .pgpass file which did not work. I have tried the way which you have suggested through script file which also did not work for me. The command is not getting executed in the system caller and getting failed to get the pg_dump in a file.

Hi ​@sneha ,

I’ve implemented a solution with specifiing in the pg_hba.conf that the ip of the fme flow and the user used in my pg_dump is trust. With this works perfeclty.

Regards

Giosp