Question

Generate Postgres backup files

clang
Rank
Badge +6

Hi Guys,

I have an ETL which writes to a Postgres database, before the writer begins the translation I would like the writer to run a script in Postgres to backup the database schema (including tables and indexes) as either a backup or sql file. I can run a script to backup/dump the entire database however I would just like to backup the schema.

Creating a backup of the schema using the pg dump utility within PgAdmin 4 looks something like this

--file "P:\\\\FINANCEBBK.BAC" --host "myserver" --port "5432" --username "postgres" --no-password --verbose --role "postgres" --format=c --blobs --encoding "UTF8" --schema "finance" "mydatabase"

Any help greatly appreciated,

Chris

10 replies

david_r
Rank
Userlevel 4

Look at the SystemCaller, it will let you call the pg_dump utility with the necessary command line parameters.

clang
Rank
Badge +6

Look at the SystemCaller, it will let you call the pg_dump utility with the necessary command line parameters.

Hi thanks for the response. 

 

 

I have taken this further and generated a bat file on the remote server which I would like the SystemCaller to run. This bat file runs fine and generates a schema backup when I remote onto the server using our service account and run the bat file using windows command. 

 

 

I can also schedule the bat file to run on Windows Task scheduler, again using the same service account.

 

 

However I cannot quite configure the System Caller to run the bat file on the remote server. As I have developed the workbench locally and my AD account does not permit access to the server I published the workbech to FME Server which runs on the same service account used to run the bat file?

 

 

Can you confirm that the following syntax I've used used in the System Caller is correct? It may well be an issue with the service account but i'm unfamiliar with using command lines.

 

 

\\myserver\F$\bat_files\finance_schema_backup.bat

 

As a test I created a simple bat file on my desktop (HELLO.bat) however I can't get the System caller to run this either? syntax used:

 

 

C:\Users\christopher.lang\Desktop\HELLO.bat

 

 

Thanks Chris

 

 

 

 

 

david_r
Rank
Userlevel 4
Hi thanks for the response. 

 

 

I have taken this further and generated a bat file on the remote server which I would like the SystemCaller to run. This bat file runs fine and generates a schema backup when I remote onto the server using our service account and run the bat file using windows command. 

 

 

I can also schedule the bat file to run on Windows Task scheduler, again using the same service account.

 

 

However I cannot quite configure the System Caller to run the bat file on the remote server. As I have developed the workbench locally and my AD account does not permit access to the server I published the workbech to FME Server which runs on the same service account used to run the bat file?

 

 

Can you confirm that the following syntax I've used used in the System Caller is correct? It may well be an issue with the service account but i'm unfamiliar with using command lines.

 

 

\\myserver\F$\bat_files\finance_schema_backup.bat

 

As a test I created a simple bat file on my desktop (HELLO.bat) however I can't get the System caller to run this either? syntax used:

 

 

C:\Users\christopher.lang\Desktop\HELLO.bat

 

 

Thanks Chris

 

 

 

 

 

When the SystemCaller is used in a workspace running on FME Server, it will run with the permissions of the FME Engine service account. Do note, however, that by default the FME Engine service does not have access to network resources, unless reconfigured with a domain user.

 

Using .bat files in the SystemCaller should work just fine, granted that the process owner has sufficient rights etc.

 

If you can't get it to work properly, please post a minimal test workspace here as well as the complete log file with errors etc.
clang
Rank
Badge +6

Look at the SystemCaller, it will let you call the pg_dump utility with the necessary command line parameters.

Hi David,

 

 

Please find attached the workbench, the log file and the bat file the workbench is set to run when saved to my desktop.

 

hello.bat

 

bat-file-test.fmw

 

bat-file-testlog.txt

 

david_r
Rank
Userlevel 4

From the log file you posted:

SystemCaller: Failed to Execute `?C:\Users\christopher.lang\Desktop\HELLO.bat'

(For some reason FME 2017.1 adds a question mark at the start of the path, weird...)

Try something like:

"c:\windows\system32\cmd.exe" "C:\Users\christopher.lang\Desktop\HELLO.bat"

Does that work?

HELLO.bat

clang
Rank
Badge +6

From the log file you posted:

SystemCaller: Failed to Execute `?C:\Users\christopher.lang\Desktop\HELLO.bat'

(For some reason FME 2017.1 adds a question mark at the start of the path, weird...)

Try something like:

"c:\windows\system32\cmd.exe" "C:\Users\christopher.lang\Desktop\HELLO.bat"

Does that work?

HELLO.bat

No that didn't work either? I've downloaded FME 2018.1 and this still fails to run (log file attached)?bat-file-testlog.txt

 

 

Following from this in 2018.1 I tried to just run the "C:\Users\christopher.lang\Desktop\HELLO.bat" command and this worked! However reverting back to my original question of trying to run a bat file on the remote server via FME server I now get the following failure message on server:

 

 

fme maximum_count clause must specify an integer. value 'none' is invalid 

 

 

according to a knowledge base article this relates to a compatability issue between desktop 2018 workbenches running on FME Server 2017?

 

david_r
Rank
Userlevel 4

No that didn't work either? I've downloaded FME 2018.1 and this still fails to run (log file attached)?bat-file-testlog.txt

 

 

Following from this in 2018.1 I tried to just run the "C:\\Users\\christopher.lang\\Desktop\\HELLO.bat" command and this worked! However reverting back to my original question of trying to run a bat file on the remote server via FME server I now get the following failure message on server:

 

 

fme maximum_count clause must specify an integer. value 'none' is invalid

 

 

according to a knowledge base article this relates to a compatability issue between desktop 2018 workbenches running on FME Server 2017?

 

Sounds like you might want to contact Safe support about that one.
virtualcitymatt
Rank
Userlevel 4
Badge +26

No that didn't work either? I've downloaded FME 2018.1 and this still fails to run (log file attached)?bat-file-testlog.txt

 

 

Following from this in 2018.1 I tried to just run the "C:\\Users\\christopher.lang\\Desktop\\HELLO.bat" command and this worked! However reverting back to my original question of trying to run a bat file on the remote server via FME server I now get the following failure message on server:

 

 

fme maximum_count clause must specify an integer. value 'none' is invalid

 

 

according to a knowledge base article this relates to a compatability issue between desktop 2018 workbenches running on FME Server 2017?

 

What you want to is to use the same version of FME Desktop to build the workspace as what you're running on FME Server. I've had no issues with the SystemCaller in FME 2017.1. For me the important thing was to make sure all file paths were in quotes and that all the slashes were pointing in the correct direction.

 

As David has mentioned permission to the remote server will be important. As a test you can try using a PATH reader followed by a logger pointing to the same directory at the batch file on the remote server. This is a good test to see if the paths is working correcly.

 

 

clang
Rank
Badge +6

From the log file you posted:

SystemCaller: Failed to Execute `?C:\Users\christopher.lang\Desktop\HELLO.bat'

(For some reason FME 2017.1 adds a question mark at the start of the path, weird...)

Try something like:

"c:\windows\system32\cmd.exe" "C:\Users\christopher.lang\Desktop\HELLO.bat"

Does that work?

HELLO.bat

Thank you both for your assistance.

 

 

As suggested by Matt I pointed the file path reader  same folder as the batch file and this has correctly picked up all the files in this directory on the server.So this rules out an issue with a syntax error.

 

 

I placed the bat file on a different server and FME Server successfully ran this bat file. Therefore I think it;s a permissions issue? I'll speak with our IT and return with the anwser,

 

 

 

virtualcitymatt
Rank
Userlevel 4
Badge +26
Thank you both for your assistance.

 

 

As suggested by Matt I pointed the file path reader same folder as the batch file and this has correctly picked up all the files in this directory on the server.So this rules out an issue with a syntax error.

 

 

I placed the bat file on a different server and FME Server successfully ran this bat file. Therefore I think it;s a permissions issue? I'll speak with our IT and return with the anwser,

 

 

 

Good Luck!

 

 

Reply


Community Stats

28,687
Posts
109,424
Replies
36,543
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.