One place for hosting & domains

      Migrate

      How To Migrate Redis Data to a DigitalOcean Managed Database


      Introduction

      There are a number of methods you can use to migrate data from one Redis instance to another, such as replication or snapshotting. However, migrations can get more complicated when you’re moving data to a Redis instance managed by a cloud provider, as managed databases often limit how much control you have over the database’s configuration.

      This tutorial outlines one method you can use to migrate data to a Redis instance managed by DigitalOcean. The method uses Redis’s internal migrate command to securely pass data through a TLS tunnel configured with stunnel. This guide will also go over a few other commonly-used migration strategies and why they’re problematic when migrating to a DigitalOcean Managed Database.

      Prerequisites

      To complete this tutorial, you will need:

      Note: To help keep things clear, this guide will refer to the Redis instance hosted on your Ubuntu server as the “source.” Likewise, it will refer to the instance managed by DigitalOcean as either the “target” or the “Managed Database.”

      Things To Consider When Migrating Redis Data to a Managed Database

      There are several methods you can employ to migrate data from one Redis instance to another. However, some of these approaches present problems when you’re migrating data to a Redis instance managed by DigitalOcean.

      For example, you can use replication to turn your target Redis instance into an exact copy of the source. To do this, you would connect to the target Redis server and run the replicaof command with the following syntax:

      • replicaof source_hostname_or_ip source_port

      This will cause the target instance to replicate all the data held on the source without destroying any data that was previously stored on it. Following this, you would promote the replica back to being a primary instance with the following command:

      However, Redis instances managed by DigitalOcean are configured to only become read-only replicas. If you have clients writing data to the source database, you won’t be able to configure them to write to the managed instance as it’s replicating data. This means you would lose any data sent by the clients after you promote the managed instance from being a replica and before you configure the clients to begin writing data to it, making replication suboptimal migration solution.

      Another method for migrating Redis data is to take a snapshot of the data held on your source instance with either Redis’s save or bgsave commands. Both of these commands export the snapshot to a file ending in .rdb, which you would then transfer to the target server. Following that, you’d restart the Redis service so it can load the data.

      However, many managed database providers — including DigitalOcean — don’t allow you to access the managed database server’s underlying file system. This means there’s no way to upload the snapshot file or make the necessary changes to the target database’s configuration file to allow the Redis to import the data.

      Because the configuration of DigitalOcean’s Managed Databases limit the efficacy of both replication and snapshotting as means of migrating data, this tutorial will instead use Redis’s migrate command to move data from the source to the target. The migrate command is designed to only move one key at a time, but we will use some handy command line tricks to move an entire Redis database with a single command.

      Step 1 — (Optional) Loading Your Source Redis Instance with Sample Data

      This optional step involves loading your source Redis instance with some sample data so you can experiment with migrating data to your Managed Redis Database. If you already have data that you want to migrate over to your target instance, you can move ahead to Step 2.

      To begin, run the following command to access your Redis server:

      If you’ve configured your Redis server to require password authentication, run the auth command followed by your Redis password:

      Then run the following commands. These will create a number of keys holding a few strings, a hash, a list, and a set:

      • mset string1 "Redis" string2 "is" string3 "fun!"
      • hmset hash1 field1 "Redis" field2 "is" field3 "fast!"
      • rpush list1 "Redis" "is" "feature-rich!"
      • sadd set1 "Redis" "is" "free!"

      Additionally, run the following expire commands to provide a few of these keys with a timeout. This will make them volatile, meaning that Redis will delete them after the specified amount of time, 7500 seconds:

      • expire string2 7500
      • expire hash1 7500
      • expire set1 7500

      With that, you have some example data you can export to your target Redis instance. You can keep the redis-cli prompt open for now, since we will run a few more commands from it in the next step in order to back up this data.

      Step 2 — Backing Up Your Data

      Previously, we discussed using Redis’s bgsave command to take a snapshot of a Redis database and migrate it to another instance. While we won’t use bgsave as a means of migrating Redis data, we will use it here to back up the data in case we encounter an error during the migration process.

      If you don’t already have it open, start by opening up the Redis command line interface:

      Also, if you’ve configured your Redis server to require password authentication, run the auth command followed by your Redis password:

      Next, run the bgsave command. This will create a snapshot of your current data set and export it to a dump file whose name ends in .rdb:

      Note: As mentioned in the previous Things To Consider section, you can take a snapshot of your Redis database with either the save or bgsave commands. The reason we use the bgsave command here is that the save command runs synchronously, meaning it will block any other clients connected to the database. Because of this, the save command documentation recommends that this command should almost never be run in a production environment.

      Instead, it suggests using the bgsave command which runs asynchronously. This will cause Redis to fork the database into two processes: the parent process will continue to serve clients while the child saves the database before exiting:

      Note that if clients add or modify data while the bgsave operation is running or after it finishes, these changes won’t be captured in the snapshot.

      Following that, you can close the connection to your Redis instance by running the exit command:

      If you need it in the future, you can find this dump file in your Redis installation’s working directory. If you’re not sure which directory this is, you can check by opening up your Redis configuration file with your preferred text editor. Here, we’ll use nano:

      • sudo nano /etc/redis/redis.conf

      Navigate to the line that begins with dbfilename. It will look like this by default:

      /etc/redis/redis.conf

      . . .
      # The filename where to dump the DB
      dbfilename dump.rdb
      . . .
      

      This directive defines the file to which Redis will export snapshots. The next line (after any comments) will look like this:

      /etc/redis/redis.conf

      . . .
      dir /var/lib/redis
      . . .
      

      The dir directive defines Redis’s working directory where any Redis snapshots are stored. By default, this is set to /var/lib/redis, as shown in this example.

      Close the redis.conf file. Assuming you didn’t make any changes to the file, you can do so by pressing CTRL+X.

      Then, list the contents of your Redis working directory to confirm that it’s holding the exported data dump file:

      If the dump file was exported correctly, you will see it in this command’s output:

      Output

      dump.rdb

      Once you’ve confirmed that you successfully backed up your data, you can begin the process of migrating it to your Managed Database.

      Step 3 — Migrating the Data

      Recall that this guide uses Redis’s internal migrate command to move keys one by one from the source database to the target. However, unlike the previous steps in this tutorial, we won’t run this command from the redis-cli prompt. Instead, we’ll run it directly from the server’s bash prompt. Doing so will allow us to use a few bash tricks to migrate all the keys on the source database with one command.

      Note: If you have clients writing data to your source Redis instance, now would be a good time to configure them to also write data to your Managed Database. This way, you can migrate the existing data from the source to your target without losing any writes that occur after the migration.

      Also, be aware that this migration command will not replace any existing keys on the target database unless one of the existing keys has the same name as a key you’re migrating.

      The migration will occur after running the following command. Before running it, though, we will break it down piece by piece:

      • redis-cli -n source_database -a source_password scan 0 | while read key; do redis-cli -n source_database -a source_password MIGRATE localhost 8000 "$key" target_database 1000 COPY AUTH managed_redis_password; done

      Let’s look at each part of this command separately:

      • redis-cli -n source_database -a source_password scan 0 . . .

      The first part of the command, redis-cli, opens a connection to the local Redis server. The -n flag specifies which of Redis’s logical databases to connect to. Redis has 16 databases out of the box (with the first being numbered 0, the second numbered 1, and so on), so source_database can be any number between 0 and 15. If your source instance only holds data on the default database (numbered 0), then you do not need to include the -n flag or specify a database number.

      Next, comes the -a flag and the source instance’s password, which together authenticate the connection. If your source instance does not require password authentication, then you do not need to include the -a flag.

      It then runs Redis’s scan command, which iterates over the keys held in the data set and returns them as a list. scan requires that you follow it with a cursor — the iteration begins when the cursor is set to 0, and terminates when the server returns a 0 cursor. Hence, we follow scan with a cursor of 0 so as to iterate over every key in the set.

      • . . . | while read key; do . . .

      The next part of the command begins with a vertical bar (|). In Unix-like systems, vertical bars are known as pipes and are used to direct the output of one process to the input of another.

      Following this is the start of a while loop. In bash, as well as in most programming languages, a while loop is a control flow statement that lets you repeat a certain process, code, or command as long as a certain condition remains true.

      The condition in this case is the sub-command read key, which reads the piped input and assigns it to the variable key. The semicolon (;) signifies the end of the while loop’s conditional statement, and the do following it precedes the action to be repeated as long as the while expression remains true. Every time the do statement completes, the conditional statement will read the next line piped from the scan command and assign that input to the key variable.

      Essentially, this section says “as long as there is output from the scan command to be read, perform the following action.”

      • . . . redis-cli -n source_database -a source_password migrate localhost 8000 "$key" . . .

      This section of the command is what performs the actual migration. After another redis-cli call, it once again specifies the source database number with the -n flag and authenticates with the -a flag. You have to include these again because this redis-cli call is distinct from the one at the start of the command. Again, though, you do not need to include the -n flag or database number if your source Redis instance only holds data in the default 0 database, and you don’t need to include the -a flag if it doesn’t require password authentication.

      Following this is the migrate command. Any time you use the migrate command, you must follow it with the target database’s hostname or IP address and its port number. Here, we follow the convention established in the prerequisite stunnel tutorial and point the migrate command to localhost at port 8000.

      $key is the variable defined in the first part of the while loop, and represents the keys from each line of the scan command’s output.

      • . . . target_database 1000 copy auth managed_redis_password; done

      This section is a continuation of the migrate command. It begins with target_database, which represents the logical database on the target instance where you want to store the data. Again, this can be any number from 0 to 15.

      Next is a number representing a timeout. This timeout is the maximum amount of idle communication time between the two machines. Note that this isn’t a time limit for the operation, just that the operation should always make some level of progress within the defined timeout. Both the database number and timeout arguments are required for every migrate command.

      Following the timeout is the optional copy flag. By default, migrate will delete each key from the source database after it transfers them to the target; by including this option, though, you’re instructing the migrate command to merely copy the keys so they will persist on the source.

      After copy comes the auth flag followed by your Managed Redis Database’s password. This isn’t necessary if you’re migrating data to an instance that doesn’t require authentication, but it is necessary when you’re migrating data to one managed by DigitalOcean.

      Following this is another semicolon, indicating the end of the action to be performed as long as the while condition holds true. Finally, the command closes with done, indicating the end of the loop. The command checks the condition in the while statement and repeats the action in the do statement until it’s no longer true.

      All together, this command performs the following steps:

      • Scan a database on the source Redis instance and return every key held within it
      • Pass each line of the scan command’s output into a while loop
      • Read the first line and assign its content to the key variable
      • Migrate any key in the source database that matches the key variable to a database on the Redis instance at the other end of the TLS tunnel held on localhost at port 8000
      • Go back and read the next line, and repeat the process until there are no more keys to read

      Now that we’ve gone over each part of the migration command, you can go ahead and run it.

      If your source instance only has data on the default 0 database, you do not need to include either of the -n flags or their arguments. If, however, you’re migrating data from any database other than 0 on your source instance, you must include the -n flags and change both occurrences of source_database to align with the database you want to migrate.

      If your source database requires password authentication, be sure to change source_password to the Redis instance’s actual password. If it doesn’t, though, make sure that you remove both occurrences of -a source_password from the command. Also, change managed_database_password to your own Managed Database’s password and be sure to change target_database to the number of whichever logical database on your target instance that you want to write the data to:

      Note: If you don’t have your Managed Redis Database’s password on hand, you can find it by first navigating to the DigitalOcean Control Panel. From there, click on Databases in the left-hand sidebar menu and then click on the name of the Redis instance to which you want to migrate the data. Scroll down to the Connection Details section where you’ll find a field labeled password. Click on the show button to reveal the password, then copy and paste it into the migration command — replacing managed_redis_password — in order to authenticate.

      • redis-cli -n source_database -a source_password scan 0 | while read key; do redis-cli -n source_database -a source_password MIGRATE localhost 8000 "$key" target_database 1000 COPY AUTH managed_redis_password; done

      You will see output similar to the following:

      Output

      NOKEY OK OK OK OK OK OK

      Note: Notice the first line of the command’s output which reads NOKEY. To understand what this means, run the first part of the migration command by itself:

      • redis-cli -n source_database -a source_password scan 0

      If you migrated the sample data added in Step 2, this command’s output will look like this:

      Output

      1) "0" 2) 1) "hash1" 2) "string3" 3) "list1" 4) "string1" 5) "string2" 6) "set1"

      The value "0" held in the first line is not a key held in your source Redis database, but a cursor returned by the scan command. Since there aren’t any keys on the server named “0”, there’s nothing there for the migrate command to send to your target instance and it returns NOKEY.

      However, the command doesn’t fail and exit. Instead, it continues on by reading and migrating the keys found in the next lines of the scan command’s output.

      To test whether the migration was successful, connect to your Managed Redis Database:

      • redis-cli -h localhost -p 8000 -a managed_redis_password

      If you migrated data to any logical database other than the default, connect to that database with the select command:

      Run a scan command to see what keys are held there:

      If you completed Step 2 of this tutorial and added the example data to your source database, you will see output like this:

      Output

      1) "0" 2) 1) "set1" 2) "string2" 3) "hash1" 4) "list1" 5) "string3" 6) "string1"

      Lastly, run a ttl command on any key which you’ve set to expire in order to confirm that it is still volatile:

      Output

      (integer) 3944

      This output shows that even though you migrated the key to your Managed Database, it still set to expire based on the expireat command you ran previously.

      Once you’ve confirmed that all the keys on your source Redis database were exported to your target successfully, you can close your connection to the Managed Database. If you have clients writing data to the source Redis instance and you’ve already configured them to send their writes to the target, you can at this point configure them to stop sending data to the source.

      Conclusion

      By completing this tutorial, you will have moved data from your self-managed Redis data store to a Redis instance managed by DigitalOcean. The process outlined in this guide may not be optimal in every case. For example, you’d have to run the migration command multiple times (once for every logical database holding data) if your source instance is using databases other than the default one. However, when compared to other methods like replication or snapshotting, it is a fairly straightforward process that works well with a DigitalOcean Managed Database’s configuration.

      Now that you’re using a DigitalOcean Managed Redis Database to store your data, you could measure its performance by running some benchmarking tests. Also, if you’re new to working with Redis, you could check out our series on How To Manage a Redis Database.



      Source link

      Should I Switch Web Hosts? How to Know When It’s Time to Migrate Your Site


      When it comes to starting a website, web hosting is one of the most crucial yet most confusing aspects to tackle. With dozens of providers on the market, it can be hard to cut through the noise and figure out which one offers the best plan for you.

      Fortunately, several signs will make it clear when it’s time to move to a new host. While they’re not so pleasant to deal with in the moment, these issues may lead you to a better service provider that can help you boost your site’s success.

      In this post, we’ll discuss these signs and how to spot them on your website. Then we’ll explain how to migrate your site to a new web hosting platform. Let’s get started!

      Have a website? We’ll move it for you!

      Migrating to a new web hosting provider can be a pain. We’ll move your existing site within 48 hours without any interruption in service. Included FREE with any DreamPress plan.

      How to Know When It’s Time to Migrate (6 Tell-Tale Signs)

      It’s possible you’ve been experiencing problems with your website for a while now without really knowing why. In some cases, it may be that your web hosting provider isn’t a good fit for your website. These six signs will let you know it’s time to switch web hosts.

      1. You’re Experiencing More Downtime Than Usual

      Any time your website is unavailable to users, it’s considered ‘down.’ Even if your site is only unavailable for seconds at a time, it could cause serious problems. For starters, downtime makes your website appear unreliable and low-quality to both users and search engines.

      If your site is experiencing frequent outages, your users will come to find they can’t rely on it to be available when needed. The Google algorithm will account for this, and your search engine rankings will fall as well, hurting your site’s visibility.

      Plus, if your site generates revenue, you’ll be missing out on income every time your site has an outage. If your site is down often or for long periods of time, you could be losing hundreds or even thousands of dollars. When you’re running an online store, uptime truly affects your bottom line.

      Web hosting is one of the most common causes of website downtime, as there are many ways in which your server can impact your site’s availability, including:

      • The quality and reliability of your hosting equipment
      • The type of server your website is on, as shared servers tend to become overloaded more quickly than other types of servers.
      • Your host’s security features, since malicious attacks can lead to downtime.

      So, if you keep finding your website is down, there’s a fair chance your host may have something to do with it. Moving to a more reliable server is the best thing for your site in a situation like this.

      2. Your Website’s Loading Speed Is Slow

      Site speed is also key to Search Engine Optimization (SEO), users’ opinions of your site, and your conversion rate. It’s wise to test your site’s speed every once in a while using tools such as Google PageSpeed Insights and Pingdom to make sure your loading times are staying low and to fix any performance issues.

      Pingdom’s results screen.

      While a crowded server can certainly slow your loading times, your server’s location also plays a role in how fast your site delivers information to visitors. Servers located far away from end users aren’t able to serve them content as quickly.

      An easy way to determine if this is the case for your website is to use Pingdom to test your site speed from a variety of locations. If your site loads quickly from some places yet takes a long time to load in others, you’ll know server location is causing speed issues for users in those regions.

      If your host only has servers in one location and doesn’t offer a Content Delivery Network (CDN), it’s almost guaranteed that some portion of your users will experience less-than-ideal site speed. It may be worth looking into hosts with more or different locations, or ones offering a CDN.

      3. Customer Service Isn’t Helpful

      A solid relationship with your web host is priceless. For starters, there are going to be times when server-related errors occur on your site. In these instances, you’ll need to be able to get ahold of your host quickly to resolve the issue and get your site back up. Plus, you may sometimes have questions about billing or other account details.

      However, the best hosts also offer support in other areas of website management. For example, many hosts provide troubleshooting guidance for different types of errors on your website or support for platforms such as WordPress.

      If your host is difficult to get in touch with, provides inadequate solutions, or doesn’t offer support in areas directly related to your hosting account, consider switching to a new provider. While you may be able to get by without quality customer support, at some point, you’ll have to reach someone for help with a server-related problem, so you’ll want a reliable team at your back.

      4. You Need More Space Than Your Current Provider Can Offer

      Most websites start small and grow over time. Your current host may have been a great fit when you were first launching your site, but if your traffic levels have increased significantly, this may no longer be the case.

      As your site accumulates more recurring users, you’ll need a server that can handle more traffic as well as more and larger website files. Moving from shared hosting to a dedicated server can help, but switching hosts can often provide a greater benefit.

      Some providers specialize in shared or Virtual Private Network (VPN) hosting and may not offer dedicated servers. As such, if your site continues to grow, you’ll need a dedicated web hosting service at some point — so a switch may be inevitable.

      Other hosts may have dedicated servers available, but still not offer as much storage as you need. Ultimately, you’ll want to compare plans between companies to see which one offers the most space for the best price.

      5. It’s Getting Too Expensive to Stay With Your Current Host

      Web hosting is a recurring expense. It’s also sometimes the largest expense associated with running a website, especially for WordPress users working with a free Content Management System (CMS) and mainly free plugins and themes.

      It’s true that you often get what you pay for with hosting. However, there are also times when an expensive plan isn’t necessary. If your site is still small and not using the amount of server space you’re paying for, or if your current hosting plan comes with several features you never touch, you’re probably paying too much.

      There’s no sense in breaking the bank to host your website when there are plenty of affordable options available. For example, we offer high-quality managed WordPress hosting plans for as low as $16.95 per month.

      If you’re shelling out more money for web hosting than what your website brings in, you might want to consider downsizing or switching hosts to stay within your budget. Plus, it never hurts to pocket a little extra cash each month.

      6. Server Security Is Sub-Par

      As we mentioned earlier in this post, hosts are responsible for securing their servers. Not every provider is as diligent as they should be when it comes to security, and hackers will sometimes exploit weaknesses in your server to gain access to your site.

      This can be detrimental to your website for multiple reasons, including:

      • The loss of parts or all of your site due to a malicious attack that destroys key files and data.
      • Compromised user data, including sensitive information such as private records and credit card details.
      • Decreased credibility, as users will see your site as less reliable if it’s hacked.

      Investing in secure hosting is a smart move. Even if you have to pay a little extra or go through the trouble of migrating to a new host, you’ll save yourself a lot of trouble down the line.

      Some security features you may want to keep an eye out for are Secure Sockets Layer (SSL) certificates, malware scanning, and server firewalls. Of course, no matter how secure your server is, you should always follow security best practices for your site itself, too.

      How to Migrate Your Website to a New Hosting Provider

      If you’ve considered the signs mentioned above and determined you should switch hosting providers, you’ll need to migrate your website. This requires you to copy all your website’s files and move them to your new hosting account.

      Typically, the migration process is pretty involved. You’ll have to contact your current host, back up your site files, then use Secure File Transfer Protocol (SFTP) and a client such as FileZilla to connect to your new server and upload your files. You’ll also want to consider transferring your domain since there are benefits to keeping your domain registration and web hosting under one roof.

      As you might imagine, there are a lot of things that could go wrong during this process. For example, corrupted backups are always a possibility, and using SFTP still poses a risk to your site’s files as you could mistakenly delete some or all of them (we recommend users always have a recent backup of their site on hand).

      These things considered, it’s helpful if you can get an expert on board to migrate your site for you. Fortunately, if you’re a WordPress user and have decided to switch to DreamHost, our managed WordPress hosting plans include free website migration services.

      DreamHost’s WordPress migration services.

      We’ll handle moving your site at no extra cost. If you’d prefer one of our shared hosting plans or have a website built without using WordPress, never fear. You can still take advantage of our migration service for just $99.

      Our migration experts will get your site moved to your new hosting account within 48 hours of your request. You’ll also avoid downtime altogether, so you don’t have to worry about negatively impacting your users’ experience while you move your site and get acquainted with the DreamHost control panel.

      Looking for a New Hosting Provider?

      We make moving easy. Our hassle-free, high-performance WordPress hosting includes a FREE professional migration service ($99 savings)!

      Switching Web Hosts

      Hosting can be one of the most confusing aspects of owning a website. With so many options to choose from, it can be difficult to know if your web hosting provider is the best one available for your needs.

      If you’ve noticed these issues on your website and have decided it’s time for a change, consider checking out our DreamPress hosting plans. Our managed WordPress hosting service will provide you with the speed, support, and security your WordPress site needs. Plus, you’ll be able to use our site migration services for free.



      Source link

      How To Migrate a MySQL Database to PostgreSQL Using pgLoader


      Introduction

      PostgreSQL, also known as “Postgres,” is an open-source relational database management system (RDBMS). It has seen a drastic growth in popularity in recent years, with many developers and companies migrating their data to Postgres from other database solutions.

      The prospect of migrating a database can be intimidating, especially when migrating from one database management system to another. pgLoader is an open-source database migration tool that aims to simplify the process of migrating to PostgreSQL. It supports migrations from several file types and RBDMSs — including MySQL and SQLite — to PostgreSQL.

      This tutorial provides instructions on how to install pgLoader and use it to migrate a remote MySQL database to PostgreSQL over an SSL connection. Near the end of the tutorial, we will also briefly touch on a few different migration scenarios where pgLoader may be useful.

      Prerequisites

      To complete this tutorial, you’ll need the following:

      • Access to two servers, each running Ubuntu 18.04. Both servers should have a firewall and a non-root user with sudo privileges configured. To set these up, you can follow our Initial Server Setup guide for Ubuntu 18.04.
      • MySQL installed on one of the servers. To set this up, follow Steps 1, 2, and 3 of our guide on How To Install MySQL on Ubuntu 18.04. Please note that in order to complete all the prerequisite tutorials linked here, you will need to configure your root MySQL user to authenticate with a password, as described in Step 3 of the MySQL installation guide.
      • PostgreSQL installed on the other server. To set this up, complete Step 1 of our guide How To Install and Use PostgreSQL on Ubuntu 18.04.
      • Your MySQL server should also be configured to accept encrypted connections. To set this up, complete every step of our tutorial on How To Configure SSL/TLS for MySQL on Ubuntu 18.04, including the optional Step 6. As you follow this guide, be sure to use your PostgreSQL server as the MySQL client machine, as you will need to be able to connect to your MySQL server from your Postgres machine in order to migrate the data with pgLoader.

      Please note that throughout this guide, the server on which you installed MySQL will be referred to as the “MySQL server” and any commands that should be run on this machine will be shown with a blue background, like this:

      Similarly, this guide will refer to the other server as the "PostgreSQL" or "Postgres" server and any commands that must be run on that machine will be shown with a red background:

      Please keep these in mind as you follow this tutorial so as to avoid any confusion.

      Step 1 — (Optional) Creating a Sample Database and Table in MySQL

      This step describes the process of creating a test database and populating it with dummy data. We encourage you to practice using pgLoader with this test case, but if you already have a database you want to migrate, you can move on to the next step.

      Start by opening up the MySQL prompt on your MySQL server:

      After entering your root MySQL user's password, you will see the MySQL prompt.

      From there, create a new database by running the following command. You can name your database whatever you'd like, but in this guide we will name it source_db:

      • CREATE DATABASE source_db;

      Then switch to this database with the USE command:

      Output

      Database changed

      Within this database, use the following command to create a sample table. Here, we will name this table sample_table but feel free to give it another name:

      • CREATE TABLE sample_table (
      • employee_id INT PRIMARY KEY,
      • first_name VARCHAR(50),
      • last_name VARCHAR(50),
      • start_date DATE,
      • salary VARCHAR(50)
      • );

      Then populate this table with some sample employee data using the following command:

      • INSERT INTO sample_table (employee_id, first_name, last_name, start_date, salary)
      • VALUES (1, 'Elizabeth', 'Cotten', '2007-11-11', '$105433.18'),
      • (2, 'Yanka', 'Dyagileva', '2017-10-30', '$107540.67'),
      • (3, 'Lee', 'Dorsey', '2013-06-04', '$118024.04'),
      • (4, 'Kasey', 'Chambers', '2010-08-18', '$116456.98'),
      • (5, 'Bram', 'Tchaikovsky', '2018-09-16', '$61989.50');

      Following this, you can close the MySQL prompt:

      Now that you have a sample database loaded with dummy data, you can move on to the next step in which you will install pgLoader on your PostgreSQL server.

      Step 2 — Installing pgLoader

      pgLoader is a program that can load data into a PostgreSQL database from a variety of different sources. It uses PostgreSQL's COPY command to copy data from a source database or file — such as a comma-separated values (CSV) file — into a target PostgreSQL database.

      pgLoader is available from the default Ubuntu APT repositories and you can install it using the apt command. However, in this guide we will take advantage of pgLoader's useSSL option, a feature that allows for migrations from MySQL over an SSL connection. This feature is only available in the latest version of pgLoader which, as of this writing, can only be installed using the source code from its GitHub repository.

      Before installing pgLoader, you will need to install its dependencies. If you haven't done so recently, update your Postgres server's package index:

      Then install the following packages:

      • sbcl: A Common Lisp compiler
      • unzip: A de-archiver for .zip files
      • libsqlite3-dev: A collection of development files for SQLite 3
      • gawk: Short for "GNU awk", a pattern scanning and processing language
      • curl: A command line tool for transferring data from a URL
      • make: A utility for managing package compilation
      • freetds-dev: A client library for MS SQL and Sybase databases
      • libzip-dev: A library for reading, creating, and modifying zip archives

      Use the following command to install these dependencies:

      • sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

      When prompted, confirm that you want to install these packages by pressing ENTER.

      Next, navigate to the pgLoader GitHub project's Releases page and find the latest release. For this guide, we will use the latest release at the time of this writing: version 3.6.1. Scroll down to its Assets menu and copy the link for the tar.gz file labeled Source code. Then paste the link into the following wget command. This will download the tarball to your server:

      • wget https://github.com/dimitri/pgloader/archive/v3.6.1.tar.gz

      Extract the tarball:

      This will create a number of new directories and files on your server. Navigate into the new pgLoader parent directory:

      Then use the make utility to compile the pgloader binary:

      This command will take some time to build the pgloader binary.

      Move the binary file into the /usr/local/bin directory, the location where Ubuntu looks for executable files:

      • sudo mv ./build/bin/pgloader /usr/local/bin/

      You can test that pgLoader was installed correctly by checking its version, like so:

      Output

      pgloader version "3.6.1" compiled with SBCL 1.4.5.debian

      pgLoader is now installed, but before you can begin your migration you'll need to make some configuration changes to both your PostgreSQL and MySQL instances. We'll focus on the PostgreSQL server first.

      Step 3 — Creating a PostgreSQL Role and Database

      The pgloader command works by copying source data, either from a file or directly from a database, and inserting it into a PostgreSQL database. For this reason, you must either run pgLoader as a Linux user who has access to your Postgres database or you must specify a PostgreSQL role with the appropriate permissions in your load command.

      PostgreSQL manages database access through the use of roles. Depending on how the role is configured, it can be thought of as either a database user or a group of database users. In most RDBMSs, you create a user with the CREATE USER SQL command. Postgres, however, comes installed with a handy script called createuser. This script serves as a wrapper for the CREATE USER SQL command that you can run directly from the command line.

      Note: In PostgreSQL, you authenticate as a database user using the Identification Protocol, or ident, authentication method by default, rather than with a password. This involves PostgreSQL taking the client's Ubuntu username and using it as the allowed Postgres database username. This allows for greater security in many cases, but it can also cause issues in instances where you'd like an outside program to connect to one of your databases.

      pgLoader can load data into a Postgres database through a role that authenticates with the ident method as long as that role shares the same name as the Linux user profile issuing the pgloader command. However, to keep this process as clear as possible, this tutorial describes setting up a different PostgreSQL role that authenticates with a password rather than with the ident method.

      Run the following command on your Postgres server to create a new role. Note the -P flag, which tells createuser to prompt you to enter a password for the new role:

      • sudo -u postgres createuser --interactive -P

      You may first be prompted for your sudo password. The script will then prompt you to enter a name for the new role. In this guide, we'll call this role pgloader_pg:

      Output

      Enter name of role to add: pgloader_pg

      Following that, createuser will prompt you to enter and confirm a password for this role. Be sure to take note of this password, as you'll need it to perform the migration in Step 5:

      Output

      Enter password for new role: Enter it again:

      Lastly, the script will ask you if the new role should be classified as a superuser. In PostgreSQL, connecting to the database with a superuser role allows you to circumvent all of the database's permissions checks, except for the right to log in. Because of this, the superuser privilege should not be used lightly, and the PostgreSQL documentation recommends that you do most of your database work as a non-superuser role. However, because pgLoader needs broad privileges to access and load data into tables, you can safely grant this new role superuser privileges. Do so by typing y and then pressing ENTER:

      Output

      . . . Shall the new role be a superuser? (y/n) y

      PostgreSQL comes with another useful script that allows you to create a database from the command line. Since pgLoader also needs a target database into which it can load the source data, run the following command to create one. We'll name this database new_db but feel free to modify that if you like:

      • sudo -u postgres createdb new_db

      If there aren't any errors, this command will complete without any output.

      Now that you have a dedicated PostgreSQL user and an empty database into which you can load your MySQL data, there are just a few more changes you'll need to make before performing a migration. You'll need to create a dedicated MySQL user with access to your source database and add your client-side certificates to Ubuntu's trusted certificate store.

      Step 4 — Creating a Dedicated User in MySQL and Managing Certificates

      Protecting data from snoopers is one of the most important parts of any database administrator's job. Migrating data from one machine to another opens up an opportunity for malicious actors to sniff the packets traveling over the network connection if it isn't encrypted. In this step, you will create a dedicated MySQL user which pgLoader will use to perform the migration over an SSL connection.

      Begin by opening up your MySQL prompt:

      From the MySQL prompt, use the following CREATE USER command to create a new MySQL user. We will name this user pgloader_my. Because this user will only access MySQL from your PostgreSQL server, be sure to replace your_postgres_server_ip with the public IP address of your PostgreSQL server. Additionally, replace password with a secure password or passphrase:

      • CREATE USER 'pgloader_my'@'your_postgres_server_ip' IDENTIFIED BY 'password' REQUIRE SSL;

      Note the REQUIRE SSL clause at the end of this command. This will restrict the pgloader_my user to only access the database through a secure SSL connection.

      Next, grant the pgloader_my user access to the target database and all of its tables. Here, we'll specify the database we created in the optional Step 1, but if you have your own database you'd like to migrate, use its name in place of source_db:

      • GRANT ALL ON source_db.* TO 'pgloader_my'@'your_postgresql_server_ip';

      Then run the FLUSH PRIVILEGES command to reload the grant tables, enabling the privilege changes:

      After this, you can close the MySQL prompt:

      Now go back to your Postgres server terminal and attempt to log in to the MySQL server as the new pgloader_my user. If you followed the prerequisite guide on configuring SSL/TLS for MySQL then you will already have mysql-client installed on your PostgreSQL server and you should be able to connect with the following command:

      • mysql -u pgloader_my -p -h your_mysql_server_ip

      If the command is successful, you will see the MySQL prompt:

      After confirming that your pgloader_my user can successfully connect, go ahead and close the prompt:

      At this point, you have a dedicated MySQL user that can access the source database from your Postgres machine. However, if you were to try to migrate your MySQL database using SSL the attempt would fail.

      The reason for this is that pgLoader isn't able to read MySQL's configuration files, and thus doesn't know where to look for the CA certificate or client certificate that you copied to your PostgreSQL server in the prerequisite SSL/TLS configuration guide. Rather than ignoring SSL requirements, though, pgLoader requires the use of trusted certificates in cases where SSL is needed to connect to MySQL. Accordingly, you can resolve this issue by adding the ca.pem and client-cert.pem files to Ubuntu's trusted certificate store.

      To do this, copy over the ca.pem and client-cert.pem files to the /usr/local/share/ca-certificates/ directory. Note that you must also rename these files so they have the .crt file extension. If you don't rename them, your system will not be able to recognize that you've added these new certificates:

      • sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt
      • sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt

      Following this, run the update-ca-certificates command. This program looks for certificates within /usr/local/share/ca-certificates, adds any new ones to the /etc/ssl/certs/ directory, and generates a list of trusted SSL certificates — ca-certificates.crt — based on the contents of the /etc/ssl/certs/ directory:

      • sudo update-ca-certificates

      Output

      Updating certificates in /etc/ssl/certs... 2 added, 0 removed; done. Running hooks in /etc/ca-certificates/update.d... done.

      With that, you're all set to migrate your MySQL database to PostgreSQL.

      Step 5 — Migrating the Data

      Now that you've configured remote access from your PostgreSQL server to your MySQL server, you're ready to begin the migration.

      Note: It's important to back up your database before taking any action that could impact the integrity of your data. However, this isn't necessary when performing a migration with pgLoader, since it doesn't delete or transform data; it only copies it.

      That said, if you're feeling cautious and would like to back up your data before migrating it, you can do so with the mysqldump utility. See the official MySQL documentation for details.

      pgLoader allows users to migrate an entire database with a single command. For a migration from a MySQL database to a PostgreSQL database on a separate server, the command would have the following syntax:

      • pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name?option_1=value&option_n=value postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value

      This includes the pgloader command and two connection strings, the first for the source database and the second for the target database. Both of these connection strings begin by declaring what type of DBMS the connection string points to, followed by the username and password that have access to the database (separated by a colon), the host address of the server where the database is installed, the name of the database pgLoader should target, and various options that affect pgLoader's behavior.

      Using the parameters defined earlier in this tutorial, you can migrate your MySQL database using a command with the following structure. Be sure to replace any highlighted values to align with your own setup:

      • pgloader mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true postgresql://pgloader_pg:postgresql_password@localhost/new_db

      Note that this command includes the useSSL option in the MySQL connection string. By setting this option to true, pgLoader will connect to MySQL over SSL. This is necessary, as you've configured your MySQL server to only accept secure connections.

      If this command is successful, you will see an output table describing how the migration went:

      Output

      table name errors rows bytes total time ----------------------- --------- --------- --------- -------------- fetch meta data 0 2 0.111s Create Schemas 0 0 0.001s Create SQL Types 0 0 0.005s Create tables 0 2 0.017s Set Table OIDs 0 1 0.010s ----------------------- --------- --------- --------- -------------- source_db.sample_table 0 5 0.2 kB 0.048s ----------------------- --------- --------- --------- -------------- COPY Threads Completion 0 4 0.052s Index Build Completion 0 1 0.011s Create Indexes 0 1 0.006s Reset Sequences 0 0 0.014s Primary Keys 0 1 0.001s Create Foreign Keys 0 0 0.000s Create Triggers 0 0 0.000s Install Comments 0 0 0.000s ----------------------- --------- --------- --------- -------------- Total import time ✓ 5 0.2 kB 0.084s

      To check that the data was migrated correctly, open up the PostgreSQL prompt:

      From there, connect to the database into which you loaded the data:

      Then run the following query to test whether the migrated data is stored in your PostgreSQL database:

      • SELECT * FROM source_db.sample_table;

      Note: Notice the FROM clause in this query specifying the sample_table held within the source_db schema:

      • . . . FROM source_db.sample_table;

      This is called a qualified name. You could go further and specify the fully qualified name by including the database's name as well as those of the schema and table:

      • . . . FROM new_db.source_db.sample_table;

      When you run queries in a PostgreSQL database, you don't need to be this specific if the table is held within the default public schema. The reason you must do so here is that when pgLoader loads data into Postgres, it creates and targets a new schema named after the original database — in this case, source_db. This is pgLoader's default behavior for MySQL to PostgreSQL migrations. However, you can use a load file to instruct pgLoader to change the table's schema topubliconce it's done loading data. See the next step for an example of how to do this.

      If the data was indeed loaded correctly, you will see the following table in the query's output:

      Output

      employee_id | first_name | last_name | start_date | salary -------------+------------+-------------+------------+------------ 1 | Elizabeth | Cotten | 2007-11-11 | $105433.18 2 | Yanka | Dyagileva | 2017-10-30 | $107540.67 3 | Lee | Dorsey | 2013-06-04 | $118024.04 4 | Kasey | Chambers | 2010-08-18 | $116456.98 5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50 (5 rows)

      To close the Postgres prompt, run the following command:

      Now that we've gone over how to migrate a MySQL database over a network and load it into a PostgreSQL database, we will go over a few other common migration scenarios in which pgLoader can be useful.

      Step 6 — Exploring Other Migration Options

      pgLoader is a highly flexible tool that can be useful in a wide variety of situations. Here, we'll take a quick look at a few other ways you can use pgLoader to migrate a MySQL database to PostgreSQL.

      Migrating with a pgLoader Load File

      In the context of pgLoader, a load file, or command file, is a file that tells pgLoader how to perform a migration. This file can include commands and options that affect pgLoader's behavior, giving you much finer control over how your data is loaded into PostgreSQL and allowing you to perform complex migrations.

      pgLoader's documentation provides comprehensive instructions on how to use and extend these files to support a number of migration types, so here we will work through a comparatively rudimentary example. We will perform the same migration we ran in Step 5, but will also include an ALTER SCHEMA command to change the new_db database's schema from source_db to public.

      To begin, create a new load file on the Postgres server using your preferred text editor:

      Then add the following content, making sure to update the highlighted values to align with your own configuration:

      pgload_test.load

      LOAD DATABASE
           FROM      mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true
           INTO pgsql://pgloader_pg:postgresql_password@localhost/new_db
      
       WITH include drop, create tables
      
      ALTER SCHEMA 'source_db' RENAME TO 'public'
      ;
      

      Here is what each of these clauses do:

      • LOAD DATABASE: This line instructs pgLoader to load data from a separate database, rather than a file or data archive.
      • FROM: This clause specifies the source database. In this case, it points to the connection string for the MySQL database we created in Step 1.
      • INTO: Likewise, this line specifies the PostgreSQL database in to which pgLoader should load the data.
      • WITH: This clause allows you to define specific behaviors for pgLoader. You can find the full list of WITH options that are compatible with MySQL migrations here. In this example we only include two options:
        • include drop: When this option is used, pgLoader will drop any tables in the target PostgreSQL database that also appear in the source MySQL database. If you use this option when migrating data to an existing PostgreSQL database, you should back up the entire database to avoid losing any data.
        • create tables: This option tells pgLoader to create new tables in the target PostgreSQL database based on the metadata held in the MySQL database. If the opposite option, create no tables, is used, then the target tables must already exist in the target Postgres database prior to the migration.
      • ALTER SCHEMA: Following the WITH clause, you can add specific SQL commands like this to instruct pgLoader to perform additional actions. Here, we instruct pgLoader to change the new Postgres database's schema from source_db to public, but only after it has created the schema. Note that you can also nest such commands within other clauses — such as BEFORE LOAD DO — to instruct pgLoader to execute those commands at specific points in the migration process.

      This is a demonstrative example of what you can include in a load file to modify pgLoader's behavior. The complete list of clauses that one can add to a load file and what they do can be found in the official pgLoader documentation.

      Save and close the load file after you've finished adding this content. To use it, include the name of the file as an argument to the pgloader command:

      • pgloader pgload_test.load

      To test that the migration was successful, open up the Postgres prompt:

      Then connect to the database:

      And run the following query:

      • SELECT * FROM sample_table;

      Output

      employee_id | first_name | last_name | start_date | salary -------------+------------+-------------+------------+------------ 1 | Elizabeth | Cotten | 2007-11-11 | $105433.18 2 | Yanka | Dyagileva | 2017-10-30 | $107540.67 3 | Lee | Dorsey | 2013-06-04 | $118024.04 4 | Kasey | Chambers | 2010-08-18 | $116456.98 5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50 (5 rows)

      This output confirms that pgLoader migrated the data successfully, and also that the ALTER SCHEMA command we added to the load file worked as expected, since we didn't need to specify the source_db schema in the query to view the data.

      Note that if you plan to use a load file to migrate data held on one database to another located on a separate machine, you will still need to adjust any relevant networking and firewall rules in order for the migration to be successful.

      Migrating a MySQL Database to PostgreSQL Locally

      You can use pgLoader to migrate a MySQL database to a PostgreSQL database housed on the same machine. All you need is to run the migration command from a Linux user profile with access to the root MySQL user:

      • pgloader mysql://root@localhost/source_db pgsql://sammy:postgresql_password@localhost/target_db

      Performing a local migration like this means you don't have to make any changes to MySQL's default networking configuration or your system's firewall rules.

      Migrating from a CSV file

      You can also load a PostgreSQL database with data from a CSV file.

      Assuming you have a CSV file of data named load.csv, the command to load it into a Postgres database might look like this:

      • pgloader load.csv pgsql://sammy:password@localhost/target_db

      Because the CSV format is not fully standardized, there's a chance that you will run into issues when loading data directly from a CSV file in this manner. Fortunately, you can correct for irregularities by including various options with pgLoader's command line options or by specifying them in a load file. See the pgLoader documentation on the subject for more details.

      Migrating to a Managed PostgreSQL Database

      It's also possible to perform a migration from a self-managed database to a managed PostgreSQL database. To illustrate how this kind of migration could look, we will use the MySQL server and a DigitalOcean Managed PostgreSQL Database. We'll also use the sample database we created in Step 1, but if you skipped that step and have your own database you'd like to migrate, you can point to that one instead.

      Note: For instructions on how to set up a DigitalOcean Managed Database, please refer to our Managed Database Quickstart guide.

      For this migration, we won't need pgLoader’s useSSL option since it only works with remote MySQL databases and we will run this migration from a local MySQL database. However, we will use the sslmode=require option when we load and connect to the DigitalOcean Managed PostgreSQL database, which will ensure your data stays protected.

      Because we're not using the useSSL this time around, you can use apt to install pgLoader along with the postgresql-client package, which will allow you to access the Managed PostgreSQL Database from your MySQL server:

      • sudo apt install pgloader postgresql-client

      Following that, you can run the pgloader command to migrate the database. To do this, you'll need the connection string for the Managed Database.

      For DigitalOcean Managed Databases, you can copy the connection string from the Cloud Control Panel. First, click Databases in the left-hand sidebar menu and select the database to which you want to migrate the data. Then scroll down to the Connection Details section. Click on the drop down menu and select Connection string. Then, click the Copy button to copy the string to your clipboard and paste it into the following migration command, replacing the example PostgreSQL connection string shown here. This will migrate your MySQL database into the defaultdb PostgreSQL database as the doadmin PostgreSQL role:

      • pgloader mysql://root:password@localhost/source_db postgres://doadmin:password@db_host/defaultdb?sslmode=require

      Following this, you can use the same connection string as an argument to psql to connect to the managed PostgreSQL database andhttps://www.digitalocean.com/community/tutorials/how-to-migrate-mysql-database-to-postgres-using-pgloader#step-1-%E2%80%94-(optional)-creating-a-sample-database-and-table-in-mysql confirm that the migration was successful:

      • psql postgres://doadmin:password@db_host/defaultdb?sslmode=require

      Then, run the following query to check that pgLoader correctly migrated the data:

      • SELECT * FROM source_db.sample_table;

      Output

      employee_id | first_name | last_name | start_date | salary -------------+------------+-------------+------------+------------ 1 | Elizabeth | Cotten | 2007-11-11 | $105433.18 2 | Yanka | Dyagileva | 2017-10-30 | $107540.67 3 | Lee | Dorsey | 2013-06-04 | $118024.04 4 | Kasey | Chambers | 2010-08-18 | $116456.98 5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50 (5 rows)

      This confirms that pgLoader successfully migrated your MySQL database to your managed PostgreSQL instance.

      Conclusion

      pgLoader is a flexible tool that can perform a database migration in a single command. With a few configuration tweaks, it can migrate an entire database from one physical machine to another using a secure SSL/TLS connection. Our hope is that by following this tutorial, you will have gained a clearer understanding of pgLoader's capabilities and potential use cases.

      After migrating your data over to PostgreSQL, you may find the following tutorials to be of interest:



      Source link