PostgreSQL Tip: Bulk Copying Data Between Tables

Friday, June 17, 2011.

Suppose you have two different PostgreSQL databases, db1 and db2. You want to populate db2.table2 with data from db1.table1. How?

Try this:

psql -c 'COPY table1 TO STDOUT' db1 | \
psql -c 'COPY table2 FROM STDIN' db2

Is there a more efficient way to do this if the two databases are hosted by the same server instance? Probably.

Then again, if the databases are on different servers, this works:

psql -c 'COPY table1 TO STDOUT' db1 | \
ssh host2 psql -c 'COPY table2 FROM STDIN' db2

Bonus: with pv(1), you can see how quickly the data is flowing:

psql -c 'COPY table1 TO STDOUT' db1 | pv | \
psql -c 'COPY table2 FROM STDIN' db2
Posted by Alan on Friday, June 17, 2011. (Discuss)

blog comments powered by Disqus
maelstrom

"After a little while I became possessed with the keenest curiosity about the whirl itself. I positively felt a wish to explore its depths, even at the sacrifice I was going to make; and my principal grief was that I should never be able to tell my old companions on shore about the mysteries I should see."

Illustration for Edgar Allan Poe's story "Descent into the Maelstrom" by Harry Clarke, published in 1919.