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 – dezso Apr 18 '13 at 11:56
  • @dezso I'm using pg_dump, is that actually calling psql? postgresql.org/docs/9.1/static/app-pgdump.html – Nyxynyx Apr 18 '13 at 12:02
  • No, but your command line contains psql -h localhost "test" --table "staging.planet_osm_polygon" – dezso Apr 18 '13 at 12:06
  • @dezso Oh I didn't notice that!! I guess it will always be copied to public schema then – Nyxynyx Apr 18 '13 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 – Hiram Walker Oct 9 '18 at 19:44

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 Apr 18 '13 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 Apr 18 '13 at 12:07
  • 3
    Alternative with sudo: sudo -Hiu postgres – Max Lobur Sep 19 '16 at 20:17

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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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