How to move data from one schema to another in Postgres?

I saw a question today :

I have PostpreSQL 9.1. In my database there are 2 schemas: public and test. How can I quickly move all objects and data from public to test?

Opps. I’m stuck with this issue for some time. I was thinking about dump and restore tricks, about direct changes to the pg_catalog schema etc. I even appeared on the IRC channel with this question.

There I had conversation with Jon SuckMojo Erdman, who’s first thought was dump way either. But then we have found a simple and elegant solution independently.

Assuming schema test is empty:





8 thoughts on “How to move data from one schema to another in Postgres?

      1. I am talking about cloning the schema so in the end there are two copies. One with data and other other without. Like a template. I am looking into this for a mufti-tenant database. When a new client signs up create a new schema for them.

        1. I do that with databases instead of schemas. After I’ve loaded the schema, it’s simply a matter of running:

          CREATE DATABASE newtemplate TEMPLATE mydb;
          UPDATE pg_database SET datistemplate=true WHERE datname=’newtemplate’;

          Then when I need a new db, I run:

          CREATE DATABASE newdb TEMPLATE newtemplate;

  1. This is Case when we are performing everything on same cluster. If need to clone on new cluster then what will be scenarios along with Dump/ Restore ..

  2. Another option would have been to generate a script that does an ALTER TABLE foobar SET schema test; for all tables in the public schema

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s