1

I am using 4D for front-end and postgresql for back-end. So i have the requirement to take database backups from front-end.

Here what i have done so far for taking backups in 4D.

C_LONGINT(i_pg_connection)
i_pg_connection:=PgSQL Connect ("localhost";"admin";"admin";"test_db")
LAUNCH EXTERNAL PROCESS("C:\\Program Files\\PostgreSQL\\9.5\\bin\\pg_dump.exe -h localhost -p 5432 -U admin -F c -b -v -f C:\\Users\\Admin_user\\Desktop\\backup_test\\db_backup.backup test_db")
PgSQL Close (i_pg_connection)

But the it's not taking the backup. The backup command is ok because it works perfectly while firing on command prompt.

What's wrong in my code? Thanks in advance.

Suniel
  • 1,449
  • 8
  • 27
  • 39

1 Answers1

1

Unneeded commands in your code

If you are using LAUNCH EXTERNAL PROCESS to do the backup then you do not need the PgSQL CONNECT and PgSQL CLOSE.

These plug-in commands do not execute in the same context as LAUNCH EXTERNAL PROCESS so they are unneeded in this situation.

Make sure you have write access

If the 4D Database is running as a Service, or more specifically as a user that does not have write access to C:\Users\Admin_user\..., then it could be failing due to a permissions issue.

Make sure that you are writing to a location that you have write access to, and also be sure to check the $out and $err parameters to see what the Standard Output and Error Streams are.

You need to specify a password for pg_dump

Another problem is that you are not specifying the password.

You could either use the PGPASSWORD environment variable or use a pgpass.conf file in the user's profile directory.

Regarding the PGPASSWORD environment variable; the documentation has the following warning:

Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using the ~/.pgpass file

Example using pgpass.conf

The following example assumes you have a pgpass.conf file in place:

C_TEXT($c;$in;$out;$err)
$c:="C:\\Program Files\\PostgreSQL\\9.5\\bin\\pg_dump.exe -h localhost -p 5432 -U admin -F"
$c:=$c+" c -b -v -f C:\\Users\\Admin_user\\Desktop\\backup_test\\db_backup.backup test_db"
LAUNCH EXTERNAL PROCESS($c;$in;$out;$err)
TRACE

Example using PGPASSWORD environment variable

The following example sets the PGPASSWORD environment variable before the call to pg_dump and then clears the variable after the call:

C_TEXT($c;$in;$out;$err)
SET ENVIRONMENT VARIABLE ( "PGPASSWORD" ; "your postgreSQL password" )  
$c:="C:\\Program Files\\PostgreSQL\\9.5\\bin\\pg_dump.exe -h localhost -p 5432 -U admin -F"
$c:=$c+" c -b -v -f C:\\Users\\Admin_user\\Desktop\\backup_test\\db_backup.backup test_db"
LAUNCH EXTERNAL PROCESS($c;$in;$out;$err)
SET ENVIRONMENT VARIABLE ( "PGPASSWORD" ; "" )  // clear password for security
TRACE

Debugging

Make sure to use the debugger to check the $out and $err to see what the underlying issue is.

Tim Penner
  • 3,551
  • 21
  • 36
  • Thank you. I tried your code and debug it. It fires the command and displays the password prompt. After i enter the password it stucks there. it does process further. The only way to get out is to quit the command prompt. Plus it creates the backup file but empty. And it happens before the value assigned to $err, so i also could not get the error value as well. – Suniel Jun 28 '16 at 05:55
  • $err is only filled if the command writes to the error stream. Regarding the password you will need to embed it as a parameter. – Tim Penner Jun 28 '16 at 06:01
  • In reference to password as a parameter, do you mean to append '-W password' into the above command right after username? its not working. – Suniel Jun 28 '16 at 10:07
  • First make sure you have a working command from the command prompt, without the password prompt, this is important because you won't be able to send additional data to the stream afterwards, such as if the command prompts for a password. Once you have the command working from the command prompt then feed the same command and parameters into 4D's `LAUNCH EXTERNAL PROCESS`. It looks like you may need to use a [pgpass file](https://www.postgresql.org/docs/9.1/static/libpq-pgpass.html) at `%APPDATA%\postgresql\pgpass.conf` – Tim Penner Jun 28 '16 at 17:34
  • an alternative to the pgpass.conf file is to use the [`PGPASSWORD` environment variable](https://www.postgresql.org/docs/9.1/static/libpq-envars.html) – Tim Penner Jun 28 '16 at 17:37
  • 1
    Thank you so much tim. Now the backup and restore of postgresql database is working without supplying password manually following your way. Thanks again. – Suniel Jun 29 '16 at 04:07