copy data from public schema to another schema in postgresql

Question

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?

• user1822: 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
• András Váczi: 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.

Answer 1

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

• tungsten_carbide: 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.
• Fabrizio Mazzoni: Edit the dump and add the set schema option at the beginning of it