CODEWITHSUNDEEP
databasepostgresqlbackuppostgresql-9.3
curious_one
curious_one

Reputation: 260

Postgres database backup error - special character in password

I'm trying to take a PostgreSQL backup with pg_dump. But I'm not able to take it due to the following error.

I have successfully taken backups for different IP addresses without the special character @ in it.

command used and working

sudo /usr/bin/pg_dump --file "/home/myusername/my_first_db.backup" \
  --verbose --format=t --blobs -v \
  --dbname postgresql://postgres:[email protected]:5432/my_first_db

command used and not working

sudo /usr/bin/pg_dump --file "/home/myuser/xyz_db/DB_BACKUP/db_file.backup" \
  --verbose --format=t --blobs -v  \
  --dbname postgresql://111.222.333.444:5432/prod_live?password=123th@123th4&user=postgres

sudo /usr/bin/pg_dump --file "/home/myuser/xyz_db/DB_BACKUP/db_file.backup" \
  --verbose --format=t --blobs -v  \
  --dbname postgresql://111.222.333.444:5432/prod_live?password=123th%40123th4&user=postgres

Error I'm getting:

[4] 8555
myuser@myuser:~$ pg_dump: [archiver (db)] connection to database "prod_live" failed: FATAL:  password authentication failed for user "root"
FATAL:  password authentication failed for user "root"

I cannot change the password, because it is production.

Upvotes: 4

Views: 1703

Answers (1)

Abelisto
Abelisto

Reputation: 15624

As I can see...
Unquoted character & in your command line sends the task to background as described, for example, here: Linux: Start Command In Background. So anything after & character ignored (or interpreted as separate command) by *nix shell.
Solution
Just try to quote the whole string like this:

sudo /usr/bin/pg_dump --file "/home/myuser/xyz_db/DB_BACKUP/db_file.backup" \
  --verbose --format=t --blobs -v  \
  --dbname 'postgresql://111.222.333.444:5432/prod_live?password=123th@123th4&user=postgres'

Explanation
In the output provided by you the line [4] 8555 means Background job #4 with process ID 8555 was started
And single quotes around the string allows to interpret it "as-is", without parameters substitution and other special characters interpreting.

PS: Use $'...' syntax to translate special escaped characters like \n \t \uxxxx and others.

There is several examples:

$ echo abc&defgh
[1] 3426
abc
defgh: command not found
[1]+  Done                    echo abc

As you can see the output is like to provided by you in the part [x] xxxx

$ echo 'abc&defgh'
abc&defgh

In this case command echo prints exactly what you want
And last but not least:

$ echo '1: abc&\ndefgh'; echo $'2: abc&\ndefgh'
1: abc&\ndefgh
2: abc&
defgh

Upvotes: 2

Related Questions