Manually Migrating a Django Postgres DB Between Managed Providers (And Every Error I Hit Along the Way)

There’s a one-click import tool on Neon that would have handled this migration for me. I didn’t use it.

Not out of stubbornness — well, not entirely — but because I’ve found that letting a GUI handle infrastructure work means you understand exactly nothing when something breaks. And something always breaks. Doing it manually once means you’re never completely at the mercy of a button that might stop working.

This is the story of migrating my blog’s Postgres database from Aiven’s free tier to NeonDB’s free tier: why I moved, the cascade of errors I hit along the way, and what I learned from each one.

The Stack and the Problem

My blog runs on Django for the backend and Next.js for the frontend, with Postgres as the database. Images are handled separately through Cloudinary, so the database itself is almost entirely text — posts, metadata, user data, that sort of thing. Not a storage-heavy workload at all.

I started on Aiven’s free tier when I was in early development and testing. That was the right call. Aiven is straightforward to set up, the dashboard is clean, and for a project where you’re mostly running queries locally and kicking the tyres, it works fine. The free tier gives you a small managed Postgres instance without much friction.

The problem showed up when I started preparing to go live. During one of my Next.js build compilations, I hit a connection limit error. The build process opens multiple database connections in parallel, and the free instance ran out of capacity. Aiven’s free tier does include connection pooling via PgBouncer, but the ceiling is low, and I wasn’t going to get headroom without paying for an upgrade.

At that point I started looking at alternatives. Neon came up quickly, and the more I looked at it, the more it made sense — not just as a stopgap, but as a proper step up before launch.

Aiven vs Neon: A Practical Free Tier Comparison

Before getting into the migration itself, it’s worth explaining why Neon was the right move here. These are both managed Postgres providers with free tiers, but they’re built around different priorities.

Aiven Free Tier

• Single shared Postgres instance
• Connection pooling available but with tight limits
• Fixed region, no branching
• Good dashboard, solid monitoring
• Free tier is genuinely useful for early development and side projects with light traffic
• Storage is modest but workable for text-heavy databases

Neon Free Tier

• Serverless Postgres architecture — compute scales to zero when idle
• Built-in connection pooling via PgBouncer, with more generous limits
• Database branching: you can create lightweight copy-on-write branches of your database, which is useful for staging environments and testing migrations
• 0.5 GB storage on the free tier, which is plenty when your images live somewhere else (Cloudinary in my case)
• Active development, newer platform, strong DX focus

The trade-off worth knowing about: Neon’s serverless model means there can be a cold start latency when your database hasn’t been queried in a while and the compute spins back up. For a blog that might sit quiet overnight, you may occasionally notice the first request of the day is slower. For my use case that’s acceptable, but it’s worth knowing if you’re building something that needs consistent low-latency response at all times.

For my situation specifically — a text-heavy database, images handled externally via Cloudinary, and a blog about to go public — Neon’s free tier covers everything I need. Storage isn’t a concern. If the blog grows to the point where I need more compute or connections, buying a plan is a reasonable and expected next step, not a problem.

For pure early-stage testing, Aiven was fine. For going live with real traffic and a Next.js frontend that opens several connections during builds, Neon makes more sense.

The Plan: pg_dump and pg_restore

The manual migration approach is straightforward in theory:

1. Dump the database from Aiven into a file using pg_dump
2. Restore that file into Neon using psql or pg_restore

pg_dump exports a Postgres database into a SQL script or archive file. You can use plain SQL format (a .sql file you can read and pipe directly into psql) or the custom binary format (which requires pg_restore but is faster and more flexible for large databases). For a small text-heavy database, plain SQL format is perfectly fine.

The intended command was something like this:

pg_dump "postgres://user:password@host:port/dbname?sslmode=require" \
  --format=plain \
  --no-owner \
  --no-acl \
  -f backup.sql

The --no-owner and --no-acl flags strip out ownership and permission statements that would fail on a different provider where your usernames don’t match.

Simple enough. Except it didn’t work.

First Wall: The Version Mismatch

Running that command produced this:

pg_dump: error: server version: 16.13; pg_dump version: 14.17
pg_dump: error: aborting because of server version mismatch

The rule with pg_dump is strict: the client-side pg_dump binary must be the same version or newer than the Postgres server you’re dumping from. My local Windows installation was Postgres 14. My Aiven instance was running Postgres 16. That’s not allowed.

There are two ways to fix this: install a newer pg_dump standalone, or upgrade your local Postgres. Since I needed a properly maintained local installation anyway, I decided to upgrade everything — Aiven first, then Windows.

On the Aiven side, upgrading the managed instance is handled through their dashboard. I went to v17 directly rather than v16, since I was already doing the work and v17 was available. The managed upgrade on their end was uneventful.

The Windows upgrade was not.

Upgrading PostgreSQL on Windows (Unexpectedly Eventful)

On Windows, the assumption when upgrading already-installed software is that you run the installer and it handles everything — a mundane process. For Postgres, that’s only partially true. Each installer deploys its own independent version, leaving you to decide which ones to keep. Here’s what actually happens.

What the Installer Does

Installing a newer version of Postgres on Windows when you already have an older one running does not replace it. It installs alongside it, running on a different port (5433 by default, since 5432 is taken). You now have two Postgres services running simultaneously. For my case, the old version was Postgres 14 and the new version was Postgres 17 — I’ll refer to them as such throughout.

Editing pg_hba.conf

Before pg_upgrade can run, you need to allow it to connect to both instances. On both the old (v14) and new (v17) installs, open the pg_hba.conf file. You’ll find it in the data directory for each version:

C:\Program Files\PostgreSQL\14\data\pg_hba.conf
C:\Program Files\PostgreSQL\17\data\pg_hba.conf

Find the IPv4 and IPv6 local connection entries and change the method from scram-sha-256 (or md5) to trust:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

trust means no password is required for local connections. You can tighten this back up after the migration is done.

Dropping the adminpack Extension

pg_upgrade may refuse to proceed if the old cluster has the adminpack extension installed, as it can cause compatibility issues. Drop it before running the upgrade:

DROP EXTENSION IF EXISTS adminpack;

Connect to your v14 instance and run that. If it wasn’t installed, no harm done.

Running pg_upgrade

With both configurations edited, stop the new v17 service (it needs to be off for pg_upgrade to initialise it), then run:

pg_upgrade \
  -b "C:\Program Files\PostgreSQL\14\bin" \
  -B "C:\Program Files\PostgreSQL\17\bin" \
  -d "C:\Program Files\PostgreSQL\14\data" \
  -D "C:\Program Files\PostgreSQL\17\data" \
  -p 5432 \
  -P 5433

Where:

• -b / -B are the old and new bin directories
• -d / -D are the old and new data directories
• -p / -P are the old and new ports

Note: The paths above reflect the default installation directories on Windows. If you installed Postgres to a custom location, or your data directory was initialised elsewhere, replace both the bin and data paths with your actual directories. You can find your data directory by running SHOW data_directory; in psql against the relevant instance.

Updating Your System PATH

This is easy to miss and will cause confusing behaviour later. After the upgrade, your system PATH still points to the old version’s bin directory:

C:\Program Files\PostgreSQL\14\bin

Open Environment Variables (search for it in the Start menu), find the Path entry under System Variables, and update it to point to v17:

C:\Program Files\PostgreSQL\17\bin

If you skip this, running psql, pg_dump, or any other Postgres tool from the command line will silently use the old v14 binaries, and you’ll be back to version mismatch errors even though v17 is installed. Open a fresh terminal after making this change.

Handling the Port Conflict

After the upgrade, v17 picks up the data from v14 but listens on port 5433 by default (since 5432 is still held by the old v14 service). You have two options:

1. Remove the old v14 installation and reconfigure v17 to listen on 5432. This means editing postgresql.conf for v17 and changing port = 5433 to port = 5432.
2. Leave both running and update your application’s database connection string to use port 5433.

I went with option 1. Once the upgrade was verified, I uninstalled v14, updated postgresql.conf on v17 to use port 5432, and updated the DATABASES setting in Django’s settings.py accordingly.

The Dirty Cluster Error

While working through the upgrade, I hit this:

The source cluster was not shut down cleanly,
state reported as: "in production"
Failure, exiting.

pg_upgrade requires the old cluster to have been shut down cleanly. If the Postgres service was stopped abruptly — through Task Manager, a system restart, or a failed previous upgrade attempt — it leaves the data directory in a dirty state with a recovery flag set.

The fix is to start the old server properly and then stop it cleanly:

pg_ctl -D "C:\Program Files\PostgreSQL\14\data" start
pg_ctl -D "C:\Program Files\PostgreSQL\14\data" stop -m fast

The -m fast flag tells Postgres to do a clean checkpoint and shut down properly without waiting for all clients to disconnect. After this, re-run pg_upgrade and the dirty cluster error will be gone.

The SSL Error

After the local upgrade completed and I started running commands against the local v17 instance, I got this:

psql: error: connection to server at "localhost" (::1), port 5432 failed:
server does not support SSL, but SSL was required

This happens because managed Postgres providers like Aiven require SSL connections, and after a major upgrade the SSL settings may need to be explicitly configured. The local psql client was inheriting or defaulting to sslmode=require from a previously set environment variable or connection string.

The fix is to explicitly set the SSL mode for local connections:

set PGSSLMODE=disable

Or for connections to remote managed providers that require SSL:

set PGSSLMODE=require

On Windows Command Prompt, use set. In PowerShell, use $env:PGSSLMODE = "require". For the local instance where SSL isn’t configured, disable is the correct value. For the Aiven and Neon connections, require.

The Actual Migration

With local Postgres at v17, Aiven upgraded to v17, and the SSL mode sorted, the dump finally ran cleanly:

pg_dump "postgres://user:password@aiven-host:port/dbname?sslmode=require" \
  --format=plain \
  --no-owner \
  --no-acl \
  -f blog_backup.sql

Restoring into Neon:

psql "postgres://user:password@neon-host/dbname?sslmode=require" \
  -f blog_backup.sql

Neon requires SSL connections, so sslmode=require is mandatory in the connection string. Their connection string format is standard Postgres and available directly from the Neon dashboard.

After the restore, a few quick sanity checks:

# Connect to Neon and verify row counts on key tables
psql "postgres://user:password@neon-host/dbname?sslmode=require" \
  -c "SELECT COUNT(*) FROM blog_post;"

And from Django:

python manage.py check
python manage.py showmigrations

Everything came back clean.

Was It Worth Doing Manually?

The Neon import tool exists and probably would have worked fine. But the manual process surfaced things I genuinely needed to understand:

• I now know exactly what version my local tools are running and how to keep them in sync with remote servers
• I know what a dirty cluster is and how to recover from one
• I know how SSL mode behaves across local and managed connections
• I understand what pg_hba.conf does and when to touch it

None of that knowledge shows up if you click Import. That said, if you’re doing a straightforward migration between two providers with matching versions, the GUI tool is fine — use it. The manual approach pays off when you’re also doing version upgrades, or when you want to understand your database infrastructure properly before going live.

As for the connection limit problem that started all of this: after switching to Neon and pushing the Next.js build again, no errors. The build completed without hitting any ceiling.

Summary: Errors and Fixes

| Error | Cause | Fix | |||| | pg_dump version mismatch | Local pg_dump older than remote server | Upgrade local Postgres to match or exceed server version | | server does not support SSL | SSL mode mismatch after upgrade | Set PGSSLMODE env var appropriately | | source cluster not shut down cleanly | Dirty data directory from abrupt stop | Start old server, stop cleanly with pg_ctl stop -m fast | | Old binaries still running after upgrade | PATH not updated | Update system PATH to new version’s bin directory | | Port conflict after upgrade | Two versions running simultaneously | Remove old install and reconfigure port, or update app config |

Key Takeaways

• pg_dump version must be >= the server version. Check this before you start. It’s the first thing that will bite you.
• Upgrading Postgres on Windows is a multi-step process. pg_hba.conf edits, dropping adminpack, running pg_upgrade with explicit paths — none of this is handled automatically by the installer.
• Update your system PATH after upgrading. If you don’t, you’ll be running old binaries from the command line without realising it.
• A dirty cluster blocks pg_upgrade. Always shut down the old instance cleanly with pg_ctl stop -m fast before upgrading.
• SSL mode needs explicit configuration when connecting across local and managed environments after a major upgrade.
• Know your free tier limits before you go live. Aiven was the right choice for early testing. Neon is the right choice for a blog about to handle real traffic.