Migrating Drupal from MySQL to PostgreSQL

Migrating Drupal from MySQL to PostgreSQL is reasonably well documented in a variety of places (https://www.drupal.org/docs/develop/local-server-setup/linux-development-environments/set-up-a-local-development-drupal-1, https://www.digitalocean.com/community/tutorials/how-to-migrate-mysql-database-to-postgres-using-pgloader, etc.), but in my particular instance, I ran into several apparently undocumented hitches. After years of running my websites on a cloud server, I decided to move them to a proprietary server behind a firewall. It required setting up the port forwarding required, and the necessary virtual host directives in Apache, and none of that was problematic. But the migration of databases turned out to be. Here;s what worked.

First of all, recreate the original environment as best as possible on the new machine: same versions of PHP, etc. Note that the mod_php module has to be version-installed by hand. Unfortunately, Drupal requires the PDO module and at this time PDO doesn’t support a version of Postgres greater than 13.

Use pgloader to transfer the database from MySQL to PostgreSQL, and rsync to transfer the www directory tree to its new host location.

In the database, the schema will be wrong: all of the tables appear under a schema named after the database. This has to be batch-changed, so they appear under a scheme named “public”. Use the following code:

	DO $$DECLARE row record; 
		BEGIN FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'databasename' 
			LOOP 
				EXECUTE 'ALTER TABLE ' || quote_ident(row.tablename) || ' SET SCHEMA public;'; 
			END LOOP; 
		END$$

After this you can drop the old schema. You might use the same code with "OWNER TO ownername" to set the tables to be owned correctly if you need them to be.

Finally, trying to access the admin back-end fails, because a necessary sequence is missing in the database and the default value that refers to this is also missing. Create the sequence "watchdog_wid_seq", initialize it to 1 and set the increment to 1. Then alter table watchdog's "wid" field so that its default value uses the following code:

	nextval('watchdog_wid_seq'::regclass)

Of course, change the site's setting file to refer to the pgsql driver and port correctly, and the database's (new?) username and password. This should allow you to access the site and make further changes using Drupal's admin tools.

Occasionally, somehow the core Drupal files seem to somehow get confusingly out of sync, with reports of version mismatches due to partial updates, etc. The best fix seems to be to move the entire subdirectory aside, recreate the project with composer, and the rsync the composer directory OVER the original directory to replace the old core files. Then, of course, move the subdirectory back into place.