I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump.

Problem: However I'm getting the error could not change directory to "/root" and the Password: prompt appeared twice! Is there a way to perform the pg_dump when logged in as root?

root@lalaland:~# su postgres
postgres@lalaland:/root$ pg_dump -h localhost "osm" --table "public.planet_osm_polygon" | 
    psql -h localhost "test" --table "staging.planet_osm_polygon"
could not change directory to "/root"
could not change directory to "/root"
Password: Password:


Problem #2: It appears that the table is copied into the public schema even though I passed the flag --table="staging.planet_osm_polygon". Why isn't it copied to schema staging?

  • There is no --table parameter to psql, that may be the cause of problem #2 Commented Apr 18, 2013 at 11:56
  • @dezso I'm using pg_dump, is that actually calling psql? postgresql.org/docs/9.1/static/app-pgdump.html
    – Nyxynyx
    Commented Apr 18, 2013 at 12:02
  • No, but your command line contains psql -h localhost "test" --table "staging.planet_osm_polygon" Commented Apr 18, 2013 at 12:06
  • @dezso Oh I didn't notice that!! I guess it will always be copied to public schema then
    – Nyxynyx
    Commented Apr 18, 2013 at 12:26
  • This issue is because pgsql/ directory does not exists, you have to create it in /var/lib/pgsql this work for me.. Kind regards Hiram Commented Oct 9, 2018 at 19:44

2 Answers 2


Try this: Re: could not change directory to "/root":

Apparently you did "su postgres" from the root account, so you're still in root's home directory. It'd be better to do "su - postgres" to ensure you've acquired all of the postgres account's environment. Reading "man su" might help you out here.

  • Is it normal that its asking for the password twice? Typing the password a second time does not mask it.
    – Nyxynyx
    Commented Apr 18, 2013 at 11:54
  • 1
    Maybe because you are doing two operations: 1. pg_dump -h localhost "osm" --table "public.planet_osm_polygon" 2. psql -h localhost "test" --table "staging.planet_osm_polygon"
    – Ela
    Commented Apr 18, 2013 at 12:07
  • 6
    Alternative with sudo: sudo -Hiu postgres
    – Max Lobur
    Commented Sep 19, 2016 at 20:17
  • Yeah, I probably should finally read over man su / sudo carefully... Commented Jan 25, 2021 at 16:13

For me this did the trick, pay attention to quotes (')

sudo -Hiu postgres 'pg_dump --column-inserts --data-only --table=someTable entities_db > /var/backups/anywhere/$(date +%Y%m%d_%H%M%S)_someTable.sql'

Note the -Hiufor sudo, or use su - postgres

you can also put that in a cronjob for root with crontab -e


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.