Do indexes get transferred with pg_restore

Question

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.

Answer 1

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).