New Postgres Import Feature

Fresh Produce
1

Hey everyone,

We just released a new Postgres import feature that will be available with Flyctl v0.0.472.

Notable use cases:

  • Import your data from an external Postgres offering.
  • Migrate away from Stolon and onto our new Flex implementation.
  • Migrate to the latest major version of PG.
  • Seed a staging environment.
  • etc…

Example usage:

$ fly pg import $SOURCE_URI --app <target-pg-app>

? Choose a region to deploy the migration machine: Boston, Massachusetts (US) (bos)
? Select VM size: shared-cpu-1x - 256
Waiting for machine 217811106f0d89 to start...
Connecting to fdaa:0:2e26:a7b:ea:5375:c5b6:2... complete
[info] Running pre-checks
[info] Source connnection is healthy
[info] Target connnection is healthy
[info] Source PG version: 14.7 (Ubuntu 14.7-1.pgdg20.04+1)
[info] Target PG version: 15.2 (Debian 15.2-1.pgdg110+1)
[info] Migration pre-checks have completed without issues
[info] Starting migration
[info] Postgres migration has completed
Waiting for machine 217811106f0d89 to stop...
217811106f0d89 has been destroyed

Note: We currently only support single database imports.

For clarity, the source-uri should be in the following format:

postgres://<user>:<pass>@<hostname>:<port>/database

Additional options:

Usage:
  flyctl postgres import <source-uri> [flags]

Flags:
  -a, --app string       Application name
      --clean            Drop database objects prior to creating them.
  -c, --config string    Path to application configuration file
      --create           Begin by creating the database itself and reconnecting to it. If --clean is also specified, the script drops and recreates the target database before reconnecting to it. (default true)
      --data-only        Dump only the data, not the schema (data definitions).
  -h, --help             help for import
      --image string     Path to public image containing custom migration process
      --no-owner         Do not set ownership of objects to match the original database. Makes dump restorable by any user. (default true)
      --region string    Region to provision migration machine
      --vm-size string   the size of the VM

There are a few different knobs you can tweak with this feature and it’s important that you read through them carefully. If you’re intending to import data into a live production database, always make sure to test in a staging environment first. :slight_smile:

Need a custom import process?

If you need to define your own custom migration process, you are welcome to fork the following repo and do whatever you need to do:

The import process takes a custom image in case you need to do anything fancy. For issues, bug fixes, etc. please issue a PR against the repo.

18 Likes
2

Very cool, makes it so easy to setup a dev, test and live server. Thanks!

1 Like
3

I’m trying to use this…

Initially expected it would create the target app so it failed.

Then I created the empty app fly apps create my-app and it failed again because it’s not a v2 app.

Then I did fly apps create my-app --machines and it failed because:

Error The target app must be a Postgres app

So what should be the correct approach here?

Is the import feature expecting a fully working PG app?

From the first post it seems that import would allow me to configure and deploy the app.

4

@pier Sorry for the lack of clarity on that.

The first iteration of this feature requires you to target an existing Postgres app. So if you want to import some data into a fresh PG, you’ll need to provision a new app via fly pg create ..., then specify that app as the target.

2 Likes
5

Hi, Love the idea but its not working for me right now.
I am getting

[info] Starting import process... (This could take a while)
[error] failed to import database: exit status 2

I have followed the steps on Migrate a Postgres Database from Heroku · Fly Docs
Only thing I can think of is a minor version difference in postgres between heroku and fly?
Or the region difference?
Would be good to get some more helpful error messaging. Thanks

6

Hi. Can you make some improvements? I want to import Database from DigitalOcean. But for doing this, i need to set trust ip address. Can you add info About ip Address from which the import connection occurs.

7

Hi! This tool would be a great help!

[info] Starting import process... (This could take a while)
[error] failed to import database: exit status 2

I seem to be getting the same error as was mentioned above, but I wasn’t a couple of weeks ago. (I migrated a staging database initially, once I had the process verified, I came to do it on production, and now it’s not working). This makes me think I could be a version thing, so I’ve tried a few older and newest versions of flyctl and fly/postgres-importer image. Any ideas?

flyctl v0.2.106
postgres-importer 0.0.7

8

Hey @erk,

Anything interesting in the machine logs?

9

I didn’t get to see them before the ephemeral machine was destroyed. After an evening of trying everything, I managed to make it import by creating the database first with psql. I think this is something that happens when you attach a pg instance (with Fly), but i’m sure i didn’t do it in that order (attach before import) the first time.

10

Oh, as long as you have the machine id, you should be able to view the logs post-deletion. At least for a while. I’m glad you were able to figure it out though!