Migrating existing Wordpress website

[batistellabr]

I have an existing wordpress website running on WP 6.7.1 connected to MySQL.
I already migrated mysql data to a Postgres 16 instance.

I installed PG4WP and configured everything, but my website is still not working. I did the same procedure with a fresh wordpress installation and it worked perfectly. I understand the PG4WP was designed to work in a new wordpress installation from the beggining, but for me it would be important to convert my current website from MySQL to Postgres. Is there a procedure for that?

Thank you.

The error Iam facing now:

[30-Dec-2024 13:48:22 UTC] PHP Warning: Undefined array key 1 in /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/driver_pgsql.php on line 551
[30-Dec-2024 13:48:22 UTC] PHP Warning: pg_query(): Query failed: ERROR: invalid name syntax
LINE 4: WHERE i.indrelid = ''::regclass
^ in /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/driver_pgsql.php on line 478
[30-Dec-2024 13:48:22 UTC] PHP Fatal error: Uncaught TypeError: pg_fetch_assoc(): Argument #1 ($result) must be of type PgSql\Result, false given in /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/driver_pgsql.php:555
Stack trace:
#0 /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/driver_pgsql.php(555): pg_fetch_assoc()
#1 /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/core.php(34) : eval()'d code(2345): wpsqli_query()
#2 /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/core.php(34) : eval()'d code(2259): wpdb2->_do_query()
#3 /mnt/efs/wordpress/siteconfirmemais/wp-content/plugins/psn-pagespeed-ninja/public/class-pagespeedninja-public.php(349): wpdb2->query()
#4 /mnt/efs/wordpress/siteconfirmemais/wp-content/plugins/psn-pagespeed-ninja/ress/classes/dispatcher.php(111): PagespeedNinja_Public->onRunAfter()
#5 /mnt/efs/wordpress/siteconfirmemais/wp-content/plugins/psn-pagespeed-ninja/ress/ressio.php(289): Ressio_Dispatcher->triggerEvent()
#6 /mnt/efs/wordpress/siteconfirmemais/wp-content/plugins/psn-pagespeed-ninja/public/class-pagespeedninja-public.php(300): Ressio->run()
#7 [internal function]: PagespeedNinja_Public->ob_callback()
#8 /mnt/efs/wordpress/siteconfirmemais/wp-includes/functions.php(5464): ob_end_flush()
#9 /mnt/efs/wordpress/siteconfirmemais/wp-includes/class-wp-hook.php(324): wp_ob_end_flush_all()
#10 /mnt/efs/wordpress/siteconfirmemais/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters()
#11 /mnt/efs/wordpress/siteconfirmemais/wp-includes/plugin.php(517): WP_Hook->do_action()
#12 /mnt/efs/wordpress/siteconfirmemais/wp-includes/load.php(1279): do_action()
#13 [internal function]: shutdown_action_hook()
#14 {main}
thrown in /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/driver_pgsql.php on line 555

[mattbucci]

I recommend you disable the plugin psn-pagespeed-ninja using the WordPress cli or a SQL client.

If possible you can also provide the query rewrite logs from pg4wp so that we can add this failing query to the test suite.

I understand the shape of the rewritten query is something like WHERE i.indrelid = ''::regclass but we need the full unmodified mysql query and what the plugin transforms it to + what postgres expects

[mattbucci]

FWIW in case someone else comes across this in the future you can migrate your data like this, we can add something to the readme...

Add the postgresql yum repo
https://www.postgresql.org/download/linux/redhat/

Install pgloader
sudo dnf install -y pgloader

Run the import
pgloader --with "quote identifiers" --debug mysql://<username>:<password>@<src hostname>:8124/<dbname> "postgresql://<username>@<dest hostname>:5432/<schema>?sslmode=allow&password=<password>"

[batistellabr]

Thanks for the tip.
I tried using pg_loader with "quote identifiers" enabled, but I got an error (details below).
It seems it has duplicated the quotes during the process.

pgloader version "3.6.70f3557"
compiled with SBCL 2.5.0

Database error 42703: column ""ID"" of relation "wp_posts" does not exist
CONTEXT: PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows
QUERY:
DO $$
DECLARE
n integer := 0;
r record;
BEGIN
FOR r in
SELECT 'select '
|| trim(trailing ')'
from replace(pg_get_expr(d.adbin, d.adrelid),
'nextval', 'setval'))
|| ', (select greatest(max(' || quote_ident(a.attname) || '), (select seqmin from pg_sequence where seqrelid = ('''
|| pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only '
|| quote_ident(nspname) || '.' || quote_ident(relname) || '));' as sql
FROM pg_class c
JOIN pg_namespace n on n.oid = c.relnamespace
JOIN pg_attribute a on a.attrelid = c.oid
JOIN pg_attrdef d on d.adrelid = a.attrelid
and d.adnum = a.attnum
and a.atthasdef
WHERE relkind = 'r' and a.attnum > 0
and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval'
and c.oid in (select oid from reloids)
LOOP
n := n + 1;
EXECUTE r.sql;
END LOOP;

PERFORM pg_notify('seqs', n::text);
END;
$$;

[batistellabr]

Quick update. I used pg_loader with the option --with "reset no sequences" and I could load the database while using --with "quote identifiers"

It may be necessary to adjust the sequences values manually, but Ok.

[batistellabr]

After migrating the data using pgloader --with "quote identifiers", still having errors:

[30-Dec-2024 15:56:13 UTC] PHP Fatal error: Uncaught TypeError: pg_fetch_assoc(): Argument #1 ($result) must be of type PgSql\Result, false given in /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/driver_pgsql.php:555 Stack trace: #0 /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/driver_pgsql.php(555): pg_fetch_assoc() #1 /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/core.php(34) : eval()'d code(2345): wpsqli_query() #2 /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/core.php(34) : eval()'d code(2259): wpdb2->_do_query() #3 /mnt/efs/wordpress/siteconfirmemais/wp-includes/option.php(1134): wpdb2->query() #4 /mnt/efs/wordpress/siteconfirmemais/wp-includes/option.php(2145): add_option() #5 /mnt/efs/wordpress/siteconfirmemais/wp-includes/option.php(1928): add_network_option() #6 /mnt/efs/wordpress/siteconfirmemais/wp-includes/option.php(2636): add_site_option() #7 /mnt/efs/wordpress/siteconfirmemais/wp-includes/class-wp-theme.php(2033): set_site_transient() #8 /mnt/efs/wordpress/siteconfirmemais/wp-includes/class-wp-theme.php(1856): WP_Theme->set_pattern_cache() #9 /mnt/efs/wordpress/siteconfirmemais/wp-includes/block-patterns.php(368): WP_Theme->get_block_patterns() #10 /mnt/efs/wordpress/siteconfirmemais/wp-includes/class-wp-hook.php(324): _register_theme_block_patterns() #11 /mnt/efs/wordpress/siteconfirmemais/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters() #12 /mnt/efs/wordpress/siteconfirmemais/wp-includes/plugin.php(517): WP_Hook->do_action() #13 /mnt/efs/wordpress/siteconfirmemais/wp-settings.php(704): do_action() #14 /mnt/efs/wordpress/siteconfirmemais/wp-config.php(115): require_once('...') #15 /mnt/efs/wordpress/siteconfirmemais/wp-load.php(50): require_once('...') #16 /mnt/efs/wordpress/siteconfirmemais/wp-blog-header.php(13): require_once('...') #17 /mnt/efs/wordpress/siteconfirmemais/index.php(17): require('...') #18 {main} thrown in /mnt/efs/wordpress/siteconfirmemais/wp-content/pg4wp/driver_pgsql.php on line 555

[mattbucci]

Set ‎PG4WP_DEBUG to true in wp-config and then check the pg4wp directory after recreating the error. You should see a number of log files

We need the query + rewrite that caused the failure which seems to be related to the theme _register_theme_block_patterns()

[batistellabr]

Thank you.
The problem was some of my plugins were passing empty queries. I just created a simple code to ignore empty string as queries and it worked.
Thank you.