{"id":16771,"date":"2021-11-08T12:59:15","date_gmt":"2021-11-08T11:59:15","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/"},"modified":"2021-11-08T12:59:15","modified_gmt":"2021-11-08T11:59:15","slug":"postgresql-15-read-some-options-for-pg_dump-from-a-file","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/","title":{"rendered":"PostgreSQL 15: read some options for pg_dump from a file"},"content":{"rendered":"<p>PostgreSQL 15 will probably come with a new feature for <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-pgdump.html\" target=\"_blank\" rel=\"noopener\">pg_dump<\/a>: Instead of passing the arguments\/options directly to pg_dump when it is executed, you may also use a file to describe what you want to export. In the Oracle world it was (maybe still is, but I don&#8217;t know) very popular to do that with traditional exp\/imp and the expdp\/impdp utilities. pg_dump did not know that feature but this will probably change for PostgreSQL 15. Currently the <a href=\"https:\/\/commitfest.postgresql.org\/35\/2573\/\" target=\"_blank\" rel=\"noopener\">patch is &#8220;ready for committer&#8221;<\/a> so we don&#8217;t really know if it finally will be committed, but I believe chances are not too bad. Let&#8217;s have a look.<\/p>\n<p><!--more--><\/p>\n<p>For being able to dump, we need some data:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create schema s1;\nCREATE SCHEMA\npostgres=# create schema s2;\nCREATE SCHEMA\npostgres=# create table s1.t1 as select i as a, i::text as b, now() as c from generate_series(1,100) i;\nSELECT 100\npostgres=# create table s2.t2 as select i as a, i::text as b, now() as c from generate_series(1,100) i;\nSELECT 100\npostgres=# create view s1.v1 as select a from s1.t1;\nCREATE VIEW\npostgres=# create view s2.v2 as select b from s2.t2;\nCREATE VIEW\n<\/pre>\n<p>The new option for pg_dump is called:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n  --filter=FILENAME            dump objects and data based on the filter expressions\n                               in specified file\n\n<\/pre>\n<p>Currently the following types of objects are supported: <\/p>\n<ul>\n<li>tables<\/li>\n<li>schemas<\/li>\n<li>foreign_data<\/li>\n<li>data<\/li>\n<\/ul>\n<p>The first two can be either &#8220;included&#8221; or &#8220;excluded&#8221;, foreign_data can only be &#8220;included&#8221; and data can only be excluded. If, for example, we want to dump the &#8220;s1&#8221; schema but we do not want to have the &#8220;s2&#8221; schema included we can do it like this:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] echo \"include schema s1\" &gt; filter.txt\npostgres@debian11pg:\/home\/postgres\/ [pgdev] echo \"exclude schema s2\" &gt;&gt; filter.txt\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_dump --filter=filter.txt &gt; dmp.sql\npostgres@debian11pg:\/home\/postgres\/ [pgdev] grep s2 dmp.sql \n<\/pre>\n<p>Actually, for this requirement this would already be enough and the result is the same:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] echo \"include schema s1\" &gt; filter.txt\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_dump --filter=filter.txt &gt; dmp.sql\npostgres@debian11pg:\/home\/postgres\/ [pgdev] grep s2 dmp.sql \n<\/pre>\n<p>For tables it works exactly the same:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] echo \"include table s1.t1\" &gt; filter.txt\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_dump --filter=filter.txt &gt; dmp.sql\n<\/pre>\n<p>Getting only the meta data is possible by excluding the data from the dump:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] cat filter.txt \ninclude schema s1\nexclude data s1.t1\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_dump --filter=filter.txt &gt; dmp.sql\n<\/pre>\n<p>As I do not have a foreign data wrapper configured right now, I&#8217;ll skip this one, should be clear what it does. This for sure is not a killer feature but personally I always liked to work with options coming from files, and this is a good starting point.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL 15 will probably come with a new feature for pg_dump: Instead of passing the arguments\/options directly to pg_dump when it is executed, you may also use a file to describe what you want to export. In the Oracle world it was (maybe still is, but I don&#8217;t know) very popular to do that with [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-16771","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 15: read some options for pg_dump from a file - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 15: read some options for pg_dump from a file\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL 15 will probably come with a new feature for pg_dump: Instead of passing the arguments\/options directly to pg_dump when it is executed, you may also use a file to describe what you want to export. In the Oracle world it was (maybe still is, but I don&#8217;t know) very popular to do that with [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-11-08T11:59:15+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 15: read some options for pg_dump from a file\",\"datePublished\":\"2021-11-08T11:59:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/\"},\"wordCount\":286,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/\",\"name\":\"PostgreSQL 15: read some options for pg_dump from a file - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2021-11-08T11:59:15+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 15: read some options for pg_dump from a file\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 15: read some options for pg_dump from a file - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 15: read some options for pg_dump from a file","og_description":"PostgreSQL 15 will probably come with a new feature for pg_dump: Instead of passing the arguments\/options directly to pg_dump when it is executed, you may also use a file to describe what you want to export. In the Oracle world it was (maybe still is, but I don&#8217;t know) very popular to do that with [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/","og_site_name":"dbi Blog","article_published_time":"2021-11-08T11:59:15+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 15: read some options for pg_dump from a file","datePublished":"2021-11-08T11:59:15+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/"},"wordCount":286,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/","name":"PostgreSQL 15: read some options for pg_dump from a file - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-11-08T11:59:15+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-read-some-options-for-pg_dump-from-a-file\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 15: read some options for pg_dump from a file"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16771","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16771"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16771\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16771"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16771"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16771"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16771"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}