• Table of contents
• Prepare the New Server
  • Install PostgreSQL
  • Install MariaDB
• Migrate the Databases
  • Migrate PostgreSQL
    • Online Migration
    • Offline Migration
  • Migrate MariaDB
    • Online Migration
    • Offline Migration
• Resources

I recently needed to move two database services, PostgreSQL and MariaDB, from a Debian server on a VM to a FreeBSD jail. Since the database server on the VM was the database server for a few web services, I wanted as little downtime as possible.

This is a guide on the process by which I migrated the two database services to the new server.

localdb.example.com - current Debian VM database server
remotedb.example.com - new FreeBSD jail database server

Prepare the New Server¶

Start by installing and setting up the two database services in the new server.

Install PostgreSQL¶

• Install and setup PostgreSQL
  • NOTE: Make sure to test connecting to the remote server:
    

    psql -h remotedb.example.com -U pgsql template1
    

  • NOTE: Make sure to have the following set in the pg_hba.conf file to allow passwordless connections by the admin to the database socket:
    

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust
    
    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            trust
    # IPv6 local connections:
    host    all             all             ::1/128                 trust
    

Install MariaDB¶

• Install and setup MariaDB
  • NOTE: Make sure when setting up the MariaDB server to Allow remote root login during mysql_secure_installation. This will be disabled later.
  • NOTE: Make sure to add the root database user on the remote server:
    

    CREATE USER 'root'@'localdb.example.com' IDENTIFIED BY 'SuperSecretPassword';
    
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'localdb.example.com';
    
    FLUSH PRIVILEGES;
    
    quit
    

  • NOTE: Make sure to test connecting to the remote server:
    

    mysql -h remotedb.example.com -uroot -p
    

Migrate the Databases¶

Migrate PostgreSQL¶

Online Migration¶

• Migrate the local PostgreSQL databases, localdb.example.com, to the new remote server, remotedb.example.com:
  

  pg_dumpall -h localhost -U postgres --clean | PGPASSWORD="SuperSecretPassword" psql -h remotedb.example.com -U pgsql -w -d template1
  

Offline Migration¶

• Dump the databases to the postgresql-backup.sql file:
  

  pg_dumpall -h localhost -U postgres --clean -f postgresql-backup.sql
  

• Move the postgresql-backup.sql file to the new server:
  

  scp postgresql-backup.sql remotedb.example.com:
  

• Login to the remotedb.example.com server and import the databases:
  

  psql -h remotedb.example.com -U pgsql -W -d template1 < postgresql-backup.sql
  

Migrate MariaDB¶

Online Migration¶

• Migrate the local MariaDB databases, localdb.example.com to the new remote server, remotedb.example.com:
  

  mysqldump --all-databases -uroot -pSuperSecretPassword | mysql -hremotedb.example.com -uroot -pSuperSecretPassword
  

Offline Migration¶

• Dump the databases to the mariadb-backup.sql file:
  

  mysqldump --all-databases --host db.example.com --user user -p > mariadb-backup.sql
  

• Move the mariadb-backup.sql file to the new server:
  

  scp mariadb-backup.sql remotedb.example.com:
  

• Login to the remotedb.example.com server and import the databases:
  

  mysql --user root -p < mariadb-backup.sql
  

Resources¶

• http://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another
• http://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server
• http://dba.stackexchange.com/questions/17740/how-to-get-a-working-and-complete-postgresql-db-backup-and-test
• http://stackoverflow.com/questions/16618627/pg-dump-vs-pg-dumpall-which-one-to-use-to-database-backups
• https://www.postgresql.org/docs/9.0/static/app-pg-dumpall.html