What's the best practice of moving Metabase application database on PostgreSQL?

Get Help
1

Our production environment has a Metabase community edition running metabase.jar, with PostgreSQL as its application database. Keeping this way of working, we got a requirement to do an operation of moving and upgrading.

Reviewed the documents on https://www.metabase.com/docs/, we still need more specific directions on the instructions.

As you have noticed already, this operation includes migrating PosgreSQL application database to another PostgreSQL instance, not about moving from H2 to PostgreSQL.

So, about the database migration, I have the following tentative plan:

  1. SSH onto the old PostgreSQL v10 instance, dump the metabaseappdb database locally by the following command. And copy the SQL file to the new PostgreSQL for import later.
pg_dump \
  "postgres://old_metabase_db_user:old_metabase_db_pass@localhost:5432/metabaseappdb" \
  --file "$HOME/metabase-pgsql-dump/metabase-pgsql-dump.xxx.sql" \
  --format=p --no-owner --no-privileges
  1. SSH onto the new PostgreSQL v16 instance, and create the user and database locally by the following SQL statements using the database superuser postgres:
-- Create the database user
CREATE ROLE metabase_db_username WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'metabase_db_password'
;

-- Create the Metabase application database
CREATE DATABASE "metabaseappdb"
    WITH
    OWNER = metabase_db_username
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    LOCALE_PROVIDER = 'libc'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False
;

-- Grant privileges on the database
GRANT ALL PRIVILEGES ON DATABASE metabaseappdb TO metabase_db_username;

-- Grant privileges on the existing objects within the database
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO metabase_db_username;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO metabase_db_username;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO metabase_db_username;

-- Grant privileges on the future objects created in the database later
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO metabase_db_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO metabase_db_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO metabase_db_username;
  1. SSH onto the new Postgres 16 instance, import the data locally by the following command, notice it is running as the metabase_db_username, not the database superuser.
psql \
  "postgresql://metabase_db_username:metabase_db_password@localhost/metabaseappdb" \
  --file /root/shell-metabase-work/metabase-pgsql-dump.sql

After the application database migration, we will run the new version of metabase.jar referring to the new database, and the new Metabase starts.

We are new to this area, so please let me know if you see anything in the above steps. We highly appreciate any hints or suggestions.

Thank you.