Flush postgres data with indexes

I have a Postgres 9.0 database in which I often accepted data dumps.

There are many indexes in this database, and every time I restore post-packages, you run the focus task of the vacuum cleaner (right?). This task requires a lot of processing time and memory to recreate the indexes of the restored dump.

My question is:

  • Is there any way to dump database data and indexes of this database?
  • If there is a way, it’s worth the effort (I meant that dumping data with indexes would work better than a vacuum cleaner)?
  • Oracle has a "data pump" command - a faster way to imp and exp. Does postgres have something similar?

Thanks in advance Andre

+6
source share
2 answers

If you use pg_dump twice, once with --schema-only, and once with -data-only, you can cut only the output of the schema in two parts: the first with the definitions of the bare tables and the final part with constraints and indexes. Perhaps something like this can be done with pg_restore.

+4
source

Best practice probably for

  • restore schema without indexes
  • and possibly without limitation,
  • upload data
  • then create restrictions
  • and create indexes.

If an index exists, then bulk loading will force PostgreSQL to write to the database and index. And bulk loading will make your table statistics useless. But if you first load the data and then create the index, the statistics are automatically updated.

We store scripts that create indexes and scripts that create tables in different files under version control. That's why.

In your case, changing the auto-vacuum settings may help you. You may also consider disabling autovacuum for some tables or for all tables, but this can be a bit extreme.

+2
source

Source: https://habr.com/ru/post/898377/


All Articles