Re: Backup/Restore of single table in multi TB database

"John Smith" <sodgodofall@gmail.com> writes:
> After reading the documentation, it seems like the following might
> work.  Suppose the database has two tables foo and bar, and we're only
> interested in backing up table foo:

> 1. Call pg_start_backup

> 2. Use the pg_class table in the catalog to get the data file names
> for tables foo and bar.

> 3. Copy the system files and the data file for foo.  Skip the data file for bar.

> 4. Call pg_stop_backup()

> 5. Copy WAL files generated between 1. and 4. to another location.

> Later, if we want to restore the database somewhere with just table
> foo, we just use postgres's normal recovery mechanism and point it at
> the files we backed up in 2. and the WAL files from 5.

> Does anyone see a problem with this approach

Yes: it will not work, not even a little bit, because the WAL files will
contain updates for all the tables.  You can't just not have the tables
there during restore.

Why are you not using pg_dump?

            regards, tom lane