Support #828
Updated by Daniel Curtis about 9 years ago
{{>toc}}
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
h1. Prepare the New Server
Start by installing and setting up the two database services in the new server.
h2. Install PostgreSQL
* Install and setup "PostgreSQL":https://project.altservice.com/issues/606
#* *NOTE*: Make sure to test connecting to the remote server:
<pre>
psql -h remotedb.example.com -U pgsql template1
</pre>
#* *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:
<pre>
# 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
</pre>
h2. Install MariaDB
* Install and setup "MariaDB":https://project.altservice.com/issues/788
#* *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:
<pre>
CREATE USER 'root'@'localdb.example.com' IDENTIFIED BY 'SuperSecretPassword';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localdb.example.com';
FLUSH PRIVILEGES;
quit
</pre>
#* *NOTE*: Make sure to test connecting to the remote server:
<pre>
mysql -h remotedb.example.com -uroot -p
</pre>
h1. Migrate the Databases
h2. Migrate PostgreSQL
h3. Online Migration
* Migrate the local PostgreSQL databases, localdb.example.com, to the new remote server, remotedb.example.com:
<pre>
pg_dumpall -h localhost -U postgres --clean | PGPASSWORD="SuperSecretPassword" psql -h remotedb.example.com -U pgsql -w -d template1
</pre>
h3. Offline Migration
* Dump the databases to the postgresql-backup.sql file:
<pre>
pg_dumpall -h localhost -U postgres --clean -f postgresql-backup.sql
</pre>
* Move the postgresql-backup.sql file to the new server:
<pre>
scp postgresql-backup.sql remotedb.example.com:
</pre>
* Login to the remotedb.example.com server and import the databases:
<pre>
psql -h remotedb.example.com -U pgsql -W -d template1 < postgresql-backup.sql
</pre>
h2. Migrate MariaDB
h3. Online Migration
* Migrate the local MariaDB databases, localdb.example.com to the new remote server, remotedb.example.com:
<pre>
mysqldump --all-databases -uroot -pSuperSecretPassword --user user -ppassword | mysql -hremotedb.example.com -uroot -pSuperSecretPassword -hremoteserver -uremoteuser -premoteserver
</pre>
h3. Offline Migration
* Dump the databases to the mariadb-backup.sql file:
<pre>
mysqldump --all-databases --host db.example.com --user user -p > mariadb-backup.sql
</pre>
* Move the mariadb-backup.sql file to the new server:
<pre>
scp mariadb-backup.sql remotedb.example.com:
</pre>
* Login to the remotedb.example.com server and import the databases:
<pre>
mysql --user root -p < mariadb-backup.sql
</pre>
h1. 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