Dump postgres data with indexes

postgresql indexing vacuum
AndreDurao picture AndreDurao · Sep 30, 2011 · Viewed 14.4k times · Source

I've got a Postgres 9.0 database which frequently I took data dumps of it.

This database has a lot of indexes and everytime I restore a dump postgres starts background task vacuum cleaner (is that right?). That task consumes much processing time and memory to recreate indexes of the restored dump.

My question is:

  1. Is there a way to dump the database data and the indexes of that database?
  2. If there is a way, will worth the effort (I meant dumping the data with the indexes will perform better than vacuum cleaner)?
  3. Oracle has some the "data pump" command a faster way to imp and exp. Does postgres have something similar?

Thanks in advance, Andre

Answer

wildplasser picture wildplasser · Sep 30, 2011

If you use pg_dump twice, once with --schema-only, and once with --data-only, you can cut the schema-only output in two parts: the first with the bare table definitions and the final part with the constraints and indexes. Something similar can probably be done with pg_restore.

Related questions

List columns with indexes in PostgreSQL

I would like to get the columns that an index is on in PostgreSQL. In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column. mysql> show indexes from foos; +-------+------------+---------------------+--------------+…

Read More
Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created?

I have the following table: tickername | tickerbbname | tickertype ------------+---------------+------------ USDZAR | USDZAR Curncy | C EURCZK | EURCZK Curncy | C EURPLN | EURPLN Curncy | C USDBRL | USDBRL Curncy | C USDTRY | USDTRY Curncy | C EURHUF | EURHUF Curncy | C USDRUB | USDRUB Curncy | C I …

Read More
How do I force Postgres to use a particular index?

How do I force Postgres to use an index when it would otherwise insist on doing a sequential scan?

Read More