Project

General

Profile

Support #828

Updated by Daniel Curtis over 7 years ago

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 

 h2. Prepare the New Server 

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

 * 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> 

 * 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> 

 h2. Migrate the Databases 

 * Migrate the local PostgreSQL databases, localdb.example.com, to the new remote server, remotedb.example.com: 
 <pre> 
 pg_dumpall -h localhost -U postgres | PGPASSWORD="SuperSecretPassword" psql -h remotedb.example.com -U pgsql -W 
 </pre> 

 * Migrate the local MariaDB databases, localdb.example.com to the new remote server, remotedb.example.com: 
 <pre> 
 mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver 
 </pre>  

 h2. 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

Back