Wednesday, January 22, 2014

Exclude tables during pg_restore

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


70 Responses so far

  1. Unknown 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. Unknown 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. Mounika says:

    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. Mounika says:

    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. Unknown 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. Unknown 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. nilashri 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. Unknown says:

    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. Unknown says:

    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. Mounika says:

    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. Mounika says:

    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. gowsalya says:


    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. gowsalya says:


    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. Unknown 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. jaanu 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

  28. lucy88 says:

    Easily, the article is actually the best topic on this registry related issue. I fit in with your conclusions and will eagerly look forward to your next updates.Data Science Courses

  29. Rathinam says:
    This comment has been removed by the author.
  30. Aruna Ram says:
    This comment has been removed by the author.
  31. Unknown says:

    This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility Python classes in pune to see something like this..

  32. divi says:

    thanks for your information really good and very nice web design company in velachery

  33. seoexpert says:

    Nice Post...I have learn some new information.thanks for sharing.
    Click here for ExcelR Business Analytics Course

  34. Priyanka says:

    Attend The Data Analytics Course in Bangalore with Placement From ExcelR. Practical Data Analytics Course in Bangalore with Placement Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Course in Bangalore with Placement.
    ExcelR Data Analytics Course in Bangalore with Placement

  35. Awesome..I read this post so nice and very imformative information...thanks for sharing
    Click here for data science course

  36. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. IEEE final year projects on machine learning In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

  37. Tuhin says:

    keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore / https://www.excelr.com/digital-marketing-training-in-bangalore

  38. Priyanka says:

    Attend The Data Science Courses Bangalore From ExcelR. Practical Data Science Courses Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses Bangalore.
    Data Science Courses Bangalore
    Data Science Interview Questions

  39. priyash says:

    wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data science Interview Questions

  40. imexpert says:



    Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.

    digital marketing courses

  41. j8899 says:

    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.

    best digital marketing course in mumbai

  42. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

    Digital marketing courses in Bangalore

  43. Interesting and amazing how your post is! It Is Useful and helpful for me That I like it very much, and I am looking forward to Hearing from your next.. 50 real instagram likes

  44. priyash says:

    Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance

  45. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

    Simple Linear Regression

  46. priyash says:

    Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression

  47. Your work is very good and I appreciate you and hopping for some more informative posts. Thank you for sharing great information to us. buy instagram spread likes cheap

  48. varsha says:

    After reading this web site I am very satisfied simply because this site is providing comprehensive knowledge for you to audience.
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

  49. Priyanka says:

    Attend The Data Science Courses Bangalore From ExcelR. Practical Data Science Courses Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses Bangalore.
    Data Science Courses Bangalore

  50. ahmerali says:

    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. Animal Traps

  51. priyash says:

    Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

  52. Great Article
    Cloud Computing Projects


    Networking Projects

    Final Year Projects for CSE


    JavaScript Training in Chennai

    JavaScript Training in Chennai

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

Leave a Reply

Labels