23

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?

Improve this question
5
  • 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 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 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

Reset to default
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.

Improve this answer
4
  • Is it normal that its asking for the password twice? Typing the password a second time does not mask it. 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" Commented Apr 18, 2013 at 12:07
  • 6
    Alternative with sudo: sudo -Hiu postgres 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
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

Improve this answer
2
  • great! I guess -hu would suffice, explanation here: explainshell.com/explain?cmd=sudo+-Hiu Commented Apr 9, 2020 at 18:28
  • No, -H sets HOME env var and -i changes to that directory, so -iu works for me. Commented Feb 27, 2024 at 14:38

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.