After working with PostgreSQL for so many years it’s sometimes surprising to see some of the utilities, to a certain degree, have capabilities that remain relatively unknown to more than a few people. So let me introduce you to manifests.

Both pg_dump and pg_restore have switches enabling us to zone in on specific objects of interest such as functions, tables, data, etc., as we create and restore a logical dump. But there comes a time when expressing this kind of granularity as arguments can become overly complex when we look at dozens, if not hundreds, of objects.

Enter manifests

PostgreSQL dumps store not only the objects and data of a database but their descriptive meta-data too. When generated as a text file, a PostgreSQL manifest details each object on a single line. These lines can then be edited by either removing or reordering them. The edited file can then be used with pg_restore to restore the entities in the prescribed order.

Here’s how it works; the first step is to generate a standard dump using the custom compression argument -Fc

Once the dump has been created the manifest is generated using pg_restore and the -l switch, which goes to standard output:

And like a recipe, the manifest is then used to read and restore the dump following its directions:

Of course, in between generating and reading the manifest, one takes the opportunity of editing it too:

So let’s use a more realistic i.e. pgbench. The first step initializes the benchmarking database, notice the use of foreign keys:

Let’s complicate the landscape by adding a number of views including a materialized view:

And now let’s create the database dump. Remember, manifests can only be generated when the dump has been created using the custom format switch -Fc.

Now, let’s generate the manifest. I’ve opted to use the ini extension to the file name as my code editor recognizes the semicolon as a comment line and colors it accordingly:

As per the normal process of generating and restoring a logical dump, one sees from the output listed below the command creating the database, that’s from the -C switch, followed by the views, table definitions, data population, and the constraints, and finishing with the data population of the materialized view:

Once generated, the manifest can now be tailored to our requirements which in this case is to restore a subset of the database dump as follows:

  1. Create database pgbench_restore
  2. Restore only view v1_pgbench_accounts
  3. Restore and populate table pgbench_accounts
  4. Restore and populate materialized view m_pgbench_accounts

You will note that in order to restore table pgbench_accounts it will be necessary to not add all the constraints, i.e. remove the FK constraints.

After some editing here’s what the resulting manifest looks like:

With a little creativity it’s not that big of a leap to dynamically generate the appropriate manifest:

And now we proceed with the final restoration steps:

Taking a look at the resulting database confirms success:

And that’s all there is to it! 

One final caveat: one edits the manifest by deleting whole lines. It’s not possible to edit the lines themselves, so don’t change anything, nor can new lines be arbitrarily added to the manifest. Remember, manifests are like pointers in that they can only reference what already exists in the database dump.

Notify of

Newest Most Voted
Inline Feedbacks
View all comments
Frank Streitzig

Thank you for the article. I will use always parameter -Fc with pg_dump every time, now.
But i think, i have found a little mistake.
“pg_restore -1 -L pgbench_manifest.ini -f – | less -S”
Missing dump filename.
“pg_restore -1 -L pgbench_manifest.ini -f – pgbench.db | less -S”