Import remote Postgres database

It took me a while to figure this one liner out. It’s so simple. And so powerful!

The --clean option is very important as it drops the db and tables before adding them again. That way you can run this command over and over again and it will just update when new data comes in from the source.

pg_dump --clean -d postgres://remote:5432/db_name | psql -d postgres://localhost:5432/db_name

The URIs I’m using here could be anything, I’m just using “remote” to keep it simple. In the real world you will also need to provide username and a password (hopefully). And the command would look a little bit more like this:

pg_dump --clean -d postgres://user:[email protected]:5432/db_name | psql -d postgres://localhost:5432/db_name

There’s probably a lot of drawbacks from this which I will discover later, but for now I am happy as a cow on a green field and the sun is shining on me: 🐄 ☀️. Let’s see for how long that will last…