0

I have a database with several tables, let's say table_a and table_b.

I want to backup my base using pg_dump, but (for various reasons) I want each table to have its own dumping file. I could do something like:

$ pg_dump -t table_a -f export_a.sql
$ pg_dump -t table_b -f export_b.sql

but consistency wouldn't be assured: modifications could happens between the two dumps, so my two dumps would represent differents states of the database, which is not consistent.

My question is: Is there any way to ensure these two dumps to be consistent (like, to be in the same transaction or something), or to tell pg_dump to output each table of a single dump in its own file?

I have tried to use pg_dump -Fd -Z0, but outputted .dat don't seem readable enough to separate information of each table.

Motiss
  • 72
  • 1
  • 13
  • What do you mean "don't seem readable enough to separate information of each table?". The help for `-Fd` says "This will create a directory with one file for each table and blob being dumped,". Isn't that what you asked for? – Cargo23 Jun 08 '21 at 15:45
  • You will need to do `pg_restore -l -f ` to get a plain text version of the Table of Contents(TOC) from `toc.dat`. – Adrian Klaver Jun 08 '21 at 16:05
  • Thanks for your comments. I actually didn't understand how to use `toc.dat`. This https://stackoverflow.com/a/49454121/7031981 post's answer have been pretty useful. – Motiss Jun 09 '21 at 10:04

2 Answers2

4

There is no direct way to do that. I have two ideas:

  • Use a single directory format pg_dump (-F d) of all the tables. True, there will be a single toc.dat with all the table metadata, but each table will be dumped to its own file.

  • Use a single pg_dump in directory or custom format to get a consistent dump for all tables, and then create individual files from it with

    pg_restore -t table1 -f table1.sql all_tables.dmp

Further reading:

StackzOfZtuff
  • 2,534
  • 1
  • 28
  • 25
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Yeah. I think I'll go for the 2nd solution, as files seem easier to manipulate this way. Thanks for your answer. – Motiss Jun 09 '21 at 10:06
0

Add the --verbose option to pg_dump, and redirect both stdout and stderr to a file.

As tables finish dumping, you'll see something like this in the output:

pg_dump: finished item 7463 TABLE DATA mc_request

A bit of bash and awk lets you rename the 9999.dat files to "table-named" files. One problem, though, is when different schemas have same table name.

RonJohn
  • 349
  • 8
  • 20