Search code examples
postgresqlpostgresql-10pg-dump

Why postgres database server slows down after dump/restore with --no-owner option?

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

Solution

  • 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!