Wednesday, January 22, 2014

Exclude tables during pg_restore

13 comments
Let we have a database like:

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \d
        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+----------
 public | t    | table | postgres
 public | t1   | table | postgres
 public | t2   | table | postgres
(3 rows)


testdb=# insert into t1 select c, c::text||'abc' as some_text from generate_series(1,10) as c;
INSERT 0 10
testdb=# insert into t2 select c, c::text||'abc' as some_text from generate_series(1,10) as c;
INSERT 0 10
testdb=# insert into t select c, c::text||'abc' as some_text from generate_series(1,10) as c;
testdb=# insert into t select c, (c::text||'abc')::bytea as some_text from generate_series(1,10) as c;
INSERT 0 10


Take a dump of the database:

pg_dump testdb -Fc -v > testdb.sqlc

Now our task is to restore the database excluding table "t1":

Create a new database:

create database testdb_new;

Restore schema only:

pg_restore -d testdb_new -s -v testdb.sqlc

pg_restore: connecting to database for restore
Password:
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating TABLE t
pg_restore: creating TABLE t1
pg_restore: creating TABLE t2
pg_restore: creating CONSTRAINT t1_pkey
pg_restore: creating CONSTRAINT t_pkey
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for EXTENSION plpgsql
pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql
pg_restore: setting owner and privileges for TABLE t
pg_restore: setting owner and privileges for TABLE t1
pg_restore: setting owner and privileges for TABLE t2
pg_restore: setting owner and privileges for CONSTRAINT t1_pkey
pg_restore: setting owner and privileges for CONSTRAINT t_pkey

Drop table "t2":

postgres=# \c testdb_new
You are now connected to database "testdb_new" as user "postgres".
testdb_new=# drop table  t1;
DROP TABLE

Restore data:

pg_restore -d testdb_new -a -v testdb.sqlc

pg_restore: connecting to database for restore
Password:
pg_restore: restoring data for table "t"
pg_restore: restoring data for table "t1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2728; 0 35908 TABLE DATA t1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "t1" does not exist
    Command was: COPY t1 (i, some_text) FROM stdin;

pg_restore: restoring data for table "t2"
pg_restore: setting owner and privileges for TABLE DATA t
pg_restore: setting owner and privileges for TABLE DATA t1
pg_restore: setting owner and privileges for TABLE DATA t2
WARNING: errors ignored on restore: 1

Verify:

testdb_new=# \d
        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+----------
 public | t    | table | postgres
 public | t2   | table | postgres
(2 rows)
testdb_new=# select * from t;
 i  |  nam 
----+-------
  1 | 1abc
  2 | 2abc
  3 | 3abc
  4 | 4abc
  5 | 5abc
  6 | 6abc
  7 | 7abc
  8 | 8abc
  9 | 9abc
 10 | 10abc
(10 rows)


13 Responses so far

  1. ram says:

    Worth reading! Our experts also have given detailed inputs about these trainings & courses! Presenting here for your reference. Do checkout
    oracle sql training in chennai & enjoy learning more about it.

  2. Devi says:

    Sharing the same interest, Infycle feels so happy to share our detailed information about all these courses with you all! Python Training In Chennai & get to know everything you want to about software trainings.

  3. saketh says:


    Just the way I have expected. Your website really is interesting. ExcelR Data Analytics Course

  4. thagukavi says:




    This post is so interactive and informative.keep update more information...
    Tally Course in Chennai
    Tally Classes in Chennai
    Online Tally Course

  5. Being one of the most sought-after adult Bengali Call Girls in Manali, we offer a highly satisfactory relationship.Busty Call Girls in Manali The cheerful moments that guys get from the open-minded girls would be great.Nepali Escorts in Agra The idolization of beautiful women can give you some good opportunities to enjoy time with them.Low budget Call Girls in Dehradun You can also make it possible easily by hiring hot girls. Low budget Call Girls in Dehradun are ready to do everything that can make you feel better than ever. Date the right girl at the right time to feel excited.Hi Profile Escorts in Faridabad The incredible adult experiences that you can get from the gorgeous females would be extraordinary.

  6. AppNexus Technology Pvt Ltd is a Software Development Company. We are a leading IT Company, Dealing With IT Services Such as Web Development, Software Development, SEO, Web Designing &Logo Designing, Book Cover Design, And Mobile Application, SMO, PPC, BlogCreation, ContentMarketing, e-commerce website,
    digital marketing, CMS, e-commerce website.
    Visit Here:
    https://matmo.in/
    https://www.appnexustech.com/

  7. Are you having problem in learning new skills or confused to choose between right Passion . So don't worry we are here because our institution is offering CS executive classes and a free of cost CSEET classes. So what are you waiting for contact us or visit our website at https://uniqueacademyforcommerce.com/

  8. Devi says:

    Chennai's best software training institute, Infycle Technologies, offers the No.1 Oracle DBA training in Chennai for tech professionals & students along with other courses such as Python, Big Data, Selenium, Java, Hadoop, iOS, and Android development with 100% hands-on training. Once the completion of training, the students will be sent for placement interviews in the core MNC's. Call 7502633633 to get more info and a free demo.Top Oracle DBA Training in Chennai | Infycle Technologies

  9. Really Nice Information It's Very Helpful All courses Checkout Here.
    data scientist courses aurangabad

  10. Unknown says:

    Infycle Technologies, the top software training institute and placement center in Chennai offers the Best Digital Marketing course in Chennai for freshers, students, and tech professionals at the best offers. In addition to Digital Marketing, other in-demand courses such as DevOps, Data Science, Python, Selenium, Big Data, Java, Power BI, Oracle will also be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.

Leave a Reply

Labels