1
pg_dump -C -h localhost -U user_name database_name | psql -h 10.590.8.46 -U user_name database_name

I am trying to dump a database to a remote server on linux, and this is the error I keep getting:

pg_dump: [archiver (db)] connection to database "database_name" failed:   
FATAL:  invalid command-line argument for server process: -I
HINT:  Try "postgres --help" for more information.
codeyard
  • 327
  • 1
  • 2
  • 10

2 Answers2

0

The only way I can reproduce this behavior is by setting the PGOPTIONS environment variable:

export PGOPTIONS=-I

Check if that variable is set, and if yes, unset it with

unset PGOPTIONS
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • This worked, and now with a different error. pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation portal_audit_log_tb pg_dump: [archiver (db)] query was: LOCK TABLE public.portal_audit_log_tb IN ACCESS SHARE MODE – codeyard Jan 22 '18 at 11:38
  • The database user used for `pg_dump` must at least have `SELECT` privileges for all dumped objects. – Laurenz Albe Jan 22 '18 at 12:06
  • permission denied for relation portal_blacklist_det_tb is what I get for the pg_dump user. NB: This was the user that created the database! – codeyard Jan 22 '18 at 18:59
  • It might have been the user that created the database, but it still has no `SELECT` privileges on the table. Try to `SELECT` from the table as that database user! – Laurenz Albe Jan 22 '18 at 19:56
  • Thank you, works when I run it as the postgres user. – codeyard Jan 23 '18 at 10:10
-1

You can check this post pg_dump postgres database from remote server

the command to dump the database from remote is

ssh user@remote_machine "pg_dump -U dbuser -h localhost database_name " \
 >> backup_file_on_your_local_machine.sql
Juliana Davila
  • 90
  • 3