Question 113704 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres CloudSQL Migration: ERROR: permission denied to set parameter "log_min_duration_statement"

I am trying to use the Database Migration Service to migrate an existing database into CloudSQL.

When I start the migration, I receive the following error:

finished setup replication with errors: [api_production]: error importing schema: failed to restore schema: stderr=pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 3997; 0 0 DATABASE PROPERTIES api_production postgres pg_restore: error: could not execute query: ERROR: permission denied to set parameter "log_min_duration_statement" Command was: ALTER DATABASE api_production SET log_min_duration_statement TO '500ms'; pg_restore: warning: errors ignored on restore: 1 , stdout=

How can I continue the migration, ignoring the SET PARAMETER statement?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have finally been able to start the migration by resetting the parameters on the source database (setting them to match was insufficient).

    This can be done from a postgres console on the source database:

    reset log_min_duration_statement;
ALTER DATABASE <database_name> RESET log_min_duration_statement;
ALTER DATABASE postgres RESET log_min_duration_statement;

  2. 0

    The Database Migration Service is a managed Google Cloud service, it has restricted access to certain system procedures and tables that require advanced privileges.The ‘postgres’ user is the most privileged user available in Cloud SQL, but it is not a Postgres superadmin. See public docs for more information about PostgreSQL Users.
    There are some other parameters that you could run the "ALTER Database" command to change; however, "log_statement" and "log_min_duration_statement" are unfortunately not examples of these parameters.
    The PostgreSQL documentation also documents this in particular "Certain variables cannot be set this way, or can only be set by a superuser."
    However, you can change the particular setting in Console via the Flags on the database edit screen and remove these statements from the Migration job,to avoid these failure errors.
    Please refer to the documentation to know more about configuring database flags.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search