Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    7

    Unanswered: copy tables from one schema to another in the same db

    Hi all,

    first post, so please bear with me.

    I have a postgresql database with a postgis spatial extension to it. In that db I have one schema with about 120 tables. I now want to copy / move some of the tables to new schemas based on who have the rights to update the tables. How do I copy the relevant tables to the new schema? I´ve tried with the backup function in pgadmin (I guess its a GUI to pg_dump) from the source schema (only the data) and then using the restore to the destination schema without any luck. Can you guys point me in the right direction? It seems that the data is backed up (atleast it returns 0) but when I try to restore the data in the new schema the tables are not showing.

    Sincerly,

    Christian

  2. #2
    Join Date
    Jun 2010
    Posts
    17
    Could you please try to use below workaround to resolve your issue.

    Step 1: Dump structure of all tables from test database.

    ./pg_dump -t jobs -t test -t orders -s -O test >/tmp/structure.sql

    Step 2: modify the schema name in structure.sql file

    SET search_path = public, pg_catalog;

    replace to

    SET search_path = tt, pg_catalog;


    Step 3: create a new schema

    create schema tt;

    Step 4: Restore structure of all tables.

    ./psql -d test

    set search_path to tt;

    \i /tmp/test.sql

    Step 5: Dump only data using below command

    ./pg_dump -t jobs -t test -t orders -a test >/tmp/data.sql

    Step 6: modify the schema name in data.sql file

    SET search_path = public, pg_catalog;

    replace to

    SET search_path = tt, pg_catalog;

    Step 7: Please increase below parameter in Postgresql.conf file and then restart the server

    shared_buffers=1GB

    work_mem= 512 MB

    Maintenace_work_mem=1GB

    Step 8: Restore data only dump file

    ./psql -d test

    \i /tmp/data.sql

    Could you please provide feedback for the same.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Did you try:
    Code:
    CREATE TABLE newschema.my_table
    LIKE oldschema.my_table

  4. #4
    Join Date
    Jun 2010
    Posts
    17
    I am agree your point. We can create duplicate table with LIKE operator.

    postgres=# set search_path to public;
    SET
    postgres=# \dt
    List of relations
    Schema | Name | Type | Owner
    --------+-------+-------+----------
    public | test | table | postgres
    public | test3 | table | postgres
    public | test4 | table | postgres
    (3 rows)


    postgres=# create table test.test3 (like test3 including constraints);

    CREATE TABLE

    postgres=# select * from test.test3;
    bangalore
    -----------
    (0 rows)

    --Raghu

  5. #5
    Join Date
    Feb 2011
    Posts
    7
    Thanks guys,

    I´ve tried with

    CREATE TABLE new_schema.table1 (LIKE old_schema.table1 INCLUDING CONSTRAINTS INCLUDING INDEXES);

    with that its possible to create a new table under the new schema with the structure and constraints, primary key etc. as the original one. The problem is to migrate the data from the orginal table to the new one (remember that the structure is the same so it shouldn´t be that difficult). I´ve tried with SELECT * INTO but that seems to try to create a new table which is already there. If I try to use the SELECT * INTO without having created a table I will only get the data and not the primary key etc.

    I also have som sequences (data applied to the primary key). How do I copy that from the original schema to the new one?

    Sorry if this is basic knowledge, but have looked in the documentation and on the web without any luck.

    Christian

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    INSERT INTO new_schema.table1 (col1, col2, col3)
    SELECT col1, col2, col3
    FROM old_schema.table1;
    COMMIT;

  7. #7
    Join Date
    Nov 2006
    Posts
    82
    If you want to move table to another schema you can simply do that
    Code:
    alter table <table_name> set schema <new_schema_name>;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •