DEV Community

Joe Auty
Joe Auty

Posted on

2 2

The Easiest Way to Clone a PostgreSQL Database

If you use a managed database service like AWS RDS or Google Cloud SQL, it is super easy to create a snapshot and create a new instance based on this snapshot, but there is an even easier way to clone a database. At Redactics our use case was building automated workflows to test your database migrations against a copy of your production database (and, incidentally, this is free for use in case this is of interest to you), but there are likely dozens of additional use cases. For example, you may want to A/B test against a particular database or query optimization.

This approach leverages PostgreSQL's support for Linux pipes, and the fact that pg_dump output can be directed into pg_restore as input. This approach means that what would normally be two steps can be combined into one, and you don't need to set aside a bunch of disk space for this task.

pg_dump -h [DB_hostname] -U [username] -W -d [source_database] -v -Fc | pg_restore -h [DB_hostname] -U [username] -W -v -d [destination_database]
Enter fullscreen mode Exit fullscreen mode

Since you likely need for this to be run without interaction, i.e. entering the database password, you might need to create a .pgpass file or leverage environment variables for storing this info. This will allow you to remove the -h, -U, and -W arguments.

Heroku

Built for developers, by developers.

Whether you're building a simple prototype or a business-critical product, Heroku's fully-managed platform gives you the simplest path to delivering apps quickly — using the tools and languages you already love!

Learn More

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Dive into this insightful write-up, celebrated within the collaborative DEV Community. Developers at any stage are invited to contribute and elevate our shared skills.

A simple "thank you" can boost someone’s spirits—leave your kudos in the comments!

On DEV, exchanging ideas fuels progress and deepens our connections. If this post helped you, a brief note of thanks goes a long way.

Okay