1

When I do dump/restore everything works fine. But when I add option --no-owner and dump/restore database the queries slows down.
For example one complex query run about 3 minues instead of 1sec before dump/restore

The only thing that is changed is --no-owner for pg_dump utility

I suppose when dump with --no-owner option and restore such database then user, which restore database, has no access to index info. But this is just assumption

DETAILS
make docker-dbdump && make docker-dbrestore slows down query execution:

.ONESHELL:
docker-dbdump:
    file=${APP_ROOT}/db/${DB_NAME}-$$(date "+%Y-%m-%d_%H-%M-%S").sql.gz
    docker exec ${DOCKER_CONTAINER} pg_dump --no-owner -U postgres ${DB_NAME} \
      | gzip -f > $${file}
    cp $${file} ${APP_ROOT}/db/${DB_NAME}.sql.gz

docker-dbrestore: dbclear
    zcat ${APP_ROOT}/db/${DB_NAME}.sql.gz | \
        docker exec -i ${DOCKER_CONTAINER} psql -U ${DB_USER} -d ${DB_NAME}

When I remove --no-owner options then everything is fine.

UPD

ERROR:  permission denied to create extension "btree_gist"
HINT:  Must be superuser to create this extension.
ERROR:  extension "btree_gist" does not exist
...
ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I want to compare how database is dumped with and w/o --no-owner option and show later today

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
  • That seems unlikely. Check for error messages during restore. Add `EXPLAIN (ANALYZE, BUFFERS)` for a fast and a slow execution of the same query to the question. – Laurenz Albe Sep 09 '20 at 06:27
  • 1
    If you cannot install `btree_gist`, a later index creation fails. That is probably the problem. – Laurenz Albe Sep 09 '20 at 07:21
  • @LaurenzAlbe: you are right. `btree_gist` could not be created database owner. Only superuser. I also have couple: `ERROR: must be member of role "postgres"`. How to find out which functions was not created? – Eugen Konkov Sep 09 '20 at 14:19
  • Read all error messages. – Laurenz Albe Sep 09 '20 at 14:22
  • @LaurenzAlbe: There are about hundred `ALTER FUNCTION CREATE FUNCTION` messages. I do not know to which one belongs `ERROR` message: http://paste.scsys.co.uk/592585. Currently I just count all of them. Then count messages in dumped `sql.gz` file – Eugen Konkov Sep 09 '20 at 14:37

1 Answers1

0

Slows down because of error:

HINT:  Must be superuser to create this extension.
ERROR:  extension "btree_gist" does not exist

Extension is not created, so related indexes also are not created.

Without indexes database slows-down =(

When I fixed error with rights all also starts to work super fast!

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158