Index names not consistent
Running postgres 9.0.1 on Solaris 10 (Sparc)
I am testing a development database before putting it in production. The application install created the database and the indexes. When I run psql and list the indexes \di I can count the number of indexes and see the names of the indexes. If I run
pg_dump -s databasename | grep INDEX
I can also count the number of indexes and see the names of the indexes. Both the number of indexes and the names of the indexes differ when using these two methods to display index information. Is this normal? I had planned on truncating the database tables, dropping the indexes, loading production data and rebuilding the indexes. I was going to use the output from the pg_dump command to get a list of indexes to drop so I could automate the process. Then rebuild the indexes using another script with data from pg_dump. That's when I noticed the index names don't match.
I'm new to postgres so I'm not familiar with all the details of the product. However, I thought when dumping the schema, it would keep the index names the same as the existing index names. Any insight on this one? The server I am working on has one postgres install and one application database so there is no chance I'm getting two databases confused.
runner <runner@winning.com> writes:
Running postgres 9.0.1 on Solaris 10 (Sparc)
I am testing a development database before putting it in production. The application install created the database and the indexes. When I run psql and list the indexes \di I can count the number of indexes and see the names of the indexes. If I run
pg_dump -s databasename | grep INDEX
I can also count the number of indexes and see the names of the indexes. Both the number of indexes and the names of the indexes differ when using these two methods to display index information. Is this normal?
It's really difficult to say with so little detail, but there's
certainly ways that that could happen. pg_dump might create some
indexes with ALTER TABLE ADD CONSTRAINT commands, for example,
rather than syntax using the word INDEX.
regards, tom lane
Running postgres 9.0.1 on Solaris 10 (Sparc)
I am testing a development database before putting it in production. The
application install created the database and the indexes. When I run psql and
list the indexes \di I can count the number of indexes and see the names of the
indexes. If I run
pg_dump -s databasename | grep INDEX
I can also count the number of indexes and see the names of the indexes. Both
the number of indexes and the names of the indexes differ when using these two
methods to display index information. Is this normal?
It's really difficult to say with so little detail, but there's
certainly ways that that could happen. pg_dump might create some
indexes with ALTER TABLE ADD CONSTRAINT commands, for example,
rather than syntax using the word INDEX.
regards, tom lane
SOLVED: That was it Tom, thanks for the tip!