How I can copy my
public schema into the same database with full table structure, data, functions, fk, pk and etc.
My version of Postgres is 8.4
P.S. I need to copy schema NOT database
How I can copy my
migrated from stackoverflow.com Jan 10 '12 at 21:32
This question came from our site for professional and enthusiast programmers.
There's no simple way to do this in pg_dump/pg_restore itself. You could try the following if you are able to remove the database temporarily.
pg_dump -n schema_name > dump.sql vi dump.sql # edit the schema name psql: psql -f dump.sql
If you're stuck with php then use either back tics
`/usr/bin/pg_dump-n myschema mydb -U username > /tmp/dump.sql`
or the exec() command. For the change you can use sed the same way.
Here are 6 more chars
Using pgAdmin you can do the following. It's pretty manual, but might be all you need. A script based approach would be much more desirable. Not sure how well this will work if you don't have admin access and if your database is large, but should work just fine on a development database that you just have on your local computer.
Right-click schema name you want to copy and click Backup. (You can go deeper than this and choose to just backup the structure instead of both).
Give the backup file a name and also choose a format. (I usually use Tar.)
Right-click the schema you backed up from and click properties and rename it to something else temporarily. (e.g. temprename)
Click the schemas root and right-click it in the object browser and click create new schema and give the schema the name public. This will be the schema you are copying into from your backup.
Right-click the new schema public from step 5. and click restore. Restore from the backup file in step 3.
Rename new schema public to a different name (e.g. newschema).
Rename schema temprename change from step 4 back to the original name.
You could use
CREATE DATABASE new_db TEMPLATE = old_db;
Then drop all schemas you don't need:
DROP SCHEMA public CASCADE; DROP SCHEMA other CASCADE;
The only drawback is all connections to old_db must be determinated before you can create the copy (so the process that runs the
CREATE DATABASE statement must connect e.g. to template1)
If that is not an option, pg_dump/pg_restore is the only way to do it.
expanding on user1113185 answer, here's a full workflow using psql/pg_dump.
The following exports all objects of
old_schema and imports them into new
new_schema schema, as
psql -U user -d dbname -c 'ALTER SCHEMA old_schema RENAME TO new_schema' pg_dump -U user -n new_schema -f new_schema.sql dbname psql -U user -d dbname -c 'ALTER SCHEMA new_schema RENAME TO old_schema' psql -U user -d dbname -c 'CREATE SCHEMA new_schema' psql -U user -q -d dbname -f new_schema.sql rm new_schema.sql