Wednesday, January 22, 2014

Exclude tables during pg_restore

35 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)


35 Responses so far

  1. gowsalya says:

    It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted.
    Digital Marketing Training in Chennai

  2. gowsalya says:

    Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
    Digital Marketing Training in Bangalore

  3. Awesome! Education is the extreme motivation that open the new doors of data and material. So we always need to study around the things and the new part of educations with that we are not mindful.
    AWS Training in chennai

    AWS Training in bangalore

  4. Nice tips. Very innovative... Your post shows all your effort and great experience towards your work Your Information is Great if mastered very well.
    Click here:
    python training in Bangalore
    Click here:
    python training in Bangalore

  5. ummi ari says:

    It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command

    Blue Prism Training in Pune

    Blueprism training in tambaram

    Blueprism training in annanagar

  6. ummi ari says:

    It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command

    Blue Prism Training in Pune

    Blueprism training in tambaram

    Blueprism training in annanagar

  7. Nila shri says:

    Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    Data Science Training in Chennai
    Data science training in bangalore
    Data science online training
    Data science training in pune
    Data Science training in kalyan nagar
    Data Science training in OMR
    selenium training in chennai

  8. simbu says:

    It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    java training in annanagar | java training in chennai


    java training in marathahalli | java training in btm layout

  9. Nice tips. Very innovative... Your post shows all your effort and great experience towards your work Your Information is Great if mastered very well.

    angularjs Training in bangalore

    angularjs Training in btm

    angularjs Training in electronic-city

    angularjs online Training

    angularjs Training in marathahalli

  10. Really great post, I simply unearthed your site and needed to say that I have truly appreciated perusing your blog entries.
    angularjs-Training in pune

    angularjs-Training in chennai

    angularjs Training in chennai

    angularjs-Training in tambaram

    angularjs-Training in sholinganallur

  11. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    python training in pune | python training institute in chennai | python training in Bangalore

  12. Nice tutorial. Thanks for sharing the valuable information. it’s really helpful. Who want to learn this blog most helpful. Keep sharing on updated tutorials…
    python training in pune | python training institute in chennai | python training in Bangalore


  13. Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.

    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    AWS Training in Chennai | AWS Training Institute in Chennai Velachery, Tambaram, OMR


    AWS Training in Chennai |Best Amazon Web Services Training in Chennai


    AWS Training in Velachery | Best AWS Course in Velachery,Chennai


  14. Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.

    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    AWS Training in Chennai | AWS Training Institute in Chennai Velachery, Tambaram, OMR


    AWS Training in Chennai |Best Amazon Web Services Training in Chennai


    AWS Training in Velachery | Best AWS Course in Velachery,Chennai

  15. WUGI says:

    Make money on kasino online and do not regret anything gamble casino with us Make money on kaino online and do not regret anything.

  16. priya says:

    I appreciate your efforts because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also elaborately described. I hope to read more and more interesting articles from your blog. All the best.

    Microsoft Azure online training
    Selenium online training
    Java online training
    Java Script online training
    Share Point online training

  17. shethal v says:

    This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 
    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

  18. user123 says:

    It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted.
    Data Science Training in Chennai | Best Data Science Training in Chennai
    Python Course in Chennai | Best Python Training Course Institutes in Chennai
    RPA Training in Chennai | Best RPA Course Training in Chennai
    Digital Marketing Course in Chennai | Best Digital Marketing Course Training in Chennai

  19. Rathinam says:

    Well done! Your explanation way is very attractive for me and I like more tips from your great post. Surely I will share your great post for my friends and this is very useful for freshers. Keeping the great work!
    Oracle Training in Chennai
    Oracle Training institute in chennai
    Oracle DBA Training in Chennai
    Excel Training in Chennai
    Corporate Training in Chennai
    Tableau Training in Chennai
    Social Media Marketing Courses in Chennai
    Oracle Training in Chennai
    Oracle Training institute in chennai

  20. janitha says:

    Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!data science course malaysia
    big data course malaysia

  21. Aaditya says:

    Nice and good post. This is a wonderful article, Given so much info in it, keep sharing.


    ExcelR Data Science Course Bangalore

  22. Really appreciate this wonderful post that you have provided for us.Great site and a great topic as well i really get amazed to read this. Its really good.
    date analytics certification training courses
    data science courses training
    data analytics certification courses in Bangalore
    ExcelR Data science courses in Bangalore

  23. Amazing knowledge and I like to share this kind of information with my friends and hope they like it they why I do
    Data Science Course in Pune

  24. Priyanka says:

    Attend The Python Training in Bangalore From ExcelR. Practical Python Training in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python Training in Bangalore.
    Python Training in Bangalore

  25. Unknown says:

    It should be noted that whilst ordering papers for sale at paper writing service, you can get unkind attitude. In case you feel that the bureau is trying to cheat you, don't buy term paper from it.
    rpa training in malaysia

  26. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    www.technewworld.in
    How to Start A blog 2019

  27. Aditi Ray says:

    Home Mart is a site about Home Improvement, Furniture, Home Appliances and many more.
    Check out the best
    home office desks nz
    coffee table nz
    bookshelves

Leave a Reply

Labels