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?

There is no --table parameter to psql, that may be the cause of problem #2 — András Váczi, Apr 18 '13 at 11:56
@dezso I'm using pg_dump, is that actually calling psql? http://www.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" — András Váczi, 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 09 '18 at 19:44

score 34 · Accepted Answer · edited Apr 18 '13 at 11:37

34

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.

edited Apr 18 '13 at 11:37

Jack Douglas

39,869
15
101
176

answered Apr 18 '13 at 11:23

Ela

500
4
9

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
6

Alternative with sudo: sudo -Hiu postgres – Max Lobur Sep 19 '16 at 20:17
Yeah, I probably should finally read over man su / sudo carefully... – Brian Peterson Jan 25 '21 at 16:13

score 5 · Answer 2 · answered Jan 11 '19 at 10:29

5

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

answered Jan 11 '19 at 10:29

Philippe Gachoud

177
1
7

great! I guess -hu would suffice, explanation here: https://explainshell.com/explain?cmd=sudo+-Hiu – rubo77 Apr 09 '20 at 18:28
No, -H sets HOME env var and -i changes to that directory, so -iu works for me. – Ev Dolzhenko Feb 27 '24 at 14:38

PostgreSQL: Cannot change directory to /root

UPDATE

2 Answers2

Linked