Heroku PG:PULL后端解析、本地复现方法及AWS生产库克隆可行性咨询

1. What's the backend of Heroku pg:pull? #

Under the hood, Heroku's pg:pull is just a handy wrapper around two core PostgreSQL command-line tools: pg_dump and pg_restore.

Heroku handles the tedious parts—like fetching your production database's connection details (host, port, credentials) from its platform API—then runs pg_dump to create a compressed backup of the remote database, followed by pg_restore to load that backup into your local PostgreSQL instance. It saves you from having to manually build complex connection strings or memorize all the flags for the native tools.

2. How to replicate heroku pg:pull in your own environment, and can you clone an AWS-hosted database locally? #

Absolutely—you can replicate this workflow manually using the same PostgreSQL tools, and yes, AWS-hosted databases (like RDS PostgreSQL) can be cloned to your local machine as long as you have network access to them. Here's how:

Step 1: Prerequisites #

• Make sure you have PostgreSQL installed locally (this includes pg_dump and pg_restore—they come with the standard PostgreSQL distribution).
• Ensure you can reach the remote database:
  • For AWS RDS, add your local IP address to the RDS security group's inbound rules (allowing PostgreSQL traffic on port 5432).
  • Have the remote database credentials (username, password, host/endpoint, port, database name) ready.

Step 2: Replicate pg:pull manually #

a. Export the remote database backup

Run pg_dump to create a compressed backup of your production database. Replace the placeholders with your actual details:

pg_dump -h <REMOTE_HOST> -p <REMOTE_PORT> -U <REMOTE_USER> -d <REMOTE_DB_NAME> -F c -b -v -f ./prod_backup.dump

• -F c: Uses PostgreSQL's custom format (compressed, efficient for restores)
• -b: Includes large objects (like blobs) in the backup
• -v: Verbose output so you can track progress
• -f: Specifies where to save the backup file

You'll be prompted for the remote database password—enter it when asked.

b. Create a local target database

First, make an empty database locally to restore into. Replace <LOCAL_DB_NAME> with your desired name:

createdb -U <LOCAL_POSTGRES_USER> <LOCAL_DB_NAME>

If your local PostgreSQL uses the default postgres user, you can simplify this to createdb <LOCAL_DB_NAME>.

c. Restore the backup to your local database

Use pg_restore to load the backup into your local database:

pg_restore -h localhost -p 5432 -U <LOCAL_POSTGRES_USER> -d <LOCAL_DB_NAME> -v ./prod_backup.dump

• Add the --clean flag if you want to drop existing tables in the local database before restoring (useful for overwriting an existing DB).

Cloning an AWS-hosted database locally #

This follows the exact same manual steps above. For AWS RDS specifically, here are a few extra tips:

• If using IAM database authentication instead of a static password, generate a temporary auth token with the AWS CLI:

  aws rds generate-db-auth-token --hostname <RDS_ENDPOINT> --port 5432 --username <DB_USER>
  

  Use this token as the password when running pg_dump.
• If your RDS instance is in a private VPC, use a VPN or SSH tunnel to connect from your local machine (since private instances aren't publicly accessible directly).

内容的提问来源于stack exchange，提问作者S.ork