Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
There is no --table parameter to psql, that may be the cause of problem #2 – dezso Apr 18 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 at 12:02
No, but your command line contains psql -h localhost "test" --table "staging.planet_osm_polygon" – dezso Apr 18 at 12:06
@dezso Oh I didn't notice that!! I guess it will always be copied to public schema then – Nyxynyx Apr 18 at 12:26
add comment (requires an account with 50 reputation)

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
Is it normal that its asking for the password twice? Typing the password a second time does not mask it. – Nyxynyx Apr 18 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 at 12:07
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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