Google Cloud has a new product called AlloyDB to match Amazon AWS Aurora DB for PostgreSQL. I did a proof of concept on migrating from an existing PostgreSQL 14 database instance to an AlloyDB cluster. The experience wasn’t as smooth as migrating to Cloud SQL for PostgreSQL. I will share the details here.
First, you’d need to configure the source PostgreSQL 14 to allow replication. I installed PostgreSQL 14 on Ubuntu 22.04 with the following commands. Change secret123 unless you want to be hacked. Create user dms for the credential in a database migration connection profile.
$ sudo apt update && sudo apt install postgresql postgresql-contrib -y && sudo apt-get install postgresql-14-pglogical # https://github.com/2ndQuadrant/pglogical$ sudo -i -u postgres # login as postgres
$ psql
postgres=# ALTER ROLE postgres WITH PASSWORD 'secret123';
postgres=# CREATE USER dms WITH PASSWORD 'databasemigrationservice';
After the installation, allow remote connection to the postgreSQL instance by editing files at /etc/postgresql/14/main/
# modify with $ sudo nano /etc/postgresql/14/main/postgresql.conf
listen_addresses = '*' # was listen_addresses = 'localhost'# add lines to /etc/postgresql/14/main/pg_hba.conf
host all all 0.0.0.0/0 md5
host all all ::/0 md5# Remember to restart postgreSQL
sudo service postgresql restart
Before we start the database migration service job, replication needs to be properly enabled in the PostgreSQL instance and in each database under each schema. The Google cloud documentation’s configure your source instance section jumps to Install the pglogical package on the source instance which needs to be done before configuring individual databases. Reviewing Install the pglogical package on the source instance, the install postgresql-14-pglogical command above installed it. Next, either execute the following SQL statements or better append the lines to /etc/postgresql/14/main/postgresql.conf and restart PostgreSQL. Changes take effect after sudo service postgresql restart.
# append at the end of /etc/postgresql/14/main/postgresql.conf
shared_preload_libraries = 'pglogical'
wal_level = 'logical'
wal_sender_timeout = 0# recommended values at https://cloud.google.com/database-migration/docs/postgres/configure-source-database#on-premise-self-managed-postgresqlmax_replication_slots = 10
max_wal_senders = 12
max_worker_processes = 8# remember to execute $ sudo service postgresql restart
# Execute SQL -- SHOW max_worker_processes; to verify
After the instance is configured, follow the Configure your source databases steps (nested loop) for each database $db: for each schema $sch in $db including the postgres database. In my case, I only have testdb to migrate. The SQL statements below assumes no additional schemas other than public and only testdb to migrate. Statements below are expanded from the inner for loop.
-- FOR EACH $db to migrate including postgres-- CREATE EXTENSION IF NOT EXISTS pglogical;
-- GRANT USAGE on SCHEMA public to dms
-- GRANT USAGE on SCHEMA pglogical to dms
-- GRANT SELECT on ALL TABLES in SCHEMA pglogical to dms
-- GRANT SELECT on ALL TABLES in SCHEMA public to dms
-- GRANT SELECT on ALL SEQUENCES in SCHEMA public to dms
-- GRANT SELECT on ALL SEQUENCES in SCHEMA pglogical to dms
-- ALTER USER dms with REPLICATION
If all steps succeeded, you should have replication properly configured. The database migration service would also prompt you to configure the source PostgreSQL database instance to go through all the steps. There are additional infrastructure changes required to allow reverse SSH VM instance’s external IP to the source PostgreSQL database instance. Because it was a test environment, I basically created a firewall rule in the VPC to allow all IP to connect to port 5432 on network tag pgsql. Edit the source PostgreSQL database instance in compute engine to have the network tag pgsql to allow it. Test with psql command. The screenshot below showed a successful test. Don’t use port 55432 but 5432 for all database connection. Somehow 55432 caused the migration job to fail.
Somehow the migration job would fail after some time, I will follow up with Google and post updates.
