Search code examples
postgresqlindexingpg-dump

Dump Postgres 9.3 data with indexes

When using

pg_dump --section post-data

I get a dump that contains the definitions of the indexes but not the index data. As the database I'm working on is really big and complex, recreating the indexes takes a lot of time.

Is there a way get the index data into my dump, so that I can restore an actually working database?

Solution

  • There is no way to include index data in a logical dump (pg_dump).

    There's no way to extract index data from the SQL level, where pg_dump operates, nor any way to write it back. Indexes refer to the physical structure of the table (tuple IDs by page and offset) in a way that isn't preserved across a dump and reload anyway.

    You can use a low-level disk copy using pg_basebackup if you want to copy the whole DB, indexes and all. Unlike a pg_dump you can't restore this to a different PostgreSQL version, you can't dump just one database, etc; it's all or nothing.