current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
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 0 down vote favorite

I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema.

I tried:

pg_dump -U my_username -h localhost --schema=public -d my_db -Fc -f db/my_dump.backup

pg_restore -U my_username -h localhost --schema=my_schema -d my_db -a db/my_dump.backup

It does not copy the data from the public schema. Any thoughts?

share|improve this question
    
If both schemas are in the same database, you don't need pg_dump/pg_restore. Using insert into my_schema.some_table select * from public.some_table will work just as well. Or even create table my_schema.some_table as select * from public.some_table. You can automate this using a stored function, or a script that generates the necessary SQL statements –  a_horse_with_no_name Nov 18 '14 at 7:08
    
If you do pg_restore -l -f db/my_dump.backup, you'll see that the objects are dumped, meaning that the restore step does not do what you expect from it. If you are doing this regularly, the best way (in my view) is to store the database object definitions under version control, then roll them out on demand to a new schema with a few lines of shell script. –  dezso Nov 18 '14 at 9:28

1 Answer 1

up vote 0 down vote accepted

You're setting custom format.

Remove the -Fc option from the pg_dump command so the dump will be generated with COPY commands.

Add set schema 'myschema'; at the beggining of the file.

then

psql -U my_username -h localhost -d my_db -f db/my_dump.backup -v ON_ERROR_STOP=1

share|improve this answer
    
If I won't use custom format (-Fc), the dump will be an sql file. If it's an sql dump, I will be forced to use psql instead of pg_restore command. psql does not allow me to specify the schema option. –  tungsten_carbide Nov 18 '14 at 4:56
    
Edit the dump and add the set schema option at the beginning of it –  Fabrizio Mazzoni Nov 18 '14 at 4:57

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.