How to migrate Metabase to another PostgreSQL instance

Keeping everything the same, we want to move our production Metabase to another instance of PostgreSQL. And the two PostgreSQL instances are on the same software version.

Tentatively, I am thinking about making a backup of the source database and restoring it to the target, then changing the Metabase database URI to refer to the target instead.

If this method is promising, I need help with the PostgreSQL database backup and restore procedure. I am new to PostgreSQL, so I would highly appreciate your instructions with more details.

Just Google how to backup and restore a database on PostgreSQL.
It's really not that hard :slight_smile:

Not sure if there is any official procedure, so I am tentatively sharing my commands, hoping to get a second opinion.

Please let me know if you have questions or, more importantly, see anything wrong.

The commands for backup and restore:

1) backup:

pg_dump \
  --verbose \
  --password \
  --format=c \
  --file=$backup_file \
  --host=$source_host \
  --port=5432 \
  --username=$source_username \
  --schema="public" \

2) restore:

pg_restore \
  --verbose \
  --port=5432 \
  --clean \
  --no-owner \
  --format=c \
  --username=$target_username \
  --dbname=$target_dbname \

** You need to set the environment variables, and we tried it on a production system of Metabase v0.32.9 with PostgreSQL 10. In our scenario, the source and target are different in 1) hostname, 2) username, and 3) dbname.

@1780yz That looks correct. The process is not specific to Metabase, but just regular Postgres admin task.
But if you are running Metabase 0.32.9, then you should upgrade to newer releases. There are lots of changes, including security fixes.

1 Like