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.

how I can access to pg_dump and pg_restore via PHP?
–
sigraJan 10 '12 at 13:24

That depends on where you're running the database. You can access pg_dump and pg_restore from the shell on your server. If you don't have shell access then you could try using PHPs shell_exec, otherwise you'll need to look into an alternative backup method, perhaps using a PostgreSQL GUI Tool
–
user1113185Jan 10 '12 at 14:01

+1 That is the smartest solution so far. Shell command would look something like this (more in the the manual): pg_dump -n my_schema -f '/path/to/file.pgsql' my_db. Easiest as superuser (postgres) with pw-less peer authorization in pg_haba.conf. Restore after you have renamed the original schema: psql my_db -f '/path/to/file.pgsql'. If you have a plain SQL dump, you do not need pg_restore.
–
Erwin BrandstetterJan 10 '12 at 22:29

There is an easy way, see my answer. pg_dump supports the -n switch for choosing a schema. Then just edit the schema name in the dump and reload.
–
Scott MarloweJan 11 '12 at 3:01

2

Simply renaming the schema will not updated references inside functions: gist.github.com/pschultz/5387172. Replacing the name is the dump is much more reliable if you get your search and replace right.
–
PeterApr 15 '13 at 10:24

Before I asked this question, I used a similar method of cloning a database. But it spends a lot of time and I think that cloning of only schema is much faster...
–
sigraJan 10 '12 at 17:07

@sigra: a_horse's method of cloning is the fastest available for databases, because actual files can be just copied, which saves a lot of overhead. I doubt a dump and reload of the schema will be faster unless the schema is only a small part of the whole db. So, +1 for this answer even if it does not answer the actual question asked.
–
Erwin BrandstetterJan 11 '12 at 1:34

That's a lot of work to clone a single schema. dumping the schema, renaming it in the dump and reloading is much faster.
–
Scott MarloweJan 11 '12 at 4:46

@ScottMarlowe: depends on which the largest schema is. If the largest is one of the dropped ones, then yes I agree.
–
a_horse_with_no_nameJan 11 '12 at 8:02

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.

3.Click Backup.

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.