Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mtdew12oz
Contributor II
Contributor II

Automating backup with pg_dump.exe not working.


Using the Backing up a Qlik Sense Site document as a guide, http://help.qlik.com/sense/en-US/online/Subsystems/Server_deployment_and_configuration/Content/Qlik%..., I attempting to create a batch file to automate the site backup so I can schedule it.

I found that it expects a password at the command prompt so my script just hangs.  Tried passing a password using the -W switch as described in the pg_dump.exe --help, and I'm getting error message "'too many command-line arguments"

Command I'm using:

pg_dump.exe -h localhost -p 4432 -f E:\backup\dump -U USERNAME -W PASSWORD QSR

Using the prescribed command-line from Qlik, pg_dump.exe -h localhost -U USERNAME -W PASSWORD QSR > e:\backup, also produces the same error message.

Tested with first command line based on what pg_dump.exe --help provided.

pg_dump.exe --help gives me:

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=SCHEMA          dump the named schema(s) only
  -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)
  -o, --oids                   include OIDs in dump
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=TABLE            dump the named table(s) only
  -T, --exclude-table=TABLE    do NOT dump the named table(s)
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)
  --inserts                    dump data as INSERT commands, rather than COPY
  --no-security-labels         do not dump security label assignments
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments
  --no-unlogged-table-data     do not dump unlogged table data
  --quote-all-identifiers      quote all identifiers, even if not key words
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@postgresql.org>.

1 Solution

Accepted Solutions
Masaki_Hamano
Employee
Employee

Hi Sam,

You can use "SET PGPASSWORD=<PassWord>" , so that you are not asked to enter your password manually in your batch file. Please refer to the following link in detail:

Automated Backup on Windows - PostgreSQL wiki

Masaki

View solution in original post

4 Replies
Masaki_Hamano
Employee
Employee

Hi Sam,

You can use "SET PGPASSWORD=<PassWord>" , so that you are not asked to enter your password manually in your batch file. Please refer to the following link in detail:

Automated Backup on Windows - PostgreSQL wiki

Masaki

mtdew12oz
Contributor II
Contributor II
Author

Using the WikiI was able to design a script that worked.  Thanks for your response!

Anonymous
Not applicable

Hello Sam,

Could you provide your details of your script?  I'm unable to get past the prompt following the the wiki site.  pgpass.conf file follows the syntax:

hostname:port:database:username:password

SET PGPASSFILE=D:\scripts\pgpass.conf

"C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.3\bin\pg_dumpall.exe" -U username -p port > D:\backups\pgdump.bak

raheelrobson
Contributor
Contributor

Could you please share the script with me.