Migrating databases can often seem like a daunting task, but with the right tools and a clear set of instructions, it can be a smooth and straightforward process. In this blog post, I'll explore the steps of importing a PostgreSQL database into a Hetzner virtual machine (VM).
You need to have a pg_dump dumpfile (e.g. my-backup.sql) in the root directory, from where you'll access your VM. E.g. if you have a rails app, just temporarily put the sql file in your root directory. How to get the dumpfile in the first place? This post looks at a few options..
With the database dump file created and stored in your current directory from where you're going to access your Hetzner VM, the next step is to transfer it to your target VM. The scp command facilitates the secure copying of files between hosts on a network. Execute the following command, ensuring to replace target-vm-ip with your target VM's IP address, filename.sql with your dump file's name, and /path/to/destination with the desired path on the target VM.
scp filename.sql user@target-vm-ip:/path/to/destination
In our case this should look something like this:
scp your-backup.sql root@73.37.73.112:~/
Now, it's time to switch over to your target VM. SSH into the target VM using:
ssh user@target-vm-ip
Or somthing like this:
ssh root@73.37.73.112
If you followed the previous instructions, the dump file should be in the root's home directory. You can check by running:
ls -l ~/your-backup.sql
Since in our case we're running several Docker containers on our Hetzner VM (following this post), and our Database has its own Docker container, we need to identify, which container we're going to migrate our dump file to. So still sshed into our Hetzer VM, check all the running Docker container:
docker ps
This should give you a list of all containers, including a 'Container ID'. Find the one containing the name of your app and that has a postgres image and copy its ID.
Access the PostgreSQL shell using the ocker exec command. Replace your_postgres_container with the actual name or ID of your PostgreSQL Docker container from the previous step.
docker exec -it your_postgres_container psql -U your_app_user -d your_app_production
You should find your_app_user and your_app_production in your deploy.yml file as the values of POSTGRES_USER: and POSTGRES_DB:.
Once inside the PostgreSQL shell, you can drop all tables or use the dropdb and createdb commands. But be careful, this destroy the current database!! If you want to replace the current DB with the data from your pg_dump, you'll have to drop all tables, by doing this:
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO your_app_user;
GRANT ALL ON SCHEMA public TO public;
Replace your_app_user with your actual pg user, as described in the previous step, execute the SQL query and then quit the PostgreSQL shell (\q + Enter)
Use the docker exec command to import the SQL file into your PostgreSQL container. Replace your_postgres_container with the actual container name or ID, and make sure the path to your SQL dump file is correct, which in our case should be the directory from which we've SSHed into our VM:
cat ~/your-backup.sql | docker exec -i your_postgres_container psql -U your_app_user -d your_app_production
pg_dump -U username dbname > filename.sql
psql -U username dbname < /path/to/destination/filename.sql
When prompted, enter the password for the PostgreSQL user.Before importing, make sure that the PostgreSQL service is running on your VM. If it's not running, start it with:
service postgresql start
Install require packages:
sudo apt-get update
sudo apt install postgresql-client-common
sudo apt-get install -y postgresql-client