35

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

sigra
  • 453
  • 1
  • 4
  • 5

6 Answers6

28

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.

  1. Take a dump of your public schema using pg_dump
  2. run "ALTER SCHEMA public RENAME TO public_copy"
  3. Restore your dump of your public schema from step 1 using pg_restore
user1113185
  • 404
  • 5
  • 3
  • how I can access to pg_dump and pg_restore via PHP? –  Jan 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 –  Jan 10 '12 at 14:01
  • 1
    +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 Brandstetter Jan 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 Marlowe Jan 11 '12 at 03:01
  • @ScottMarlowe: The solution above is superior in that in only needs to launch a single ALTER SCHEMA command instead of replacing every instance of the schema name in the dump. The result is the same. Hacking the dump is in order if you cannot afford to take the master schema off the grid for a few seconds or if you have hard-coded schema-qualified names in functions. – Erwin Brandstetter Jan 11 '12 at 13:51
  • 2
    Simply renaming the schema will not updated references inside functions: https://gist.github.com/pschultz/5387172. Replacing the name is the dump is much more reliable if you get your search and replace right. – Peter Apr 15 '13 at 10:24
  • Here's a roundabout way that does not touch the original schema: Backup schema, Create another database, restore schema to that database, rename new schema, backup renamed schema, restore renamed schema to original database. Worked for me, hope this helps. – Giles Jul 11 '22 at 09:41
10
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

Community
  • 1
Scott Marlowe
  • 1,869
  • 12
  • 13
  • 2
    it is safer to rename the schema and load back the backed up original schema, especially when schema name might appear as contents (e.g. public). – artm Jun 29 '15 at 14:19
8

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 the backup file a name and 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. (e.g. temprename)

  5. 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.

  6. Right-click the new schema public from step 5. and click restore. Restore from the backup file in step 3.

  7. Rename new schema public to a different name (e.g. newschema).

  8. Rename schema temprename change from step 4 back to the original name.

Note: The new schema created in step 5 must have the same name as the schema you backed up, otherwise pgAdmin won't restore anything

Shivkumar Mallesappa
  • 107
  • 6
Kuberchaun
  • 443
  • 1
  • 4
  • 11
  • 1
    The new schema created in step 5 must have the same name as the schema you backed up, otherwise pgAdmin won't restore anything. – Cao Minh Tu Mar 02 '13 at 01:46
6

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.

  • 1
    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... –  Jan 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 Brandstetter Jan 11 '12 at 01: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 Marlowe Jan 11 '12 at 04:46
  • @ScottMarlowe: depends on which the largest schema is. If the largest is one of the dropped ones, then yes I agree. –  Jan 11 '12 at 08:02
5

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 user, in dbname database:

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
dwelle
  • 277
  • 2
  • 6
1

It's possible to use a function implementation which inspects the source schema you want to copy and generates a target schema with an exact copy.

The postgresql wiki exemplifies this with a partial implementation which clones only tables:

CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS
$BODY$
DECLARE 
  objeto text;
  buffer text;
BEGIN
    EXECUTE 'CREATE SCHEMA ' || dest_schema ;

FOR objeto IN
    SELECT table_name::text FROM information_schema.tables WHERE table_schema = source_schema
LOOP        
    buffer := dest_schema || '.' || objeto;
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || objeto || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
    EXECUTE 'INSERT INTO ' || buffer || '(SELECT * FROM ' || source_schema || '.' || objeto || ')';
END LOOP;




END;
$BODY$
LANGUAGE plpgsql VOLATILE;

From that wiki you can reach a post by Melvin Davidson on postgresql mailing list which provides a complete implementation - sql script - which copies everything, optionally including the data.

https://github.com/denishpatel/pg-clone-schema also contains an implementation of a clone_schema function, but it's only compatible with PostgreSQL versions over v10.x

laconbass
  • 141
  • 4