On 1/28/26 03:02, Wim Rouquart wrote:
> Internal
>
> Bottom line the index exists, it is just not being applied.
>
> -> It seems to exist indeed, but not visible for pg_dump and some other catalog queries...
>
> Questions:
>
> 1) What is the restore command being used?
>
> -> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was
donefrom the production db so I could have a playground for this situation. It's clear the situation is the same on the
originaland the backup copy.
Whoa, pg_basebackup does not involve pg_dump. They are two different
beasts, where pg_basebackup is a file based binary method and
pg_dump/pg_restore is a logical method of issuing commands. So the
restore method is definitely something that needs to be looked at. Even
if in the production scenario pg_basebackup is not being used how the
schema and data are being restored is important as that seems to be the
step where information goes missing.
> -> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem
primarykey index fail to create because it's not created, which makes sense. It's not created because it's not
exported.
If you are using pg_dump on one end of the process and pg_basebackup on
the other end I can see where there are issues, though I would expect
more problems.
In a pg_dump/pg_restore cycle I don't know how a user created index
could be present in the system catalog without also being present in the
pg_dump commands or throwing some sort of error.
>
> 4) What happens if you create a test database and restore bcf_work_type by itself, with and without data?
>
> -> I could test this, how would you suggest to do the backup/restore part, also pg_dump?
For table w/data:
pg_dump -d some_db -U some_user -t name_hidden.bcf_work_type -f
bcf_work_type.sql
with table schema only:
pg_dump -d some_db -U some_user -s -t name_hidden.bcf_work_type -f
bcf_work_type.sql
This will produce a plain text SQL script.
To restore:
psql -d some_other_db -U some_user -f bcf_work_type.sql
--
Adrian Klaver
adrian.klaver@aklaver.com
