POSTGRESQL 9.51 (using Password) Backup Job stalls


#1

Scheduled or run now job sits at “The maximum file size” (log file below)
Noticed the following behaviour:
command line output sent to pg_dump --no-password (but the database does have password??)
In task manager pg_dump only one write to disk??
After stopping SQLBackupAndFTP service, pg_dump starts backing up the database to the temp folder

I have tried creating pgpass file but same issue.
Any help would be appreciated.

15/03/2019 13:07:35|Info|Starting job “DB backup” - “Full”. Computer “WWDB”. App v."12.2.1"
15/03/2019 13:07:37|Info|Connecting to PostgreSQL Server: PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit. WWDB
15/03/2019 13:07:37|Info|The backup folder “D:\Temp\Pranas.NET\SQLBackupAndFTP\backup” has “83.789GB” free space. The temporary folder “D:\Temp\Pranas.NET\SQLBackupAndFTP\backup” has “83.789GB” free space
15/03/2019 13:07:37|Info|Backing up “WWDB” databases with pg_dump.exe: pg_dump (PostgreSQL) 9.5.1
15/03/2019 13:07:38|Debug|Getting maximum file size…
15/03/2019 13:07:38|Debug|The maximum file size: ""
15/03/2019 18:25:06|Debug|Cancelling job "DB backup "

Thanks

Nivag


#2

Hello Gavin_Ashton,

Thank you for the details, please give us some time to check this issue.

Sorry for the inconvenience.


#3

Hello Gavin_Ashton,

We have added some changes to SQLBackupAndFTP Alpha version https://sqlbackupandftp.com/download/alpha

If you choose the option to pass the password through a file (In Password File), then SQLBakcupAndFTP will transfer the password through the pgpass.conf file. The software will add this file itself.

But if you want to specify the password in the file manually, then please mark the file as ReadOnly so that SQLBackupAndFTP does not overwrite the data. Note that by default, a backup job runs on behalf of the local system user. The pgpass.conf file for that user is located at: \Windows\system32\config\systemprofile\AppData\Roaming\postgresql\


#4

Hello Alexander!

i have similar problem with postgresql 10.5.
Try your alpha version by link below, but the progress still freeze on “Maximum file size…”

Advanced log -
Link

Thanks!


#5

Hi,

Thank you for the log, please give us some time to check it.

Sorry for the inconvenience.


#6

Hi,

We have checked this log and were not able to find the process that was freeze. Could you please send us a log of the backup job where this issue happened. Just open your backup job log from the “History & restore” column and click “Export”.

Sorry for the inconvenience.


#7

11.07.2019 7:54 Starting job "Backup Job - 1 " - “Full”. Computer “WINDOWSSERVER-F”. Account “СИСТЕМА@MARIS”. App v.“12.2.9”.
11.07.2019 7:54 Connecting to PostgreSQL Server: PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit. localhost
11.07.2019 7:54 The backup folder “C:\Windows\TEMP\СИСТЕМА\Pranas.NET\SQLBackupAndFTP\backup” has “13,089GB” free space. The temporary folder “C:\Windows\TEMP\СИСТЕМА\Pranas.NET\SQLBackupAndFTP\backup” has “13,089GB” free space
11.07.2019 7:54 Backing up “localhost” databases with pg_dump.exe: pg_dump (PostgreSQL) 11.1
11.07.2019 7:54 Getting maximum file size…
11.07.2019 7:54 The maximum file size: “”
11.07.2019 7:56 Failed to backup “01_docflow” database with “Full” backup type: C:\Program Files (x86)\SQLBackupAndFTP\DBMS\PostgreSql\pg_dump.exe process has finished with “1” code. The error message: "pg_dump: [sorter] WARNING: could not resolve dependency loop among these items:
pg_dump: [sorter] DUMMY TYPE _reference83_byfieldfld4663 (ID 3203 OID 110324)
pg_dump: [sorter] INDEX _reference83_byfieldfld4663 (ID 30975 OID 110322)
pg_dump: [sorter] POST-DATA BOUNDARY (ID 37022)
pg_dump: [sorter] TABLE DATA config (ID 34579 OID 16857)
pg_dump: [sorter] PRE-DATA BOUNDARY (ID 37021)
pg_dump: [sorter] WARNING: could not resolve dependency loop among these items:
pg_dump: [sorter] DUMMY TYPE _reference83_byparentfieldfld4664 (ID 3288 OID 110327)
pg_dump: [sorter] INDEX _reference83_byparentfieldfld4664 (ID 30982 OID 110325)
pg_dump: [sorter] POST-DATA BOUNDARY (ID 37022)
pg_dump: [sorter] TABLE DATA config (ID 34579 OID 16857)
pg_dump: [sorter] PRE-DATA BOUNDARY (ID 37021)
pg_dump: [sorter] WARNING: could not resolve dependency loop among these ite…
11.07.2019 7:56 Cancelling job "Backup Job - 1 "
11.07.2019 7:56 Job cancelled. Job cancelled by user
11.07.2019 7:56 Job "Backup Job - 1 " finished with “2” errors
11.07.2019 7:56 Sending report to ${https://sqlbackupandftp.com/weblog?jobid=5ec4ce4c-2e65-4ecf-8b7d-a75145cf8a27|Web Log}


#8

Hello
And i click “Send log to developers”
application id from about form - 2223c655-ecfb-4b17-9412-43474384ec07
thanks


#9

Hello,

Thank you for the log. Please give us some time to check them.

Sorry for the inconvenience.


#10

Hello,

Thank you for your log we have checked it. It seems the issue is on pg_dump level. SQLBackupAndFTP creates backups of PostgreSQL Server databases via pg_dump tool.

Could you please try to perform a backup manually via pg_dump tool?

Sorry for the inconvenience.


#11

Thats the thing - while your soft is not working properly, i have to backing up my database throught my script with pg_dump. And it’s work!

@echo Backup database %PG_PATH%%PG_FILENAME%
@echo off
SET 7_BIN=“C:\Program files\7-Zip\7z.exe”
SET PG_BIN=“C:\Program Files\PostgreSQL\10.5-24.1C\bin\pg_dump.exe”
SET PG_HOST=localhost
SET PG_PORT=5432
SET PG_DATABASE1=01_docflow
SET PG_DATABASE2=01_wh
SET PG_USER=postgres
SET PGPASSWORD=*
SET PG_PATH=C:\Program Files\PostgreSQL\10.5-24.1C\bin
SET FECHAYHORA=%date:/=%-%time:-0,8%
SET FECHAYHORA=%FECHAYHORA::=-%
SET FECHAYHORA=%FECHAYHORA: =0%
Set Tdate=%date:~6,4%%date:~3,2%%date:~0,2%
SET PG_FILENAME1=%PG_DATABASE1%%Tdate%.sql
SET PG_FILENAME2=%PG_DATABASE2%
%Tdate%.sql
SET PG_FILENAME1z=%PG_DATABASE1%%Tdate%
SET PG_FILENAME2z=%PG_DATABASE2%
%Tdate%

%PG_BIN% -h %PG_HOST% -p %PG_PORT% -U %PG_USER% %PG_DATABASE1% > C:_Backup%PG_FILENAME1%
%PG_BIN% -h %PG_HOST% -p %PG_PORT% -U %PG_USER% %PG_DATABASE2% > C:_Backup%PG_FILENAME2%

“C:\Program files\7-Zip\7z.exe” a -tzip -mx8 -r0 C:_Backup%PG_FILENAME1z% C:_Backup%PG_FILENAME1%
“C:\Program files\7-Zip\7z.exe” a -tzip -mx8 -r0 C:_Backup%PG_FILENAME2z% C:_Backup%PG_FILENAME2%
erase /Q C:_Backup%PG_FILENAME1%
erase /Q C:_Backup%PG_FILENAME2%

@echo Backup Taken Complete %PG_PATH%%PG_FILENAME%


#12

Hello,

Thank you for the details. To investigate this case we need more information. Could you please enable your advanced log with the “Trace” level, reproduce the issue, then open the advanced log

and find the following substring:

SqlBak.Job.PostgreSql.Helper.PgDumpHelper|Dump. Command:

There will be arguments that we pass to pg_dump. Could you please use them and manually try to run those arguments via pg_dump to reproduce the issue.

Sorry for the inconvenience.