Migrating Postgres v15 to Google Cloud error pq: cannot drop a template database

[Igoris]

I am trying to deploy Postgres database to CloudSQL. Setup all the prerequisites on the source database. But migration job fails due to an error:

finished setup replication with errors: failed to drop database "template_postgis": generic::unknown: retry budget exhausted (10 attempts): pq: cannot drop a template database

How can I resolve the issue? And why does it try to delete template_postgis?

Thanks!

[ms4446 · Accepted solution]

PostgreSQL utilizes template databases, such as template0 and template1, as foundational blueprints for creating new databases. Custom templates, notably template_postgis, are specifically designed to support extensions like PostGIS for geospatial data handling. Modifying template1 should be approached with caution due to its role in influencing the creation of new databases.

Migration Restrictions: The Cloud SQL migration process is designed to safeguard template databases on your source PostgreSQL instance, prohibiting any modifications or deletions. This measure ensures the preservation of your database setup's integrity.

The Error Explained: Encountering the error "pq: cannot drop a template database" signals that the migration attempt included the template_postgis database, which PostgreSQL protects to prevent accidental deletion.

Resolving the Issue:

1. Exclude the Template Database:

   • When to Use: This approach is recommended for its simplicity and safety, particularly viable if you're prepared to manually install PostGIS on your Cloud SQL instance post-migration.
   • How: Adjust your migration tool or Cloud SQL settings to specifically exclude template_postgis and any other template databases. For detailed instructions, consult your tool's documentation, looking for terms like "database exclusion," "migration settings," or "selective migration."

2. Convert the Template to a Regular Database:

   • When to Use: Suitable if your Cloud SQL database requires PostGIS and you're ready to undertake database-level modifications.
   • How: Apply the following SQL command on your source database:
     UPDATE pg_database SET datistemplate = false WHERE datname = 'template_postgis';
     Caution: Prior to executing this command, assess its potential impact on other databases or processes dependent on template_postgis. Such operations should be performed by individuals with database administration experience or after consulting a database expert.

3. Create a Selective Dump:

   • When to Use: Opt for this method to maintain precise control over the migration content, specifically excluding template_postgis.
   • How: Utilize pg_dump to export individual databases, and pg_dumpall --globals-only for capturing global objects like roles and permissions. Example command:
     pg_dump -h [source_db_host] -U [username] -d mydb1 -Fc > mydb1_dump.sql

Important Considerations:

• Data Consistency: Thoroughly evaluate the impact of changes to template databases on your existing system to ensure data integrity.
• Backups: Always secure a complete backup of your source database before initiating major changes or migration. Utilize pg_dumpall for a comprehensive backup of all databases and global objects, providing a safety net for data recovery.
• Manual Installation of PostGIS: After migration, install PostGIS manually on your Cloud SQL instance by executing CREATE EXTENSION postgis; within the target database. This step is essential for enabling geospatial data handling capabilities in your new environment.
• Post-Migration Verification: Following the migration, conduct a thorough verification to confirm that all data has been accurately transferred and that all required extensions, including PostGIS, are fully operational. This verification ensures the migration's success and the database's readiness for use.

[ms4446 · Accepted solution]

PostgreSQL utilizes template databases, such as template0 and template1, as foundational blueprints for creating new databases. Custom templates, notably template_postgis, are specifically designed to support extensions like PostGIS for geospatial data handling. Modifying template1 should be approached with caution due to its role in influencing the creation of new databases.

Migration Restrictions: The Cloud SQL migration process is designed to safeguard template databases on your source PostgreSQL instance, prohibiting any modifications or deletions. This measure ensures the preservation of your database setup's integrity.

The Error Explained: Encountering the error "pq: cannot drop a template database" signals that the migration attempt included the template_postgis database, which PostgreSQL protects to prevent accidental deletion.

Resolving the Issue:

1. Exclude the Template Database:

   • When to Use: This approach is recommended for its simplicity and safety, particularly viable if you're prepared to manually install PostGIS on your Cloud SQL instance post-migration.
   • How: Adjust your migration tool or Cloud SQL settings to specifically exclude template_postgis and any other template databases. For detailed instructions, consult your tool's documentation, looking for terms like "database exclusion," "migration settings," or "selective migration."

2. Convert the Template to a Regular Database:

   • When to Use: Suitable if your Cloud SQL database requires PostGIS and you're ready to undertake database-level modifications.
   • How: Apply the following SQL command on your source database:
     UPDATE pg_database SET datistemplate = false WHERE datname = 'template_postgis';
     Caution: Prior to executing this command, assess its potential impact on other databases or processes dependent on template_postgis. Such operations should be performed by individuals with database administration experience or after consulting a database expert.

3. Create a Selective Dump:

   • When to Use: Opt for this method to maintain precise control over the migration content, specifically excluding template_postgis.
   • How: Utilize pg_dump to export individual databases, and pg_dumpall --globals-only for capturing global objects like roles and permissions. Example command:
     pg_dump -h [source_db_host] -U [username] -d mydb1 -Fc > mydb1_dump.sql

Important Considerations:

• Data Consistency: Thoroughly evaluate the impact of changes to template databases on your existing system to ensure data integrity.
• Backups: Always secure a complete backup of your source database before initiating major changes or migration. Utilize pg_dumpall for a comprehensive backup of all databases and global objects, providing a safety net for data recovery.
• Manual Installation of PostGIS: After migration, install PostGIS manually on your Cloud SQL instance by executing CREATE EXTENSION postgis; within the target database. This step is essential for enabling geospatial data handling capabilities in your new environment.
• Post-Migration Verification: Following the migration, conduct a thorough verification to confirm that all data has been accurately transferred and that all required extensions, including PostGIS, are fully operational. This verification ensures the migration's success and the database's readiness for use.

[Igoris]

Thank you for the reply.

I have noticed that during first run of the migration job I am getting completely different error related to pglogical extension (I have set all prerequisites on source DB). After the error is raised, blank databases are created on ClousdSQL (destination database). 

So I guess when I try to restart the job, the job tries to cleanup destination database and then get error as template_postgis database cannot be dropped.

Error I get when starting migration job for the first time:
finished setup replication with errors: [postgres]: error restoring predata: failed to restore schema: stderr=pg_restore: error: could not execute query: ERROR: Could not open extension control file: "/.../share/extension/pglogical_origin.control": No such file or directory Command was: CREATE EXTENSION IF NOT EXISTS pglogical_origin WITH SCHEMA pglogical_origin; pg_restore: error: could not execute query: ERROR: extension "pglogical_origin" does not exist Command was: COMMENT ON EXTENSION pglogical_origin IS 'Dummy extension for compatibility when upgrading from Postgres 9.4'; pg_restore: warning: errors ignored on restore: 2 , stdout=; [default]: error restoring predata: failed to restore schema: stderr=pg_restore: error: could not execute query: ERROR: Could not open extension control file: "/.../share/extension/pglogical_origin.control": No such file or directory Command was: CREATE EXTENSION IF NOT EXISTS pglogical_origin WITH SCHEMA pglogical_origin; pg_restore: error: could not execute query: ERROR: extension "pglogical_origin" does not exist Command was: COMMENT ON EXTENSION pglogical_origin IS 'Dummy extension for compatibility when upgrading from Postgres 9.4'; pg_restore: warning: errors ignored on restore: 2 , stdout=; [template_postgis]: error restoring predata: failed to restore schema: stderr=pg_restore: error: could not execute query: ERROR: permission denied for table pg_database Command was: UPDATE pg_catalog.pg_database SET datistemplate = false WHERE datname = 'template_postgis'; pg_restore: error: could not execute query: ERROR: Could not open extension control file: "/.../share/extension/pglogical_origin.control": No such file or directory Command was: CREATE EXTENSION IF NOT EXISTS pglogical_origin WITH SCHEMA pglogical_origin; pg_restore: error: could not execute query: ERROR: extension "pglogical_origin" does not exist Command was: COMMENT ON EXTENSION pglogical_origin IS 'Dummy extension for compatibility when upgrading from Postgres 9.4'; pg_restore: warning: errors ignored on restore: 3 , stdout=

What can I do here? Why there is an issue with the extension? I am using Database Migration Service by Google, have limited possibilities here. As for example, I cannot exclude database from migration why using DMS.

[ms4446 · Accepted solution]

The error messages indicate that the pglogical_origin extension files are missing or inaccessible. This issue could stem from several factors:

• Version Compatibility: There might be a compatibility issue with the PostgreSQL version, affecting the pglogical extension and its components.

• Configuration of File Paths: The specified directory for the extension control file does not exist, suggesting a possible misconfiguration in the PostgreSQL file path settings.

2. Permission Denied for Table pg_database

This error suggests an attempt to modify the pg_database table without adequate permissions. Modifying system catalog tables requires superuser privileges, indicating the migration process is being executed with insufficient permissions.

Recommended Solutions

• Ensure Extension Installation: Prior to migration, verify that all necessary extensions, including pglogical and pglogical_origin, are installed on the destination database. Manual installation may be required if these extensions are missing.

• Verify Superuser Privileges: Ensure the user account executing the migration possesses superuser privileges to allow modifications to system catalog tables.

• Check PostgreSQL Version Compatibility: Confirm that the PostgreSQL versions on both the source and destination are compatible with the pglogical extension. Adjustments or upgrades may be necessary to ensure a smooth migration process.

• File Path Configuration: Review and correct the PostgreSQL configuration on the destination server to ensure the proper location of extension files. The absence of the pglogical_origin.control file indicates a potential misconfiguration.

Additional Recommendations

• Migration Logs: Thoroughly review the detailed logs from the DMS job for further insights into the errors and to identify any additional issues.

• Contact Google Cloud Support: For further assistance and a more personalized troubleshooting experience, consider reaching out to Google Cloud support, especially if the issues involve specific configurations of the Google Cloud DMS or your PostgreSQL setup.