PostgreSQL: Cannot change directory to /root

Question

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:

---

UPDATE

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?

• András Váczi: There is no --table parameter to psql, that may be the cause of problem #2
• Nyxynyx: @dezso I'm using pg_dump, is that actually calling psql? postgresql.org/docs/9.1/static/app-pgdump.html
• András Váczi: No, but your command line contains psql -h localhost "test" --table "staging.planet_osm_polygon"
• Nyxynyx: @dezso Oh I didn't notice that!! I guess it will always be copied to public schema then
• Hiram Walker: 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

Answer 1

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.

• Nyxynyx: Is it normal that its asking for the password twice? Typing the password a second time does not mask it.
• Ela: 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"
• Max Lobur: Alternative with sudo: sudo -Hiu postgres
• Brian Peterson: Yeah, I probably should finally read over man su / sudo carefully...

Answer 2

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

• rubo77: great! I guess -hu would suffice, explanation here: explainshell.com/explain?cmd=sudo+-Hiu
• Ev Dolzhenko: No, -H sets HOME env var and -i changes to that directory, so -iu works for me.