7

I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore). However, it looks like it did in my recent dump/restore and I am not sure if it really did transfer over or not.

I migrated my database over from PostgreSQL 9.3 to 9.4.5 and used dump/restore in order to do so.

Below are the commands used:

sudo -u postgres pg_dump -h localhost -p 5432 -d nominatim -F d -f dump/postgres/backup -j 20
sudo -u postgres pg_restore --create --dbname=nominatim --exit-on-error -h localhost -p 5432 -F d -j 7 dump/postgres/backup

The dump and restore was successful (no errors).

I set autovacuum to off while doing the restore and so I ran analyze (no parameters) from within psql and while connected to the database that was restored.

The first thing I noticed is that under 9.3, the data directory took up roughly 860GB, whereas under 9.4, it's taking up about 680GB. Is 9.4 THAT much more efficient?

Second thing I noticed is that I see the indices in the database:

nominatim=# \d country_name
                   Table "public.country_name"
            Column             |         Type         | Modifiers 
-------------------------------+---------------------------------    
country_code                  | character varying(2) |   
name                          | hstore               |   
country_default_language_code | character varying(2) |   
partition                     | integer              |  

Indexes:
        "idx_country_name_country_code" btree (country_code)


nominatim=# \d idx_country_name_country_code
    Index "public.idx_country_name_country_code"
    Column    |         Type         |  Definition  
--------------+----------------------+--------------
 country_code | character varying(2) | country_code

btree, for table "public.country_name"


nominatim=# select * from pg_indexes where tablename = 'country_name';
 schemaname |  tablename   |           indexname           | tablespace |                                       indexdef                                        
------------+--------------+-------------------------------+------------+---------------------------------------------------------------------------------------
 public     | country_name | idx_country_name_country_code |            | CREATE INDEX idx_country_name_country_code ON country_name USING btree (country_code)
(1 row)

Do I still need to reindex or did my indexes somehow or other make it over or is what I am seeing simply the index definition? Any further tips on better managing the dump/restore, checking my indexes, etc. would definitely be appreciated.

13

Do indexes get transferred with pg_restore?
I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore)

That seems to be a misunderstanding. The index itself (containing all the data) is not in the dump. Just the commands to recreate it. So, indexes get "transferred", but really, they are recreated in pristine condition, without any bloat or dead tuples

Do I still need to reindex?

No. After the restore you have all indexes in perfect condition. No REINDEX needed. ANALYZE would make sense, though, as advised in the manual in the chapter "Restoring the Dump":

After restoring a backup, it is wise to run ANALYZE on each database so the query optimizer has useful statistics;

And finally:

Is 9.4 THAT much more efficient?

No, not generally. Well, the size of GIN indexes has been reduced substantially. But what you see is most probably the effect of removing bloat from all tables and indexes (including system tables).

|improve this answer|||||
  • Because of the other answers, I decided to start the reindex anyway, figuring it might be done by the time I woke up. 5+ hours later, it's still running. I don't suppose I can kill the reindex safely...? Additionally, the database only gets added to, to my knowledge, nothing is ever deleted from it. Under those circumstances, would there ever be bloat or dead tuples? Thanks for the help! – Brooks Dec 16 '15 at 9:21
  • Some system tables will still have dead tuples, but that's insubstantial for a big database and no problem at all. And indexes may need some internal optimization after much growth. For a DB with 680 GB there will be quite a few big indexes so REINDEX is am expensive operation - especially if it does not have enough maintenance_work_mem. You should set that rather high (temporarily for the session running `) - except, of course, that we don't need to run it at all in your case ... – Erwin Brandstetter Dec 16 '15 at 10:18
  • @Brooks And yes, you can stop reindex, gut that will also be expensive that far into the operation. Compare: dba.stackexchange.com/questions/34717/… – Erwin Brandstetter Dec 16 '15 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.