How to migrate from Heroku Postgres to managed PG on a VPS

Для migrating Heroku Postgres to a VPS with a managed configuration, the most effective approach is using the pg_dump and pg_restore combination for databases up to 100 GB, or logical replication to minimize downtime. This allows you to reduce monthly infrastructure costs by 5-8 times with comparable performance from NVMe drives and dedicated CPU cores.

Why you should migrate Heroku Postgres to a VPS right now?

Heroku has long been the gold standard for developer convenience. However, strict connection limits, lack of file system access, and the high cost of additional resources make heroku pg migration a logical step for a growing project. Moving to your own infrastructure or using a VPS with automated database management provides full control over the postgresql.conf configuration and allows you to implement extensions that are unavailable in Heroku's closed ecosystem.

Economic Efficiency and Performance

The cost of Heroku Postgres on Standard and Premium plans starts at $50 and $200 per month, respectively. For this price, you get limited resources. In comparison, a modern VPS based on processors with 3.4+ GHz frequencies and NVMe storage provides 2-3 times more IOPS (input/output operations per second), which is critical for high-load databases. A detailed breakdown of infrastructure migration can be found in the article how to move from Heroku to VPS in 2026: a step-by-step guide.

Removing Heroku's Limitations

Heroku imposes limits not only on data volume but also on the number of concurrent connections. In a managed configuration on a VPS, you can set up pgBouncer or Odyssey yourself, allowing you to handle thousands of connections without performance degradation. Furthermore, you aren't restricted in your choice of PostgreSQL version — you can use bleeding-edge features of Postgres 16 or 17 without waiting for Heroku to update its images.

Choosing a Configuration: heroku pg migration and System Requirements

Before starting a postgres migration vps, it is necessary to correctly select the server parameters. The database is the most resource-intensive component of the system. An error in choosing the disk subsystem or RAM capacity will cause queries that ran in milliseconds on Heroku to start "lagging."

Recommended VPS Specifications

• CPU: Minimum 2 cores for small databases, 4-8 cores for high-load systems. PostgreSQL actively uses multi-threading for background processes (Background Writer, Checkpointer).
• RAM: Memory capacity should be sufficient for shared_buffers to hold "hot" data (usually 25% of total RAM), plus a buffer for the operating system and file system cache.
• Disk: NVMe only. PostgreSQL is sensitive to WAL (Write Ahead Log) write latency. Sequential write speed should be at least 500 MB/s.

Parameter	Heroku Standard 0 ($50/mo)	VPS Managed-PG ($25/mo)	VPS Advantage
RAM	4 GB	8-12 GB	2-3x more
CPU	Shared (limited)	4 Dedicated vCPU	Predictable load
Disk	256 GB (Standard)	160 GB NVMe	Low latency (IOPS)
Connections	120	Unlimited (via pgBouncer)	Scalability

When choosing a server for read/write-intensive databases, consider the processor architecture. If you plan to use the database for analytics or ML tasks, check out the material Bare-metal vs VPS for ML inference on CPU to understand the difference in compute core performance.

Main postgres migration vps Methods: pg_dump vs pg_basebackup

There are three main ways to migrate data. The choice depends on the database size and the application's acceptable downtime.

Method 1: pg_dump and pg_restore (Logical Backup)

This is the standard and simplest method. It creates an SQL script or a data archive, which is then deployed on the new server. Pros: Simplicity, ability to change Postgres versions (e.g., from 13 to 16). Cons: Requires stopping writes to the database during export to ensure data consistency.

Method 2: Logical Replication

Allows transferring changes from Heroku in real-time. You set up the VPS as a Subscriber and Heroku as a Publisher. Pros: Minimal downtime (a few seconds for DNS switching). Cons: Requires configuring access permissions, which may be restricted on lower-tier Heroku plans.

Method 3: pg_basebackup and Physical Replication

This method is rarely applicable to Heroku because the service does not provide direct access to the file system or the streaming replication protocol at the superuser level. Therefore, in the context of managed pg vps, we focus on logical migration methods.

Step-by-Step Guide: Implementing managed pg vps via pg_dump

For a successful migration, you will need the heroku cli installed on your local machine or an intermediate server and SSH access to the new VPS.

Step 1: Preparing the Target Server

Install the required version of PostgreSQL on your VPS. We recommend using the official PGDG (PostgreSQL Global Development Group) repositories to get the latest security patches.

sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16

Edit /etc/postgresql/16/main/postgresql.conf to allow external connections if necessary and configure memory limits:

listen_addresses = '*'
shared_buffers = 2GB # For a server with 8GB RAM
work_mem = 64MB
maintenance_work_mem = 512MB

Step 2: Creating a Dump from Heroku

Use the -Fc (custom format) flag, which allows for multi-threaded restoration, significantly speeding up the heroku pg migration.

# Get the database URL
heroku config:get DATABASE_URL -a your-app-name

# Create the dump
pg_dump -Fc --no-acl --no-owner -d "postgres://user:pass@host:port/dbname" > heroku_db.dump

Step 3: Restoring Data on the VPS

Transfer the dump file to the VPS and perform the restoration. Using the -j flag (number of cores) will speed up the index creation process.

# Create the database on the new server
sudo -u postgres createdb my_new_db

# Restore the data
pg_restore -v -d my_new_db -j 4 heroku_db.dump

If you have previously used other cloud platforms, the process will be similar. For example, check out how to move from Render.com to VPS in 2026 to compare database and application migration approaches.

Setting Up Fault Tolerance with pgBackRest

On Heroku, backups work "out of the box." When moving to a managed pg vps, the responsibility for data safety falls on you. The pgBackRest tool is the gold standard for managing PostgreSQL backups.

Advantages of pgBackRest

• Parallel data copying and compression.
• Incremental and differential backups (save space).
• Support for S3-compatible storage for off-site backups.
• Point-in-Time Recovery (PITR) — the ability to roll back the database to any specific second in the past.

pgBackRest Configuration Example

The /etc/pgbackrest.conf file on your VPS should look something like this:

[main]
pg1-path=/var/lib/postgresql/16/main

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
process-max=4
log-level-file=info

[global:archive-push]
compress-level=3

To configure WAL log archiving, add to postgresql.conf:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

Post-Migration Performance Optimization

Once the migrate heroku postgres is complete, it is important to ensure the database is running optimally. Heroku automatically configures many parameters; on a VPS, you will have to do this manually.

Configuring Indexes and Statistics

Immediately after importing the data, run the ANALYZE command. This updates the query planner statistics, without which Postgres might choose inefficient execution plans (e.g., Sequential Scan instead of Index Scan).

sudo -u postgres psql -d my_new_db -c "ANALYZE VERBOSE;"

Monitoring and Logging

Install the pg_stat_statements extension to track the slowest queries. This is a "must-have" for any managed pg vps. Add to the configuration:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

If your application uses a Serverless or Edge architecture, as with Cloudflare, migrating the database to a VPS may require proxy configuration. See the guide how to move from Cloudflare Workers to VPS to understand networking nuances.

Comparison of Costs and Specifications

Let's look at real numbers. Heroku charges for convenience, but as the database grows (over 50 GB), the price becomes disproportionate to the resources provided.

Feature	Heroku Postgres Premium-2	Valebyte VPS High-Perf	Local Server (Bare Metal)
Monthly Payment	$350	$45	$120
RAM	8 GB	16 GB	64 GB
Disk Type	EBS (Network storage)	NVMe (Local)	NVMe RAID-1
Migration Downtime	0 (within Heroku)	~5-10 min (pg_dump)	~5-10 min

Using a VPS allows you not only to save money but also to scale vertically in one click. Unlike Heroku, where moving to a new plan can take significant time due to the need to copy data between instances, on modern cloud hosting, expanding a disk or adding RAM happens almost instantly.

Security and Network Access

One of Heroku's advantages is automatic database isolation. When setting up a managed pg vps, you must take care of security yourself.

1. Firewall (UFW/iptables): Close port 5432 for all IPs except your application server.
2. SSL/TLS: Be sure to set up certificates to encrypt traffic between the application and the database.
3. Role Model: Do not use the postgres user for application connections. Create a separate role with limited permissions.
4. Fail2Ban: Install brute-force protection for SSH and, if necessary, for database port monitoring.

# Example of creating a user with limited permissions
CREATE USER app_user WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE my_new_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Conclusions

Moving from Heroku Postgres to a VPS is the most effective way to scale your database while reducing costs by 70-80%. For stable operation, we recommend using a combination of PostgreSQL 16+, NVMe drives, and the pgBackRest tool for automated backups.