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
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
If you're stuck with php then use either back tics
or the exec() command. For the change you can use sed the same way.
Here are 6 more chars
You could use
Then drop all schemas you don't need:
The only drawback is all connections to old_db must be determinated before you can create the copy (so the process that runs the
If that is not an option, pg_dump/pg_restore is the only way to do it.
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.)
1.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).
2.Give backup file a name also choose a format I usually use Tar.
4.Right click the schema you backed up from and click properties and rename it to something else temporarily.
5.Click the Schemas root and right click it in the object browser and click create new schema. This will be the schema you are coping into.
5.Right click the new schema from step 5 and click restore. Restore from backup in step 3.
6.Rename new schema to new name.
7.Rename schema name change from step 4 back to original name.